Hi,
In the following script, the last line, namely
session.execute("select * from drop_constraint_if_exists('foo', 'foo_pkey',
'public');")
doesn't drop the constraint. It does if autocommit is turned off, and a
session.commit() is issued after the statement.
The autocommit setting works with similar statements that are not wrapped
up in a function, specifically
session.execute("ALTER TABLE foo DROP CONSTRAINT foo_pkey;")
I also notice that in debug mode, the db issues a COMMIT in the case of
session.execute("ALTER TABLE foo DROP CONSTRAINT foo_pkey;")
but not in the case of
session.execute("select * from drop_constraint_if_exists('foo', 'foo_pkey',
'public');")
so presumably the problem is that in this case SQLA is not, in fact,
autocommitting for some reason. Clarifications appreciated.
Regards,
Faheem
*****************************************************************************************
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy import MetaData
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) || ' cascade ';
ELSE
EXECUTE 'alter table ' || quote_ident(t) || ' drop constraint ' ||
quote_ident(k) || ' cascade ';
END IF;
EXCEPTION WHEN undefined_object THEN
END;
$$ LANGUAGE plpgsql;
""")
meta = MetaData()
def make_foo(meta):
foo = Table(
'foo', meta,
Column('id', Integer, nullable=False, primary_key=True),
)
return foo
dbuser =
password =
dbname =
dbstring = "postgres://%s:%...@localhost:5432/%s"%(dbuser, password, dbname)
from sqlalchemy import create_engine
db = create_engine(dbstring)
meta.bind = db
db.echo = 'debug'
foo = make_foo(meta)
meta.create_all()
Session = sessionmaker(autocommit=True)
session = Session(bind=db)
session.execute("select * from drop_constraint_if_exists('foo', 'foo_pkey',
'public');")
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
