Обсуждение: executing a sql script
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
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