VeighNa量化社区
你的开源社区量化交易平台
Member
avatar
加入于:
帖子: 8
声望: 0

大家好,这个问题困扰了几天了,请教大家,多谢回复哦:

最新VNPY的2.14版本&Windows环境,想测试一下.csv文件导入到SQLite数据库中的功能,用VN.Trader图形化界面的,.csv文件数据导入模块可以正常实现数据导入和CTA策略回测。
但是同样的.csv文件(格式都已经参照了VNPY的标准格式),用PY脚本想手动操作导入测试,报错信息如下:(database.db也尝试删除过)

PS C:\Users\Administrator\Desktop\vnpy-master> python Move_data_sql.py #(运行脚本)
Traceback (most recent call last):
File "Move_data_sql.py", line 95, in <module>
move_df_to_sqlite(imported_data)
File "Move_data_sql.py", line 70, in move_df_to_sqlite
sql_manager.save_bar_data(bars)
File "C:\Users\Administrator\Desktop\vnpy-master\vnpy\trader\database\database_sql.py", line 385, in save_bar_data
ds = [self.class_bar.from_bar(i) for i in datas]
File "C:\Users\Administrator\Desktop\vnpy-master\vnpy\trader\database\database_sql.py", line 385, in <listcomp>
ds = [self.class_bar.from_bar(i) for i in datas]
File "C:\Users\Administrator\Desktop\vnpy-master\vnpy\trader\database\database_sql.py", line 98, in from_bar
dt = bar.datetime.astimezone(DB_TZ)
File "pandas_libs\tslibs\timestamps.pyx", line 860, in pandas._libs.tslibs.timestamps.Timestamp.tz_convert
TypeError: Cannot convert tz-naive Timestamp, use tz_localize to localize #(报错信息:好像关于时区的)


脚本部分代码如下:
imported_data = pd.read_csv('C:/Users/Administrator/Desktop/vnpy-master/USDJPY_D1.csv')

datetime_format = '%Y%m%d %H:%M:%S'
imported_data['datetime'] = pd.to_datetime(imported_data['datetime'],format=datetime_format)

imported_data['exchange']=Exchange.OTC
imported_data['symbol']='USDJPY'
imported_data['interval']=Interval.DAILY

move_df_to_sqlite(imported_data)   #(调用函数)

def move_df_to_sqlite(data_df:pd.DataFrame): #(数据导入函数)
bars = []
start = None
count = 0

for row in data_df.itertuples():
    bar = BarData(
        symbol=row.symbol,
        exchange=row.exchange,
        datetime=row.datetime,
        interval=row.interval,
        volume=float(row.volume),
        open_price=float(row.open),
        high_price=float(row.high),
        low_price=float(row.low),
        close_price=float(row.close),
        open_interest=float(row.open_interest),
        gateway_name="DB",
    )

    bars.append(bar)

    # do some statistics
    count += 1
    if not start:
        start = bar.datetime
end = bar.datetime
# insert into database
sql_manager.save_bar_data(bars)
print(f'插入{count} 根bar 从 {start} 到 {end}')

,csv文件格式如下:
datetime,open,high,low,close,volume,open_interest
2019-08-02 21:00:00,107.327,107.562,106.502,106.563,0.0,0.0


这个问题蛮卡的,一直没有有效解决,希望得到陈老师,VNPY团队,各位群友老师的指导,感谢大家的回复!

Member
avatar
加入于:
帖子: 141
声望: 57

你用的是pandas的timestamp,要用tz_localize设置时区

        def from_bar(bar: BarData):
            """
            从BarData生成DbBarData
            """
            try:    #datetime timestamp
                dt = bar.datetime.astimezone(DB_TZ)
            except:     #pandas timestamp
                dt = bar.datetime.tz_localize(DB_TZ)
            db_bar = DbBarData()

            db_bar.symbol = bar.symbol
            db_bar.exchange = bar.exchange.value
            db_bar.datetime = dt
            db_bar.interval = bar.interval.value
            db_bar.volume = bar.volume
            db_bar.open_interest = bar.open_interest
            db_bar.open_price = bar.open_price
            db_bar.high_price = bar.high_price
            db_bar.low_price = bar.low_price
            db_bar.close_price = bar.close_price

            return db_bar
Member
avatar
加入于:
帖子: 8
声望: 0

感谢您的回复!

你这个是需要修改VN的源代码在文件中是吧!(database_sql.py,大概在100行左右)

我按照这个意思,已经修改了,但是运行脚本报这个最新的错误了?

PS C:\Users\Administrator\Desktop\vnpy-master> python Move_data_sql.py
Traceback (most recent call last):
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "Move_data_sql.py", line 105, in <module>
move_df_to_sqlite(imported_data)
File "Move_data_sql.py", line 74, in move_df_to_sqlite
sql_manager.save_bar_data(bars)
File "C:\Users\Administrator\Desktop\vnpy-master\vnpy\trader\database\database_sql.py", line 392, in save_bar_data
self.class_bar.save_all(ds)
File "C:\Users\Administrator\Desktop\vnpy-master\vnpy\trader\database\database_sql.py", line 162, in save_all
c).on_conflict_replace().execute()
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 1886, in inner
return method(self, database, args, **kwargs)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 1957, in execute
return self._execute(database)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 2707, in _execute
return super(Insert, self)._execute(database)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 2442, in _execute
cursor = database.execute(self)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3112, in execute
return self.execute_sql(sql, params, commit=commit)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3106, in execute_sql
self.commit()
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 2873, in exit
reraise(new_type, new_type(exc_value,
exc_args), traceback)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 183, in reraise
raise value.with_traceback(tb)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
peewee.InterfaceError: Error binding parameter 2 - probably unsupported type. #(最新报错信息)
到最后的这一步,就是和原帖的最后报错信息一样了,按照原帖的处理,程序依然有其他的报错信息,报错信息有点死循环了!

[点击请看原帖的脚本导入的网址链接]
(https://www.vnpy.com/forum/topic/3203-bian-xie-pythonjiao-ben-shi-xian-shu-ju-ru-ku?page=1#pid11498)

请教各位老师了!

Member
avatar
加入于:
帖子: 4680
声望: 285

要不然你研究一下data_manager里的import_data_from_csv函数里对datetime的处理,基于你的脚本修改一下,因为这两个最后都是用databse_manager.save_bar()。而且你说你通过图形界面是可以导入的。

Member
avatar
加入于:
帖子: 8
声望: 0

好的,感谢各位老师的回复,还有大神陈老师,VNPY团队的技术指导。
我再仔细分析一下,虽然这个是小问题,但是的调试的过程中也收获不少VN.PY开源框架的不少技术知识。

(其实我这个问题就是.csv文件字段datatime导入到Sqlite数据库的对应datatime字段(数据类型的不同方式的传递引起的问题)的时区匹配的问题,Sqlite虽然入门简单方便,但确实完整性不如其他数据库,会存在一些使用上的小问题),不过按老师的指导,我这个问题已经逐步排查过程中,倒是有几个不同的处理方法。

Member
avatar
加入于:
帖子: 1
声望: 0

我也是遇到了这个问题,如果用 mydatetime.tz_localize('UTC') 设置时区 就会报错

InterfaceError: Error binding parameter 2 - probably unsupported type.

如果把 mydatetime 类型改成字符串 mydatetime .strftime('%Y%m%d %H:%M:%S') 又报错

AttributeError: 'str' object has no attribute 'astimezone'

死循环 哭了

Member
avatar
加入于:
帖子: 4680
声望: 285

可参考该帖12楼和13楼

Member
avatar
加入于:
帖子: 3
声望: 0

泼奇 wrote:

感谢您的回复!

你这个是需要修改VN的源代码在文件中是吧!(database_sql.py,大概在100行左右)

我按照这个意思,已经修改了,但是运行脚本报这个最新的错误了?

PS C:\Users\Administrator\Desktop\vnpy-master> python Move_data_sql.py
Traceback (most recent call last):
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "Move_data_sql.py", line 105, in <module>
move_df_to_sqlite(imported_data)
File "Move_data_sql.py", line 74, in move_df_to_sqlite
sql_manager.save_bar_data(bars)
File "C:\Users\Administrator\Desktop\vnpy-master\vnpy\trader\database\database_sql.py", line 392, in save_bar_data
self.class_bar.save_all(ds)
File "C:\Users\Administrator\Desktop\vnpy-master\vnpy\trader\database\database_sql.py", line 162, in save_all
c).on_conflict_replace().execute()
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 1886, in inner
return method(self, database, args, **kwargs)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 1957, in execute
return self._execute(database)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 2707, in _execute
return super(Insert, self)._execute(database)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 2442, in _execute
cursor = database.execute(self)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3112, in execute
return self.execute_sql(sql, params, commit=commit)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3106, in execute_sql
self.commit()
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 2873, in exit
reraise(new_type, new_type(exc_value,
exc_args), traceback)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 183, in reraise
raise value.with_traceback(tb)
File "C:\ProgramData\Anaconda3\lib\site-packages\peewee.py", line 3099, in execute_sql
cursor.execute(sql, params or ())
peewee.InterfaceError: Error binding parameter 2 - probably unsupported type. #(最新报错信息)
到最后的这一步,就是和原帖的最后报错信息一样了,按照原帖的处理,程序依然有其他的报错信息,报错信息有点死循环了!

[点击请看原帖的脚本导入的网址链接]
(https://www.vnpy.com/forum/topic/3203-bian-xie-pythonjiao-ben-shi-xian-shu-ju-ru-ku?page=1#pid11498)

请教各位老师了!
楼主这个问题你解决了吗,我遇到了一样的问题。。

© 2015-2022 上海韦纳软件科技有限公司
备案服务号:沪ICP备18006526号

沪公网安备 31011502017034号

【用户协议】
【隐私政策】
【免责条款】