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

Search Discussions

  • Adrian Klaver at Jan 17, 2009 at 3:14 pm

    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
  • Johnf at Jan 17, 2009 at 3:21 pm

    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
  • Joshua D. Drake at Jan 17, 2009 at 5:41 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 17, '09 at 7:09a
activeJan 17, '09 at 5:41p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase