Windows環境のPythonからSQLiteを操作する(その2)

前回に引き続きSQLiteの話です。Windows環境のPythonからSQLiteを操作します。

前回はデータベースの作成、テーブルの作成・一覧取得・削除を行いました。

Windows環境のPythonからSQLiteを操作する

今回は、既存のテーブルに対してレコード(データ)を操作します。

テーブルからレコードを取得する

前回の内容を踏まえて、すでに”hoge.db”というデータベースに、”users”というテーブルがあるものとします。テーブルのレコードをすべて読み込むプログラムは下記のとおりです。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)

# テーブルからレコードを取得する
sql = "SELECT id, name, age FROM users"
cursor = conn.execute(sql)
for row in cursor:
    print("id:", row[0], ", name:", row[1], ", age:", row[2])

# データベースを閉じる
conn.close()

ターミナルにはすべてのレコードが表示されます。

また、下記のようなプログラムでもレコードの一覧を取得できます。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)
# row_factoryにsqlite3.Rowを設定
conn.row_factory = sqlite3.Row 
 
# カーソルオブジェクトを取得する
cur = conn.cursor()

sql = 'select * from users'
cur.execute(sql)
for row in cur:
    # カラム名でアクセスすることができる。
    print(row['id'],row['name'],row['age'])
  
# カーソルオブジェクトを閉じる
cur.close()

# データベースを閉じる
conn.close()

テーブルにレコードを追加する

テーブルにレコードを追加するプログラムは下記のとおりです。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)

# sqliteを操作するカーソルオブジェクトを作成
cur = conn.cursor()

# レコードを追加する
# すでに"users"というテーブルがあるものとする
sql = "INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25)"
cur.execute(sql)

# レコードを取得する
sql = "SELECT id, name, age FROM users"
cur.execute(sql)
for row in cur:
    print("id:", row[0], ", name:", row[1], ", age:", row[2])

# データベースへコミット。これで変更が反映される。
conn.commit()

# カーソルオブジェクトを閉じる
cur.close()

# データベースを閉じる
conn.close()

先ほどのテーブルのレコード一覧を取得するスクリプトを組み込んでいますので、追加したレコードがターミナルに表示されます。

テーブルにレコードを複数追加する

テーブルに複数のレコードを追加する場合はどうすればいいでしょうか。以下のようにレコードの数だけ追加操作を繰り返してもよいですが、追加したいレコードの数が増えると可読性が低下します。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)

# データを追加する
# すでに"users"というテーブルがあるものとする
sql = "INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25)"
cur.execute(sql)
sql = "INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30)"
cur.execute(sql)
sql = "INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 35)"
cur.execute(sql)

# データを取得する
sql = "SELECT id, name, age FROM users"
cur.execute(sql)
for row in cur:
    print("id:", row[0], ", name:", row[1], ", age:", row[2])

# データベースへコミット。これで変更が反映される。
conn.commit()

# カーソルオブジェクトを閉じる
cur.close()

# データベースを閉じる
conn.close()

“executemany”メソッドでテーブルにレコードを複数追加する

executemanyメソッドを使うことで、複数のレコードを一括して挿入することができます。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)

# 複数のレコードを一括して挿入するには、executemany()メソッドにinsert文を渡します。
sql = 'insert into users (id, name, age) values (?,?,?)'
data = [(1, 'Alice', 25), 
        (2, 'Bob', 30), 
        (3, 'Charlie', 35)]
cur.executemany(sql, data)

# レコードを取得する
sql = "SELECT id, name, age FROM users"
cur.execute(sql)
for row in cur:
    print("id:", row[0], ", name:", row[1], ", age:", row[2])

# データベースへコミット。これで変更が反映される。
conn.commit()

# カーソルオブジェクトを閉じる
cur.close()

# データベースを閉じる
conn.close()

テーブルからレコードを削除する

テーブルからレコードを削除する場合は、キーで指定します。今回は主キーである”id”の値でレコードを指定します。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)

# sqliteを操作するカーソルオブジェクトを作成
cur = conn.cursor()

# レコードの削除
# "id"が"1"のレコードを削除
sql = "DELETE from users where id = "1""
cur.execute(sql)

# コミット
db.commit()

# カーソルオブジェクトを閉じる
cur.close()

# データベースを閉じる
conn.close()

テーブルから全てのレコードを削除する

以下のようにキーを指定しなかった場合、テーブルから全てのレコードが削除されます。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)

# sqliteを操作するカーソルオブジェクトを作成
cur = conn.cursor()

# レコードの削除
# 全てのレコードを削除する
sql = "DELETE from users"
cur.execute(sql)

# コミット
db.commit()

# カーソルオブジェクトを閉じる
cur.close()

# データベースを閉じる
conn.close()

“TRUNCATE”メソッドでテーブルから全てのレコードを削除する

以下のようにTRUNCATEメソッドを使うことで、DELETEよりも高速に削除することができます。

import sqlite3

# SQLiteデータベースに接続する(ない場合は作られる)
dbname = 'C:/SQLite/hoge.db'
conn = sqlite3.connect(dbname)

# sqliteを操作するカーソルオブジェクトを作成
cur = conn.cursor()

# レコードの削除
# 全てのレコードを削除する
sql = "TRUNCATE table users"
cur.execute(sql)

# コミット
db.commit()

# カーソルオブジェクトを閉じる
cur.close()

# データベースを閉じる
conn.close()