Re: How to send multiple SQL commands from Python?

On Sat, Oct 10, 2009 at 4:14 PM, Adrian Klaver <aklaver(at)comcast(dot)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
> > aklaver(at)comcast(dot)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