Sqlite3 の’SQL文’を使ってみる。

本当はFlask SQLAlchemyを習得したくて、幾つかのTutorialをみて、簡単なCRUDは作れるようになったのだけど、実際に、では実用に足りるDatabeseを自分で作ってみようと思うと、チュートリアルに書いてあることでは、基本（データベース・テーブルの確認、データベース自身の削除）などさえ出来ないので、少し基本からやりなおす。

まずは、Python3に標準でついてくるsqlite3 を Python3から使う方法を学習する。

== databaseのオンラインチェック用 url ==

SQLite Viewer: http://inloop.github.io/sqlite-viewer/

SQLiteのコマンドラインを直接操作する場合は、こちらの投稿を。
SQLiteのコマンドラインから操作する。

---

1. Setting Up A Connection

python app. に以下を記述しながら学習する。

◇  インポート
import sqlite3

◇sqlite3へのコネクションの作成
connection = sqlite3.connect('example.db')
なければ、example.dbが作られる。

この段階でPython IDEでファイルを実行すると、'example.db'が作成できる。
（又は, windowsのcmdからpython を起動して実行しても同じ結果。）


◇cursor object の作成： .cursor()

dbとのやりとりが可能になる。
cursor = connection.cursor()


◇Statementの実行： .execute( )

*テーブルの作成
Pt.1) Table名: namesの作成の場合
cursor.execute('CREATE TABLE names (id INTEGER PRIMARY KEY, name TEXT)')

Pt.2)  Table名: urlの作成の場合
cursor.execute('CREATE TABLE url (id INTEGER PRIMARY KEY, url TEXT, count INTEGER);')

*テーブルが作られた後で、再びこのコードを実行するとエラーになる。


*データの挿入

変数の引数として代入する変数に、一度 ? を置いて、次に代入する値を引数として指定する。　これはSQL Injenction（セキュリティ）対策上のベストプラクティス。.format を使用したり、 単純にstrings　と　+　で記述するよりも安全と言われている。

Pt.1) Table名: namesの作成の場合
cursor.execute('INSERT INTO names VALUES(?, ?)', (1, "Donald"))


Pt.2)  Table名: urlの作成の場合
cursor.execute('INSERT INTO url(url, count) VALUES(?, ?)', (url, 0))

* SQLiteでは、IntegerにPrimary Keyが設定された場合、自動で番号が付与されるため、このケースでidに値を与える必要がない。


*データベースに反映(commit)する
connection.commit()

この段階でデータベースを確認すると、以下の図のよう。

*データベースの追加
cursor.execute('INSERT INTO names (name) VALUES(?)', ("Richard"))
connection.commit()

cursor.execute('INSERT INTO names VALUES(?, ?)', (3, "Tom"))
connection.rollback()
*comitする前であれば、rollbackしてデータベースに登録しないことが可能。


*データベースを閉じる
connection.close()


以下のコードを一度に実行すると、

import sqlite3
connection = sqlite3.connect('example.db')
cursor = connection.cursor()
cursor.execute('CREATE TABLE names (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute('INSERT INTO names VALUES(?, ?)', (1, "Donald"))
cursor.execute('INSERT INTO names (name) VALUES(?)', ("Richard"))
connection.commit()
cursor.execute('INSERT INTO names VALUES(?, ?)', (3, "Tom"))
connection.rollback()
cursor.execute('INSERT INTO names VALUES(?, ?)', (4, "Tom"))
connection.commit()
connection.close()
(Output)

*データの検索：selection querｙ
cursor.execute('SELECT * FROM names WHERE id=?', (1, ))
row = cursor.fetchone()
id = row[0]
name = row[1]
print("id :" +str(id))
print("name :" +str(name))
(output)
id :1
name :Donald

*データの検索：複数のrowが該当するような検索
cursor.execute('SELECT * FROM names')
row = cursor.fetchone()
while row is not None:
    id = row[0]
    name = row[1]
    print("id :" +str(id) + "     name :" +str(name))
    row = cursor.fetchone()

connection.commit()
(Print)
id :1     name :Donald
id :2     name :Richard
id :4     name :Tom

又は、fetchall()
cursor.execute('SELECT * FROM names')
rows = cursor.fetchall()
for row in rows:
    print("id :" +str(row[0]) + "     name :" +str(row[1]))


(Print)
id :1     name :Donald
id :2     name :Richard
id :4     name :Tom 

*Databaseに作られているテーブルの全検索

>>> import sqlite3
>>> connection = sqlite3.connect('msg.db')
>>> cursor = connection.cursor()
>>> cursor.execute("select * from sqlite_master where type='table'")
<sqlite3.Cursor object at 0x0000000001F18880>
>>> for x in cursor.fetchall():
...     print(x)
...
('table', 'user', 'user', 2, 'CREATE TABLE user (\n\tid INTEGER NOT NULL, \n\tusername VARCHAR(20) NOT NULL, \n\temail V
ARCHAR(120) NOT NULL, \n\timage_file VARCHAR(20) NOT NULL, \n\tpassword VARCHAR(60) NOT NULL, \n\tPRIMARY KEY (id), \n\t
UNIQUE (username), \n\tUNIQUE (email)\n)')
('table', 'tw_msg', 'tw_msg', 5, 'CREATE TABLE tw_msg (\n\tid INTEGER NOT NULL, \n\ttime VARCHAR(6), \n\tmessage VARCHAR
(140), \n\thashtag VARCHAR(140), \n\turl VARCHAR(140), \n\tlength INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (id)\n)')

>>> cursor.description
(('id', None, None, None, None, None, None), ('time', None, None, None, None, None, None), ('message', None, None, None,
 None, None, None), ('hashtag', None, None, None, None, None, None), ('url', None, None, None, None, None, None), ('leng
th', None, None, None, None, None, None))


>>> cursor.execute('SELECT * FROM tw_msg')
<sqlite3.Cursor object at 0x00000000021E8880>
>>> for row in cursor.fetchall():
...     x = dict(zip([d[0] for d in cursor.description], row))
...     results.append(x)
...
>>> results
[{'id': 1, 'time': '1:10', 'message': '３個に変更', 'hashtag': '', 'url': ''}, {'id': 2, 'time': '12:20', 'message': 'ひよこのあと', 'hashtag': '', 'url': ''}]
>>>



参考）
https://www.youtube.com/watch?v=SQj17D1Q_6s

同じオーサーがちょっとだけ違う事例で説明、
https://nitratine.net/blog/post/python-sqlite3-basics/

テーブル要素の確認
https://teratail.com/questions/74005

（Sample Code）
Foodというテーブルを作成
Foodというテーブルを削除

>>> import sqlite3
>>> connection = sqlite3.connect('admin/msg.db')
>>> cursor = connection.cursor()
>>> cursor.execute('CREATE TABLE food (id INTEGER PRIMARY KEY, name TEXT)')
<sqlite3.Cursor object at 0x0000000001EF86C0>
>>> cursor.execute('INSERT INTO food VALUES(?, ?)', (1, "Ramen"))
<sqlite3.Cursor object at 0x0000000001EF86C0>
>>> cursor.execute("select * from sqlite_master where type='table'")
<sqlite3.Cursor object at 0x0000000001EF86C0>
>>> for x in cursor.fetchall():
...     print(x)
...
（テーブルが３つあるね、）
('table', 'user', 'user', 2, 'CREATE TABLE user (\n\tid INTEGER NOT NULL, \n\tusername VARCHAR(20) NOT NULL, \n\temail V
ARCHAR(120) NOT NULL, \n\timage_file VARCHAR(20) NOT NULL, \n\tpassword VARCHAR(60) NOT NULL, \n\tPRIMARY KEY (id), \n\t
UNIQUE (username), \n\tUNIQUE (email)\n)')
('table', 'tw_msg', 'tw_msg', 5, 'CREATE TABLE tw_msg (\n\tid INTEGER NOT NULL, \n\ttime VARCHAR(6), \n\tmessage VARCHAR
(140), \n\thashtag VARCHAR(140), \n\turl VARCHAR(140), \n\tlength INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (id)\n)')
('table', 'food', 'food', 7, 'CREATE TABLE food (id INTEGER PRIMARY KEY, name TEXT)')


>>> dropTableStatement = "DROP TABLE food"
>>> cursor.execute(dropTableStatement)
<sqlite3.Cursor object at 0x0000000001EF86C0>
>>> cursor.execute("select * from sqlite_master where type='table'")
<sqlite3.Cursor object at 0x0000000001EF86C0>
>>> for x in cursor.fetchall():
...     print(x)
...
（テーブルが２つになったね、）
('table', 'user', 'user', 2, 'CREATE TABLE user (\n\tid INTEGER NOT NULL, \n\tusername VARCHAR(20) NOT NULL, \n\temail V
ARCHAR(120) NOT NULL, \n\timage_file VARCHAR(20) NOT NULL, \n\tpassword VARCHAR(60) NOT NULL, \n\tPRIMARY KEY (id), \n\t
UNIQUE (username), \n\tUNIQUE (email)\n)')
('table', 'tw_msg', 'tw_msg', 5, 'CREATE TABLE tw_msg (\n\tid INTEGER NOT NULL, \n\ttime VARCHAR(6), \n\tmessage VARCHAR
(140), \n\thashtag VARCHAR(140), \n\turl VARCHAR(140), \n\tlength INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (id)\n)')
>>>