On Saturday 10 October 2009 12:27:39 pm Adrian Klaver wrote: > On Saturday 10 October 2009 12:09:29 pm Kynn Jones wrote: > > I am porting some code from Perl to Python; in the Perl original I use > > either DBI::do or a rickety home-built module to pass multiple SQL > > statements (as one single block of SQL) to the Pg server. The typical > > usage is something like this: > > $dbh->do( <<EOSQL ); > > ALTER TABLE $xn OWNER TO xdev; > > GRANT ALL ON TABLE $xn TO xdev; > > > > REVOKE ALL ON TABLE $xn FROM PUBLIC; > > GRANT SELECT ON TABLE $xn TO PUBLIC; > > EOSQL > > > > > > How can I do this sort of thing from Python? > > > > I've looked into the docs for psycopg2, but I can't find anything like > > the do command used above. Did I overlook it? If not, what can I use > > instead? > > > > I'm not wedded to psycopg2, in fact its lack of documentation worries me; > > if there's a better alternative that I can use from Python please let me > > know. > > > > TIA! > > > > kynn > > One way > Using psycopg2 > DSN = "dbname=? user=? port=? host=?" > con = psycopg2.connection(DSN) > cur = con.cursor() > cur.execute(statement1) > cur.execute(statement2) > .... > con.commit() > > Another way, not tested, is triple quote entire block above and pass it to > cur.execute. > > -- > Adrian Klaver > aklaver@xxxxxxxxxxx I missed the part where you wanted to do it as one block with variables already substituted. For that I usually do something like: sql_str = "ALTER TABLE " + $xn + " OWNER TO xdev;" sql_str += "GRANT ALL ON TABLE " + $xn + " TO xdev;" sql_str += "REVOKE ALL ON TABLE " + $xn + " FROM PUBLIC;" sql_str += "GRANT SELECT ON TABLE " + $xn + " TO PUBLIC;" cur.execute(sql_str) On a side note the Psycopg mailing list is: http://lists.initd.org/mailman/listinfo/psycopg It is very helpful and makes up for the documentation issues. -- Adrian Klaver aklaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
