Python sqlalchemy.types.String() Examples

def visit_column_comment(element, compiler, **kw):
    ddl = "COMMENT ON COLUMN {table_name}.{column_name} IS {comment}"
    comment = (
        compiler.sql_compiler.render_literal_value(
            element.comment, sqltypes.String()
        )
        if element.comment is not None
        else "NULL"
    )

    return ddl.format(
        table_name=format_table_name(
            compiler, element.table_name, element.schema
        ),
        column_name=format_column_name(compiler, element.column_name),
        comment=comment,
    ) 

def _render_server_default_for_compare(metadata_default,
                                       metadata_col, autogen_context):
    rendered = _user_defined_render(
        "server_default", metadata_default, autogen_context)
    if rendered is not False:
        return rendered

    if isinstance(metadata_default, sa_schema.DefaultClause):
        if isinstance(metadata_default.arg, compat.string_types):
            metadata_default = metadata_default.arg
        else:
            metadata_default = str(metadata_default.arg.compile(
                dialect=autogen_context.dialect))
    if isinstance(metadata_default, compat.string_types):
        if metadata_col.type._type_affinity is sqltypes.String:
            metadata_default = re.sub(r"^'|'$", "", metadata_default)
            return repr(metadata_default)
        else:
            return metadata_default
    else:
        return None 

def _mysql_colspec(
    compiler, nullable, server_default, type_, autoincrement, comment
):
    spec = "%s %s" % (
        compiler.dialect.type_compiler.process(type_),
        "NULL" if nullable else "NOT NULL",
    )
    if autoincrement:
        spec += " AUTO_INCREMENT"
    if server_default is not False and server_default is not None:
        spec += " DEFAULT %s" % _render_value(compiler, server_default)
    if comment:
        spec += " COMMENT %s" % compiler.sql_compiler.render_literal_value(
            comment, sqltypes.String()
        )

    return spec 

def test_should_enum_choice_convert_enum():
    class TestEnum(enum.Enum):
        es = u"Spanish"
        en = u"English"

    field = get_field(ChoiceType(TestEnum, impl=types.String()))
    graphene_type = field.type
    assert issubclass(graphene_type, graphene.Enum)
    assert graphene_type._meta.name == "MODEL_COLUMN"
    assert graphene_type._meta.enum.__members__["es"].value == "Spanish"
    assert graphene_type._meta.enum.__members__["en"].value == "English" 

def with_variant(self, type_, dialect_name):
        """Produce a new type object that will utilize the given
        type when applied to the dialect of the given name.

        e.g.::

            from sqlalchemy.types import String
            from sqlalchemy.dialects import mysql

            s = String()

            s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

        The construction of :meth:`.TypeEngine.with_variant` is always
        from the "fallback" type to that which is dialect specific.
        The returned type is an instance of :class:`.Variant`, which
        itself provides a :meth:`.Variant.with_variant`
        that can be called repeatedly.

        :param type_: a :class:`.TypeEngine` that will be selected
         as a variant from the originating type, when a dialect
         of the given name is in use.
        :param dialect_name: base name of the dialect which uses
         this type. (i.e. ``'postgresql'``, ``'mysql'``, etc.)

        .. versionadded:: 0.7.2

        """
        return Variant(self, {dialect_name: to_instance(type_)}) 

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 test_should_intenum_choice_convert_enum():
    class TestEnum(enum.IntEnum):
        one = 1
        two = 2

    field = get_field(ChoiceType(TestEnum, impl=types.String()))
    graphene_type = field.type
    assert issubclass(graphene_type, graphene.Enum)
    assert graphene_type._meta.name == "MODEL_COLUMN"
    assert graphene_type._meta.enum.__members__["one"].value == 1
    assert graphene_type._meta.enum.__members__["two"].value == 2 

def define_tables(cls, metadata):
        Table('test_table', metadata,
              Column('id', Integer, primary_key=True),
              Column('data', String(50))
              ) 

def bind_processor(self, dialect):
            if dialect._cx_oracle_with_unicode:
                def process(value):
                    if value is None:
                        return value
                    else:
                        return unicode(value)
                return process
            else:
                return super(
                    _NativeUnicodeMixin, self).bind_processor(dialect)

    # we apply a connection output handler that returns
    # unicode in all cases, so the "native_unicode" flag
    # will be set for the default String.result_processor. 

def get_columns(self, connection, table_name, schema=None, **kw):
        SQL_COLS = """
            SELECT CAST(a.attname AS VARCHAR(128)) as name,
                   a.atttypid as typeid,
                   not a.attnotnull as nullable,
                   a.attcolleng as length,
                   a.format_type
            FROM _v_relation_column a
            WHERE a.name = :tablename
            ORDER BY a.attnum
        """
        s = text(SQL_COLS,
                 bindparams=[bindparam('tablename', type_=sqltypes.String)],
                 typemap={'name': NAME,
                          'typeid': sqltypes.Integer,
                          'nullable': sqltypes.Boolean,
                          'length': sqltypes.Integer,
                          'format_type': sqltypes.String,
                          })
        c = connection.execute(s, tablename=table_name)
        rows = c.fetchall()
        # format columns
        columns = []
        for name, typeid, nullable, length, format_type in rows:
            coltype_class, has_length = oid_datatype_map[typeid]
            if coltype_class is sqltypes.Numeric:
                precision, scale = re.match(
                    r'numeric\((\d+),(\d+)\)', format_type).groups()
                coltype = coltype_class(int(precision), int(scale))
            elif has_length:
                coltype = coltype_class(length)
            else:
                coltype = coltype_class()
            columns.append({
                'name': name,
                'type': coltype,
                'nullable': nullable,
            })
        return columns 

def load_dialect_impl(self, dialect):
        if dialect.name == "mysql":
            return dialect.type_descriptor(
                MySQLString(
                    charset="utf8mb4", collation="utf8mb4_unicode_ci", length=self.impl.length
                )
            )
        else:
            return dialect.type_descriptor(String(length=self.impl.length)) 

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_variety_info():
    """保存期货交易所品种信息，一次性数据导入，以后基本上不需要使用了"""
    table_name = 'ifind_future_variety_info'
    exchange_list = [
        '上海期货交易所',
        '大连商品交易所',
        '郑州商品交易所',
        '中国金融期货交易所',
        '纽约商业期货交易所(NYMEX)',
        '纽约商品交易所(COMEX)',
        # '纽约期货交易所(NYBOT)',  # 暂无数据
        '芝加哥期货交易所(CBOT)',
        # '洲际交易所(ICE)',  # 暂无数据
        '伦敦金属交易所(LME)',
        '马来西亚衍生品交易所（BMD）',
        '新加坡证券交易所（SGX）',
    ]
    # 设置 dtype
    dtype = {
        'exchange': String(20),
        'ID': String(20),
        'SECURITY_NAME': String(20),
    }
    data_df_list = []
    data_count = len(exchange_list)
    try:
        for num, exchange in enumerate(exchange_list):
            logger.debug("%d/%d) %s 获取交易品种信息", num, data_count, exchange)
            data_df = invoker.THS_DataPool('variety', exchange, 'variety:Y,id:Y')
            data_df['exchange'] = exchange
            data_df_list.append(data_df)
    finally:
        if len(data_df_list) > 0:
            tot_data_df = pd.concat(data_df_list)
            # tot_data_df.to_sql('ifind_variety_info', engine_md, index=False, if_exists='append', dtype=dtype)
            tot_data_count = bunch_insert_on_duplicate_update(tot_data_df, table_name, engine_md, dtype=dtype)
        else:
            tot_data_count = 0

        logger.info('保存交易所品种信息完成， %d条数据被保存', tot_data_count) 

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 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) 

def sqltype_to_stdtype(sqltype):
    import sqlalchemy.types as sqltypes
    if isinstance(sqltype, (sqltypes.VARCHAR, sqltypes.CHAR, sqltypes.TEXT, sqltypes.Enum, sqltypes.String)):
        return _STRING_TYPE
    if isinstance(sqltype, (sqltypes.DATETIME, sqltypes.DATE, sqltypes.TIME, sqltypes.TIMESTAMP)):
        return _DATE_TYPE
    if isinstance(sqltype, (sqltypes.INTEGER, sqltypes.BIGINT, sqltypes.SMALLINT, sqltypes.Integer)):
        return _INTEGER_TYPE
    if isinstance(sqltype, (sqltypes.REAL, sqltypes.DECIMAL, sqltypes.NUMERIC, sqltypes.FLOAT)):
        return _DECIMAL_TYPE
    if isinstance(sqltype, sqltypes.BOOLEAN):
        return _BOOLEAN_TYPE 

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

def convert_scalar_list_to_list(type, column, registry=None):
    return List(String) 

def convert_sqlalchemy_hybrid_method(hybrid_prop, resolver, **field_kwargs):
    if 'type' not in field_kwargs:
        # TODO The default type should be dependent on the type of the property propety.
        field_kwargs['type'] = String

    return Field(
        resolver=resolver,
        **field_kwargs
    ) 

def _resolve_type(self, t, **kw):
        """
        Resolve types for String, Numeric, Date/Time, etc. columns
        """
        t = self.normalize_name(t)
        if t in ischema_names:
            #print(t,ischema_names[t])
            t = ischema_names[t]
            
            if issubclass(t, sqltypes.String):
                return t(length=kw['length']/2 if kw['chartype']=='UNICODE' else kw['length'],\
                            charset=kw['chartype'])

            elif issubclass(t, sqltypes.Numeric):
                return t(precision=kw['prec'], scale=kw['scale'])

            elif issubclass(t, sqltypes.Time) or issubclass(t, sqltypes.DateTime):
                #Timezone
                tz=kw['fmt'][-1]=='Z'

                #Precision                
                prec = kw['fmt']    
                #For some timestamps and dates, there is no precision, or indicatd in scale
                prec = prec[prec.index('(') + 1: prec.index(')')] if '(' in prec else 0
                prec = kw['scale'] if prec=='F' else int(prec)

                #prec = int(prec[prec.index('(') + 1: prec.index(')')]) if '(' in prec else 0
                return t(precision=prec,timezone=tz)

            elif issubclass(t, sqltypes.Interval):
                return t(day_precision=kw['prec'],second_precision=kw['scale'])

            else:
                return t() # For types like Integer, ByteInt

        return ischema_names[None] 

def test_sql_alchemy_subclass_column_types():
    class F(FilterSet):
        ALLOWED_FILTERS = {types.Integer: ['eq', 'gt']}

        class Meta:
            abstract = True

    class MyNVARCHAR(types.NVARCHAR):
        pass

    class TestModel(Base):
        __tablename__ = 'test'

        id = Column(types.SmallInteger, primary_key=True, autoincrement=True)
        text = Column(MyNVARCHAR)

    class TestFilter(F):
        EXTRA_ALLOWED_FILTERS = {types.String: ['eq']}

        class Meta:
            model = TestModel
            fields = {'id': [...], 'text': [...]}

    filter_fields = set(TestFilter._meta.fields)
    ok = {'id', 'id_gt', 'text', 'text_is_null', 'and', 'not', 'or'}

    assert filter_fields == ok 

def assert_tables_equal(self, table, reflected_table, strict_types=False):
        assert len(table.c) == len(reflected_table.c)
        for c, reflected_c in zip(table.c, reflected_table.c):
            eq_(c.name, reflected_c.name)
            assert reflected_c is reflected_table.c[c.name]
            eq_(c.primary_key, reflected_c.primary_key)
            eq_(c.nullable, reflected_c.nullable)

            if strict_types:
                msg = "Type '%s' doesn't correspond to type '%s'"
                assert isinstance(reflected_c.type, type(c.type)), \
                    msg % (reflected_c.type, c.type)
            else:
                self.assert_types_base(reflected_c, c)

            if isinstance(c.type, sqltypes.String):
                eq_(c.type.length, reflected_c.type.length)

            eq_(
                set([f.column.name for f in c.foreign_keys]),
                set([f.column.name for f in reflected_c.foreign_keys])
            )
            if c.server_default:
                assert isinstance(reflected_c.server_default,
                                  schema.FetchedValue)

        assert len(table.primary_key) == len(reflected_table.primary_key)
        for c in table.primary_key:
            assert reflected_table.primary_key.columns[c.name] is not None 

def test_varchar_reflection(self):
        typ = self._type_round_trip(sql_types.String(52))[0]
        assert isinstance(typ, sql_types.String)
        eq_(typ.length, 52) 

def define_tables(cls, metadata):
        Table('test_table', metadata,
              Column('id', Integer, primary_key=True),
              Column('data', String(50))
              ) 

def with_variant(self, type_, dialect_name):
        """Produce a new type object that will utilize the given
        type when applied to the dialect of the given name.

        e.g.::

            from sqlalchemy.types import String
            from sqlalchemy.dialects import mysql

            s = String()

            s = s.with_variant(mysql.VARCHAR(collation='foo'), 'mysql')

        The construction of :meth:`.TypeEngine.with_variant` is always
        from the "fallback" type to that which is dialect specific.
        The returned type is an instance of :class:`.Variant`, which
        itself provides a :meth:`.Variant.with_variant`
        that can be called repeatedly.

        :param type_: a :class:`.TypeEngine` that will be selected
         as a variant from the originating type, when a dialect
         of the given name is in use.
        :param dialect_name: base name of the dialect which uses
         this type. (i.e. ``'postgresql'``, ``'mysql'``, etc.)

        .. versionadded:: 0.7.2

        """
        return Variant(self, {dialect_name: to_instance(type_)}) 

def insert_into_db(data_df_list, engine_md):
    data_count = len(data_df_list)
    table_name = 'wind_stock_tick'
    has_table = engine_md.has_table(table_name)
    param_list = [
        ('datetime', DateTime),
        ('open', DOUBLE),
        ('high', DOUBLE),
        ('low', DOUBLE),
        ('close', DOUBLE),
        ('ask1', DOUBLE),
        ('bid1', DOUBLE),
        ('asize1', DOUBLE),
        ('bsize1', DOUBLE),
        ('volume', DOUBLE),
        ('amount', DOUBLE),
        ('preclose', DOUBLE),
    ]
    dtype = {key: val for key, val in param_list}
    dtype['wind_code'] = String(20)
    if data_count > 0:
        data_df_all = pd.concat(data_df_list)
        data_df_all.index.rename('datetime', inplace=True)
        data_df_all.reset_index(inplace=True)
        bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md, dtype=dtype)
        logger.info('%d data imported', data_df_all.shape[0])
        if not has_table and engine_md.has_table(table_name):
            alter_table_2_myisam(engine_md, [table_name])
            build_primary_key([table_name])

    return data_count 

def bind_processor(self, dialect):
            if dialect._cx_oracle_with_unicode:
                def process(value):
                    if value is None:
                        return value
                    else:
                        return unicode(value)
                return process
            else:
                return super(
                    _NativeUnicodeMixin, self).bind_processor(dialect)

    # we apply a connection output handler that returns
    # unicode in all cases, so the "native_unicode" flag
    # will be set for the default String.result_processor. 

def new_wikipedia_tag(self, languages):
        sitelinks = {code[:-4]: link['title']
                     for code, link in self.item.sitelinks().items()
                     if code.endswith('wiki')}

        for lang in languages:
            code = lang if isinstance(lang, str) else lang.wikimedia_language_code
            if code in sitelinks:
                return (code, sitelinks[code])
        return (None, None)

# class ItemCandidateTag(Base):
#     __tablename__ = 'item_candidate_tag'
#     __table_args__ = (
#         ForeignKeyConstraint(['item_id', 'osm_id', 'osm_type'],
#                              [ItemCandidate.item_id,
#                               ItemCandidate.osm_id,
#                               ItemCandidate.osm_type]),
#     )
#
#     item_id = Column(Integer, primary_key=True)
#     osm_id = Column(BigInteger, primary_key=True)
#     osm_type = Column(osm_type_enum, primary_key=True)
#     k = Column(String, primary_key=True)
#     v = Column(String, primary_key=True)
#
#     item_candidate = relationship(ItemCandidate,
#                                   backref=backref('tag_table', lazy='dynamic')) 

def _render_server_default_for_compare(
    metadata_default, metadata_col, autogen_context
):
    rendered = _user_defined_render(
        "server_default", metadata_default, autogen_context
    )
    if rendered is not False:
        return rendered

    if isinstance(metadata_default, sa_schema.DefaultClause):
        if isinstance(metadata_default.arg, compat.string_types):
            metadata_default = metadata_default.arg
        else:
            metadata_default = str(
                metadata_default.arg.compile(
                    dialect=autogen_context.dialect,
                    compile_kwargs={"literal_binds": True},
                )
            )
    if isinstance(metadata_default, compat.string_types):
        if metadata_col.type._type_affinity is sqltypes.String:
            metadata_default = re.sub(r"^'|'$", "", metadata_default)
            return repr(metadata_default)
        else:
            return metadata_default
    else:
        return None