Python sqlalchemy.types.DECIMAL Examples

def str_to_sqltype(expr):
    import re
    import sqlalchemy.types as sqltypes
    norm_expr = expr.lower()
    if norm_expr.startswith('integer'):
        match_result = re.match(r'integer\((\d+)\)', norm_expr)
        if match_result is not None:
            return sqltypes.BIGINT() if int(match_result.group(1)) > 11 else sqltypes.INTEGER()
        return sqltypes.BIGINT()
    if norm_expr == 'decimal':
        return sqltypes.DECIMAL()
    if norm_expr == 'date':
        return sqltypes.DATETIME()
    if norm_expr == 'bool' or norm_expr == 'boolean':
        return sqltypes.BOOLEAN()
    if norm_expr.startswith('string'):
        match_result = re.match(r'string\((\d+)\)', norm_expr)
        if match_result is not None:
            maxlen = int(match_result.group(1))
            return sqltypes.VARCHAR(maxlen) if maxlen < 65536 else sqltypes.TEXT
        return sqltypes.TEXT()
    raise RuntimeError("Unsupported data type [" + expr + "]") 

def load_dialect_impl(self, dialect):
        if dialect.name == 'mysql':
            return dialect.type_descriptor(
                types.DECIMAL(precision=20,
                              scale=6,
                              asdecimal=True))
        return dialect.type_descriptor(self.impl) 

def compare_against_backend(self, dialect, conn_type):
        if dialect.name == 'mysql':
            return issubclass(type(conn_type), types.DECIMAL)
        return issubclass(type(conn_type), type(self.impl)) 

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 stdtype_to_sqltype(stdtype):
    import sqlalchemy.types as sqltypes
    if isinstance(stdtype, stdtypes.StringType):
        return sqltypes.VARCHAR(length=stdtype.max_len) if 0 < stdtype.max_len < 65536 else sqltypes.TEXT()
    if isinstance(stdtype, stdtypes.BoolType):
        return sqltypes.BOOLEAN()
    if isinstance(stdtype, stdtypes.DateType):
        return sqltypes.DATE() if stdtype.only_date else sqltypes.TIMESTAMP()
    if isinstance(stdtype, stdtypes.IntegerType):
        return sqltypes.BIGINT() if stdtype.length > 11 else sqltypes.INTEGER()
    if isinstance(stdtype, stdtypes.DecimalType):
        return sqltypes.DECIMAL()
    if isinstance(stdtype, stdtypes.ArrayType):
        return sqltypes.ARRAY(item_type=stdtype.item_type) 

def __init__(self, precision=None, scale=None, asdecimal=True, **kw):
        """Construct a DECIMAL.

        :param precision: Total digits in this number.  If scale and precision
          are both None, values are stored to limits allowed by the server.

        :param scale: The number of digits after the decimal point.

        """
        super(DECIMAL, self).__init__(precision=precision, scale=scale,
                                      asdecimal=asdecimal, **kw) 

def test_decimal(self):
        for t in ['dec', 'decimal', 'numeric']:
            self._test(t, sqltypes.DECIMAL) 

def test_decimal_plain(self):
        self.assert_compile(types.DECIMAL(), "DECIMAL") 

def test_decimal_precision(self):
        self.assert_compile(types.DECIMAL(2), "DECIMAL(2)") 

def test_decimal_scale(self):
        self.assert_compile(types.DECIMAL(2, 4), "DECIMAL(2, 4)") 

def load_dialect_impl(self, dialect):
        if dialect.name == 'mysql':
            return dialect.type_descriptor(
                types.DECIMAL(precision=20,
                              scale=6,
                              asdecimal=True))
        return dialect.type_descriptor(self.impl) 

def __init__(self, precision=None, scale=None, asdecimal=True, **kw):
        """Construct a DECIMAL.

        :param precision: Total digits in this number.  If scale and precision
          are both None, values are stored to limits allowed by the server.

        :param scale: The number of digits after the decimal point.

        """
        super(DECIMAL, self).__init__(precision=precision, scale=scale,
                                      asdecimal=asdecimal, **kw) 

def get_columns(self, connection, table_name, schema=None, **kwargs):
        schema = schema or self.default_schema_name

        result = connection.execute(
            sql.text(
                """SELECT COLUMN_NAME, DATA_TYPE_NAME, DEFAULT_VALUE, IS_NULLABLE, LENGTH, SCALE, COMMENTS FROM (
                   SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, POSITION, DATA_TYPE_NAME, DEFAULT_VALUE, IS_NULLABLE,
                   LENGTH, SCALE, COMMENTS FROM SYS.TABLE_COLUMNS UNION ALL SELECT SCHEMA_NAME, VIEW_NAME AS TABLE_NAME,
                   COLUMN_NAME, POSITION, DATA_TYPE_NAME, DEFAULT_VALUE, IS_NULLABLE, LENGTH, SCALE, COMMENTS FROM
                   SYS.VIEW_COLUMNS) AS COLUMS WHERE SCHEMA_NAME=:schema AND TABLE_NAME=:table ORDER BY POSITION"""
            ).bindparams(
                schema=self.denormalize_name(schema),
                table=self.denormalize_name(table_name)
            )
        )

        columns = []
        for row in result.fetchall():
            column = {
                'name': self.normalize_name(row[0]),
                'default': row[2],
                'nullable': row[3] == "TRUE",
                'comment': row[6]

            }

            if hasattr(types, row[1]):
                column['type'] = getattr(types, row[1])
            elif hasattr(hana_types, row[1]):
                column['type'] = getattr(hana_types, row[1])
            else:
                util.warn("Did not recognize type '%s' of column '%s'" % (
                    row[1], column['name']
                ))
                column['type'] = types.NULLTYPE

            if column['type'] == types.DECIMAL:
                column['type'] = types.DECIMAL(row[4], row[5])
            elif column['type'] == types.VARCHAR:
                column['type'] = types.VARCHAR(row[4])

            columns.append(column)

        return columns 

def test_create_table(engine):
    meta = MetaData()
    table = Table(
        'test_pybigquery.test_table_create', meta,
        Column('integer_c', sqlalchemy.Integer, doc="column description"),
        Column('float_c', sqlalchemy.Float),
        Column('decimal_c', sqlalchemy.DECIMAL),
        Column('string_c', sqlalchemy.String),
        Column('text_c', sqlalchemy.Text),
        Column('boolean_c', sqlalchemy.Boolean),
        Column('timestamp_c', sqlalchemy.TIMESTAMP),
        Column('datetime_c', sqlalchemy.DATETIME),
        Column('date_c', sqlalchemy.DATE),
        Column('time_c', sqlalchemy.TIME),
        Column('binary_c', sqlalchemy.BINARY),
        bigquery_description="test table description",
        bigquery_friendly_name="test table name"
    )
    meta.create_all(engine)
    meta.drop_all(engine)

    # Test creating tables with declarative_base
    Base = declarative_base()

    class TableTest(Base):
        __tablename__ = 'test_pybigquery.test_table_create2'
        integer_c = Column(sqlalchemy.Integer, primary_key=True)
        float_c = Column(sqlalchemy.Float)

    Base.metadata.create_all(engine)
    Base.metadata.drop_all(engine) 

def _get_column_info(
        self,
        name,
        type_,
        nullable,
        autoincrement,
        default,
        precision,
        scale,
        length,
    ):

        coltype = self.ischema_names.get(type_, None)

        kwargs = {}

        if coltype in (NUMERIC, DECIMAL):
            args = (precision, scale)
        elif coltype == FLOAT:
            args = (precision,)
        elif coltype in (CHAR, VARCHAR, UNICHAR, UNIVARCHAR, NCHAR, NVARCHAR):
            args = (length,)
        else:
            args = ()

        if coltype:
            coltype = coltype(*args, **kwargs)
            # is this necessary
            # if is_array:
            #     coltype = ARRAY(coltype)
        else:
            util.warn(
                "Did not recognize type '%s' of column '%s'" % (type_, name)
            )
            coltype = sqltypes.NULLTYPE

        if default:
            default = default.replace("DEFAULT", "").strip()
            default = re.sub("^'(.*)'$", lambda m: m.group(1), default)
        else:
            default = None

        column_info = dict(
            name=name,
            type=coltype,
            nullable=nullable,
            default=default,
            autoincrement=autoincrement,
        )
        return column_info 

def _fixed_lookup_fixture(self):
        return [
            (sqltypes.String(), sqltypes.VARCHAR()),
            (sqltypes.String(1), sqltypes.VARCHAR(1)),
            (sqltypes.String(3), sqltypes.VARCHAR(3)),
            (sqltypes.Text(), sqltypes.TEXT()),
            (sqltypes.Unicode(), sqltypes.VARCHAR()),
            (sqltypes.Unicode(1), sqltypes.VARCHAR(1)),
            (sqltypes.UnicodeText(), sqltypes.TEXT()),
            (sqltypes.CHAR(3), sqltypes.CHAR(3)),
            (sqltypes.NUMERIC, sqltypes.NUMERIC()),
            (sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)),
            (sqltypes.Numeric, sqltypes.NUMERIC()),
            (sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)),
            (sqltypes.DECIMAL, sqltypes.DECIMAL()),
            (sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)),
            (sqltypes.INTEGER, sqltypes.INTEGER()),
            (sqltypes.BIGINT, sqltypes.BIGINT()),
            (sqltypes.Float, sqltypes.FLOAT()),
            (sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()),
            (sqltypes.DATETIME, sqltypes.DATETIME()),
            (sqltypes.DateTime, sqltypes.DATETIME()),
            (sqltypes.DateTime(), sqltypes.DATETIME()),
            (sqltypes.DATE, sqltypes.DATE()),
            (sqltypes.Date, sqltypes.DATE()),
            (sqltypes.TIME, sqltypes.TIME()),
            (sqltypes.Time, sqltypes.TIME()),
            (sqltypes.BOOLEAN, sqltypes.BOOLEAN()),
            (sqltypes.Boolean, sqltypes.BOOLEAN()),
            (
                sqlite.DATE(storage_format="%(year)04d%(month)02d%(day)02d"),
                sqltypes.DATE(),
            ),
            (
                sqlite.TIME(
                    storage_format="%(hour)02d%(minute)02d%(second)02d"
                ),
                sqltypes.TIME(),
            ),
            (
                sqlite.DATETIME(
                    storage_format="%(year)04d%(month)02d%(day)02d"
                    "%(hour)02d%(minute)02d%(second)02d"
                ),
                sqltypes.DATETIME(),
            ),
        ]