Python sqlalchemy.Numeric() Examples

def upgrade():
    relative_modifier = table('relative_modifier',
            column('id', sa.Integer),
            column('value', sa.Float),
            column('numeric_value', sa.Numeric(precision=8, scale=5)))
    op.add_column('relative_modifier',
            sa.Column('numeric_value', sa.Numeric(precision=8, scale=5)))
    conn = op.get_bind()
    sel = select([relative_modifier.c.id, relative_modifier.c.value])
    results = conn.execute(sel)
    q = Decimal(10) ** -5
    for id_, float_value in results:
        decimal_value = Decimal(float_value).quantize(q)
        up = update(relative_modifier).where(relative_modifier.c.id == id_)\
                .values({'numeric_value': decimal_value})
        conn.execute(up)
    op.drop_column('relative_modifier', 'value')
    op.alter_column('relative_modifier', 'numeric_value', nullable=True,
            new_column_name='value', existing_type=sa.Numeric(precision=8,
                    scale=5)) 

def test_remove_unique_index_not_reported(self):
        m1 = MetaData()
        Table(
            "order",
            m1,
            Column("order_id", Integer, primary_key=True),
            Column("amount", Numeric(10, 2), nullable=True),
            Column("user_id", Integer),
            Index("oid_ix", "order_id", "user_id", unique=True),
        )
        m2 = MetaData()
        Table(
            "order",
            m2,
            Column("order_id", Integer, primary_key=True),
            Column("amount", Numeric(10, 2), nullable=True),
            Column("user_id", Integer),
        )

        diffs = self._fixture(m1, m2)
        eq_(diffs, []) 

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 upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('state',
    sa.Column('address', sa.String(length=42), nullable=False),
    sa.Column('balance', sa.Numeric(), nullable=False),
    sa.Column('nonce', sa.Integer(), nullable=True),
    sa.Column('code', sa.Text(), nullable=True),
    sa.PrimaryKeyConstraint('address')
    )
    op.create_index(op.f('ix_state_address'), 'state', ['address'], unique=False)
    op.create_table('storage',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('address', sa.String(length=42), nullable=True),
    sa.Column('position', sa.String(length=66), nullable=False),
    sa.Column('storage', sa.String(length=66), nullable=False),
    sa.ForeignKeyConstraint(['address'], ['state.address'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_storage_position'), 'storage', ['position'], unique=False)
    op.add_column('meta_info', sa.Column('current_state_block', sa.Numeric(), nullable=True))
    # ### end Alembic commands ### 

def upgrade():
    op.create_table('provider_rates',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('valid_from', sa.DateTime(), nullable=False),
    sa.Column('provider', sa.Enum('mmg', 'twilio', 'firetext', 'ses', name='providers'), nullable=False),
    sa.Column('rate', sa.Numeric(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('provider_statistics',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('day', sa.Date(), nullable=False),
    sa.Column('provider', sa.Enum('mmg', 'twilio', 'firetext', 'ses', name='providers'), nullable=False),
    sa.Column('service_id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('unit_count', sa.BigInteger(), nullable=False),
    sa.ForeignKeyConstraint(['service_id'], ['services.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_provider_statistics_service_id'), 'provider_statistics', ['service_id'], unique=False) 

def test_numeric_default(self, connection):
        metadata = self.metadata
        # pg8000 appears to fail when the value is 0,
        # returns an int instead of decimal.
        t = Table(
            "t",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("nd", Numeric(asdecimal=True), default=1),
            Column("nf", Numeric(asdecimal=False), default=1),
            Column("fd", Float(asdecimal=True), default=1),
            Column("ff", Float(asdecimal=False), default=1),
        )
        metadata.create_all()
        connection.execute(t.insert())

        row = connection.execute(t.select()).first()
        assert isinstance(row[1], decimal.Decimal)
        assert isinstance(row[2], float)
        assert isinstance(row[3], decimal.Decimal)
        assert isinstance(row[4], float)
        eq_(row, (1, decimal.Decimal("1"), 1, decimal.Decimal("1"), 1)) 

def test_numeric_codes(self):
        from sqlalchemy.dialects.postgresql import (
            pg8000,
            pygresql,
            psycopg2,
            psycopg2cffi,
            base,
        )

        dialects = (
            pg8000.dialect(),
            pygresql.dialect(),
            psycopg2.dialect(),
            psycopg2cffi.dialect(),
        )
        for dialect in dialects:
            typ = Numeric().dialect_impl(dialect)
            for code in (
                base._INT_TYPES + base._FLOAT_TYPES + base._DECIMAL_TYPES
            ):
                proc = typ.result_processor(dialect, code)
                val = 23.7
                if proc is not None:
                    val = proc(val)
                assert val in (23.7, decimal.Decimal("23.7")) 

def init_database():

    Base = declarative_base()

    class Model(Base):
        __tablename__ = 'models'
        TS_name = Column(String(250), nullable=False,primary_key=True)
        TS_winner_name = Column(String(250), nullable=False)
        TS_model = Column(LargeBinary())
        TS_model_params = Column(String(250))
        TS_metric = Column(Numeric)
        TS_update = Column('TS_update', DATETIME, index=False, nullable=False,primary_key=True,default=datetime.datetime.utcnow)

    class TS(Base):
        __tablename__ = 'timeseries'
        TS_name = Column(String(250), nullable=False,primary_key=True)
        TS_data = Column(Text())
        TS_update = Column('TS_update', DATETIME, index=False, nullable=False,primary_key=True,default=datetime.datetime.utcnow)


    DB_NAME = 'sqlite:///Timecop_modelsv1.db'
    engine = create_engine(DB_NAME)
    #self.__db.echo = True
    Base.metadata.create_all(engine) 

def upgrade():
    notification_types = postgresql.ENUM('email', 'sms', 'letter', name='notification_type', create_type=False)
    op.create_table('rates',
    sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
    sa.Column('valid_from', sa.DateTime(), nullable=False),
    sa.Column('rate', sa.Numeric(), nullable=False),
    sa.Column('notification_type', notification_types, nullable=False),
    sa.PrimaryKeyConstraint('id')
    )

    op.create_index(op.f('ix_rates_notification_type'), 'rates', ['notification_type'], unique=False)

    op.get_bind()
    op.execute("INSERT INTO rates(id, valid_from, rate, notification_type) "
               "VALUES('{}', '2016-05-18 00:00:00', 1.65, 'sms')".format(uuid.uuid4()))
    op.execute("INSERT INTO rates(id, valid_from, rate, notification_type) "
               "VALUES('{}', '2017-04-01 00:00:00', 1.58, 'sms')".format(uuid.uuid4())) 

def test_arrays_base(self, connection):
        metadata = self.metadata
        t1 = Table(
            "t",
            metadata,
            Column("x", sqltypes.ARRAY(Float)),
            Column("y", sqltypes.ARRAY(REAL)),
            Column("z", sqltypes.ARRAY(postgresql.DOUBLE_PRECISION)),
            Column("q", sqltypes.ARRAY(Numeric)),
        )
        metadata.create_all()
        connection.execute(
            t1.insert(), x=[5], y=[5], z=[6], q=[decimal.Decimal("6.4")]
        )
        row = connection.execute(t1.select()).first()
        eq_(row, ([5], [5], [6], [decimal.Decimal("6.4")])) 

def test_float_coercion(self, connection):
        data_table = self.tables.data_table

        for type_, result in [
            (Numeric, decimal.Decimal("140.381230939")),
            (Float, 140.381230939),
            (Float(asdecimal=True), decimal.Decimal("140.381230939")),
            (Numeric(asdecimal=False), 140.381230939),
        ]:
            ret = connection.execute(
                select([func.stddev_pop(data_table.c.data, type_=type_)])
            ).scalar()

            eq_(round_decimal(ret, 9), result)

            ret = connection.execute(
                select([cast(func.stddev_pop(data_table.c.data), type_)])
            ).scalar()
            eq_(round_decimal(ret, 9), result) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('inventory_in_out_link',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('date', sa.DateTime(), nullable=False),
    sa.Column('product_id', sa.Integer(), nullable=False),
    sa.Column('in_price', sa.Numeric(precision=8, scale=2, decimal_return_scale=2), nullable=False),
    sa.Column('in_date', sa.DateTime(), nullable=False),
    sa.Column('receiving_line_id', sa.Integer(), nullable=False),
    sa.Column('out_price', sa.Numeric(precision=8, scale=2, decimal_return_scale=2), nullable=False),
    sa.Column('out_date', sa.DateTime(), nullable=False),
    sa.Column('out_quantity', sa.Numeric(precision=8, scale=2, decimal_return_scale=2), nullable=False),
    sa.Column('shipping_line_id', sa.Integer(), nullable=False),
    sa.Column('organization_id', sa.Integer(), nullable=True),
    sa.Column('remark', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['organization_id'], ['organization.id'], ),
    sa.ForeignKeyConstraint(['product_id'], ['product.id'], ),
    sa.ForeignKeyConstraint(['receiving_line_id'], ['receiving_line.id'], ),
    sa.ForeignKeyConstraint(['shipping_line_id'], ['shipping_line.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.add_column(u'inventory_transaction_line', sa.Column('saleable_quantity', sa.Numeric(precision=8, scale=2, decimal_return_scale=2), nullable=True))
    # ### end Alembic commands ### 

def __init__(self, *arg, **kw):
        """Create a TRY_CAST expression.

        :class:`.TryCast` is a subclass of SQLAlchemy's :class:`.Cast`
        construct, and works in the same way, except that the SQL expression
        rendered is "TRY_CAST" rather than "CAST"::

            from sqlalchemy import select
            from sqlalchemy import Numeric
            from sqlalchemy.dialects.mssql import try_cast

            stmt = select([
                try_cast(product_table.c.unit_price, Numeric(10, 4))
            ])

        The above would render::

            SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
            FROM product_table

        .. versionadded:: 1.3.7

        """
        super(TryCast, self).__init__(*arg, **kw) 

def test_basic_override(self):
        meta = self.metadata
        table = Table(
            "override_test",
            meta,
            Column("col1", sa.Integer, primary_key=True),
            Column("col2", sa.String(20)),
            Column("col3", sa.Numeric),
        )
        table.create()

        meta2 = MetaData(testing.db)
        table = Table(
            "override_test",
            meta2,
            Column("col2", sa.Unicode()),
            Column("col4", sa.String(30)),
            autoload=True,
        )

        self.assert_(isinstance(table.c.col1.type, sa.Integer))
        self.assert_(isinstance(table.c.col2.type, sa.Unicode))
        self.assert_(isinstance(table.c.col4.type, sa.String)) 

def test_override_upgrade_pk_flag(self):
        meta = self.metadata
        table = Table(
            "override_test",
            meta,
            Column("col1", sa.Integer),
            Column("col2", sa.String(20)),
            Column("col3", sa.Numeric),
        )
        table.create()

        meta2 = MetaData(testing.db)
        table = Table(
            "override_test",
            meta2,
            Column("col1", sa.Integer, primary_key=True),
            autoload=True,
        )

        eq_(list(table.primary_key), [table.c.col1])
        eq_(table.c.col1.primary_key, True) 

def test_numeric_no_coerce_decimal_mode(self):
        engine = testing_engine(options=dict(coerce_to_decimal=False))
        with engine.connect() as conn:
            # raw SQL no longer coerces to decimal
            value = exec_sql(conn, "SELECT 5.66 FROM DUAL").scalar()
            assert isinstance(value, float)

            # explicit typing still *does* coerce to decimal
            # (change in 1.2)
            value = conn.scalar(
                text("SELECT 5.66 AS foo FROM DUAL").columns(
                    foo=Numeric(4, 2, asdecimal=True)
                )
            )
            assert isinstance(value, decimal.Decimal) 

def upgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine

    table = Table('action', meta, autoload=True)
    table.c.start_time.alter(type=Numeric('18,6'))
    table.c.end_time.alter(type=Numeric('18,6')) 

def get_table(self, table_name, schema=None):
        schema = schema or self.meta.schema
        if schema:
            tbl = self.meta.tables.get('.'.join([schema, table_name]))
        else:
            tbl = self.meta.tables.get(table_name)

        # Avoid casting double-precision floats into decimals
        from sqlalchemy import Numeric
        for column in tbl.columns:
            if isinstance(column.type, Numeric):
                column.type.asdecimal = False

        return tbl 

def test_decimal_fp(self, connection):
        metadata = self.metadata
        self._fixture(metadata, Numeric(10, 5), decimal.Decimal("45.5"))
        val = connection.exec_driver_sql("select val from t").scalar()
        assert isinstance(val, decimal.Decimal)
        eq_(val, decimal.Decimal("45.5")) 

def get_engine_and_table():
    engine = create_engine('sqlite:///asl_data.db', echo=True)

    metadata = MetaData()
    columns = [Column('feat' + str(i), Numeric) for i in range(NUM_FEATURES)]
    columns.append(Column('sign', String(length=1)))
    tagged_data = Table('tagged_data', metadata, *columns)
    
    metadata.create_all(engine)

    return engine, tagged_data 

def test_output_type_handler(self):
        with self.engine.connect() as conn:
            for stmt, exp, kw in [
                ("SELECT 0.1 FROM DUAL", decimal.Decimal("0.1"), {}),
                ("SELECT CAST(15 AS INTEGER) FROM DUAL", 15, {}),
                (
                    "SELECT CAST(15 AS NUMERIC(3, 1)) FROM DUAL",
                    decimal.Decimal("15"),
                    {},
                ),
                (
                    "SELECT CAST(0.1 AS NUMERIC(5, 2)) FROM DUAL",
                    decimal.Decimal("0.1"),
                    {},
                ),
                (
                    "SELECT :num FROM DUAL",
                    decimal.Decimal("2.5"),
                    {"num": decimal.Decimal("2.5")},
                ),
                (
                    text(
                        "SELECT CAST(28.532 AS NUMERIC(5, 3)) "
                        "AS val FROM DUAL"
                    ).columns(val=Numeric(5, 3, asdecimal=True)),
                    decimal.Decimal("28.532"),
                    {},
                ),
            ]:
                if isinstance(stmt, util.string_types):
                    test_exp = conn.exec_driver_sql(stmt, kw).scalar()
                else:
                    test_exp = conn.scalar(stmt, **kw)
                eq_(test_exp, exp)
                assert type(test_exp) is type(exp) 

def daily_amount_select():
        return select([func.cast(func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity)
                                 /func.greatest(func.cast(func.date_part('DAY', func.current_date() - Product.create_date),Integer), 1), Numeric)]).as_scalar() 

def daily_profit_select():
        return select([func.cast(func.sum((SalesOrderLine.unit_price - Product.purchase_price) * SalesOrderLine.quantity)
                                 / func.greatest(func.cast(func.date_part('DAY', func.current_date() - Product.create_date), Integer), 1), Numeric)]).as_scalar() 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('organisation', sa.Column('_country_code', sa.String(), nullable=True))
    op.add_column('organisation', sa.Column('_default_disbursement_wei', sa.Numeric(precision=27), nullable=True))
    op.add_column('organisation', sa.Column('default_lat', sa.Float(), nullable=True))
    op.add_column('organisation', sa.Column('default_lng', sa.Float(), nullable=True))
    op.add_column('organisation', sa.Column('require_transfer_card', sa.Boolean(), nullable=True))
    # ### end Alembic commands ### 

def test_integer_types(self):
        specs = [(Integer, INTEGER()), (Numeric, INTEGER())]
        self._run_test(specs, []) 

def test_number_types(self):
        specs = [(Numeric(5, 2), NUMBER(5, 2)), (NUMBER, NUMBER())]
        self._run_test(specs, ["precision", "scale"]) 

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 get_table(self, table_name, schema=None):
        schema = schema or self.meta.schema
        if schema:
            tbl = self.meta.tables.get('.'.join([schema, table_name]))
        else:
            tbl = self.meta.tables.get(table_name)

        # Avoid casting double-precision floats into decimals
        from sqlalchemy import Numeric
        for column in tbl.columns:
            if isinstance(column.type, Numeric):
                column.type.asdecimal = False

        return tbl 

def get_table(self, table_name, schema=None):
        schema = schema or self.meta.schema
        if schema:
            tbl = self.meta.tables.get('.'.join([schema, table_name]))
        else:
            tbl = self.meta.tables.get(table_name)

        # Avoid casting double-precision floats into decimals
        from sqlalchemy import Numeric
        for column in tbl.columns:
            if isinstance(column.type, Numeric):
                column.type.asdecimal = False

        return tbl 

def upgrade():
    # NOTE(sheeprine): Hack to let the migrations pass for postgresql
    dialect = op.get_context().dialect.name
    if dialect == 'postgresql':
        constraints = ['uniq_field_threshold', 'uniq_service_threshold']
    else:
        constraints = ['uniq_field_mapping', 'uniq_service_mapping']
    op.create_table(
        'hashmap_thresholds',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('threshold_id', sa.String(length=36), nullable=False),
        sa.Column('level', sa.Numeric(precision=20, scale=8), nullable=True),
        sa.Column('cost', sa.Numeric(precision=20, scale=8), nullable=False),
        sa.Column(
            'map_type',
            sa.Enum('flat', 'rate', name='enum_map_type'),
            nullable=False),
        sa.Column('service_id', sa.Integer(), nullable=True),
        sa.Column('field_id', sa.Integer(), nullable=True),
        sa.Column('group_id', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ['field_id'],
            ['hashmap_fields.id'],
            ondelete='CASCADE'),
        sa.ForeignKeyConstraint(
            ['group_id'],
            ['hashmap_groups.id'],
            ondelete='SET NULL'),
        sa.ForeignKeyConstraint(
            ['service_id'],
            ['hashmap_services.id'],
            ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('threshold_id'),
        sa.UniqueConstraint('level', 'field_id', name=constraints[0]),
        sa.UniqueConstraint('level', 'service_id', name=constraints[1]),
        mysql_charset='utf8',
        mysql_engine='InnoDB')