sqlite3とpandasの連動確認

やっていること

0. sqliteのDB（インメモリ）の用意
1. データ準備
2. sqliteのDB（インメモリ）に書き込み
3. sqliteのDB（インメモリ）のテーブル定義を確認
4. sqliteのDB（インメモリ）のテーブルの内容を確認 ORDER BY numberでソート。
5. fillnaでNaN値を補完
6. ソートしなおして、NaNを補完した値で、sqliteに書き込み＆内容確認

ソースコード

import sqlite3
import pandas as pd

con = sqlite3.connect(":memory:")
con.isolation_level = None  # None で自動コミットモード
cur = con.cursor()
cur.execute('PRAGMA temp_store=MEMORY;')
cur.execute('PRAGMA journal_mode=MEMORY;')

list1 = [[1, 'alpha', 10.0], [2, 'Bravo', None], [3, 'Charlie', 8.5], [5, 'Echo', 9.8], [4, 'Delta', 3.6]]
#list1 = [[1,'alpha', 10],[2,'Bravo', None],[3,'Charlie', 8], [5,'Foxtrot', 9],[None,'Echo', 3]]
#list1 = [[1,'alpha', 10],[2,'Bravo', 7],[3,'Charlie', 8], [5,'Foxtrot', 9],[4,'Echo', 3]]
df1 = pd.DataFrame(list1, columns=['number', 'name', 'score'])
print(df1)
print(df1.dtypes)

# sqliteに書き込み
df1.to_sql(name='users', con=con, index=False,
           schema='number INTEGER, name TEXT, score REAL')
tmp = cur.execute("SELECT * FROM users").fetchall()

print(tmp)

# schemaに'number TEXT, name TEXT, score REAL'を指定しても、
# 'number TEXT, name TEXT, score REAL'になるので、dataframeの設定が優先される様子。
# 値にNoneがあると、INTEGERではなくREALになる。
tmp = cur.execute("SELECT * FROM sqlite_master WHERE type='table'").fetchall()

print(tmp)

# read_sql_table only supported for SQLAlchemy connectable.
df2 = pd.read_sql(sql='SELECT * FROM users ORDER BY number', con=con)

# df3.to_sql(・・・,if_exists="replace")を実行したとき、
# pandas.io.sql.DatabaseError: Execution failed on sql 'DROP TABLE "users"': database table is locked
# になることがある。再実行するか、curを閉じるとよい。

print(df2)
print(df2.dtypes)

df3 = df2.fillna(method="ffill")
print(df3)

df3.to_sql(name='users', con=con, index=False,
           schema='number INTEGER, name TEXT, score REAL', if_exists="replace")
tmp = cur.execute("SELECT * FROM users").fetchall()
cur.close()
print(tmp)

con.close()

実行結果

   number     name  score
0       1    alpha   10.0
1       2    Bravo    NaN
2       3  Charlie    8.5
3       5     Echo    9.8
4       4    Delta    3.6
number      int64
name       object
score     float64
dtype: object
[(1, 'alpha', 10.0), (2, 'Bravo', None), (3, 'Charlie', 8.5), (5, 'Echo', 9.8), (4, 'Delta', 3.6)]
[('table', 'users', 'users', 2, 'CREATE TABLE "users" (\n"number" INTEGER,\n  "name" TEXT,\n  "score" REAL\n)')]
   number     name  score
0       1    alpha   10.0
1       2    Bravo    NaN
2       3  Charlie    8.5
3       4    Delta    3.6
4       5     Echo    9.8
number      int64
name       object
score     float64
dtype: object
   number     name  score
0       1    alpha   10.0
1       2    Bravo   10.0
2       3  Charlie    8.5
3       4    Delta    3.6
4       5     Echo    9.8
[(1, 'alpha', 10.0), (2, 'Bravo', 10.0), (3, 'Charlie', 8.5), (4, 'Delta', 3.6), (5, 'Echo', 9.8)]