On Sat, Oct 10, 2009 at 4:14 PM, Adrian Klaver <akla...@comcast.net> wrote:
> 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 > > akla...@comcast.net > > 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) > Many thanks! > 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. That's good to know. Thanks again. kynn
