Python PostgreSQL Statement Problem psycopg2 cursor.execute(Table Union)

1
votes

I am new in python, and using Python & PostgreSQL (9.03) (and psycopg2 to interface between the two) in Windows XP environment. I am working on a huge spatial dataset road network dataset, and seperating the data per Country through ArcGIS Geoprocessing, and automatically store and them in a PostGIS (1.5) Database. While when retrieving values from the database everything works as planned:

... try: conn = psycopg2.connect("host = '" + HostName + "' dbname='" + DBName + "' user='" + Username + "' password='" + Password + "'") curs = conn.cursor() except: print "Unable to connect to the database"

SQLStatement = "SELECT data_partition FROM datasets WHERE map_partition='" + MapPartitions[0] + "'" curs.execute(SQLStatement) ...

When I am trying to pass the following Union Statement to Postgres, there is no resulting table, while if I take the printed SQL Statement and run it in as an SQL Statement and run it PostgresSQL, it creates the desired resulting table:

conn = psycopg2.connect("host = '" + HostName + "' dbname='" + DBName + "' user='" + Username + "' password='" + Password + "'") cur = conn.cursor()

SQLStatement = (

            "CREATE TABLE " + Schema + "." + PartitionTableName + " AS \n"
            "SELECT * FROM " + Schema + "." + partName + "_Lines_" + Rel + "_Net0 UNION \n"
            "SELECT * FROM " + Schema + "." + partName + "_Lines_" + Rel + "_Net1 UNION \n"
            "SELECT * FROM " + Schema + "." + partName + "_Lines_" + Rel + "_Net2 UNION \n"
            "SELECT * FROM " + Schema + "." + partName + "_Lines_" + Rel + "_Net3 UNION \n"
            "SELECT * FROM " + Schema + "." + partName + "_Lines_" + Rel + "_Net4 UNION \n"
            "SELECT * FROM " + Schema + "." + partName + "_Lines_" + Rel + "_Net5;\n"
            "\n"
            "\n"
            "ALTER TABLE " + Schema + "." + partName + "_Lines_" + Rel + "\n"
            "DROP COLUMN gid;\n"

cur.execute(SQLStatement)

        conn.commit()

        cur.close()

If we print the SQL Statement, this is the resulting query:

print SQLStatement

CREATE TABLE compresseddata.FRA24_Lines_2011_03 AS SELECT * FROM compresseddata.FRA24_Lines_2011_03_Net0 UNION SELECT * FROM compresseddata.FRA24_Lines_2011_03_Net1 UNION SELECT * FROM compresseddata.FRA24_Lines_2011_03_Net2 UNION SELECT * FROM compresseddata.FRA24_Lines_2011_03_Net3 UNION SELECT * FROM compresseddata.FRA24_Lines_2011_03_Net4 UNION SELECT * FROM compresseddata.FRA24_Lines_2011_03_Net5;

ALTER TABLE compresseddata.FRA24_Lines_2011_03

DROP COLUMN gid;

I am using variables in the to Merge different Road Network Classes, and due to different Partitions of my dataset, I need to iterate through, them, but for some reason that I cannot still understand, there is no table being produced.

Any ideas?

Thanx in advance for the help

3
pythonpostgresqlpostgispsycopg2

3 Answers

1
votes

THe SQL you are sending are actually 3 statements, not 1.

I never tried this but I expect execute to complain about this.

Additionally there is a semicolon missing in the ALTER TABLE statement.

I would recommend to add exception handling to your code and execute each SQL statement separately so you get better error reporting on what might go wrong.

1
votes

Indeed Peter, this seems to be the case. More specifically Each SQL Statement must be passed separately through: 

curs.execute(SQLStatement)

and them committed via: 

conn.commit()

All the changes will then be apparent in the database.

Thanx again

0
votes

As already mentioned, individually executing each statement and checking the exception can provide good insight to what is occurring.

In particular psycopg2 will raise psycopg2.ProgrammingError. If the error message is not useful, you may have better luck looking up the exception's pgcode and then investigating that. PGCodes for 9.1: http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html ).

try:
    cur.execute(SQLQUERY)
except psycopg2.ProgrammingError as e:
    # Err code lookup at http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html
    print "psycopg2 error code %s" % e.pgcode
    raise e

NOTE: A cursors execute statement CAN take multiple sql statements in a single string. ex: cur.execute('create table ABBA (); create table BETA ();') is a perfectly legitimate statement. For this reason, do not expect cursor.execute to perform any sanity checks on a string only input!

I'd suggest (except for special rare circumstances) to execute each statement individually.