Обсуждение: How to send multiple SQL commands from Python?

Поиск
Список
Период
Сортировка

How to send multiple SQL commands from Python?

От
Kynn Jones
Дата:
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

Re: How to send multiple SQL commands from Python?

От
Adrian Klaver
Дата:
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

Re: How to send multiple SQL commands from Python?

От
Adrian Klaver
Дата:
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

Re: How to send multiple SQL commands from Python?

От
Kynn Jones
Дата:


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)

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

Re: How to send multiple SQL commands from Python?

От
Rich Shepard
Дата:
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

Re: How to send multiple SQL commands from Python?

От
Adrian Klaver
Дата:
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

Re: How to send multiple SQL commands from Python?

От
"Massa, Harald Armin"
Дата:
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

Re: How to send multiple SQL commands from Python?

От
Adrian Klaver
Дата:
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

Re: How to send multiple SQL commands from Python?

От
Sam Mason
Дата:
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