python sqlite3 の練習

Pythonでsqlite3を使ってみたのでメモ。
例として使うデータテーブルは公式ドキュメントから引用しています。
https://docs.python.org/3/library/sqlite3.html

sqlite3モジュールはAnacondaで丸ごとインストールすると入っているはずです。

ターミナルorコマンドプロンプトで

$ sqlite3 --version

とやってバージョンを確認できます。できなかったら手動でインストールしましょう。

自分の失敗としては、

$ sqlite --version

と3なしで入力してエラーが出てしまい、勘違いして入れ直したりして嵌ってしまいました。"3"が必要です。

---

なお、SQLでは大文字小文字の区別がありません。(case-insensitive)
https://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive

sqlite3起動

データベースファイルの拡張子は　.sqlite です。
ターミナルから起動するには
sqlite3 database.sqlite

でこんな画面が出ます。

SQLite version 3.24.0 2018-06-04 19:24:41

Enter ".help" for usage hints.

sqlite>

コマンドはドットで始まります。
.help でヘルプ
.exit でプログラムから抜けます。

SQLiteのdatabaseに何が入っているかを確認する

.table

でテーブル名を出力します。

.schema <table-name>

でtable-nameのスキーマ構造を出力します。table-nameなしの場合、全てのテーブルの構造が出力されます。

SQL文では末尾にセミコロンが必要

sqlite> select * from <table-name> ;

などとします。;を忘れてエンターキーを押してしまったときは、　「... >」が出ているはずなので、; を付け足せばOKです。


sqlite> select * from <table-name> 

   ...> ;

という感じです。

Pythonでsqlite3を操作

>>> import sqlite3

# バージョン確認

>>> sqlite3.version

# データベース接続。データベースファイル(.db)がない場合は自動で作成

conn = sqlite3.connect('example.db')

# カーソルオブジェクトを作ってメソッド(executeなど)を使う

c = conn.cursor()

# c.execute('SQL文')でSQLコマンドが入力される

# テーブル作成。(''' ''' にすると複数行で書ける)

c.execute('''CREATE TABLE stocks
            (data text, trans text, symbol text, qty real, price real)''')

# 同じ名前のテーブルを作成しようとするとエラーが出る
OperationalError: table stocks already exists

# テーブルを削除

c.execute('DROP TABLE stocks')

エラーが出てもそのまま続けてよい(ロールバック不要)
例えばpostgreSQLではロールバックが必要だが、sqliteでは不要

IF EXISTSで条件付きの実行
# IF EXISTS を追加すると「あるなら●●する」
# IF NOT EXISTS を追加すると「ないなら●●する」
# 例: IF NOT EXISTS を入れているので、already existsエラーは出ない

c.execute('''CREATE TABLE IF NOT EXISTS stocks
            (data text, trans text, symbol text, qty real, price real)''')

複数行の記述
# 複数行にSQL文を書くとき、''' ''' を使わなくても、1行ごとに' ' で囲んでもよい

c.execute('CREATE TABLE IF NOT EXISTS stocks'
        　'(data text, trans text, symbol text, qty real, price real)')

レコードの挿入(INSERT)、選択(SELECT)

# レコード(行)を挿入する

c.execute("INSERT INTO stocks VALUES('2006-01-05','BUY','RHAT',100, 35.14)")

# SELECTしてからfetchall しないと表示できないので注意。
# fetchmany(num)でnum個だけ表示 fetchone()で1つだけ表示することもできる。

c.execute("SELECT * FROM stocks")
print(c.fetchall())

# WHERE で条件をつけてSELECT

c.execute('SELECT * FROM stocks WHERE symbol="RHAT"')
print(c.fetchall())

# プレースホルダを使うときは?を使います。SQLインジェクションを避けるため、t = ('RHAT',) というようにタプルにします。

 t = ('RHAT',)
c.execute("SELECT * FROM stocks WHERE symbol=?",t)
print(c.fetchall())

# 一度に複数のレコードを挿入する

purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

# レコード数はlenでは出せない
# len(c.execute("SELECT * FROM stocks"))
# cursorはイテレートできる

 for row in c.execute("SELECT * FROM stocks"):
    print(row)
[Out:]
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0)
('2006-04-05', 'BUY', 'MSFT', 1000.0, 72.0)
('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)

保存して閉じる

conn.commit()
conn.close()

---

SQLITEのコマンド

.onece -x

今回だけエクセルで出力

shimo lab2

2018年3月14日水曜日