更新数据报"too many SQL variables"的解决办法

[dwj1979]

安装完以后执行 Stock.record_data（）接口，provider指定eastmoney和exchange都报错误

Stock.record_data(provider='exchange')

Stock registered recorders:{'joinquant': <class 'zvt.recorders.joinquant.meta.china_stock_meta_recorder.JqChinaStockRecorder'>, 'exchange': <class 'zvt.recorders.exchange.china_stock_list_spider.ExchangeChinaStockListRecorder'>, 'eastmoney': <class 'zvt.recorders.eastmoney.meta.china_stock_meta_recorder.EastmoneyChinaStockListRecorder'>}
Empty DataFrame
Columns: [code, name, list_date]
Index: []
Traceback (most recent call last):
File "D:\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
context)
File "D:\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 509, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: too many SQL variables

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "", line 1, in
File "D:\Anaconda3\lib\site-packages\zvt\contract\schema.py", line 152, in record_data
r.run()
File "D:\Anaconda3\lib\site-packages\zvt\recorders\exchange\china_stock_list_spider.py", line 23, in run
self.download_stock_list(response=resp, exchange='sh')
File "D:\Anaconda3\lib\site-packages\zvt\recorders\exchange\china_stock_list_spider.py", line 60, in download_stock_list
df_to_db(df=df, data_schema=self.data_schema, provider=self.provider, force_update=False)
File "D:\Anaconda3\lib\site-packages\zvt\contract\api.py", line 446, in df_to_db

[foolcage]

“too many SQL variables” 错误会在某些sqlite版本出现，这是sqlite 参数长度限制 的设置。
可以百度一下解决办法。

PS:目前mac,linux下暂时没碰到。根本原因是在更新数据时，传入长串的id判断是否重复导致。

[scanfyu]

目前测试win环境下会遇到该问题，主要原因是参数过多超过了SQLite允许的上线（999）。

参见：https://stackoverflow.com/questions/26971050/sqlalchemy-sqlite-too-many-sql-variables

[foolcage]

@scanfyu 可以做个判断，win下df_to_db把sub_size设得足够足够小。。

[foolcage]

sqlite variables的限制，可以通过命令行来更改：

➜  ~ sqlite3
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .limit variable_number 400000
     variable_number 400000
sqlite> .limit
              length 2147483645
          sql_length 1000000000
              column 2000
          expr_depth 1000
     compound_select 500
             vdbe_op 250000000
        function_arg 127
            attached 10
 like_pattern_length 50000
     variable_number 400000
       trigger_depth 1000
      worker_threads 0

[ranglang]

➜ ~ sqlite3
SQLite version 3.24.0 建议增加版本建议 centos 7自带的sqlite就不行 @foolcage

[coolsnake]

将SQLite的版本升级到3.32.0以上即可解决。

windows下：

1、关闭anaconda里的程序；

2、下载sqlite3最新版

https://www.sqlite.org/download.html

sqlite-dll-win64-x64-3340100.zip（根据情况，选择相应版本，这里选择64位的）
sqlite-tools-win32-x86-3340100.zip

解压：sqlite3.dll,sqlite3.exe

3、打开：\Anaconda3\Library\bin

替换 sqlite3.dll,sqlite3.exe

[Leo-zhanglj]

MAC版本信息
requests == 2.20.1
SQLAlchemy == 1.4.20
pandas == 1.1.4
arrow == 0.11.0
tzlocal == 2.1
xlrd == 1.2.0
demjson == 2.2.4
marshmallow-sqlalchemy == 0.23.1
marshmallow == 3.2.2
plotly==4.12.0
dash==1.17.0
simplejson==3.16.0
jqdatapy==0.1.6
dash-bootstrap-components
dash_daq

sqlite版本
leo@Leo-MacBookPro ~ % sqlite3
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

python版本3.7，遇到too many问题，后安装python3.9解决

[epoches]

sqlite variables的限制，可以通过命令行来更改：

➜  ~ sqlite3
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .limit variable_number 400000
     variable_number 400000
sqlite> .limit
              length 2147483645
          sql_length 1000000000
              column 2000
          expr_depth 1000
     compound_select 500
             vdbe_op 250000000
        function_arg 127
            attached 10
 like_pattern_length 50000
     variable_number 400000
       trigger_depth 1000
      worker_threads 0

命令后没有修改数据，经过检查 python3.7无法解决，更换sqllite3也不行。windows下预编译版本sqlite variables的限制是无法改变的。