Обсуждение: How to send multiple SQL commands from Python?
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
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@comcast.net
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@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) 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@comcast.net
On Sat, Oct 10, 2009 at 4:14 PM, Adrian Klaver <aklaver@comcast.net> wrote:
I missed the part where you wanted to do it as one block with variables alreadyOn 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@comcast.net
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
On Sat, 10 Oct 2009, Adrian Klaver wrote: > 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. Both work with pysqlite so I imagine they'll work with postgres as well. Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Saturday 10 October 2009 1:24:05 pm Kynn Jones wrote: > On Sat, Oct 10, 2009 at 4:14 PM, Adrian Klaver <aklaver@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 > > > aklaver@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) > > > kynn While I was walking the dog I thought of a better solution. sql_str = "ALTER TABLE %(xn)s OWNER TO xdev;" sql_str += "GRANT ALL ON TABLE %(xn)s TO xdev;" sql_str += "REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;" sql_str += "GRANT SELECT ON TABLE %(xn)s TO PUBLIC;" cur.execute(sql_str,{'xn':table_name}) -- Adrian Klaver aklaver@comcast.net
Adrian,
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
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
On Saturday 10 October 2009 5:48:39 pm Massa, Harald Armin wrote: > 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. Thanks for pointing that out. -- Adrian Klaver aklaver@comcast.net
On Sat, Oct 10, 2009 at 01:14:56PM -0700, Adrian Klaver wrote: > 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;" One minor stylistic point. Python appears to follow the same string literal rules as C in that multiple adjacent string literals are concatenated at compile time[1]. Thus you could write the above as: sql_str = ( "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;" ); This wouldn't help much here, but may in more complicated bits of code. -- Sam http://samason.me.uk/ [1] http://docs.python.org/reference/lexical_analysis.html#string-literal-concatenation