Search Postgresql Archives

Re: How to send multiple SQL commands from Python?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Saturday 10 October 2009 1:24:05 pm Kynn Jones wrote:
> On Sat, Oct 10, 2009 at 4:14 PM, Adrian Klaver <aklaver@xxxxxxxxxxx> 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@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)
>
>
> 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@xxxxxxxxxxx

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux