diadia

興味があることをやってみる。自分のメモを残しておきます。

sqlite3を使ってみる

postgresql sqlite3 python

使う経緯

apiやスクレイピングで取得したデータはcsvモジュールで管理をしてきた。この方法にはデメリットが有る。それはcsvファイルやpyファイルが量産されてしまうことだ。
そこでdjangoのアプリケーションを作り、リレーショナルなデータベースで管理することにした。
これは出力されたcsvファイルの管理やcsvを出力するpyファイルの管理を省くことが出来て改善された。
しかしながらまだ快適ではなかった。
なぜなら、データのインポートやエクスポートが面倒だし、その上djangoのサーバーを起動しなければいけないのは煩わしい。
djangoのdbであるsqlite3を直接いじれば更に楽に管理できるのでは？と思ったのがsqlite3を使うきっかけになった。

 

sqlite3でできるようになりたいこと

  • テーブルの作成
  • csvデータのインサート
  • csvファイルの出力
  • apiを混じえてデータのアップデート

 

この記事の内容一覧

  1. sqlite3モジュールのダウンロード、インストール
  2. テーブルの作成
  3. csvデータのインサート
  4. sqlite3に格納されたデータの表示
  5. データのアップデート
  6. executeメソッドの整理
  7. テーブルのカラムを参照する方法
  8. sqlite3の大文字、小文字の取り扱い
  9. sqlite3のGUI

sqlite3モジュールのダウンロード、インストール

参考文献：https://docs.python.org/ja/3/library/sqlite3.html

上記によるとsqlite3はpythonの標準ライブラリである。したがってダウンロード、インストールの手続きは不要である。sqlite3モジュールを使う際は以下のようにimportすれば良い。

import sqlite3

 

テーブルの作成

参考文献：https://docs.python.org/ja/3/library/sqlite3.html

import sqlite3
connection = sqlite3.connect(dbpath) # この場合つなぐ先はファイル
# 例えばconnectの引数として"mydb"とした場合でmydbがなければ、新たにmydbというファイルが作成される。
# 新たにdbのファイルを作る場合は、拡張子がなくても作ることができるが、他のモジュール利用のためにも.sqlite等の拡張子をつけるべき。


cursor = connection.cursor()

#create table
cursor.execute("CREATE TABLE mydb (url TEXT, part TEXT)")

テーブルの作成はexecuteメソッドを使う。executeメソッドの引数にはテーブル作成のSQL文をわたす。これがテーブル作成のイメージである。

 

csvデータのインサート

参考文献：https://docs.python.org/ja/3/library/sqlite3.html

import sqlite3
conn = sqlite3.connect("mydb.sqlite")

cur  = conn.cursor()
#insert 
cur.execute("INSERT INTO mydb VALUES (?, ?)", (a,b))

 

INSERTに関しては記法が２つあるようだ。どちらもpostgresqlと同じ書き方ではない。

個人的にqmarkスタイルが書き易いと思った。

SQL実行メソッドであるexecuteの記法については以下を見ると良い。

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

データ型としてはデフォルトで以下のものに限られている。

SQLite はネイティブで TEXT、INTEGER、REAL、BLOB および NULL のみをサポートしています。その他のタイプを使用したい場合はあなた自身で追加しなければなりません。detect_types パラメータおよび、register_converter() 関数でモジュールレベルで登録できるカスタム 変換関数 を使用することで簡単に追加できます。

データ型については以下に説明があった。 https://www.dbonline.jp/sqlite/type/index1.html

 

sqlite3に格納されたデータを表示するには？

INSERTしたデータをどうやって表示するか？

sqlite3はファイル形式のデータベースではあるけれども、それ自体を直接読み込むことはできない。データを表示するには、コンソールで表示する方法、csvファイルに出力しcsvファイルを読み込んで表示する方法がある。

データ表示は他のデータベースの操作と同じく、SELECTを用いて処理する。
例えばpostgresqlでは以下のように書いた。

SELECT * FROM mydb;

 

sqlite3ではSQL文をexecute()の引数とすればデータを参照できる。

import sqlite3
conn   = sqlite3.connect("mydb.sqlite")  # mydb.sqliteというファイルに接続する
cursor = conn.cursor()

cursor.execute("SELECT * FROM mydb") #mydb.sqliteの中のmydbテーブルを参照する。
# 全件取得 一件の場合はfetchone()を使う
res = cursor.fetchall()
for line in res:
    print(res)

 

これでコンソールからデータを表示できる。あとはcsvモジュールを使えばcsvファイルとしても利用できる。

 

UPDATEについて

postgresqlでは以下のような感じでUPDATEしていた。

UPDATE mydb SET url="dauduia" WHERE part="today"

sqlite3の場合ではこのSQL文を引数として利用すれば良い。しかし、sqlite3特有の書き方を用いる。

import sqlite3
conn = sqlite3.connect("mydb.sqlite")
cur  = conn.cursor()

cur.execute('UPDATE mydb SET url=? WHERE part=?',(a,b))
# urlにaの値が使われ、partはbの値が使われる。

executeメソッドの整理

まずSQL文を実行するメソッドがexecute()である。そしてexecute()の引数としてSQL文を書く。
しかしながらこのSQL文はpostgresqlと同じものではない。sqlite3特有の書き方である。 この特有の書き方は２種類ある。qmark記法、named記法である。qmarkではSQL文の入力値は?を便宜的に書く。そして実際の入力値は第二引数のタプルで渡す。
イメージでは以下のような感じだ。

cursor.execute("?を使ったSQL文",(実際の入力値))

executeメソッドで行えるのはSQL文なのでCREATE TABLE やSELECT, UPDATE INSERT である。

# CREATE 
cursor.execute("CREATE TABLE mydb (url TEXT, part1 TEXT, part2 TEXT)")

 

# INSERT
cursor.execute("INSERT INTO mydb VALUES (?, ?, ?)",("https://fdsahfufbsaui.com", "zxzxzx", None  ))
#postgresqlでは右のように書く。INSERT INTO mydb VALUES (a, b)

 

# UPDATE
cursor.execute("UPDATE mydb SET url=? WHERE part1=?",("urlの入力値", "part1の入力値"))

#複数値のUPDATEの場合
cursor.execute("UPDATE mydb SET url=?, author=? title=? WHERE part1=?", ("urlの入力値","authorの入力値","titleの入力値"))
#>postgresqlでは右のように書く。UPDATE mydb SET url="dauduia" WHERE part1="today"

INSERTやUPDATEは実際の入力値を伴うものだからSQL文の中に？を記述することになる。一方CREATE TABLEやSELECT文はデータベースに入力する値がないためSQL文そのままで書くことになる。

カラムを追加する方法

postgresqlでは以下のように書く。

ALTER TABLE mydb ADD COLUMNS column_a CHAR

sqlite3は以下のように書く。

cur.execute("ALTER TABLE mydb ADD COLUMN column_a TEXT")

executeのメモ

例えばテーブルのあるカラムのデータ型をREALとして定義する。そしてフィールドとして-1（INTEGER）をUPDATEで渡したらどうなるか？
結果はエラーが出ることなくexecute()メソッドが実行され、結果が-1.0としてデータが格納された。

 

テーブルのカラムを参照する方法

参考文献：https://docs.python.org/ja/3/library/sqlite3.html#sqlite3.Cursor.description

cursorオブジェクトにはdescripyionアトリビュートがある。これを使うとテーブルのカラムを参照することができる。

 

descriptionの使用例：
import sqlite3

conn = sqlite3.connect("mydb.sqlite")
cur  = conn.cursor()
cur.execute("SELECT * FROM mydb_jp") #クエリを発行することがdescriptionを使う前提
res = cur.fetchone()
for line in res:
    print(line)
    
print(cur.description)

この状態だと各タプル内の無駄なもの(None)も表示されてしまう。リスト型データとしてcolsを出力する。 

カラムをリスト型データ("cols")に格納
import sqlite3

conn = sqlite3.connect("mydb.sqlite")
cur  = conn.cursor()
cur.execute("SELECT * FROM mydb_jp") #クエリを発行することがdescriptionを使う前提
res = cur.fetchone()
cols = list()
for col in cur.description:
    cols.append(col[0])

print(cols)

 

DEFAULT値の設定について

add columnsでもcreate tableでもColum名 データ型　DEFAULT　デフォルト値の形式にすれば設定できる。

参考:https://www.dbonline.jp/sqlite/table/index12.html

sqlite3の大文字、小文字の取り扱いについて

sqlite3の場合大文字、小文字の取り扱いに区別がないことが判明した。
例えばimageUrl1,imageUrl2というカラムを備えたテーブルを作りデータを格納する。これをSELECT文で参照するときIMAGEURL1でもimageURL2でも参照することができた。UPDATEにおいても文字の大小は区別関係なくSQL文を実行できた。

 

sqlite3のGUIを導入すると便利

参照文献:https://intellectual-curiosity.tokyo/2018/10/15/sqlite%E3%81%AEgui%E3%83%84%E3%83%BC%E3%83%AB-db-browser-for-sqlite/
http://sqlitebrowser.org/dl/
上記リンク先からダウンロードする。直感的なのでテーブルの中身やテーブルのカラム構成など快適に確認できる。