did some googling around and what I think is involved in that weird exception
is that there's a recursion overflow. Looked at the code, this would be
because you're calling upon raw_connection() within the connect() event, hence
endless recursion. The "dbapi_connection" argument passed to def connect() is
the actual DBAPI connection you operate upon:
@event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_rec)
dbapi_connection.create_function("foo", 1, foo)
On Mar 23, 2012, at 4:08 PM, Tom Kralidis wrote:
>
> 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 <[email protected]> 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 [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
