Search Postgresql Archives

Re: How to send multiple SQL commands from Python?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux