Postgres Pro
Downloads
Home   >   mailing lists

Thread: executing a sql script

executing a sql script

From
johnf
Date:
I'm using python and can execute standard "select,update,delete,functions".
What I'd like to do is execute a sql script (a text file).  But I don't know
how?
Some thing like:
 import psycopg2
 import psycopg2.extensions
        conn = psycopg2.connect("host=%s dbname=%s user =%s  password
=%s " %(self.pgSqlHostID.Value,self.pgSqlDatabaseID.Value,self.pgSqlUserID.Value,self.msSqlPasswordID.Value))

conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
        tempCursor= conn.cursor()
        try:
            tempCursor.execute("run script %s " % FileNameScript)
            tempCursor.execute('commit')
--
John Fabiani

Re: executing a sql script

From
Adrian Klaver
Date:
On Friday 16 January 2009 10:09:06 pm johnf wrote:
> I'm using python and can execute standard "select,update,delete,functions".
> What I'd like to do is execute a sql script (a text file).  But I don't
> know how?
> Some thing like:
>  import psycopg2
>  import psycopg2.extensions
>         conn = psycopg2.connect("host=%s dbname=%s user =%s  password
> =%s "
> %(self.pgSqlHostID.Value,self.pgSqlDatabaseID.Value,self.pgSqlUserID.Value,
>self.msSqlPasswordID.Value))
>
> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
>         tempCursor= conn.cursor()
>         try:
>             tempCursor.execute("run script %s " % FileNameScript)
>             tempCursor.execute('commit')
> --
> John Fabiani

I don't know how complicated the script files are, but two methods come to mind.
The first is to use open() to read the file and parse the lines to the
execute() method. The second would be to use os.system() to do psql -d db -U
user -f FileNameScript.

--
Adrian Klaver
aklaver@comcast.net

Re: executing a sql script

From
johnf
Date:
On Saturday 17 January 2009 07:14:06 am Adrian Klaver wrote:
> On Friday 16 January 2009 10:09:06 pm johnf wrote:
> > I'm using python and can execute standard
> > "select,update,delete,functions". What I'd like to do is execute a sql
> > script (a text file).  But I don't know how?
> > Some thing like:
> >  import psycopg2
> >  import psycopg2.extensions
> >         conn = psycopg2.connect("host=%s dbname=%s user =%s  password
> > =%s "
> > %(self.pgSqlHostID.Value,self.pgSqlDatabaseID.Value,self.pgSqlUserID.Valu
> >e, self.msSqlPasswordID.Value))
> >
> > conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
> >         tempCursor= conn.cursor()
> >         try:
> >             tempCursor.execute("run script %s " % FileNameScript)
> >             tempCursor.execute('commit')
> > --
> > John Fabiani
>
> I don't know how complicated the script files are, but two methods come to
> mind. The first is to use open() to read the file and parse the lines to
> the execute() method. The second would be to use os.system() to do psql -d
> db -U user -f FileNameScript.
>
> --
> Adrian Klaver
> aklaver@comcast.net
I can't use the second suggestion. But I have considered the first.  The
script is not complex just long.  About 178 create tables along with index
info.  I was hoping there was something I was missing.


--
John Fabiani

Re: executing a sql script

From
"Joshua D. Drake"
Date:
On Fri, 2009-01-16 at 22:09 -0800, johnf wrote:
> I'm using python and can execute standard "select,update,delete,functions".
> What I'd like to do is execute a sql script (a text file).  But I don't know
> how?

You need to open the text file and pass it as an argument:

try:
      file = "%s/%s" % (str(sqlpath),str("myfile.sql"))
      procedures  = open(file,'r').read()
      dbcur.execute(procedures)
except psycopg2.DatabaseError, e:
      print
      print "EXCEPTION: procedures :%s" % str(e)
      print
      exit(1)


> Some thing like:
>  import psycopg2
>  import psycopg2.extensions
>         conn = psycopg2.connect("host=%s dbname=%s user =%s  password
> =%s " %(self.pgSqlHostID.Value,self.pgSqlDatabaseID.Value,self.pgSqlUserID.Value,self.msSqlPasswordID.Value))
>
> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
>         tempCursor= conn.cursor()
>         try:
>             tempCursor.execute("run script %s " % FileNameScript)
>             tempCursor.execute('commit')
> --
> John Fabiani
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997