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)]