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 at https://groups.google.com/forum/?fromgroups#!searchin/sqlalchemy/sqlite/sqlalchemy/eDpJ-yZEnqU/_XJ4Pmd712QJ 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 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.
