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.
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.
