【python】データベースの入門としてSQLiteを使ってみた
今まではスクレイピングしたデータをｃｓｖにして書き込んでいましたが、データベースもいいですよね。
というか調べるとデータベースのほうがいろいろ便利そうです。
そこで今回はデータベースの入門編ということでSQLiteを使ってみたので、自分なりのメモを残しておこうかと思います。
理解できれば簡単なんでしょうけど最初は難しいと思うので僕なりの解釈でかみ砕いて書きます。
間違ってたらごめんな。
※2019/2/22 応用編も書きました。
www.htmllifehack.xyz
SQLiteでSQL入門
コマンドラインで使うならMySQLとかになるんでしょうけどpython内で手軽に使いたいというときはSQLiteです。
標準ライブラリなのでpipしなくてもすぐに使えます。
環境はwindows10、python3、jupyternotebook、SQLite3です。
import sqlite3
データベースにアクセス、あるいは新規作成
データベースはExcelでいうブックみたいなものです。
ブック名.dbで作ります。
con = sqlite3.connect('sample.db')
sample.dbがすでに存在すればアクセスします。
存在しなければ新規で作成されます。
アクセスするのでconnectってのはわかりやすいですね。
カーソルオブジェクトを作成
sqlにはカーソルという概念が存在します。
Excelでいう行のことと僕は考えています。
1行目、2行目というように下がっていくイメージ。
cursor = con.cursor()
カーソルオブジェクトを作成しました。
基本的にカーソルを使って操作していきます。
テーブルを作成
テーブルはExcelでいうとsheetか表ですかね。
cursor.execute("CREATE TABLE data_set(id, name, date)");
テーブルの操作はexecuteメソッドを使います。
create table テーブル名（どんなデータを入れるか）という形で宣言します。
この場合data_setという名前のテーブルを作成し、id, name, date の3つの要素をこれから追加するよって意味です。
最後に；(セミコロン)が付いていますがなくても作成できました。
セミコロンありだとoutにはなにも表示されず、セミコロンなしだとこうなります。
out[1] : <sqlite3.Cursor at 0x21fd3085260>
メモリ番地なのかなんなのかわかる人いたら教えてください。
テーブルが存在していた場合は一旦削除して新規作成する
例えばdata_setというテーブルにもう一列bloodという要素を追加したい場合、
in[1] : cursor.execute("CREATE TABLE data_set(id, name, date, blood)"); out[1] : OperationalError : table data_set already exists
のようにエラーがでます。
こういうときはexecutescriptとexistsを使います。
executescriptは一度に複数のSQL文を命令するメソッドです。
cursor.executescript(""" DROP TABLE IF EXISTS data_set; CREATE TABLE data_set(id , name , date ) """)
複数の命令を書くときはトリプルクォーテーションで囲みます。
2行目の意味は、もしもdata_setというテーブルが存在する場合そのテーブルを削除します。
ここも最後にセミコロンを付けています。
次の3行目でテーブルをクリエイトしています。
テーブルにデータを追加する
1行ごとのデータのことをレコードと呼びます。
レコードを追加する場合はinsert文を使います。
cursor.execute("INSERT INTO data_set VALUES(1, 'saito', 19980810)")
ちなみに上記の方法だとSQLインジェクションという脆弱性を突かれて攻撃されてしまいます。
今の段階では必要ないかもしれませんが、ログインなどを管理するサービスでは注意が必要です。
SQLインジェクションについてはこちらをご覧ください。
▲安全なSQLの呼び出し方 - Qiita
CTFで少しやりましたがあとでもう少し勉強しておきます。
SQLインジェクション対策として使われるのがプレースホルダです。
プレースホルダを利用することで安全に値を渡すことができます。
pythonで使うSQLiteの場合は？を使います。
cursor.execute("INSERT INTO data_set(id, name, date) VALUES(?,?,?)", (1,'saito',19980810))
変数を使うとこうなります。
p = "INSERT INTO data_set(id, name, date) VALUES(?, ?, ?)" cursor.execute(p, (1, 'saito', 19980810))
このvaluesにある？は、あとで３つの値を渡しますよという意味です。
続けて第2引数に実際の値を渡します。
予約みたいなもんですかね。
？を使って代入すると文字列は勝手にクォート処理され、データの中にクォートがあった場合は
自動的にエスケープされるようです。
テーブルに複数行データを追加する
複数のデータを追加したい場合はexecutemanyメソッドを使います。
data = [
(1, "saito", 19980810),
(2, "hori", 19961015),
(3, "yoda", 20000505),
(4, "nishino", 19940525),
(5, "kitano", 19960717)
]
cursor.executemany("INSERT INTO data_set VALUES(?,?,?)", data)
まずは変数を用意しその中にタプルでデータを入れます。
あとは上と同じようにプレースホルダを使って変数を代入するだけです。
データベースに反映させる
テーブルにデータを入れましたがこのままだとまだ反映されていません。
保存のような作業が必要になります。
con.commit()
データを追加したら毎回commitする必要があります。
自動コミット
コミットし忘れちゃったﾃﾍッってことがないように自動コミットなる機能が存在します。
con = sqlite3.connect('sample.db', isolation_level=None)
データベース作成の際、第2引数にisolation_levelを指定することで自動的にコミットできるようになります。
※自動コミットは時間がかかる等の記事がちらほら見当たります。
確かにpythonの技術書には手動コミットが使われています。少なくとも僕が読んだ本には。
また僕の場合、database is locked というエラーが出てデータベースが開けなくなったりしたので手動で行っています。
データの参照をおこなう
次はテーブル内のデータを呼び出してみます。
cursor.execute('SELECT * FROM data_set') cursor.fetchall() out : [(1, 'saito', 19980810), (2, 'hori', 19961015), (3, 'yoda', 20000505), (4, 'nishino', 19940525), (5, 'kitano', 19960717)]
select * ですべてのデータを参照し、fromでどのテーブルからデータを呼ぶのか指定しています。
アスタリスクはおなじみのワイルドカードです。
fetchallですべての行のデータを取り出します。
一つ一つ取り出す場合はfetchoneを使います。
フェッチを使った取出し方法だとリストで返ってくるのでforを使った方法もあります。
cursor.execute("select * from data_set") for row in cursor: print(row[0], row[1], row[2]) out : 1 saito 19980810 2 hori 19961015 3 yoda 20000505 4 nishino 19940525 5 kitano 19960717
データの削除
データ（レコード）を削除する場合です。
試しに2番目のデータを削除してみます。
cursor.execute('delete from data_set where id=?', (2,)) cursor.execute('select * from data_set') cursor.fetchall() out : [(1, 'saito', 19980810), (3, 'yoda', 20000505), (4, 'nishino', 19940525), (5, 'kitano', 19960717)]
削除の場合はdeleteを使用します。
whereのあとに消したいデータの条件を書いて指定することができます。
このテーブルの1行目の要素はidなのでidが２のデータを指定したことになります。
テーブルの中身を削除
テーブルの中にあるデータをリセットしたい場合もdeleteを使用します。
cursor.execute('delete from data_set') cursor.execute('select * from data_set') cursor.fetchall() out : []
delete from テーブル名 のあとにwhereで条件を書かなければテーブル内のデータを削除できます。
テーブル自体を削除する
データだけでなくテーブル本体を削除する場合はdropを使用します。
cursor.execute('drop table data_set')
con.commit()
select で参照するとno such tableとエラーがでるはずです。
SQLiteの型
SQLiteにも型が存在します。
pythonでいうstrやintです。
| python | SQLite |
|---|---|
| None | Null |
| int | integer |
| float | real |
| str | text |
| bytes | blob |
なのでcreate table(id INTEGER, name TEXT, date INTEGER)のように型を指定したほうがいいのかもしれません。
ただ別に指定しなくてもできるので僕は指定しないで書いています。
varchar()について
SQLiteの使い方を調べているとvarcharなるものがなんの説明もなく突然でてきます。
例えばCREATE TABLE(id, name varchar(10))とかこんな感じ。
varcharは文字数制限のようです。
上記の場合だと10文字まで可という意味。
しかしこんな記事を見つけました。
▼Redirecting…
SQLiteは文字の長さを強制しないのでvarcharで文字数を指定しても意味がないらしいです。
大文字なのか小文字なのか
cursor.executeは小文字で、SELECT * FROMは大文字、data_setは小文字。
別にすべて小文字でも平気です。
ただどこがSQL文なのか判断しやすいように大文字にしています。
おそらく技術書などもそういう理由で小文字と大文字を使っているのではなかろうか。
まとめ
まずPyQというサイトでデータベースの扱い方を学びました。
PyQはすでに出来上がったデータベースの操作方法が主だったので、１からデータベースを作る方法として下記のサイトを参考にしました。
また書籍はもともと持っていたオライリーなどの技術書が参考になりました。
3日間くらい仕事の休憩時間にやってやっと少し扱えるようになりました。
あと個人的にUdemy
はとてもよかったです。
動画で勉強ができて、わからないこともサイト内のメールで問い合わせることができるので良きです。
基本的な使い方からマイナーなことまで、おまけにプログラミング以外の動画もでているので興味がある人は買ってみても損はないです。
セール中だと￥1,200～￥1,800ほどで購入できるのでお見逃しなく！
Twitterでpythonやって3か月でサービス作って公開してる方がいらっしゃいますけどすごいよね。
僕なんて家帰ったら眠くてぜんぜん捗らないんですよ。
休みの日にやればいいんだろうけど、なかなかね。
僕もpython初めてそろそろ1年経つので何かしら作りたいなとは思っています。
よし、とりあえず寝よう！
おやすみ！
今回参考にさせていただいたサイト
▼sqlite3 --- SQLite データベースに対する DB-API 2.0 インタフェース — Python 3.8.0 ドキュメント
▼PythonでSQLiteを操作する方法：sqlite3 | UX MILK
▼PyQ - 本気でプログラミングを学びたい人のPythonオンライン学習サービス
参考書籍
クジラ飛行机 ソシム 2016-12-06
※2019/2/22 こんな感じで使いますっていう応用編も書きました。よかったら見てね。
www.htmllifehack.xyz
