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.
