LoginSignup
248
297

More than 3 years have passed since last update.

python3でsqlite3の操作。作成や読み出しなどの基礎。

Last updated at Posted at 2019-02-06

この記事にはpython3でsqlite3を操作して、データベースの作成や、編集の基礎的なことをまとめてます。家計簿や収入、株式投資のためにデータベースを利用していきたい。
本当に基礎的なことなので、この辺りを理解すれば、やりたいことに必要なことがクリアになると思います。

  • DBを作成する
  • tableを作成する
  • tableへデータのINSERT
  • tableの内容を確認する (terminal)
  • tableの内容を確認する (python)
  • csvからtableを作成する (pandas利用)
  • DBをpandasで読み出す
  • DBをメモリ上に作成する

DBを作成する

カレントディレクトリにTEST.dbというデータベースを作成する。

create_db.py
import sqlite3

# TEST.dbを作成する
# すでに存在していれば、それにアスセスする。
dbname = 'TEST.db'
conn = sqlite3.connect(dbname)

# データベースへのコネクションを閉じる。(必須)
conn.close()

tableを作成する

create_table.py
import sqlite3

dbname = 'TEST.db'
conn = sqlite3.connect(dbname)
# sqliteを操作するカーソルオブジェクトを作成
cur = conn.cursor()

# personsというtableを作成してみる
# 大文字部はSQL文。小文字でも問題ない。
cur.execute(
    'CREATE TABLE persons(id INTEGER PRIMARY KEY AUTOINCREMENT,
     name STRING)')

# データベースへコミット。これで変更が反映される。
conn.commit()
conn.close()

上記のSQL文は、"id""name"を格納するテーブルを作成する。"INTEGER"(整数), "STRING"(文字列)は、絡むへ入力するデータ型を指定している。
"AUTOINCREMENT"は、"name"をテーブルへ追加すると自動で"id"が追加されていくようにしてくれる。

tableへデータのINSERT

create_table.py
import sqlite3

dbname = 'TEST.db'
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# "name"に"Taro"を入れる
cur.execute('INSERT INTO persons(name) values("Taro")')
# 同様に
cur.execute('INSERT INTO persons(name) values("Hanako")')
cur.execute('INSERT INTO persons(name) values("Hiroki")')

conn.commit()

cur.close()
conn.close()

tableの内容を確認する (terminal)

terminalを開いて、$ "sqlite3 TEST.db" を実行する。
sqliteの対話型モードに入る。

terminal
sqlite> .tables #.tablesでデータベースのテーブル一覧を取得
persons 
sqlite> SELECT * FROM persons; # personsの中身を確認。"id"が自動で追加されている.
1|Taro
2|Hanako
3|Hiroki

tableの中身を確認する (python)

check_table.py
import sqlite3

dbname = 'TEST.db'
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# terminalで実行したSQL文と同じようにexecute()に書く
cur.execute('SELECT * FROM persons')

# 中身を全て取得するfetchall()を使って、printする。
print(cur.fetchall())

cur.close()
conn.close()
結果
[(1,"Taro"), (2,"Hanako"), (3,"Hiroki")]

リストとして返ってくる。

csvからtableを作成する (pandas利用)

csv内のデータをpandasデータフレームとして読み出し、データベースへ書き込む。

create_DB.py
import sqlite3
import pandas as pd

# pandasでカレントディレクトリにあるcsvファイルを読み込む
# csvには、1列目にyear, 2列目にmonth, 3列目にdayが入っているとする。
df = pd.read_csv("calendar.csv")

# カラム名(列ラベル)を作成。csv file内にcolumn名がある場合は、下記は不要
# pandasが自動で1行目をカラム名として認識してくれる。
df.columns = ['year', 'month', 'day']

dbname = 'TEST.db'

conn = sqlite3.connect(dbname)
cur = conn.cursor()

# dbのnameをsampleとし、読み込んだcsvファイルをsqlに書き込む
# if_existsで、もしすでにexpenseが存在していたら、書き換えるように指示
df.to_sql('sample', conn, if_exists='replace')

# 作成したデータベースを1行ずつ見る
select_sql = 'SELECT * FROM sample'
for row in cur.execute(select_sql):
    print(row)

cur.close()
conn.close()
結果
(0, 2000, 1, 1)
(1, 2000, 1, 2)
(2, 2000, 1, 3)
...

DBをpandasで読み出す

read_DB_from_pandas.py
import sqlite3
import pandas as pd

dbname = "TEST.db"
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# dbをpandasで読み出す。
df = pd.read_sql('SELECT * FROM sample', conn)

print(df)

cur.close()
conn.close()

DBをメモリ上に作成する

connectionがcloseされた時点で内容は消失する。

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

# データベースを色々操作

conn.close()
# 閉じたら消える。
248
297
4

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
248
297