Python sqlalchemy.types.Numeric() Examples

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 test_arrays_pg(self, connection):
        metadata = self.metadata
        t1 = Table(
            "t",
            metadata,
            Column("x", postgresql.ARRAY(Float)),
            Column("y", postgresql.ARRAY(REAL)),
            Column("z", postgresql.ARRAY(postgresql.DOUBLE_PRECISION)),
            Column("q", postgresql.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_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_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 _convert_type(self, dj_field, sa_type):
        kwargs = {}
        if sa_type is SA_ARRAY:
            internal_type = dj_field.base_field.get_internal_type()
            kwargs['item_type'] = self._types.get(internal_type)
            if kwargs['item_type'] is None:
                raise ConversionError(
                    'Unable convert array: '
                    'item type "%s" not found' % internal_type
                )
        elif sa_type is Geometry:
            kwargs['geometry_type'] = 'POINT'
            kwargs['srid'] = dj_field.srid
        elif sa_type is sa_types.Numeric:
            kwargs['scale'] = dj_field.decimal_places,
            kwargs['precision'] = dj_field.max_digits
        elif sa_type in (sa_types.String, sa_types.Text):
            kwargs['length'] = dj_field.max_length
        elif sa_type is SA_UUID:
            kwargs['as_uuid'] = True
        return sa_type(**kwargs) 

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 test_cume_dist(self):
        expr = func.cume_dist(0.5).within_group(column("data", Integer).desc())
        is_(expr.type._type_affinity, Numeric) 

def test_tuple_flag(self, connection):
        metadata = self.metadata

        t1 = Table(
            "t1",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", self.ARRAY(String(5), as_tuple=True)),
            Column(
                "data2", self.ARRAY(Numeric(asdecimal=False), as_tuple=True)
            ),
        )
        metadata.create_all()
        connection.execute(
            t1.insert(), id=1, data=["1", "2", "3"], data2=[5.4, 5.6]
        )
        connection.execute(
            t1.insert(), id=2, data=["4", "5", "6"], data2=[1.0]
        )
        connection.execute(
            t1.insert(),
            id=3,
            data=[["4", "5"], ["6", "7"]],
            data2=[[5.4, 5.6], [1.0, 1.1]],
        )

        r = connection.execute(t1.select().order_by(t1.c.id)).fetchall()
        eq_(
            r,
            [
                (1, ("1", "2", "3"), (5.4, 5.6)),
                (2, ("4", "5", "6"), (1.0,)),
                (3, (("4", "5"), ("6", "7")), ((5.4, 5.6), (1.0, 1.1))),
            ],
        )
        # hashable
        eq_(
            set(row[1] for row in r),
            set([("1", "2", "3"), ("4", "5", "6"), (("4", "5"), ("6", "7"))]),
        ) 

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 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 test_numerics_coercion(self, op, other):
        expr = op(column("bar", types.Numeric(10, 2)), column("foo", other))
        assert isinstance(expr.type, types.Numeric)
        expr = op(column("foo", other), column("bar", types.Numeric(10, 2)))
        assert isinstance(expr.type, types.Numeric) 

def test_asdecimal_int_to_numeric(self):
        expr = column("a", Integer) * column("b", Numeric(asdecimal=False))
        is_(expr.type.asdecimal, False)

        expr = column("a", Integer) * column("b", Numeric())
        is_(expr.type.asdecimal, True)

        expr = column("a", Integer) * column("b", Float())
        is_(expr.type.asdecimal, False)
        assert isinstance(expr.type, Float) 

def test_asdecimal_numeric_to_int(self):
        expr = column("a", Numeric(asdecimal=False)) * column("b", Integer)
        is_(expr.type.asdecimal, False)

        expr = column("a", Numeric()) * column("b", Integer)
        is_(expr.type.asdecimal, True)

        expr = column("a", Float()) * column("b", Integer)
        is_(expr.type.asdecimal, False)
        assert isinstance(expr.type, Float) 

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 test_numeric_reflection(self):
        for typ in self._type_round_trip(
            sql_types.Numeric(18, 5),
        ):
            assert isinstance(typ, sql_types.Numeric)
            eq_(typ.precision, 18)
            eq_(typ.scale, 5) 

def test_percent_rank(self):
        expr = func.percent_rank(0.5).within_group(column("data", Integer))
        is_(expr.type._type_affinity, Numeric) 

def _type_affinity(self):
        if bool(self.scale and self.scale > 0):
            return sqltypes.Numeric
        else:
            return sqltypes.Integer 

def result_processor(self, dialect, type_):
        if not self.asdecimal:
            return processors.to_float
        else:
            return sqltypes.Numeric.result_processor(self, dialect, type_) 

def test_numeric_reflection(self):
        for typ in self._type_round_trip(
            sql_types.Numeric(18, 5),
        ):
            assert isinstance(typ, sql_types.Numeric)
            eq_(typ.precision, 18)
            eq_(typ.scale, 5) 

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 _type_affinity(self):
        if bool(self.scale and self.scale > 0):
            return sqltypes.Numeric
        else:
            return sqltypes.Integer 

def result_processor(self, dialect, type_):
        if not self.asdecimal:
            return processors.to_float
        else:
            return sqltypes.Numeric.result_processor(self, dialect, type_) 

def test_numeric_reflection(self):
        for typ in self._type_round_trip(
                            sql_types.Numeric(18, 5),
                        ):
            assert isinstance(typ, sql_types.Numeric)
            eq_(typ.precision, 18)
            eq_(typ.scale, 5) 

def _type_affinity(self):
        if bool(self.scale and self.scale > 0):
            return sqltypes.Numeric
        else:
            return sqltypes.Integer 

def result_processor(self, dialect, type_):
        if not self.asdecimal:
            return processors.to_float
        else:
            return sqltypes.Numeric.result_processor(self, dialect, type_) 

def _type_affinity(self):
        if bool(self.scale and self.scale > 0):
            return sqltypes.Numeric
        else:
            return sqltypes.Integer 

def result_processor(self, dialect, type_):
        if not self.asdecimal:
            return processors.to_float
        else:
            return sqltypes.Numeric.result_processor(self, dialect, type_) 

def result_processor(self, dialect, type_):
        if not self.asdecimal:
            return processors.to_float
        else:
            return sqltypes.Numeric.result_processor(self, dialect, type_) 

def test_numeric_reflection(self):
        for typ in self._type_round_trip(
            sql_types.Numeric(18, 5),
        ):
            assert isinstance(typ, sql_types.Numeric)
            eq_(typ.precision, 18)
            eq_(typ.scale, 5) 

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_