[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 drop_constraint_if_exists (t text, k text, s >> text = NULL) returns void as $$\nBEGIN\nIF s IS NOT NUL\ >> L\nTHEN\nEXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t) >> || ' drop constraint ' || quote_ident(k);\nELSE\nEXECUTE\ >> 'alter table ' || quote_ident(t) || ' drop constraint ' || >> quote_ident(k);\nEND IF;\nEXCEPTION WHEN undefined_object THEN\nEND;\n$\ >> $ LANGUAGE plpgsql;\n" {} >> >> -- >> 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.
