[sqlalchemy] UnboundExecutionError when running session.query
I'm getting the following error when I attempt to run session.query() with a declarative base object UnboundExecutionError: Parent instance Blah is not bound to a Session; lazy load operation of attribute 'attribute' cannot proceed But why? `session` is a brand new session I've just started, another session (I'm creating multiple sessions to different databases) is used just fine (selects, inserts, and updates running through that one). Any ideas what I might be missing? Here's (basically) where it happens from sqlalchemy import orm session = sessionmaker(bind=engine) sess = session() ids = ['1234'] try: query = session.query(Blah).select_from(orm.join(Blah, BlahAttribute)) if len(ids): query = query.filter(BlahAttribute.item_id.in_(ids)) return query.all() finally: session.close() Blah and BlahAttribute both inherit from a declarative_base object that is bound to their engine. Any help is appreciated. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] Re: UnboundExecutionError when running session.query
Oh, forgot to mention versions SA @ 0.5.6 Python 2.5 On Apr 6, 11:08 am, mozillalives mozillali...@gmail.com wrote: I'm getting the following error when I attempt to run session.query() with a declarative base object UnboundExecutionError: Parent instance Blah is not bound to a Session; lazy load operation of attribute 'attribute' cannot proceed But why? `session` is a brand new session I've just started, another session (I'm creating multiple sessions to different databases) is used just fine (selects, inserts, and updates running through that one). Any ideas what I might be missing? Here's (basically) where it happens from sqlalchemy import orm session = sessionmaker(bind=engine) sess = session() ids = ['1234'] try: query = session.query(Blah).select_from(orm.join(Blah, BlahAttribute)) if len(ids): query = query.filter(BlahAttribute.item_id.in_(ids)) return query.all() finally: session.close() Blah and BlahAttribute both inherit from a declarative_base object that is bound to their engine. Any help is appreciated. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] Re: UnboundExecutionError when running session.query
Whoops, cancel that request - I found out where the error was occurring. It was happening when I iterated over the query results. Guess I need to pay more attention to the traceback next time :) On Apr 6, 11:22 am, mozillalives mozillali...@gmail.com wrote: Oh, forgot to mention versions SA @ 0.5.6 Python 2.5 On Apr 6, 11:08 am, mozillalives mozillali...@gmail.com wrote: I'm getting the following error when I attempt to run session.query() with a declarative base object UnboundExecutionError: Parent instance Blah is not bound to a Session; lazy load operation of attribute 'attribute' cannot proceed But why? `session` is a brand new session I've just started, another session (I'm creating multiple sessions to different databases) is used just fine (selects, inserts, and updates running through that one). Any ideas what I might be missing? Here's (basically) where it happens from sqlalchemy import orm session = sessionmaker(bind=engine) sess = session() ids = ['1234'] try: query = session.query(Blah).select_from(orm.join(Blah, BlahAttribute)) if len(ids): query = query.filter(BlahAttribute.item_id.in_(ids)) return query.all() finally: session.close() Blah and BlahAttribute both inherit from a declarative_base object that is bound to their engine. Any help is appreciated. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] Prepared Statements in Postgresql
Hello Everyone, I am new to both sqlalchemy and elixir, but I have been using them for the past couple of weeks and I really like them. But I have a question about prepared statements for Postgresql. For one specific application, I am doing a bunch of inserts (200,000+). From what I can tell, it looks like these are not prepared statements. I rewrote the code to issue prepared statements and this cuts the insertion time in half, but the code is crude. My question's are: Is there a way to tell sqlalchemy or the engine (which would be psycopg2, correct?) to use prepared statements? I've noticed some opinions online indicating that psycopg2 does not have prepared statement support (e.g. - http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due_to_missing_prepared_statement_support/) - can I plug another engine into sqlalchemy that does? If I can't do any of the above and just need to prepare the statements manually, is there at least a method in sqlalchemy to properly quote my data before sending it to postgres? Thanks, Phil -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] Re: Prepared Statements in Postgresql
Thanks for your quick response Michael.
To answer your question, this is how I was issuing the queries
conn.execute(PREPARE insert_statement(text) AS ...)
conn.execute(EXECUTE insert_statement('%s') % val)
And I'm sorry if it seemed that I was attacking sqlalchemy, I just
wasn't sure what it did and how it works with psycopg2. From what you
wrote it seems that my question is more for the psycopg2 group than
here.
Thanks for helping me out.
Phil
On Jan 15, 12:16 pm, Michael Bayer mike...@zzzcomputing.com wrote:
mozillalives wrote:
Hello Everyone,
I am new to both sqlalchemy and elixir, but I have been using them for
the past couple of weeks and I really like them. But I have a question
about prepared statements for Postgresql.
For one specific application, I am doing a bunch of inserts
(200,000+). From what I can tell, it looks like these are not prepared
statements. I rewrote the code to issue prepared statements and this
cuts the insertion time in half, but the code is crude. My question's
are:
how did you use prepared statements in Python if you don't know that
psycoopg2 uses prepared statements ? was this in another language or did
you implement a raw socket connection to your database ?
Is there a way to tell sqlalchemy or the engine (which would be
psycopg2, correct?) to use prepared statements?
to efficiently execute the same statement many times, use the
executemany style of execution - the tutorial describes this
athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multip...
. I don't think that psycopg2 actually uses prepared statements for
this purpose but I am not sure. The DBAPI executemany() method is used.
I've noticed some opinions online indicating that psycopg2 does not
have prepared statement support (e.g. -
http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due...)
the comment at the bottom of that post ultimately references a psycopg2
message from 2007 so you'd need to ask the psycopg2 folks for updated
information. However psycopg2 can do an executemany with great
efficiency as it is using methodologies for which you'd have to ask them,
so if they don't use PG's actual prepared mechanism, its probably
unnecessary. psycopg2 is an extremely mature and high performing product.
- can I plug another engine into sqlalchemy that does?
there's the pg8000 engine which may or may not do this. But its written
in pure python, is not as fast as psycopg2, and is very new and not widely
used since its author doesn't seem to promote it very much (but it is a
very well written library).
If I can't do any of the above and just need to prepare the statements
manually, is there at least a method in sqlalchemy to properly quote
my data before sending it to postgres?
Despite some of the fud-like links mentioned on that blog, SQLAlchemy, as
it says on the website since the day we launched 5 years ago, always uses
bind parameters, in all cases, for all literal values, everywhere. We do
not and have never quoted anything within SQLA as that is left up to the
services provided by the DBAPI. DBAPI does not have prepared statement
API. It has executemany(), for which the underlying implementation may
or may not use prepared statements + server-level bind processing as an
implementation detail. Psycopg2 handles the quoting in this case.
cx_oracle, OTOH, uses Oracle's native data binding facilities provided by
OCI. DBAPI abstracts this detail away.
Thanks,
Phil
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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.
