20 Python SQLite3モジュール
SQLiteは，軽量なデータベースエンジンであり，Pythonの標準ライブラリに含まれている．Pythonでは，sqlite3モジュールを使用してSQLiteデータベースを操作できる．
Pythonを使用する経験がない場合は，以下の手順でColabを使用してSQLiteデータベースを操作できる．
- Googleアカウントにログインする．
- Google Colabにアクセスする．
- 「+ New notebook」をクリックして新しいノートブックを作成する．
- ソースコードをセルにコピー＆ペーストする．
- 実行ボタンまたはShift + Enterキーを押してセルを実行する．
- 必要に応じて，「+ Code」をクリックして新しいセルを追加できる．
20.1 Cursor
20.1.1 データベースの作成
import sqlite3を使用してSQLiteモジュールをインポートする．
sqlite3.connect() を呼び出して，SQLiteデータベースを作成する．以下のコードは，example.dbというデータベースに接続する．
import sqlite3
con = sqlite3.connect('example.db')データベースが存在しない場合は，新しく作成される．ブラウザの左側のファイルアイコンをクリックすると，example.dbが作成されていることが確認できる．
20.1.2 cursorの作成
SQL文の実行や，結果を取得するために，con.cursor()が必要である．
cur = con.cursor()20.1.3 テーブルの作成
execute()メソッドを使用して，SQL文を実行する．
以下の例では，studentsというテーブルを作成する．
cur.execute('''
CREATE TABLE students (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL
)''')作成したテーブルを確認するために，sqlite_masterテーブルをクエリする．
res = cur.execute("SELECT name FROM sqlite_master")
res.fetchone()('students',)のような結果が返されれば，テーブルが正しく作成されている．
20.1.4 データの追加
execute()メソッドを使用して，データをテーブルに追加する．
以下の例では，studentsテーブルに3人の学生のデータを挿入する．
cur.execute('''
INSERT INTO students (id, name, age) VALUES
('s001', 'Alice', 20),
('s002', 'Bob', 22),
('s003', 'Charlie', 21)
''')con.commit()を呼び出して，変更をデータベースに保存する．
con.commit()res = cur.execute("SELECT * FROM students")
res.fetchall()[('s001', 'Alice', 20), ('s002', 'Bob', 22), ('s003', 'Charlie', 21)]のような結果が返されれば，データが正しく追加されている．
この結果は，Pythonのlistであり，一つのlistに複数のtupleが含まれている．各tupleは，テーブルの各行を表している．
20.1.5 listからのデータの追加
以下の例では，pythonのlistからデータを追加する．
data = [
('s004', 'David', 23),
('s005', 'Eve', 19)
]
cur.executemany('''
INSERT INTO students (id, name, age) VALUES (?, ?, ?)''', data)
con.commit()?はplaceholderで，executemany()メソッドはdataリストの各要素を順番に置き換える．
res = cur.execute("SELECT * FROM students")
res.fetchall()cur.execute()の結果をiterateすることで，各行を個別に処理することもできる．
for row in cur.execute("SELECT name, age FROM students ORDER BY age"):
print(row)20.1.6 close()メソッド
データベースの操作が完了したら，close()メソッドを使用して，cursorとデータベース接続を閉じる．
cur.close()
con.close()20.1.7 データベースに接続する
作成したデータベースに再度接続するには，同じようにsqlite3.connect()を使用する．
new_con = sqlite3.connect('example.db')
new_cur = new_con.cursor()
res = new_cur.execute("SELECT * FROM students ORDER BY age DESC")
student_id, name, age = res.fetchone()
print(f'The oldest student is {name} with age {age}.')
new_cur.close()
new_con.close()20.2 pandas
import pandas as pd
import sqlite3
# sample data
df = pd.DataFrame({
'id': ['s001', 's002', 's003'],
'name': ['Alice', 'Bob', 'Charlie'],
'age': [20, 22, 21]
})
# write to SQLite database
con = sqlite3.connect('pd_example.db')
df.to_sql('students', con, if_exists='replace', index=False)
# read from SQLite database
df_read = pd.read_sql_query('SELECT * FROM students', con)
print(df_read)
# another query
df_read = pd.read_sql_query('SELECT name, age FROM students WHERE age > 20', con)
print(df_read)
# close connection
con.close()20.2.1 pandas.DataFrame.to_sql()
- https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html
DataFrameをSQLiteデータベースに書き込む．
pandas.DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)
"""
parameters
---
name : str
書き込むテーブルの名前．
con : sqlite3.Connection
SQLiteデータベースへの接続オブジェクト．
"""20.2.2 pandas.read_sql_query()
- https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html
SQLクエリの結果をDataFrameに読み込む．
pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None)
"""
parameters
---
sql : str
SQLクエリ文．
con : str or sqlite3 connection
SQLiteデータベースへの接続オブジェクト．
"""