[sqlalchemy] Re: Error trying to use session.execute
[This message has also been posted.] On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer mike...@zzzcomputing.com wrote: this pattern: try: foo() except: pass will get you killed every time. the difference in result is between the session's default of transactionalized execution and the Connection's default of autocommit execution. Hi Mike, Thanks for the quick reply, as usual. I'm going to assume comment 1 and comment 2 are unrelated. If they aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no CREATE LANGUAGE IF EXISTS in PostgreSQL, so CREATE LANGUAGE exits with an error if the language already exists. So, what alternative do you suggest? I guess catching the exception within pg itself is an option - I've done it in at least one other case. The advantage with that approach is that one can then finetune the catching of the exception more precisely. However, I don't know if this is relevant to what you mean. As far as 2, I'm not sure what transactionalized execution is. I tried Googling for this, and came up, for example, with http://www.sqlalchemy.org/docs/dbengine.html#using-transactions-with-connection but this didn't help me understand what the problem was. If the problem is that the transaction does not commit immediately, why should that cause an error? I remembered that text() has the autocommit=True option, and tried using it with create_drop_constraint_if_exists_function, but it didn't help. Regards, Faheem. On Jun 12, 2010, at 3:30 PM, Faheem Mitha wrote: Hi, While rearranging some Python code using SQLAlchemy, I managed to get this puzzling error. The oddest bit is that using conn.execute works, while session.connect doesn't. Below, the code that doesn't work, the code that works, and last, the traceback for the code that doesn't work. If anyone could explain to me what I'm missing, I'd appreciate it. Regards, Faheem. ** from sqlalchemy.sql import text create_plpgsql = text( CREATE LANGUAGE plpgsql; ) create_drop_constraint_if_exists_function = text( CREATE OR REPLACE FUNCTION drop_constraint_if_exists (t text, k text, s text = NULL) returns void as $$ BEGIN IF s IS NOT NULL THEN EXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t) || ' drop constraint ' || quote_ident(k); ELSE EXECUTE 'alter table ' || quote_ident(t) || ' drop constraint ' || quote_ident(k); END IF; EXCEPTION WHEN undefined_object THEN END; $$ LANGUAGE plpgsql; ) * this does not work * from sqlalchemy.orm import sessionmaker dbstring = postgres://snp:pqxxro...@localhost:5432/affy6_faheem from sqlalchemy import create_engine db = create_engine(dbstring) Session = sessionmaker(bind=db) session = Session() try: session.execute(create_plpgsql) except: pass session.execute(create_drop_constraint_if_exists_function) * this works * rrom sqlalchemy.orm import sessionmaker dbstring = postgres://snp:pqxxro...@localhost:5432/affy6_faheem from sqlalchemy import create_engine db = create_engine(dbstring) conn = db.connect() try: conn.execute(create_plpgsql) except: pass conn.execute(create_drop_constraint_if_exists_function) Traceback (most recent call last): File stdin, line 34, in module File /usr/lib/pymodules/python2.5/sqlalchemy/orm/session.py, line 753, in execute clause, params or {}) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 874, in _execute_clauseelement return self.__execute_context(context) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block \nCREATE OR REPLACE FUNCTION
[sqlalchemy] Re: Error trying to use session.execute
On Sun, 13 Jun 2010 13:33:39 +0530, Faheem Mitha fah...@email.unc.edu wrote: I'm going to assume comment 1 and comment 2 are unrelated. If they aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no CREATE LANGUAGE IF EXISTS in PostgreSQL, so CREATE LANGUAGE exits with an error if the language already exists. So, what alternative do you suggest? I guess catching the exception within pg itself is an option - I've done it in at least one other case. The advantage with that approach is that one can then finetune the catching of the exception more precisely. However, I don't know if this is relevant to what you mean. As far as 2, I'm not sure what transactionalized execution is. I tried Googling for this, and came up, for example, with http://www.sqlalchemy.org/docs/dbengine.html#using-transactions-with-connection but this didn't help me understand what the problem was. If the problem is that the transaction does not commit immediately, why should that cause an error? I remembered that text() has the autocommit=True option, and tried using it with create_drop_constraint_if_exists_function, but it didn't help. [Following up to myself] Adding session.commit() after try: session.execute(create_plpgsql) except: pass makes this work. I'm still not sure what is happening here, but possibly session.execute(create_plpgsql) needs a chance to clean up after itself before another transaction? If that is right, then your two comments were in fact related. :-) I note that setting autocommit=True on create_plpgsql doesn't work, presumably because it errors out, and therefore cannot be committed. Regardless, a expert explanation would be appreciated. Would using something like http://wiki.postgresql.org/wiki/CREATE_OR_REPLACE_LANGUAGE be a reasonable solution to this? Then the try except would not be necessary. Thanks, Faheem. -- 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] Windows and Linux Tips
Hi friends, Do u need Windows and Linux Tips? Please visit the following site: http://windowsandlinuxtips.blogspot.com/ -- 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.
Re: [sqlalchemy] Re: Error trying to use session.execute
On Jun 13, 2010, at 4:03 AM, Faheem Mitha wrote: On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer mike...@zzzcomputing.com wrote: this pattern: try: foo() except: pass will get you killed every time. the difference in result is between the session's default of transactionalized execution and the Connection's default of autocommit execution. Hi Mike, Thanks for the quick reply, as usual. I'm going to assume comment 1 and comment 2 are unrelated. If they aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no When an exception occurs in a transaction while Postgresql, you in most cases must issue a rollback() (that is what (InternalError) current transaction is aborted, commands ignored until end of transaction block means, thats a PG message).Therefore you cannot attempt an operation with PG inside a transaction, have it fail, ignore the failure, then continue in the same transaction.You need to roll the transaction back and start a new one, or use an autocommit mode which accomplishes the same thing. As far as 2, I'm not sure what transactionalized execution is. It means a statement is executed while a transaction is in progress. Each subsequent statement occurs within the same transaction as the previous, until a rollback() or commit() is issued. This is the opposite of autocommit, where each statement occurs in a distinct transaction. -- 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] Multiple databases or multiple schemas?
I'm starting a new project which is roughly hosting a bunch of sites. I want the sites to be isolated, they'll all have the same schema (data definition), but will store their data on different 'name-spaces'. The front end will be a python web app. And I'd like to have this application talk to all the 'name-spaces' at the same time: With a request on example.com/site1, the web app will use the 'name-space' site1, with example.com/site2 it will use site2. I am using Postgres. There are 2 options for the 'name-space': Database or Schema [1]: 1. One database per site 2. One database for all the sites and 1 schema per site Solution #1 would require to maintain 1 connection per site per python process. That means: lots of connections, lots of memory needed. One the other hand, this solution is supported by SQLAlchemy out-of-the-box. I'll have a dictionary like that: {'site1': Engine('postgres://.../site1', 'site2': Engine('postgres://.../site2', ...} And whenever a request comes in I get the right engine via this dictionary. Solution #2 is not supported natively by SQLAlchemy. Each time a request comes-in I'll have to issue an additional query SET search_path TO MY_SITE where MY_SITE is the schema associated with the site. Solution #2 seems much more lightweight to me. The only problem is the small overhead that might be created by the additional query. What do you guys think? Will I get into trouble with solution #2? If you have alternative suggestions I'd like to hear them :) Regards, [1]: http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html -- Henry PrĂȘcheur -- 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: Multiple databases or multiple schemas?
[This message has also been posted.] On Sun, 13 Jun 2010 13:27:34 -0700, Henry Precheur he...@precheur.org wrote: I'm starting a new project which is roughly hosting a bunch of sites. I want the sites to be isolated, they'll all have the same schema (data definition), but will store their data on different 'name-spaces'. The front end will be a python web app. And I'd like to have this application talk to all the 'name-spaces' at the same time: With a request on example.com/site1, the web app will use the 'name-space' site1, with example.com/site2 it will use site2. I am using Postgres. There are 2 options for the 'name-space': Database or Schema [1]: 1. One database per site 2. One database for all the sites and 1 schema per site Solution #1 would require to maintain 1 connection per site per python process. That means: lots of connections, lots of memory needed. One the other hand, this solution is supported by SQLAlchemy out-of-the-box. I'll have a dictionary like that: {'site1': Engine('postgres://.../site1', 'site2': Engine('postgres://.../site2', ...} And whenever a request comes in I get the right engine via this dictionary. Solution #2 is not supported natively by SQLAlchemy. Each time a request comes-in I'll have to issue an additional query SET search_path TO MY_SITE where MY_SITE is the schema associated with the site. Sqlalchemy's table can take the qschema as argument, eg. pheno_table = Table( 'pheno', metadata, Column('patientid', String(60), primary_key=True), Column('famid', String(60), nullable=True), Column('sex_id', None, ForeignKey(schemaname+'.sex.val', onupdate='CASCADE', ondelete='CASCADE'), index=True), Column('race_id', None, ForeignKey(schemaname+'.race.val', onupdate='CASCADE', ondelete='CASCADE'), index=True), Column('phenotype', SmallInteger), schema = schemaname, ) So I don't think you do have to do that. Solution #2 seems much more lightweight to me. The only problem is the small overhead that might be created by the additional query. I'm actually using multiple schemas in one db myself, and it seems to me sqla supports this just fine. The only time I have to do SET search_path TO MY_SITE is when I access the db directly using psql. Of course, you might have to worry whether the web end of things support schemas too. Faheem. What do you guys think? Will I get into trouble with solution #2? If you have alternative suggestions I'd like to hear them :) Regards, [1]: http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html -- Henry PrĂȘcheur -- 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.