vn.py量化社区
By Traders, For Traders.
Member
加入于:
帖子: 31
声望: 0

这个怎么解决,谢谢。

Administrator
avatar
加入于:
帖子: 198
声望: 35

是2.0.4版本升级,数据库结构变化导致的。
群主在q群上上传了2个文件,直接运行即可解决

Member
加入于:
帖子: 31
声望: 0

你好,没有找到这两个文件。能给一下文件名么,或者把文件贴上来也行,相比还有其他人有一样的问题

Member
加入于:
帖子: 31
声望: 0

请问是这个文件么,vnpy_database_index_fix.sql,
我用sqlite执行会出错,

near ‘DROP’: syntax error

Administrator
avatar
加入于:
帖子: 198
声望: 35

附上这两个文件代码

 

2019-6-14_open_interset_sql_only.py

from enum import Enum

from peewee import Database, MySQLDatabase, PostgresqlDatabase, SqliteDatabase
from vnpy.trader.utility import get_file_path

import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)


class Driver(Enum):
    SQLITE = "sqlite"
    MYSQL = "mysql"
    POSTGRESQL = "postgresql"
    MONGODB = "mongodb"


def init(driver: Driver, settings: dict):
    init_funcs = {
        Driver.SQLITE: init_sqlite,
        Driver.MYSQL: init_mysql,
        Driver.POSTGRESQL: init_postgresql,
    }
    assert driver in init_funcs

    db = init_funcs[driver](settings)
    return db


def init_sqlite(settings: dict):
    database = settings["database"]
    path = str(get_file_path(database))
    db = SqliteDatabase(path)
    return db


def init_mysql(settings: dict):
    keys = {"database", "user", "password", "host", "port"}
    settings = {k: v for k, v in settings.items() if k in keys}
    db = MySQLDatabase(**settings)
    return db


def init_postgresql(settings: dict):
    keys = {"database", "user", "password", "host", "port"}
    settings = {k: v for k, v in settings.items() if k in keys}
    db = PostgresqlDatabase(**settings)
    return db


def fetch_columns(driver: Driver, db: Database, table_name: str):
    if driver is Driver.SQLITE:
        c = db.execute_sql(f"PRAGMA table_info({table_name});")
        res = c.fetchall()
        return [i[1] for i in res]
    else:
        c = db.execute_sql(
            f"SELECT column_name "
            f"FROM INFORMATION_SCHEMA.COLUMNS "
            f"WHERE table_name = '{table_name}';"
        )
        res = c.fetchall()
        return [i[0] for i in res]


def upgrade(driver: Driver, db: Database):
    columns = fetch_columns(driver, db, "dbbardata")
    if 'open_interest' not in columns:
        db.execute_sql("ALTER TABLE dbbardata "
                       " ADD COLUMN open_interest FLOAT DEFAULT 0;")

    columns = fetch_columns(driver, db, "dbtickdata")
    if 'open_interest' not in columns:
        db.execute_sql("ALTER TABLE dbtickdata "
                       " ADD COLUMN open_interest FLOAT DEFAULT 0;")

    print("upgrade succeed!")


def down_grade(driver: Driver, db: Database):
    assert driver is not Driver.SQLITE  # sqlite doesn't support drop column

    columns = fetch_columns(driver, db, "dbbardata")
    if 'open_interest' in columns:
        db.execute_sql("ALTER TABLE dbbardata "
                       " DROP COLUMN open_interest;")

    columns = fetch_columns(driver, db, "dbtickdata")
    if 'open_interest' in columns:
        db.execute_sql("ALTER TABLE dbtickdata "
                       " DROP COLUMN open_interest;")

    print("down grade succeed!")


def main():
    from vnpy.trader.setting import get_settings

    settings = get_settings("database.")
    driver = Driver(settings["driver"])
    if driver is not Driver.MONGODB:
        db = init(driver, settings=settings)
        upgrade(driver, db)
    else:
        raise NotImplementedError()


if __name__ == '__main__':
    main()

 

2019-6-14_index_sql_only.py

import logging
from enum import Enum

from peewee import Database, MySQLDatabase, PostgresqlDatabase, SqliteDatabase
from vnpy.trader.utility import get_file_path

logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)


class Driver(Enum):
    SQLITE = "sqlite"
    MYSQL = "mysql"
    POSTGRESQL = "postgresql"
    MONGODB = "mongodb"


def init(driver: Driver, settings: dict):
    init_funcs = {
        Driver.SQLITE: init_sqlite,
        Driver.MYSQL: init_mysql,
        Driver.POSTGRESQL: init_postgresql,
    }
    assert driver in init_funcs

    db = init_funcs[driver](settings)
    return db


def init_sqlite(settings: dict):
    database = settings["database"]
    path = str(get_file_path(database))
    db = SqliteDatabase(path)
    return db


def init_mysql(settings: dict):
    keys = {"database", "user", "password", "host", "port"}
    settings = {k: v for k, v in settings.items() if k in keys}
    db = MySQLDatabase(**settings)
    return db


def init_postgresql(settings: dict):
    keys = {"database", "user", "password", "host", "port"}
    settings = {k: v for k, v in settings.items() if k in keys}
    db = PostgresqlDatabase(**settings)
    return db


def fetch_indexes(driver: Driver, db: Database, table_name: str):
    if driver is Driver.SQLITE:
        c = db.execute_sql(f"PRAGMA index_list({table_name});")
        res = c.fetchall()
        res = [i[1] for i in res]
        return res
    elif driver is Driver.POSTGRESQL:
        c = db.execute_sql(
            f"SELECT indexname "
            f" FROM pg_indexes"
            f" WHERE tablename = '{table_name}';"
        )
        return [i[0] for i in c.fetchall()]
    else:
        c = db.execute_sql(
            f"SELECT DISTINCT INDEX_NAME"
            f" FROM INFORMATION_SCHEMA.STATISTICS"
            f" WHERE TABLE_NAME = '{table_name}';"
        )
        return [i[0] for i in c.fetchall()]


def create_index_if_not_exists(driver: Driver, db: Database, table_name: str, index_name: str,
                               *args):
    indexes = fetch_indexes(driver, db, table_name)
    if index_name not in indexes:
        if driver is Driver.POSTGRESQL:
            # psql does'nt supports ``
            keys = ",".join([
                f'"{key}"'
                for key in args
            ])
            db.execute_sql(f'CREATE UNIQUE INDEX "{index_name}" '
                           f' ON "{table_name}" ({keys});')
        else:
            # mysql needs ``, others supports ``
            keys = ",".join([
                f'`{key}`'
                for key in args
            ])
            db.execute_sql(f'CREATE UNIQUE INDEX `{index_name}` '
                           f' ON `{table_name}` ({keys});')


def drop_index_if_exists(driver: Driver, db: Database, table_name: str, index_name: str):
    if driver is Driver.MYSQL:
        # mysql needs DROP ... ON
        indexes = fetch_indexes(driver, db, table_name)
        if index_name in indexes:
            db.execute_sql(f"DROP INDEX `{index_name}` ON `{table_name}`;")
        return
    else:
        db.execute_sql(f'DROP INDEX  IF EXISTS  "{index_name}";')
        return


def upgrade(driver: Driver, db: Database):
    create_index_if_not_exists(driver, db,
                               'dbbardata',
                               'dbbardata_symbol_exchange_interval_datetime',
                               "symbol", "exchange", "interval", "datetime",
                               )
    drop_index_if_exists(driver, db,
                         'dbbardata',
                         'dbbardata_datetime_interval_symbol_exchange')

    create_index_if_not_exists(driver, db,
                               'dbtickdata',
                               'dbtickdata_symbol_exchange_datetime',
                               "symbol", "exchange", "datetime",
                               )
    drop_index_if_exists(driver, db,
                         'dbtickdata',
                         'dbtickdata_datetime_symbol_exchange')

    print("upgrade succeed!")


def downgrade(driver: Driver, db: Database):
    create_index_if_not_exists(driver, db, 'dbbardata',
                               'dbbardata_datetime_interval_symbol_exchange',
                               "datetime", "interval", "symbol", "exchange",
                               )
    drop_index_if_exists(driver, db, "dbbardata", "dbbardata_symbol_exchange_interval_datetime")

    create_index_if_not_exists(driver, db, 'dbtickdata',
                               'dbtickdata_datetime_symbol_exchange',
                               "datetime", "symbol", "exchange",
                               )
    drop_index_if_exists(driver, db, "dbtickdata", "dbtickdata_symbol_exchange_datetime")

    print("downgrade succeed!")


def main():
    from vnpy.trader.setting import get_settings

    settings = get_settings("database.")
    driver = Driver(settings["driver"])
    if driver is not Driver.MONGODB:
        db = init(driver, settings=settings)
        upgrade(driver, db)
    else:
        raise NotImplementedError()


if __name__ == '__main__':
    main()
Member
加入于:
帖子: 31
声望: 0

感谢,总算不报这个错了。群里面并没有这样两个文件。只有两个sql文件,我执行了也没成功。

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