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.
