PySQLite

PySQLite

• SQLite DBに対する標準化された Python DBI API 2.0 準拠インターフェースを提供
• Python標準ライブラリ（2.5以降）
  https://docs.python.org/ja/3/library/sqlite3.html
• pysqlite
  https://pypi.org/project/pysqlite/

  pip install pysqlite

SQLite

• https://www.sqlite.org/

  • SQL DBエンジンを実装したC言語ライブラリ
  • サーバーレス
    • アプリケーションはコネクターを通じてDBファイルを直接読み書き
  • ACID
    • システムクラッシュや電源が落ちた際にもトランザクションのACIDを保つ
  • ライセンス：パブリックドメイン

• SQLite Tutorial

  • https://www.sqlitetutorial.net/

データベース接続・connectionオブジェクトの生成

• https://docs.python.org/ja/3/library/sqlite3.html#module-functions

  import sqlite3
  dbpath = 'sample.db'
  conn = sqlite3.connect(dbpath)

• DBファイルがない場合は新規に作成
• sqlite3.connect(':memory:')とすることでインメモリに作成することも可能
• autocommitなどの引数を指定（デフォルト値のsqlite3.LEGACY_TRANSACTION_CONTROLはオートコミット）

カーソルオブジェクトの生成

• https://docs.python.org/ja/3/library/sqlite3.html#sqlite3.Cursor
• SQL文の実行やクエリから結果を取得するために使用

  cur = conn.cursor()

• description属性
  • 最後のクエリ結果に対する列(column)名を提供する読み取り専用属性
    cur.description
  • Python DB API との互換性を維持するために、戻り値は各列ごとに 7 項目のタプルで構成
  • 先頭の項目が列名、残りの6項目は None

SQL文の実行

• https://docs.python.org/ja/3/library/sqlite3.html#sqlite3.Cursor.execute

  id = 42
  query = "SELECT * FROM <table> WHRE id = ?;"
  cur.execute(query, (id,))

• Pythonの変数はプレースホルダを使用すること
  • 文字列としての操作（f-stringsなど）はSQLインジェクションに対して脆弱
    https://docs.python.org/ja/3/library/sqlite3.html#sqlite3-placeholders

結果の取得

• 結果が複数行ある場合

  rows = cur.fetchall()

  • 戻り値はタプルのリスト

• 結果が複数行で、受け取る行数を指定したい場合

  num_rows = 5
  rows = cur.fetchmany(num_rows)

  • 戻り値はタプル

• 結果が１行の場合

  row = cur.fetchone()

  • 戻り値はタプル

• fetchした時点でコネクションオブジェクトは閉じて問題ない

  conn.close()

コンテキストマネージャー

dbpath = 'sample.db'
query = "SELECT * FROM <table> LIMIT 3;"
with sqlite3.connect(dbpath) as conn:
  cur.execute(query)
  rows = cur.fetchall():
  for row in rows:
    print(row)

try-except

import sqlite3

sql = 'UPDATE <table> SET col1 = ?, col2 = ? WHERE col3 = ?'

try:
    with sqlite3.connect('<sample>.db') as conn:
        cursor = conn.cursor()
        cursor.execute(sql, (val1,val2,val3) )
        conn.commit()
except sqlite3.Error as e:
    print(e)

pandas.read_sql_query

https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html
カラム名を付きでクエリ結果を受け取る

import pandas as pd
import sqlite3

dbpath = sample.db'
query = "SELECT * FROM <table> LIMIT 3;"

with sqlite3.connect(dbpath) as conn:
    df = pd.read_sql_query(query, conn)

DB構造の抽出

• SQLiteのバージョン　＊sqlite3（PySQLite）のバージョンではない sqlite3.version

  query = "select sqlite_version();"

• スキーマ

  query = "SELECT * FROM sqlite_schema"
  query = "SELECT * FROM sqlite_master;" # Ver 3.33.0以降はこちらも可

  query = "SELECT * FROM sqlite_master;"
  cur.execute(query)
  rows = cur.fetchall()
  for row in rows:
  print(row)
  # 戻り値のカラム名
  cols = cur.description
  print([col[0] for col in cols])