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.
