If you need precision numerics, you should be using the NUMERIC type in any
case, the MySQL driver will give you back Python Decimal objects, see example
(note this has nothing to do with SQLAlchemy):
import MySQLdb
import decimal
c = MySQLdb.connect(db='test')
curs = c.cursor()
curs.execute("""
create table test(
f_data float,
n_data numeric(16, 12)
)
""")
f_num = 45.67920438967
d_num = decimal.Decimal("45.67920438967")
curs.execute("insert into test(f_data, n_data) values (%s, %s)", (f_num, d_num))
curs.execute("select f_data, n_data from test")
result = curs.fetchall()
curs.execute("drop table test")
print result
You can see in the result, floats are truncated, Decimal is not:
((45.6792, Decimal('45.679204389670')),)
On Apr 6, 2013, at 2:02 PM, scientificSteve <[email protected]> wrote:
> Hello Michael,
>
> I fixed the problem by converting the float values to strings using the repr
> function and passing the strings to the sqlalchemy ORM instance. Its just a
> first guess, but it seems, that the error occurs when inserting the float
> values in to the INSERT INTO statement:
> INSERT INTO unit_test_event (ev_catalog_id, start_time, end_time, public_id,
> pref_origin_id, pref_magnitude_id, pref_focmec_id, ev_type,
> ev_type_certainty, description, comment, tags, agency_id, agency_uri, author,
> author_uri, creation_time, version) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,
> %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
>
> Here are my versions:
> MySQL Server: 5.5.29
> storage engine: InnoDB
> MySQL-python==1.2.4
> SQLAlchemy==0.8.0b2
>
> Regards,
> Stefan.
>
>
>
> Am Donnerstag, 4. April 2013 20:36:47 UTC+2 schrieb Michael Bayer:
> the MySQL dialects within SQLAlchemy don't touch floats at all, so this is
> probably a product of the DBAPI in use. Key information here would
> include specific MySQL version, storage engine, and DBAPI name / version -
> Python and OS version may be important as well.
>
>
> On Apr 4, 2013, at 1:57 PM, scientificSteve <[email protected]> wrote:
>
>> Hello,
>>
>> I have got a problem with inserting large float values into a mysql database
>> table.
>> When inserting a new EventDb using a session with large values and 6 digits,
>> the float value is truncated to 5 digits. See the output of the database
>> engine:
>> 2013-04-04 19:54:38,245 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
>> 2013-04-04 19:54:38,246 INFO sqlalchemy.engine.base.Engine INSERT INTO
>> unit_test_event (ev_catalog_id, start_time, end_time, public_id,
>> pref_origin_id, pref_magnitude_id, pref_focmec_id, ev_type,
>> ev_type_certainty, description, comment, tags, agency_id, agency_uri,
>> author, author_uri, creation_time, version) VALUES (%s, %s, %s, %s, %s, %s,
>> %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
>> 2013-04-04 19:54:38,246 INFO sqlalchemy.engine.base.Engine (None,
>> 946684800.0, 946688400.0, None, None, None, None, None, None, None, None,
>> None, None, None, None, None, 1365098078.232819, None)
>> 2013-04-04 19:54:38,247 INFO sqlalchemy.engine.base.Engine COMMIT
>>
>> I have tried to enter the large values manually into the database using the
>> commandline mysql client and the values haven't been truncated.
>>
>> Any help is appreciated.
>> Stefan.
>>
>> P.S.:
>> Here's my table definition using ORM:
>>
>> class EventDb(base):
>> __tablename__ = 'event'
>> __table_args__ = (
>> UniqueConstraint('public_id'),
>> {'mysql_engine': 'InnoDB'}
>> )
>>
>> id = Column(Integer(10), primary_key = True, autoincrement = True)
>> ev_catalog_id = Column(Integer(10),
>> ForeignKey('event_catalog.id',
>> onupdate = 'cascade',
>> ondelete = 'set null'),
>> nullable = True)
>> start_time = Column(Float(53), nullable = False)
>> end_time = Column(Float(53), nullable = False)
>> public_id = Column(String(255), nullable = True)
>> pref_origin_id = Column(Integer(10), nullable = True)
>> pref_magnitude_id = Column(Integer(10), nullable = True)
>> pref_focmec_id = Column(Integer(10), nullable = True)
>> ev_type = Column(Integer(10), nullable = True)
>> ev_type_certainty = Column(String(50), nullable = True)
>> description = Column(Text, nullable = True)
>> comment = Column(Text, nullable = True)
>> tags = Column(String(255), nullable = True)
>> agency_id = Column(String(64), nullable = True)
>> agency_uri = Column(String(255), nullable = True)
>> author = Column(String(255), nullable = True)
>> author_uri = Column(String(255), nullable = True)
>> creation_time = Column(Float(53), nullable = True)
>> version = Column(String(30), nullable = True)
>>
>>
>> def __init__(self, start_time, end_time, public_id, pref_origin_id,
>> pref_magnitude_id, pref_focmec_id, ev_type,
>> ev_type_certainty,
>> agency_id, agency_uri, author, author_uri,
>> creation_time, version):
>> self.start_time = start_time
>> self.end_time = end_time
>> self.public_id = public_id
>> self.pref_origin_id = pref_origin_id
>> self.pref_magnitude_id = pref_magnitude_id
>> self.pref_focmec_id = pref_focmec_id
>> self.ev_type = ev_type
>> self.ev_type_certainty = ev_type_certainty
>> self.agency_id = agency_id
>> self.agency_uri = agency_uri
>> self.author = author
>> self.author_uri = author_uri
>> self.creation_time = creation_time
>> self.version = version
>>
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
