On Jul 28, 2010, at 2:33 PM, Faheem Mitha wrote:
>
> 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.
Autocommit does not apply to all statements:
http://www.sqlalchemy.org/docs/dbengine.html#understanding-autocommit
Autocommit can be controlled on a per statement level:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.Executable.execution_options
for textual SQL, you'd use text() with autocomimt:
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.text
>
> 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.
>
--
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.
