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?
A workaround is to use a "dummy" Date type that returns None for
bind_processor() and result_processor().
Not gonna fly here, there's too many projects and developers this would
touch :'(
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.
