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