PythonでSQLite3を扱うときのメモ

Pythonで日付、時間を操作する際のメモ。
すーぐ忘れるので。
以下、コードスニペットのように関数を書いて行くが、以下の通りimportしてあることが前提。

import sqlite3

SQLite3基本のキ

接続してハンドラ、カーソルの初期化等。
クラスの中での処理を想定するので頭にself.がついてます。

sqlfile = ":memory:"  # こうするとメモリ上に作成

# 接続し、コネクタハンドラを記憶
self.con = sqlite3.connect(sqlfile)
# カーソルも記憶
self.cur = self.con.cursor()

# コミット方法。データベースを更新したら忘れずに
self.con.commit()

すでにテーブルがあれば消す

テーブル名決め打ちにしているとテーブル名がかぶる。
そこで同名のテーブルがあれば問答無用で消す。

self.cur.execute("DROP TABLE IF EXISTS tablename")
self.con.commit()

CREATE文の部品

リストに入れた要素でカラムを作る。
細かいことはいい、ぜんぶtextで突っ込め、というときに便利

culmn_list = ['A', 'B', 'C', 'D', 'E', ]
_db_col = list(map(lambda s: s+" text", culmn_list))
_db_col = ",".join(_db_col)
# 以下のようになる
# A text,B text,C text,D text,E text

# これを以下のようにする
# "CREATE TABLE dbtable(" + _db_col + ");"

リストをSQL文で使えるよう、便利な文字列を作る

['A', 'B', 'C', ]というリストから、以下の２つの文字列を作る。
あとでINSERTするとき想定。

  • 'A', 'B', 'C'
  • ?, ?, ?
def list_to_quoted_strings(_vallist):
    return_str = ''
    vals_str = ''
    for i in range(len(_vallist)):
        return_str += "'" + _vallist[i] + "',"
        vals_str += "?,"
    # 最後の"',"を消す
    return return_str[:-2], vals_str[:-2]

INSERTのとき

上記メモを踏まえてINSERT
vals_strは上記メモ参照
_cols_dataには入力データのリストが入る。

_insert_sql = 'INSERT INTO table VALUES(' + vals_str + ');')
self.cur.execute(_insert_sql, _cols_data)

SELECTの結果をyield

何も難しいことない。

def tiny_select_yielder(self, _select_sql):
    self.cur.execute(_select_sql)
    for record in self.cur:
        yield record

SQL文の評価される順番

いつも混乱する。

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP(LIMIT)

たとえば以下のようなSQL文の場合;

SELECT A, COUNT(B) AS C
FROM (
    SELECT DISTINCT A, B FROM table
    WHERE A<>"N/A"
)
GROUP BY A
HAVING C > 1
  1. 以下の結果から（外側のFROM）
    1. tableから（FROM）
    2. AがN/Aではないレコードの（WHERE）
    3. A, Bを抜き出し（SELECT）
    4. A, Bのペアが一意なものをまとめる（DISTINCT）
  2. Aでまとめ（GROUP BY）
  3. そのうちBの出現数が1より大きいもの（HAVING）の
  4. AとB出現回数を抜き出す

という順番になる
注意すべきはASでの別名定義はSELECTが評価されて初めて参照できるようになること。
WHERE, GROUP BY等々、SELECTの前に評価される命令では別名を扱えない。

SQL結果を一個だけ抜き出す

よくわからないけど、最大値とか行数とか、返ってくる結果がひとつだけと分かっているものに使っている。

self.cur.execute('SELECT COUNT(*) from table')
return(self.cur.fetchone())
self.cur.execute('SELECT max(columnA) from table')
return(self.cur.fetchone())

Last Update: 2021-12-28