Re: How to send multiple SQL commands from Python?

Adrian,

While I was walking the dog I thought of a better solution.
>
> sql_str = """ALTER TABLE %(xn)s OWNER TO xdev;
> GRANT ALL ON TABLE %(xn)s TO xdev;
> REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;
> GRANT SELECT ON TABLE %(xn)s TO PUBLIC;"""
>
> cur.execute(sql_str,{'xn':table_name})
> --
>
This will not work.

Because: "xn" will be escaped as "data", that is... the resulting string
will be:

ALTER TABLE E'waschbaer' ONER TO xdev;

which obviously is not what you want.

You can do

sql=sql_str % dict(xn=table_name)

and after taht

cur.execute(sql)

be aware that there is no quoting; so there is the danger of SQL injection,
table_name should not come from outside.

Mutliline strings are easy in Python by using triple-quoting:

sql_str = """ALTER TABLE %(xn)s OWNER TO xdev;
GRANT ALL ON TABLE %(xn)s TO xdev;
REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;
GRANT SELECT ON TABLE %(xn)s TO PUBLIC;"""

With psycopg2 there is also the cursor-attribute "query", so with:

print cur.query

you can see the query actually passed to PostgreSQL (with %(whatever)s
replaced by psycopg2s calls to libpq)

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality