0. はじめに
SQLiteの記事を過去に書いてきました。
その中で実際に株のデータをSQLに入れるという記事があるのですが、実際にPandasで読み出した株価＊年分のDataframeをそのままto_sqlでSQLに挿入しようとすると、無茶苦茶時間がかかります。
(過去記事と記載した該当部分は以下)
"""
実際に動かしてみるとわかるが、これ無茶苦茶時間かかります。
"""
#dfをto_sqlでデータベースに変換する。DBのテーブル名はpriceとする
df.to_sql('price', db, if_exists='append', index=None)
そこで本日はそんなときの対処方法を書いておきます。
1. 対処方法
実は簡単でmethod='multi'を入れるだけでいいのです。
"""
重い操作。内部的には1行1行insertしているので遅くなっている。
"""
df.to_sql('price', db, if_exists='append', index=None)
"""
一瞬。内部的には一括でinsertさせている
"""
df.to_sql('price', db, if_exists='append', index=None, method='multi')
たったこれだけです。
method='multi'を入れるだけですぐに終わるので試してみてください。
2. too many SQL variablesの対処法
2-1.エラーの確認
しかし、実はこのmethod='multi'は行数が大きすぎるとエラーで実行できない。
以下は実際の私の株データの例です。
まずはDataframeの大きさをprintさせてますが、見るとわかるように1500万行もあります。
これを1行1行SQLに入れてたら1日で終わるかどうかも怪しいです。
かといって、method='multi'だと以下のようにtoo many SQL variablesというエラーがでてしまいます
print(df.shape)
df.to_sql('price', db, if_exists='append', index=None, method='multi')
(15462514, 7)
(途中略)
1558 data_list = list(data_iter)
1559 flattened_data = [x for row in data_list for x in row]
-> 1560 conn.execute(self.insert_statement(num_rows=len(data_list)), flattened_data)
1561
1562 def _create_table_setup(self):
OperationalError: too many SQL variables
2-2.対処法
実はPandasではchunksizeというパラメータがあって、メモリに乗り切らないような大容量データを用いるときに一回に読み込む量を指定することが出来る。
これとto_sql、method='multi'を組み合わせればいいだけである。
"""
chunksize=5000で5千行ずつ書き込みさせている。
too many SQL variablesにならない程度に数字を大きくすればいいと思います。
"""
df.to_sql('price', con, if_exists='append', index=None, method='multi', chunksize=5000)
実際に私の環境で本命令時間を計測した結果、1分でSQLに挿入が出来ました。
3.chunksizeは読み出しにも使えます
to_sqlで保存した大量のdfを再度SQLからdfに戻す方法も書いておきます。
※これもchunksize使わないと無茶苦茶時間かかります！
# データベースの接続
dbname = ('***.db')
db = sqlite3.connect(dbname, isolation_level=None)
# 同じくchunksizeを使用してdfで読み出す
dfs = []
for chunk in pd.read_sql_query('SELECT * FROM price', con=db, chunksize=1000):
dfs.append(chunk)
df = pd.concat(dfs)
4.おわりに
実はこれto_sqlやread_sql_query以外にpd.read_csv等でも使用できます。(ただし読み込みがテキストになるが)
Pnadasでメモリに乗らないような大きな読み書きをする際には、chunksizeを指定して快適に操作しましょう！
参考：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html
5.追記
書き込むときの方法はわかったとして、逆に読み出す時に遅くてイライラしちゃってる方は以下記事も参考にしてみてください。結構早く読み出せますよ