Python sqlalchemy.types.Date() Examples

def test_extract_expression(self, connection):
        meta = self.metadata
        table = Table("test", meta, Column("dt", DateTime), Column("d", Date))
        meta.create_all(connection)
        connection.execute(
            table.insert(),
            {
                "dt": datetime.datetime(2010, 5, 1, 12, 11, 10),
                "d": datetime.date(2010, 5, 1),
            },
        )
        rs = connection.execute(
            select([extract("year", table.c.dt), extract("month", table.c.d)])
        )
        row = rs.first()
        assert row[0] == 2010
        assert row[1] == 5
        rs.close() 

def fields_map(self, field_type):
        if field_type == "primary":
            return ID(stored=True, unique=True)
        type_map = {
            'date': types.Date,
            'datetime': types.DateTime,
            'boolean': types.Boolean,
            'integer': types.Integer,
            'float': types.Float
        }
        if isinstance(field_type, str):
            field_type = type_map.get(field_type, types.Text)

        if not isinstance(field_type, type):
            field_type = field_type.__class__

        if issubclass(field_type, (types.DateTime, types.Date)):
            return DATETIME(stored=True, sortable=True)
        elif issubclass(field_type, types.Integer):
            return NUMERIC(stored=True, numtype=int)
        elif issubclass(field_type, types.Float):
            return NUMERIC(stored=True, numtype=float)
        elif issubclass(field_type, types.Boolean):
            return BOOLEAN(stored=True)
        return TEXT(stored=True, analyzer=self.analyzer, sortable=False) 

def test_conn_execute(self, connection):
        from sqlalchemy.sql.expression import FunctionElement
        from sqlalchemy.ext.compiler import compiles

        class myfunc(FunctionElement):
            type = Date()

        @compiles(myfunc)
        def compile_(elem, compiler, **kw):
            return compiler.process(func.current_date())

        x = connection.execute(func.current_date()).scalar()
        y = connection.execute(func.current_date().select()).scalar()
        z = connection.scalar(func.current_date())
        q = connection.scalar(myfunc())

        assert (x == y == z == q) is True 

def test_interval_coercion(self):
        expr = column("bar", types.Interval) + column("foo", types.Date)
        eq_(expr.type._type_affinity, types.DateTime)

        expr = column("bar", types.Interval) * column("foo", types.Numeric)
        eq_(expr.type._type_affinity, types.Interval) 

def date_fixture(self, metadata):
        t = Table(
            "test_dates",
            metadata,
            Column("adate", Date),
            Column("atime1", Time),
            Column("atime2", Time),
            Column("adatetime", DateTime),
            Column("adatetimeoffset", DATETIMEOFFSET),
        )

        d1 = datetime.date(2007, 10, 30)
        t1 = datetime.time(11, 2, 32)
        d2 = datetime.datetime(2007, 10, 30, 11, 2, 32)
        return t, (d1, t1, d2) 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def fresh_tushare_stock_fund_holdings(year, quarter):
    table_name = 'tushare_stock_fund_holdings'
    logging.info("更新 %s 表%s年%s季度基金持股信息开始", table_name, year, quarter)
    has_table = engine_md.has_table(table_name)
    tushare_fund_holdings_indicator_param_list = [
        ('ts_code', String(20)),
        ('sec_name', String(20)),
        ('end_date', Date),
        ('nums', DOUBLE),
        ('nlast', DOUBLE),
        ('count', DOUBLE),
        ('clast', DOUBLE),
        ('amount', DOUBLE),
        ('ratio', DOUBLE),
    ]
    tushare_fund_holdings_dtype = {key: val for key, val in tushare_fund_holdings_indicator_param_list}
    data_df_list, data_count, all_data_count, = [], 0, 0
    data_df = invoke_fund_holdings(year, quarter)
    ts_code_list = []
    for i in data_df.code:
        if i[0] == '6':
            sh = i + '.SH'
            ts_code_list.append(sh)
        else:
            sz = i + '.SZ'
            ts_code_list.append(sz)
    data_df.code = ts_code_list
    data_df = data_df.rename(columns={'code': 'ts_code', 'name': 'sec_name', 'date': 'end_date'})
    bunch_insert_on_duplicate_update(data_df, table_name, engine_md, tushare_fund_holdings_dtype)
    logging.info("%s年%s季度 %s 更新 %d 条基金持股信息", year, quarter, table_name, all_data_count) 

def fund_nav_df_2_sql(table_name, fund_nav_df, engine_md, is_append=True):
    col_name_param_list = [
        ('NAV_DATE', Date),
        ('NAV', DOUBLE),
        ('NAV_ACC', DOUBLE),

    ]
    col_name_dic = {col_name.upper(): col_name.lower() for col_name, _ in col_name_param_list}
    dtype = {col_name.lower(): val for col_name, val in col_name_param_list}
    dtype['wind_code'] = String(200)
    dtype['trade_date'] = Date
    #    print('reorg dfnav data[%d, %d]' % fund_nav_df.shape)
    try:
        fund_nav_df['NAV_DATE'] = pd.to_datetime(fund_nav_df['NAV_DATE']).apply(lambda x: x.date())
    except Exception as exp:
        logger.exception(str(fund_nav_df['NAV_DATE']))
        return None
    trade_date_s = pd.to_datetime(fund_nav_df.index)
    trade_date_latest = trade_date_s.max().date()
    fund_nav_df['trade_date'] = trade_date_s
    fund_nav_df.rename(columns=col_name_dic, inplace=True)
    # fund_nav_df['trade_date'] = trade_date_s
    fund_nav_df.set_index(['wind_code', 'trade_date'], inplace=True)
    fund_nav_df.reset_index(inplace=True)
    # action_str = 'append' if is_append else 'replace'
    #    print('df--> sql fundnav table if_exists="%s"' % action_str)
    bunch_insert_on_duplicate_update(fund_nav_df, table_name, engine_md, dtype=dtype)

    # fund_nav_df.to_sql(table_name, engine_md, if_exists=action_str, index_label=['wind_code', 'trade_date'],
    #                    dtype={
    #                        'wind_code': String(200),
    #                        'nav_date': Date,
    #                        'trade_date': Date,
    #                    })  # , index=False
    logger.info('%d data inserted', fund_nav_df.shape[0])
    return trade_date_latest 

def test_python_type(self):
        eq_(types.Integer().python_type, int)
        eq_(types.Numeric().python_type, decimal.Decimal)
        eq_(types.Numeric(asdecimal=False).python_type, float)
        eq_(types.LargeBinary().python_type, util.binary_type)
        eq_(types.Float().python_type, float)
        eq_(types.Interval().python_type, datetime.timedelta)
        eq_(types.Date().python_type, datetime.date)
        eq_(types.DateTime().python_type, datetime.datetime)
        eq_(types.String().python_type, str)
        eq_(types.Unicode().python_type, util.text_type)
        eq_(types.Enum("one", "two", "three").python_type, str)

        assert_raises(
            NotImplementedError, lambda: types.TypeEngine().python_type
        ) 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def test_native_datetime(self):
        dbapi = testing.db.dialect.dbapi
        connect_args = {
            "detect_types": dbapi.PARSE_DECLTYPES | dbapi.PARSE_COLNAMES
        }
        engine = engines.testing_engine(
            options={"connect_args": connect_args, "native_datetime": True}
        )
        t = Table(
            "datetest",
            MetaData(),
            Column("id", Integer, primary_key=True),
            Column("d1", Date),
            Column("d2", sqltypes.TIMESTAMP),
        )
        t.create(engine)
        try:
            with engine.begin() as conn:
                conn.execute(
                    t.insert(),
                    {
                        "d1": datetime.date(2010, 5, 10),
                        "d2": datetime.datetime(2010, 5, 10, 12, 15, 25),
                    },
                )
                row = conn.execute(t.select()).first()
                eq_(
                    row,
                    (
                        1,
                        datetime.date(2010, 5, 10),
                        datetime.datetime(2010, 5, 10, 12, 15, 25),
                    ),
                )
                r = conn.execute(func.current_date()).scalar()
                assert isinstance(r, util.string_types)
        finally:
            t.drop(engine)
            engine.dispose() 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def test_interval_coercion(self):
        expr = column("bar", postgresql.INTERVAL) + column("foo", types.Date)
        eq_(expr.type._type_affinity, types.DateTime)

        expr = column("bar", postgresql.INTERVAL) * column(
            "foo", types.Numeric
        )
        eq_(expr.type._type_affinity, types.Interval)
        assert isinstance(expr.type, postgresql.INTERVAL) 

def render_literal_value(self, value, type_):
        value = super(ClickHouseCompiler, self).render_literal_value(value, type_)
        if isinstance(type_, sqltypes.DateTime):
            value = 'toDateTime(%s)' % value
        if isinstance(type_, sqltypes.Date):
            value = 'toDate(%s)' % value
        return value 

def test_string_dates_passed_raise(self, connection):
        assert_raises(
            exc.StatementError,
            connection.execute,
            select([1]).where(bindparam("date", type_=Date)),
            date=str(datetime.date(2007, 10, 30)),
        ) 

def test_cant_parse_datetime_message(self, connection):
        for (typ, disp) in [
            (Time, "time"),
            (DateTime, "datetime"),
            (Date, "date"),
        ]:
            assert_raises_message(
                ValueError,
                "Couldn't parse %s string." % disp,
                lambda: connection.execute(
                    text("select 'ASDF' as value").columns(value=typ)
                ).scalar(),
            ) 

def __init__(self):
        self._types = {
            # Django internal type => SQLAlchemy type
            'ArrayField': SA_ARRAY,
            'AutoField': sa_types.Integer,
            'BigAutoField': sa_types.BigInteger,
            'BigIntegerField': sa_types.BigInteger,
            'BooleanField': sa_types.Boolean,
            'CharField': sa_types.String,
            'DateField': sa_types.Date,
            'DateTimeField': sa_types.DateTime,
            'DecimalField': sa_types.Numeric,
            'DurationField': sa_types.Interval,
            'FileField': sa_types.String,
            'FilePathField': sa_types.String,
            'FloatField': sa_types.Float,
            'GenericIPAddressField': sa_types.String,
            'IntegerField': sa_types.Integer,
            'JSONField': SA_JSONB,
            'NullBooleanField': sa_types.Boolean,
            'PointField': Geometry,
            'PositiveIntegerField': sa_types.Integer,
            'PositiveSmallIntegerField': sa_types.SmallInteger,
            'SlugField': sa_types.String,
            'SmallIntegerField': sa_types.SmallInteger,
            'TextField': sa_types.Text,
            'TimeField': sa_types.Time,
            'UUIDField': SA_UUID,
            # TODO: Add missing GIS fields
        } 

def get_type(data_type):
    type_map = {
        "bytes": types.LargeBinary,
        "boolean": types.Boolean,
        "date": types.Date,
        "datetime": types.DateTime,
        "double": types.Numeric,
        "text": types.String,
        "keyword": types.String,
        "integer": types.Integer,
        "half_float": types.Float,
        "geo_point": types.String,
        # TODO get a solution for nested type
        "nested": types.String,
        # TODO get a solution for object
        "object": types.BLOB,
        "long": types.BigInteger,
        "float": types.Float,
        "ip": types.String,
    }
    type_ = type_map.get(data_type)
    if not type_:
        logger.warning(f"Unknown type found {data_type} reverting to string")
        type_ = types.String
    return type_ 

def fields_map(self, field_type):
        if field_type == "primary":
            return {'type': 'keyword'}

        type_map = {
            'date': types.Date,
            'datetime': types.DateTime,
            'boolean': types.Boolean,
            'integer': types.Integer,
            'float': types.Float,
            'binary': types.Binary
        }
        if isinstance(field_type, str):
            field_type = type_map.get(field_type, types.Text)

        if not isinstance(field_type, type):
            field_type = field_type.__class__

        if issubclass(field_type, (types.DateTime, types.Date)):
            return {'type': 'date'}
        elif issubclass(field_type, types.Integer):
            return {'type': 'long'}
        elif issubclass(field_type, types.Float):
            return {'type': 'float'}
        elif issubclass(field_type, types.Boolean):
            return {'type': 'boolean'}
        elif issubclass(field_type, types.Binary):
            return {'type': 'binary'}
        return {'type': 'string'}


# https://medium.com/@federicopanini/elasticsearch-6-0-removal-of-mapping-types-526a67ff772 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def test_should_date_convert_string():
    assert get_field(types.Date()).type == graphene.String 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def import_jq_stock_info(chain_param=None, refresh=False):
    """ 获取全市场股票代码及名称
    """
    table_name = TABLE_NAME
    logging.info("更新 %s 开始", table_name)
    # has_table = engine_md.has_table(table_name)
    param_list = [
        ('jq_code', String(20)),
        ('display_name', String(20)),
        ('name', String(20)),
        ('start_date', Date),
        ('end_date', Date),
    ]
    # 设置 dtype
    dtype = {key: val for key, val in param_list}

    # 数据提取
    # types: list: 用来过滤securities的类型, list元素可选:
    # 'stock', 'fund', 'index', 'futures', 'etf', 'lof', 'fja', 'fjb'。types为空时返回所有股票, 不包括基金,指数和期货
    # date: 日期, 一个字符串或者 [datetime.datetime]/[datetime.date] 对象,
    # 用于获取某日期还在上市的股票信息. 默认值为 None, 表示获取所有日期的股票信息
    stock_info_all_df = get_all_securities()
    stock_info_all_df.index.rename('jq_code', inplace=True)
    stock_info_all_df.reset_index(inplace=True)

    logging.info('%s stock data will be import', stock_info_all_df.shape[0])
    data_count = bunch_insert_on_duplicate_update(
        stock_info_all_df, table_name, engine_md, dtype=dtype,
        myisam_if_create_table=True, primary_keys=['jq_code'], schema=config.DB_SCHEMA_MD)
    logging.info("更新 %s 完成 存量数据 %d 条", table_name, data_count)
    # 更新 map 表
    update_from_info_table(table_name) 

def import_info_table(type_name, insert_db=True) -> pd.DataFrame:
    """
    调用 get_all_securities 获取指定 type 的信息
    type: 'stock', 'fund', 'index', 'futures', 'etf', 'lof', 'fja', 'fjb'。types为空时返回所有股票, 不包括基金,指数和期货
    :param type_name:
    :return:
    """
    table_name = f'jq_{type_name}_info'
    logger.info("更新 %s 开始", table_name)
    # has_table = engine_md.has_table(table_name)
    param_list = [
        ('jq_code', String(20)),
        ('display_name', String(20)),
        ('name', String(20)),
        ('start_date', Date),
        ('end_date', Date),
    ]
    # 设置 dtype
    dtype = {key: val for key, val in param_list}

    # 数据提取
    # types: list: 用来过滤securities的类型, list元素可选:
    # 'stock', 'fund', 'index', 'futures', 'etf', 'lof', 'fja', 'fjb'。types为空时返回所有股票, 不包括基金,指数和期货
    # date: 日期, 一个字符串或者 [datetime.datetime]/[datetime.date] 对象,
    # 用于获取某日期还在上市的股票信息. 默认值为 None, 表示获取所有日期的股票信息
    stock_info_all_df = get_all_securities(types=type_name)
    stock_info_all_df.index.rename('jq_code', inplace=True)
    stock_info_all_df.reset_index(inplace=True)

    if insert_db:
        logger.info('%s 数据将被导入', stock_info_all_df.shape[0])
        data_count = bunch_insert_p(stock_info_all_df, table_name=table_name, dtype=dtype, primary_keys=['jq_code'])
        logger.info("更新 %s 完成 存量数据 %d 条", table_name, data_count)

    return stock_info_all_df 

def import_jq_trade_date(chain_param=None):
    """
    插入股票日线数据到最近一个工作日-1。
    如果超过 BASE_LINE_HOUR 时间，则获取当日的数据
    :return:
    """
    logger.info("更新 %s 开始", TABLE_NAME)
    # 判断表是否已经存在
    has_table = engine_md.has_table(TABLE_NAME)
    if has_table:
        trade_day_max = execute_scalar(f'SELECT max(trade_date) FROM {TABLE_NAME}', engine_md)
        trade_date_list = get_trade_days(start_date=(trade_day_max + timedelta(days=1)),
                                         end_date=(date.today() + timedelta(days=366)))
    else:
        trade_date_list = get_all_trade_days()

    date_count = len(trade_date_list)
    if date_count == 0:
        logger.info("没有更多的交易日数据可被导入")
        return

    logger.info("%d 条交易日数据将被导入", date_count)
    trade_date_df = pd.DataFrame({'trade_date': trade_date_list})
    bunch_insert_on_duplicate_update(trade_date_df, TABLE_NAME, engine_md,
                                     dtype={'trade_date': Date},
                                     myisam_if_create_table=True,
                                     primary_keys=['trade_date'], schema=config.DB_SCHEMA_MD)
    logger.info('%d 条交易日数据导入 %s 完成', date_count, TABLE_NAME) 

def save_future_daily_df_list(data_df_list):
    """将期货历史数据保存的数据库"""
    data_df_count = len(data_df_list)
    if data_df_count > 0:
        logger.info('merge data with %d df', data_df_count)
        data_df = pd.concat(data_df_list)
        data_count = data_df.shape[0]
        data_df.to_sql('ifind_future_daily', engine_md, if_exists='append', index=False,
                       dtype={
                           'ths_code': String(20),
                           'time': Date,
                           'preClose': String(20),
                           'open': DOUBLE,
                           'high': DOUBLE,
                           'low': DOUBLE,
                           'close': DOUBLE,
                           'volume': DOUBLE,
                           'amount': DOUBLE,
                           'avgPrice': DOUBLE,
                           'change': DOUBLE,
                           'changeRatio': DOUBLE,
                           'preSettlement': DOUBLE,
                           'settlement': DOUBLE,
                           'change_settlement': DOUBLE,
                           'chg_settlement': DOUBLE,
                           'openInterest': DOUBLE,
                           'positionChange': DOUBLE,
                           'amplitude': DOUBLE,
                       })
        logger.info("更新 wind_future_daily 结束 %d 条记录被更新", data_count)
    else:
        logger.info("更新 wind_future_daily 结束 0 条记录被更新") 

def _compare_type_affinity(self, other):
        return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) 

def import_tushare_stock_info(chain_param=None, refresh=False):
    """ 获取全市场股票代码及名称
    """
    table_name = 'tushare_stock_info'
    logging.info("更新 %s 开始", table_name)
    has_table = engine_md.has_table(table_name)
    tushare_indicator_param_list = [
        ('ts_code', String(20)),
        ('symbol', String(20)),
        ('name', String(40)),
        ('area', String(100)),
        ('industry', String(200)),
        ('fullname', String(100)),
        ('enname', String(200)),
        ('market', String(100)),
        ('exchange', String(20)),
        ('curr_type', String(20)),
        ('list_status', String(20)),
        ('list_date', Date),
        ('delist_date', Date),
        ('is_hs', String(20)),
    ]
    #     # 获取列属性名，以逗号进行分割 "ipo_date,trade_code,mkt,exch_city,exch_eng"
    param = ",".join([key for key, _ in tushare_indicator_param_list])
    # 设置 dtype
    dtype = {key: val for key, val in tushare_indicator_param_list}
    dtype['ts_code'] = String(20)

    # 数据提取

    stock_info_all_df = pro.stock_basic(exchange='',
                                        fields='ts_code,symbol,name,area,industry,fullname,enname,market,exchange,curr_type,list_status,list_date,delist_date,is_hs,is_hs,is_hs')

    logging.info('%s stock data will be import', stock_info_all_df.shape[0])
    data_count = bunch_insert_on_duplicate_update(
        stock_info_all_df, table_name, engine_md, dtype=dtype,
        myisam_if_create_table=True, primary_keys=['ts_code'], schema=config.DB_SCHEMA_MD)
    logging.info("更新 %s 完成 存量数据 %d 条", table_name, data_count)

    # 更新 code_mapping 表
    # update_from_info_table(table_name)