I have a MySQL database that has been defined outside of my Python code. I'm using reflection to get it into SQLAlchemy, so I don't have any class definitions I can modify. I don't have to worry about losing precision, and I do some arithmetic with the results in Python, so I'd rather not have to manually convert a bunch of values to either float or Decimal.
import sqlalchemy as sa
eng = sa.create_engine("mysql+pymysql://user:passwd@server/database")
eng.execute("create table if not exists foo (x double not null)")
eng.execute("insert into foo (x) values (0.1)")
md = sa.MetaData(bind=eng)
md.reflect()
foo = md.tables["foo"]
res = eng.execute(foo.select())
row = res.fetchone()
print(type(row.x))
print(repr(foo.c.x.type))
Output:
<class 'decimal.Decimal'>
DOUBLE
Using the suggestion from this post, and not using the reflected tables until I set the asdecimal
attribute, I can get floats instead of Decimals.
import sqlalchemy as sa
eng = sa.create_engine("mysql+pymysql://chiptest:fryisthedevil@database/bench_drylake")
eng.execute("create table if not exists foo (x double not null)")
eng.execute("insert into foo (x) values (0.1)")
md = sa.MetaData(bind=eng)
md.reflect()
foo = md.tables["foo"]
# this needs to happen before any queries
for table in md.tables.values():
for column in table.columns.values():
if isinstance(column.type, sa.Numeric):
column.type.asdecimal = False
res = eng.execute(foo.select())
row = res.fetchone()
print(type(row.x))
print(repr(foo.c.x.type))
Output:
<class 'float'>
DOUBLE(asdecimal=False)
Note: If you do a query on the reflected table before setting asdecimal = False
, column.type
still shows up as DOUBLE(asdecimal=False)
, but the type of the value is still Decimal
. I'm guessing this is because SQLAlchemy is doing some sort of caching, but I'm not going to figure this out for sure right now.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With