ticket #1717
On Feb 26, 2010, at 10:28 AM, Michael Bayer wrote:
>
> On Feb 26, 2010, at 8:50 AM, Chris Withers wrote:
>
>> Michael Bayer wrote:
>>> not sure if this is obvious, its the sqlite3.PARSE_DECLTYPES. The
>>> SQLite date types don't expect this to be turned on. That is a
>>> handy feature which I'm not sure was available in such a simple form
>>> when I first wrote against the pysqlite dialect in Python 2.3.
>>
>> Indeed, the dates are the problem here. As we talked about at PyCon, the
>> dates thing is a side effect of the thing my colleagues were trying to
>> solve. I've attached a test case which demonstrates the problem.
>>
>> The test can be made to parse by adding the following:
>>
>> import sqlite3
>> sqlite3.register_converter('NUMERIC',Decimal)
>>
>> ...and creating the engine as follows:
>>
>> engine = create_engine(
>> "sqlite://",
>> connect_args = {'detect_types':sqlite3.PARSE_DECLTYPES}
>> )
>>
>> ..but then we have the problem that my original mail was about. Any other
>> solutions or explanations on the truncating Decimals front?
>
> The short answer is that Pysqlite's functionality is not fine grained enough
> (cant do it just for decimals, it forces itself in for dates) and it is
> simply not compatible with SQLAlchemy's system - it is seriously flawed in
> that it doesn't even provide its typing information in cursor.description so
> its impossible for us to smoothly work around it and detect when it has
> kicked in and when it has not (see the doc below for details).
>
> If you'd like to use it, add the "native_datetime" flag as described here:
> http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html#compatibility-with-sqlite3-native-date-and-datetime-types
> .
>
> If you'd like to stay on planet earth with us and not try to use Pysqlite's
> not very useful behavior, I still don't have any confirming test of what the
> issue with Decimals is. I'd like a test case that uses no special SQLite
> flags whatsoever.
>
>
>> Not gonna fly here, there's too many projects and developers this would
>> touch :'(
>
> its just an import. Python is pretty handy like that.
>
>
>
>
>
>>
>>> A workaround is to use a "dummy" Date type that returns None for
>>> bind_processor() and result_processor().
>>
>>
>>> I don't see any accessor on the SQLite connection that could tell us
>>> if this flag is enabled. We don't want to do an isinstance()
>>> because those are quite expensive.
>>>
>>
>>> So what we can do here is utilize 0.6's "test the connection" trick,
>>> to issue a "select current_timestamp()" from the SQLite connection,
>>> and if it comes back as datetime we'd assume PARSE_DECLTYPES is on,
>>> or at least some kind of date-based processor has been added. then
>>> the SQLite date types would consult this flag. I added #1685 for
>>> this which is tentatively targeted at 0.6.0 just so I dont lose
>>> track of it.
>>
>> It sounds a bit icky, but I guess if there's no other way?
>>
>>> We might want to look into having 0.6 set a default handler for date
>>> types in any case, would need to ensure its completely compatible
>>> with what we're doing now.
>>
>> I dunno what this means...
>>
>>> Also not sure if you're aware, "pool_recycle" is not advisable with
>>> a :memory: database. it would zap out your DB. sqlite also doesnt
>>> require any "encoding" since it only accepts unicode strings - the
>>> param is unused by SQLalchemy with sqlite.
>>
>> Yeah, both of these are there 'cos we swap out testing engine between MySQL
>> and SQLite, I'll make sure they're only passed when we're really using
>> MySQL...
>>
>> cheers,
>>
>> Chris
>>
>> --
>> Simplistix - Content Management, Batch Processing & Python Consulting
>> - http://www.simplistix.co.uk
>> from sqlalchemy import create_engine
>> from sqlalchemy.orm import sessionmaker
>> from sqlalchemy.orm.session import Session
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.schema import Column
>> from sqlalchemy.types import String, Numeric, Integer
>>
>> import unittest
>> from decimal import Decimal
>>
>> class Test(unittest.TestCase):
>>
>> def test_truncate(self):
>> # setup
>> engine = create_engine("sqlite://")
>> self.Session = sessionmaker(
>> bind=engine,
>> autoflush=True,
>> autocommit=False
>> )
>> Base = declarative_base(bind=engine)
>> class MyModel(Base):
>> __tablename__ = 'test'
>> id = Column(Integer, primary_key=True)
>> value = Column(Numeric(precision=36,scale=12))
>> Base.metadata.create_all()
>> session = self.Session()
>>
>> # precision=36 scale=12 should mean this can handle 12 decimal places
>> # and this has 12 decimal places.
>> session.add(MyModel(value="152.737826714556"))
>> session.commit()
>>
>> obj = session.query(MyModel).one()
>>
>> # this will fail with the output, it shouldn't
>> # Decimal("152.737826715") != Decimal("152.737826714556")
>> self.assertEqual(obj.value, Decimal("152.737826714556"))
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
