[Python3] sqlite3の基本操作

Posted on 2019/03/05 in programming , Updated on: 2019/03/30

はじめに

この記事には python3 で sqlite3 を操作して、データベースの作成や、編集の方法を紹介する。 家計簿や収入、株式投資のためにデータベースを作りたいなど、個人レベルでも利用できる。

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

sqlite3 をインストール

$ pip3 install sqlite3

DBを作成する

カレントディレクトリにTEST.dbというデータベースを作成する。データベースファイルの拡張子は.dbにする必要がある。

# sqlite3 をインポート
import sqlite3

# dbname としてデータベース名を定義
dbname = 'TEST.db'

# データベースへコネクトする。
conn = sqlite3.connect(dbname)

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

tableを作成する

データベース内にテーブルを作成する。データベースファイルをexcelファイルとすると、テーブルはexcelファイル内のSheetにあたる。

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へデータの挿入

データを挿入するには、SQL文のINSERTを使う。

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)

sqlite 対話型モードで確認する方法。 terminalを開いて、下記コマンドを実行すると sqlite の対話型モードに入る。

$ "sqlite3 TEST.db" を実行する。

sqlite >> .tables   #.tablesでデータベースのテーブル一覧を取得
persons

sqlite >> SELECT * FROM persons;    # personsの中身を確認。"id"が自動で追加されている.
1|Taro
2|Hanako
3|Hiroki

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

python スクリプトで確認する方法。

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データフレームとして読み出し、データベースを作成し、テーブルとして内容を書き込む。

import sqlite3
# pandas をインポート
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で読み出す

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 された時点で内容は消失する。.dbファイルは作成されない。

import sqlite3

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

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

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