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.
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.
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.
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.
On Feb 10, 2010, at 12:12 PM, Chris Withers wrote:
> Hi All,
>
> With SA 0.5.8 on Python 2.5, the attached test_with_default blows up with:
>
> Traceback (most recent call last):
> File "test_default_arg_sqlite.py", line 46, in test_with_default
> peterb2 = session.query(PersonWITH).filter_by(name='PeterB').first()
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py",
> line 1300, in first
> ret = list(self[0:1])
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py",
> line 1221, in __getitem__
> return list(res)
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py",
> line 1422, in instances
> rows = [process[0](context, row) for row in fetch]
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py",
> line 2032, in main
> return _instance(row, None)
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/mapper.py",
> line 1748, in _instance
> populate_state(state, dict_, row, isnew, attrs, instancekey=identitykey)
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/mapper.py",
> line 1618, in populate_state
> populator(state, dict_, row, isnew=isnew, **flags)
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/strategies.py",
> line 120, in new_execute
> dict_[key] = row[col]
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py",
> line 1348, in __getitem__
> return self.__parent._get_col(self.__row, key)
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py",
> line 1620, in _get_col
> return processor(row[index])
> File
> "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/databases/sqlite.py",
> line 183, in process
> return fn(*[int(x or 0) for x in regexp.match(value).groups()])
> TypeError: expected string or buffer
>
> The problem is the default on the birthday column.
> Is this a bug in the sqllite dialect or does the problem lie elsewhere?
>
> cheers,
>
> Chris
> from decimal import Decimal
> from sqlalchemy import create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy.schema import Column
> from sqlalchemy.types import Integer, String, Numeric, Date
> import datetime
> import unittest
> import sqlite3
>
>
> sqlite3.register_adapter(Decimal, str)
> sqlite3.register_converter('NUMERIC', Decimal)
>
> class Test(unittest.TestCase):
>
> def create_session(self,Base):
> engine = create_engine('sqlite://',
> echo=False,
> encoding='utf-8',
> pool_recycle=3600,
> connect_args={'detect_types':
> sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES})
> Base.metadata.create_all(engine)
> return sessionmaker(bind=engine, autoflush=True, autocommit=False)()
>
> def test_with_default(self):
> Base = declarative_base()
>
> class PersonWITH(Base):
> __tablename__ = 'person_wi'
>
> id = Column(Integer, primary_key=True)
> name = Column(String(80), unique=True)
> age = Column(Numeric(precision=36, scale=12))
> money = Column(Numeric(precision=36, scale=12), index=True)
> birthday = Column(Date, default=datetime.date.today)
>
>
> session = self.create_session(Base)
>
> try:
> peterb = PersonWITH(name='PeterB', age='42.1')
> session.add(peterb)
> session.commit()
>
> peterb2 =
> session.query(PersonWITH).filter_by(name='PeterB').first()
> self.assertEquals(peterb, peterb2)
> self.assertEquals(peterb.age,Decimal('42.1'))
> self.assertEquals(peterb2.age,Decimal('42.1'))
> self.assertTrue(isinstance(peterb2.age,Decimal))
> finally:
> session.close()
>
> def test_without_default(self):
> Base = declarative_base()
>
> class PersonWO(Base):
> __tablename__ = 'person_wo'
>
> id = Column(Integer, primary_key=True)
> name = Column(String(80), unique=True)
> age = Column(Numeric(precision=36, scale=12))
> money = Column(Numeric(precision=36, scale=12), index=True)
> birthday = Column(Date)
>
> session = self.create_session(Base)
> try:
> peterb = PersonWO(name='PeterB', age='42.1')
> session.add(peterb)
> session.commit()
>
> peterb2 = session.query(PersonWO).filter_by(name='PeterB').first()
> self.assertEquals(peterb, peterb2)
> self.assertEquals(peterb.age,Decimal('42.1'))
> self.assertEquals(peterb2.age,Decimal('42.1'))
> self.assertTrue(isinstance(peterb2.age,Decimal))
> finally:
> session.close()
>
> if __name__ == "__main__":
> unittest.main()
> --
> 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.
