前のページでダウンロードした db.sqlite データベースには staffs テーブルが登録されていました.ここでは,同じデータベースに department テーブルを作成して,データを登録してみます.
まず sqlite3 ライブラリをインポートします.
import sqlite3
次に,db.sqlite データベースに接続して,テーブルを作成します.もしも db.sqlite ファイルが存在しない場合は新規に作成されます.
conn = sqlite3.connect('db.sqlite')
curs = conn.cursor()
curs.execute("""CREATE TABLE departments (
id INT NOT NULL,
department VARCHAR(32),
PRIMARY KEY(id)
);""")
<sqlite3.Cursor at 0x1060e9c70>
一旦,データベースへの接続を終了します.
curs.close()
conn.close()
次に,データを投入してみます.データを投入した時に自動的にコミット(永続化)が行われるように,isolation_level
を None にしておきます.
conn = sqlite3.connect('db.sqlite')
conn.isolation_level = None # 自動コミットを有効にする
curs = conn.cursor()
最初のデータを投入します.
curs.execute('INSERT INTO departments VALUES(1, "人事部")')
<sqlite3.Cursor at 0x1060e9e30>
投入したデータを検索して確認します.
curs.execute("SELECT * FROM departments;")
rows = curs.fetchall()
print(rows)
[(1, '人事部')]
プレースホルダーを使うと,SQL インジェクションと言われる不正な処理を防止したり,クォートの処理を行ったりできるので,より安全になるでしょう.
sql = "INSERT INTO departments VALUES(?, ?);"
curs.execute(sql, (2, "製造部"))
<sqlite3.Cursor at 0x1060e9e30>
複数のデータを一気に投入することもできます.
data = [
(3, "営業部"), (4, "研究開発部")
]
sql = "INSERT INTO departments (id, department) VALUES (?, ?);"
curs.executemany(sql, data)
<sqlite3.Cursor at 0x1060e9e30>
全てのデータを表示します.
curs.execute("SELECT * FROM departments;")
rows = curs.fetchall()
print(rows)
[(1, '人事部'), (2, '製造部'), (3, '営業部'), (4, '研究開発部')]
最後にデータベースへの接続を切断します.
curs.close()
conn.close()
なお,isolation_level
を None に明示的に指定しなければ自動コミットが有効になりません.この場合,データを投入してもコミットするまではデータベースへの変更が反映されません.
conn = sqlite3.connect('db.sqlite')
# conn.isolation_level = None # この行をコメントアウトする
curs = conn.cursor()
sql = "INSERT INTO departments (id, department) VALUES (?, ?);"
curs.execute(sql, (5, "総務部"))
<sqlite3.Cursor at 0x105fe91f0>
投入後にコマンドプロンプト(mac ではターミナル)で db.sqlite に接続して SELECT 文を発行すると,研究開発部の登録が反映されていないことが確認できます.
% sqlite3 db.sqlite ⏎ SQLite version 3.37.0 2021-12-09 01:34:53 Enter ".help" for usage hints. sqlite> select * from departments; ⏎ 1|人事部 2|製造部 3|営業部 4|研究開発部 sqlite> .exit ⏎ %
コミットすると変更が反映されます.
conn.commit()
反映されていることが確認できました.
% sqlite3 db.sqlite ⏎ SQLite version 3.37.0 2021-12-09 01:34:53 Enter ".help" for usage hints. sqlite> select * from departments; ⏎ 1|人事部 2|製造部 3|営業部 4|研究開発部 5|総務部 sqlite> .exit ⏎ %
最後にデータベースへの接続を切断します.
curs.close()
conn.close()
またプレースホルダーを使うと,データベースに登録したい文字列にクォートやダブルクォートがあっても問題なく処理してくれます.
conn = sqlite3.connect('db.sqlite')
conn.isolation_level = None # 自動コミットを有効にする
curs = conn.cursor()
sql = "INSERT INTO departments (id, department) VALUES (?, ?);"
curs.execute(sql, (6, "情報\"システム\"部"))
curs.execute("SELECT * FROM departments;")
rows = curs.fetchall()
print(rows)
curs.close()
conn.close()
[(1, '人事部'), (2, '製造部'), (3, '営業部'), (4, '研究開発部'), (5, '総務部'), (6, '情報"システム"部')]
プレースホルダーを使わずにダブルクォートが含まれる文字列を登録しようとするとエラーとなってしまいます.
conn = sqlite3.connect('db.sqlite')
conn.isolation_level = None # 自動コミットを有効にする
curs = conn.cursor()
curs.execute('INSERT INTO departments (id, department) VALUES(7, "経\"理\"部");')
curs.execute("SELECT * FROM departments;")
rows = curs.fetchall()
print(rows)
curs.close()
conn.close()
--------------------------------------------------------------------------- OperationalError Traceback (most recent call last) Input In [30], in <cell line: 4>() 2 conn.isolation_level = None # 自動コミットを有効にする 3 curs = conn.cursor() ----> 4 curs.execute('INSERT INTO departments (id, department) VALUES(7, "経\"理\"部");') 5 curs.execute("SELECT * FROM departments;") 6 rows = curs.fetchall() OperationalError: near "理": syntax error