Pandasのto_sqlで行が多すぎて時間がかかる or エラーになった時に...
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.おわりに
実はこれto_sql以外にpd.read_csv等でも使用できます。(ただし読み込みがテキストになるが)
Pnadasでメモリに乗らないような大きな読み書きをする際には、chunksizeを指定して快適に操作しましょう！
参考：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html
Author And Source
この問題について(Pandasのto_sqlで行が多すぎて時間がかかる or エラーになった時に...), 我々は、より多くの情報をここで見つけました https://qiita.com/ku_a_i/items/77f4680651e628517db9著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .