executing a sql script

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

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

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