Thanks for the info. When I try this again I get the following error:
code:
from sqlalchemy import create_engine, __version__, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import create_session
def foo(s):
return s
print __version__
engine = create_engine('sqlite:///foo.db', echo=False)
base = declarative_base(bind=engine)
dataset = type('dataset', (base,),
dict(__tablename__='records',__table_args__={'autoload': True}))
session = create_session(engine)
@event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_rec):
dbapi_connection = engine.raw_connection()
dbapi_connection.create_function('foo', 1, foo)
query =
session.query(dataset).filter('foo("TESTVALUE")="TESTVALUE"').all()
error:
<snip/>
File "./test.py", line 18, in connect
dbapi_connection = engine.raw_connection()
File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 2544, in
raw_connection
return self.pool.unique_connection()
File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/pool.py", line 183, in
unique_connection
return _ConnectionFairy(self).checkout()
File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/pool.py", line 387, in __init__
rec = self._connection_record = pool._do_get()
File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/pool.py", line 800, in _do_get
return self._create_connection()
File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/pool.py", line 188, in
_create_connection
return _ConnectionRecord(self)
File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/pool.py", line 270, in __init__
self.connection = self.__connect()
File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
py2.6-linux-i686.egg/sqlalchemy/pool.py", line 334, in __connect
self.__pool.logger.debug("Error on connect(): %s", e)
File "/usr/lib/python2.6/logging/__init__.py", line 1043, in debug
if self.isEnabledFor(DEBUG):
File "/usr/lib/python2.6/logging/__init__.py", line 1250, in
isEnabledFor
return level >= self.getEffectiveLevel()
File "/usr/lib/python2.6/logging/__init__.py", line 1238, in
getEffectiveLevel
while logger:
AttributeError: Logger instance has no attribute '__nonzero__'
Any idea on what's wrong with the code?
Thanks
..Tom
On Mar 22, 11:28 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Mar 22, 2012, at 2:33 PM, Tom Kralidis wrote:
>
> > We have a webapp that makes use of sqlite3 create_function type
> > queries. Using 0.6, this has worked well for us.
>
> > Using 0.7 our approach breaks. I've tried to distill a minimal test
> > case to demonstrate the issue:
>
> A NullPool is used in 0.7 for SQLite whereas in 0.6 it was the
> SingletonThreadPool. Each usage of the engine will procure an entirely new
> SQLite connection with NullPool so any state that was set on a previous call
> to connect() or raw_connection() is gone.
>
> To ensure all connections have state established, use an event. The recent
> post on this list about a week ago
> athttps://groups.google.com/forum/?fromgroups#!searchin/sqlalchemy/sqli...has
> an example of this, for the same scenario.
>
>
>
>
>
>
>
>
>
> > #!/usr/bin/python
>
> > from sqlalchemy import create_engine, __version__
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import create_session
>
> > def foo(s):
> > return s
>
> > print __version__
> > engine = create_engine('sqlite:///foo.db', echo=False)
> > base = declarative_base(bind=engine)
> > dataset = type('dataset', (base,),
> > dict(__tablename__='records',__table_args__={'autoload': True}))
> > dbtype = engine.name
> > session = create_session(engine)
>
> > connection = engine.raw_connection()
> > connection.create_function('foo', 1, foo)
>
> > query =
> > session.query(dataset).filter('foo("TESTVALUE")="TESTVALUE"').all()
>
> > Using 0.6, this works. Using 0.7, we get an OperationalError: no such
> > function as per below:
>
> > 0.7.6
> > Traceback (most recent call last):
> > File "./test.py", line 20, in <module>
> > query =
> > session.query(dataset).filter('foo("TESTVALUE")="TESTVALUE"').all()
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/orm/query.py", line 2066, in all
> > return list(self)
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/orm/query.py", line 2176, in __iter__
> > return self._execute_and_instances(context)
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/orm/query.py", line 2191, in
> > _execute_and_instances
> > result = conn.execute(querycontext.statement, self._params)
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1450, in execute
> > params)
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1583, in
> > _execute_clauseelement
> > compiled_sql, distilled_params
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1697, in
> > _execute_context
> > context)
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/engine/base.py", line 1690, in
> > _execute_context
> > context)
> > File "/home/tkralidi/lib/python2.6/site-packages/SQLAlchemy-0.7.6-
> > py2.6-linux-i686.egg/sqlalchemy/engine/default.py", line 335, in
> > do_execute
> > cursor.execute(statement, parameters)
> > sqlalchemy.exc.OperationalError: (OperationalError) no such function:
> > foo u'SELECT records.id AS records_id, records.title AS records_title
> > \nFROM records \nWHERE foo("TESTVALUE")="TESTVALUE"' ()
>
> > For reference, the table structure is as follows:
> > $ sqlite3 foo.db
> > SQLite version 3.7.3
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> .dump
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > CREATE TABLE records(id int primary key, title text);
> > INSERT INTO "records" VALUES(1,'foo');
> > INSERT INTO "records" VALUES(2,'bar');
> > COMMIT;
> > sqlite>
>
> > Any idea on how to support this approach in both 0.6 and 0.7? Thanks
> > for any advice.
>
> > ..Tom
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://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 sqlalchemy@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.
