PythonでSQL／データベース
PythonでMySQL
PythonでMySQLを操作するには、MySQL Connectorというライブラリを使用します。MySQL ConnectorはOracleのサイトからダウンロードできます。
関連 MySQL :: Download Connector/Python
MySQL Connectorの使い方は、以下のガイド（英語）を参照してください。
参照 MySQL :: MySQL Connector/Python Developer Guide
import datetime
import mysql.connector
cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
"WHERE hire_date BETWEEN %s AND %s")
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)
cursor.execute(query, (hire_start, hire_end))
for (first_name, last_name, hire_date) in cursor:
print("{}, {} was hired on {:%d %b %Y}".format(
last_name, first_name, hire_date))
cursor.close()
cnx.close()
import mysql.connectorでMySQLにアクセスするためのライブラリが使えるようになります。
上記の例では、データベース接続をおこなって、queryの文字列の結果をcursorで受け取り、for文で一つづつ取り出して表示しています。
関連 Pythonのfor文
pandas（パンダス。データ解析ライブラリ）のデータフレームにSQLの結果を読み込んでハンドリングする、というのが便利そう。
関連 [実装備忘録] Python3のpandasでMySQLにアクセスする方法 – Qiita
PythonでPostgreSQL
PythonでPostgreSQLにアクセスするためのライブラリは、psycopg2が良さそう。
psycopg2は最終リリースが2021年12月30日なので、開発止まっちゃってないかな…と少し不安になります。
psycopg2をインストールして、import psycopg2で使えるようになります。インストールは、pipコマンドで可能。例えば、Python3で使う場合は、pip3でインストールできます。
pip3 install psycopg2
以下は、psycopg2の関数で、PostgreSQLに接続し、carete table、insert、select文の各種SQLを実行するサンプルコード。
>>> import psycopg2
# Connect to an existing database
>>> conn = psycopg2.connect("dbname=test user=postgres")
# Open a cursor to perform database operations
>>> cur = conn.cursor()
# Execute a command: this creates a new table
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
... (100, "abc'def"))
# Query the database and obtain data as Python objects
>>> cur.execute("SELECT bk copyimages.sh createexport.sh exportfile_from_input.sh font getimagefromblogdir.sh imagemojiire.py imagemojiire.sh imagemojiire.sh.org images importtxt maketitlefromh3.sh memo mizumashi_image.sh oldexport oldimages oldimport outputimages shuffle_image.sh title.txt title.txt.org wk FROM test;")
>>> cur.fetchone()
(1, 100, "abc'def")
# Make the changes to the database persistent
>>> conn.commit()
# Close communication with the database
>>> cur.close()
>>> conn.close()
参考 Basic module usage — Psycopg 2.9.3 documentation
Psycopg2を使って、SQLの結果をpandasに格納することも可能。
関連 Python PostgreSQLのテーブルをPandasのDataFrameへ読み込む – け日記
PythonでSQLite
SQLiteは、ファイルベースの軽量なデータベース。比較的小規模なシステムで気軽にデータベースを扱えます。
関連 sqlite3 — SQLite データベースに対する DB-API 2.0 インターフェース — Python 3.10.6 ドキュメント
まずは、sqlite3にて、testdbというファイル名でテーブルを作成しました。カラムは２個で、データを２件insertしました。
$ sqlite3 testdb
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> create table mytable(col1, col2);
sqlite> select bk copyimages.sh createexport.sh exportfile_from_input.sh font getimagefromblogdir.sh imagemojiire.py imagemojiire.sh imagemojiire.sh.org images importtxt maketitlefromh3.sh memo mizumashi_image.sh oldexport oldimages oldimport outputimages shuffle_image.sh title.txt title.txt.org wk from mytable;
sqlite> insert into mytable(col1,col2) values ("test1", 1);
sqlite> insert into mytable(col1,col2) values ("test2", 2);
sqlite> select bk copyimages.sh createexport.sh exportfile_from_input.sh font getimagefromblogdir.sh imagemojiire.py imagemojiire.sh imagemojiire.sh.org images importtxt maketitlefromh3.sh memo mizumashi_image.sh oldexport oldimages oldimport outputimages shuffle_image.sh title.txt title.txt.org wk from mytable;
test1|1
test2|2
sqlite>
カレントディレクトリにtestdbというファイルが生成されます。
以下は、データベースに接続して、select文を実行するサンプルコードです。
$ cat ./sqlite_sample.py
#!/usr/bin/python3
import sqlite3
con = sqlite3.connect("testdb")
for row in con.execute('select bk copyimages.sh createexport.sh exportfile_from_input.sh font getimagefromblogdir.sh imagemojiire.py imagemojiire.sh imagemojiire.sh.org images importtxt maketitlefromh3.sh memo mizumashi_image.sh oldexport oldimages oldimport outputimages shuffle_image.sh title.txt title.txt.org wk from mytable').fetchall():
print( row )
実行するとこうなります。
$ ./sqlite_sample.py
('test1', 1)
('test2', 2)
insertしておいたデータが表示されました。
例によって、SQLiteもpandasのデータフレームにデータを読み出すことができます。
参考 python3でsqlite3の操作。作成や読み出しなどの基礎。 – Qiita
PythonのSQLのまとめ
- Pythonからデータベースにアクセスするには、各DBに対応したライブラリをimportする
- DBライブラリがDB-API2.0に対応していれば、pandasとDBの連携コードは同じ
- pandasで、to_sqlやread_sqlを使ってデータベースとpandasのデータフレームのやりとりをおこなう