On Jul 1, 2008, at 9:00 PM, Ryan Ginstrom wrote:
>
> I'm a newbie to SQLAlchemy, so please excuse me if this is common
> knowledge.
>
> Using the sqlite backend, I'm creating custom functions and then
> running
> queries with them. I started by using the connection's create_function
> method, and then running the query with the session.execute method.
>
> self.session = self.SessionClass()
> search_string = """SELECT * FROM records
> WHERE memory_id=:id AND
> get_concordance(source)"""
> concordance_func = make_concordance_func(query)
> conn = self.session.bind.connect()
> conn.connection.create_function("get_concordance",
> 1,
> concordance_func)
>
> rows = [dict(x) for x in self.session.execute(search_string,
> dict(id=id))]
>
> return cPickle.dumps(rows)
>
> This worked fine in unit testing, but when I tried it from a cherrypy
> instance, it failed semi-randomly. The failure was
> (OperationalError) no such function: get_concordance
> It would work for half the queries, and fail for the other half, not
> always
> the same ones.
>
> I assumed that this was due to some threading issue, and changed the
> execute
> method to the connection object. This works:
>
> # ... same up to here
> rows = [dict(x) for x in conn.execute(search_string,
> dict(id=id))]
>
> return cPickle.dumps(rows)
>
> What I'd like to find out is:
> (1) What is going on to cause this?
> (2) Is there something horribly wrong with my approach?
> Thanks in advance for any enlightenment.
Theres some things you're doing that would specifically cause you to
get back a different connection than the one you created the function
on, even within a single thread; except that the SQLite dialect uses
the SingletonThreadPool pool by default which does in fact nail a
single connection to each thread. Seems like you've either changed
that setting or you are sharing the Session between multiple threads
(not something we usually recommend unless you are extremely
careful). SQLite in particular does not recommend sharing its own
connections between threads, and isn't even supported in all versions
of SQLite which is why STP is turned on by default (but you are free
to change that setting).
Calling session.bind.connect() in the general case gives you a
connection unrelated to the one used when you call Session.execute().
If you'd like the connection that the Session is actually using, the
Session must be within a transaction (via transactional=True or via
begin()), then call Session.connection(). If the Session is not in a
transaction, Session.connection() again calls an arbitrary connection
from the pool.
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
