search

zvtvz / zvt

modular quant framework.
https://zvt.readthedocs.io/en/latest/
MIT License
3.19k stars 858 forks source link

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

Open dwj1979 opened 4 years ago

dwj1979 commented 4 years ago

安装完以后执行 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 commented 4 years ago

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

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

scanfyu commented 4 years ago

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

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

foolcage commented 4 years ago

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

foolcage commented 4 years ago

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 commented 3 years ago

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

coolsnake commented 3 years ago

将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 commented 3 years ago

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 commented 3 years ago

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的限制是无法改变的。