you can use PoolListener in both 0.6 and 0.7:
http://docs.sqlalchemy.org/en/latest/core/interfaces.html#connection-pool-events
On Mar 23, 2012, at 4:35 PM, Tom Kralidis wrote:
>
> Thanks much, this did it!
>
> So in terms of a calling application having to support both 0.6 and
> 0.7, would the best way to support this be something like:
>
> from sqlalchemy import __version__
>
> if __version__ >= 0.7:
> @event.listens_for(engine, "connect")
> def connect(dbapi_connection, connection_rec)
> dbapi_connection.create_function("foo", 1, foo)
> else:
> # previous behaviour
>
>
> On Mar 23, 4:26 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> 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 <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...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
>>> 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.
>
--
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.
