Python sqlalchemy.NUMERIC Examples

def downgrade():
    connection = op.get_bind()
    if connection.engine.name != 'sqlite':
        return
    
    with op.batch_alter_table('user') as batch_op:
        batch_op.add_column(sa.Column('_password',
               type_=sa.NUMERIC(precision=128),
               server_default='',
               nullable=False
            ))
    
    connection.execute(
            UserHelper.update().values(_password=UserHelper.c.password)
        )
    
    with op.batch_alter_table('user') as batch_op:
        batch_op.drop_column('password')
        batch_op.alter_column('_password', server_default=None, new_column_name='password') 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column("stores", "default_currency")
    op.drop_column("invoices", "currency")
    op.alter_column(
        "invoices",
        column_name="price",
        new_column_name="amount",
        existing_type=sa.NUMERIC(precision=16, scale=8),
        existing_nullable=False,
        existing_server_default=None,
    )
    op.alter_column(
        "products",
        column_name="price",
        new_column_name="amount",
        existing_type=sa.NUMERIC(precision=16, scale=8),
        existing_nullable=False,
        existing_server_default=None,
    )
    # ### end Alembic commands ### 

def downgrade():
    op.drop_table('letter_rates')
    op.create_table('letter_rates',
                    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('valid_from', postgresql.TIMESTAMP(), autoincrement=False, nullable=False),
                    sa.PrimaryKeyConstraint('id', name='letter_rates_pkey'),
                    postgresql_ignore_search_path=False
                    )
    op.create_table('letter_rate_details',
                    sa.Column('id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('letter_rate_id', postgresql.UUID(), autoincrement=False, nullable=False),
                    sa.Column('page_total', sa.INTEGER(), autoincrement=False, nullable=False),
                    sa.Column('rate', sa.NUMERIC(), autoincrement=False, nullable=False),
                    sa.ForeignKeyConstraint(['letter_rate_id'], ['letter_rates.id'],
                                            name='letter_rate_details_letter_rate_id_fkey'),
                    sa.PrimaryKeyConstraint('id', name='letter_rate_details_pkey')
                    ) 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('sales_order_line', sa.Column('original_amount', sa.NUMERIC(precision=8, scale=2), autoincrement=False, nullable=True))
    op.add_column('sales_order_line', sa.Column('adjust_amount', sa.NUMERIC(precision=8, scale=2), autoincrement=False, nullable=True))
    op.add_column('sales_order_line', sa.Column('actual_amount', sa.NUMERIC(precision=8, scale=2), autoincrement=False, nullable=True))
    op.drop_table('preference')
    ### end Alembic commands ### 

def test_numeric_scale(self):
        self.assert_compile(types.NUMERIC(2, 4), "NUMERIC(2, 4)") 

def test_numeric_precision(self):
        self.assert_compile(types.NUMERIC(2), "NUMERIC(2)") 

def test_numeric_plain(self):
        self.assert_compile(types.NUMERIC(), "NUMERIC") 

def test_typedecorator_impl(self):
        for impl_, exp, kw in [
            (Float, "FLOAT", {}),
            (Float, "FLOAT(2)", {"precision": 2}),
            (Float(2), "FLOAT(2)", {"precision": 4}),
            (Numeric(19, 2), "NUMERIC(19, 2)", {}),
        ]:
            for dialect_ in (
                dialects.postgresql,
                dialects.mssql,
                dialects.mysql,
            ):
                dialect_ = dialect_.dialect()

                raw_impl = types.to_instance(impl_, **kw)

                class MyType(types.TypeDecorator):
                    impl = impl_

                dec_type = MyType(**kw)

                eq_(dec_type.impl.__class__, raw_impl.__class__)

                raw_dialect_impl = raw_impl.dialect_impl(dialect_)
                dec_dialect_impl = dec_type.dialect_impl(dialect_)
                eq_(dec_dialect_impl.__class__, MyType)
                eq_(
                    raw_dialect_impl.__class__, dec_dialect_impl.impl.__class__
                )

                self.assert_compile(MyType(**kw), exp, dialect=dialect_) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column(
        "invoices", sa.Column("currency", sa.Text(), nullable=True, server_default="USD")
    )
    op.add_column(
        "stores",
        sa.Column("default_currency", sa.Text(), nullable=True, server_default="USD"),
    )
    op.alter_column("invoices", "currency", server_default=None)
    op.alter_column("stores", "default_currency", server_default=None)
    op.alter_column(
        "invoices",
        column_name="amount",
        new_column_name="price",
        existing_type=sa.NUMERIC(precision=16, scale=8),
        existing_nullable=False,
        existing_server_default=None,
    )
    op.alter_column(
        "products",
        column_name="amount",
        new_column_name="price",
        existing_type=sa.NUMERIC(precision=16, scale=8),
        existing_nullable=False,
        existing_server_default=None,
    )
    # ### end Alembic commands ### 

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('product', 'retail_price',
               existing_type=sa.NUMERIC(precision=8, scale=2),
               nullable=True)
    op.alter_column('product', 'purchase_price',
               existing_type=sa.NUMERIC(precision=8, scale=2),
               nullable=True)
    op.alter_column('enum_values', 'code',
               existing_type=sa.VARCHAR(length=32),
               nullable=True)
    ### end Alembic commands ### 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('enum_values', 'code',
               existing_type=sa.VARCHAR(length=32),
               nullable=False)
    op.alter_column('product', 'purchase_price',
               existing_type=sa.NUMERIC(precision=8, scale=2),
               nullable=False)
    op.alter_column('product', 'retail_price',
               existing_type=sa.NUMERIC(precision=8, scale=2),
               nullable=False)
    ### end Alembic commands ### 

def _log(t, expr):
    arg, base = expr.op().args
    sa_arg = t.translate(arg)
    if base is not None:
        sa_base = t.translate(base)
        return sa.cast(
            sa.func.log(
                sa.cast(sa_base, sa.NUMERIC), sa.cast(sa_arg, sa.NUMERIC)
            ),
            t.get_sqla_type(expr.type()),
        )
    return sa.func.ln(sa_arg) 

def downgrade():
    op.alter_column('ft_billing', 'rate_multiplier',
                    existing_type=sa.Integer(),
                    type_=sa.NUMERIC())
    op.alter_column('ft_billing', 'billable_units',
                    existing_type=sa.Integer(),
                    type_=sa.NUMERIC(),
                    existing_nullable=True) 

def upgrade():
    op.alter_column('ft_billing', 'billable_units',
                    existing_type=sa.NUMERIC(),
                    type_=sa.Integer(),
                    existing_nullable=True)
    op.alter_column('ft_billing', 'rate_multiplier',
                    existing_type=sa.NUMERIC(),
                    type_=sa.Integer()) 

def _mod(t, expr):
    left, right = map(t.translate, expr.op().args)

    # postgres doesn't allow modulus of double precision values, so upcast and
    # then downcast later if necessary
    if not isinstance(expr.type(), dt.Integer):
        left = sa.cast(left, sa.NUMERIC)
        right = sa.cast(right, sa.NUMERIC)

    result = left % right
    if expr.type().equals(dt.double):
        return sa.cast(result, sa.dialects.postgresql.DOUBLE_PRECISION())
    else:
        return result 

def _round(t, expr):
    arg, digits = expr.op().args
    sa_arg = t.translate(arg)

    if digits is None:
        return sa.func.round(sa_arg)

    # postgres doesn't allow rounding of double precision values to a specific
    # number of digits (though simple truncation on doubles is allowed) so
    # we cast to numeric and then cast back if necessary
    result = sa.func.round(sa.cast(sa_arg, sa.NUMERIC), t.translate(digits))
    if digits is not None and isinstance(arg.type(), dt.Decimal):
        return result
    result = sa.cast(result, sa.dialects.postgresql.DOUBLE_PRECISION())
    return result 

def _to_sqla_type(itype, type_map=None):
    if type_map is None:
        type_map = _ibis_type_to_sqla
    if isinstance(itype, dt.Decimal):
        return sa.types.NUMERIC(itype.precision, itype.scale)
    elif isinstance(itype, dt.Date):
        return sa.Date()
    elif isinstance(itype, dt.Timestamp):
        # SQLAlchemy DateTimes do not store the timezone, just whether the db
        # supports timezones.
        return sa.TIMESTAMP(bool(itype.timezone))
    elif isinstance(itype, dt.Array):
        ibis_type = itype.value_type
        if not isinstance(ibis_type, (dt.Primitive, dt.String)):
            raise TypeError(
                'Type {} is not a primitive type or string type'.format(
                    ibis_type
                )
            )
        return sa.ARRAY(_to_sqla_type(ibis_type, type_map=type_map))
    elif geospatial_supported and isinstance(itype, dt.GeoSpatial):
        if itype.geotype == 'geometry':
            return ga.Geometry
        elif itype.geotype == 'geography':
            return ga.Geography
        else:
            return ga.types._GISType
    else:
        return type_map[type(itype)]