AnsweredAssumed Answered

working around versioning with PYODBC?

Question asked by emelinerenz on Aug 19, 2014
Latest reply on Aug 19, 2014 by emelinerenz

Has anyone else used pyodbc to work around versioning schema change limitations in SDE using Python?


Cursor - pyodbc - Cursor API documentation - Python ODBC library - Google Project Hosting


I'm running into a wall when trying to concatenate the query to find the Add table that is related to a table in my SDE. Almost certain this doesn't have anything to do with Esri, but probably the module I am using. I posted the code on /r/python and /r/learnpython, but no responses so far, so figured this was worth a shot.


I can get the regID from the sde_table_registry and concatenate the adds table ('a123'). But when I use PYODBC syntax to check for the table, it says the table does not exist (which it DOES). Even if I don't concatenate and just use the name of a table (in this example, LOCATIONS, commented out on line 34), it still says the table doesn't exist.


I've attached the entire script. Here's the portion of the code that breaks (see Bold line). Thanks in advanced for any help!:


    #Find Locations Add table

    print "Checking for the Location Adds Table..."

    locTable = "LOCATIONS"

    cursor.execute('select registration_id from sde_table_registry where table_name = ?', (locTable,))

    row = cursor.fetchone()

    if row:

        regID = row[0]

        print regID ##WORKS!

        addsTable = "a" + str(regID)

        print addsTable ##WORKS!


    #Alter Locations Add table if it exists

    tableChk = "table=" + addsTable

    print tableChk ##WORKS!

    #tableChk = "table='LOCATIONS'"

    if cursor.tables(tableChk).fetchone():

        print "Altering the Locations Add table"

        alterAddsTable = "'ALTER TABLE " + addsTable + " ALTER COLUMN [Comments] [nvarchar](255) NULL'"

        print alterAddsTable

##        cursor.execute(alterAddsTable)

##        cursor.commit()

        print "Completed successfully"


        print "Location Add table does not exist" ##<<< This is the response I get.