working around versioning with PYODBC?

800
4
Jump to solution
08-19-2014 08:22 AM
emelinerenz1
New Contributor II

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"

    else:

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

Tags (4)
0 Kudos
1 Solution

Accepted Solutions
emelinerenz1
New Contributor II

GO TIT figured out!

DOESN'T WORK:

    tableChk = "table=" + addsTable

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

WORKS:

if cursor.tables(table=addsTable).fetchone():

Thanks for the input, though, Jake!

View solution in original post

0 Kudos
4 Replies
JakeSkinner
Esri Esteemed Contributor

Hey Emeline!

Is the 'gisadmin' user the owner of the feature class?  If not, you will need to update the addsTable variable to include the feature class owner name.

0 Kudos
emelinerenz1
New Contributor II

Hi Jake!

The issue here is more with the table "existing" than making the actual change. In the original script (not part of the one I posted here), I am able to use an ALTER statement to update the LOCATIONS table. But when I try to use the

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

syntax when

tableChk = "table='LOCATIONS'"

the script still tells me that the Table does not exist (basically that cursor.tables(tablechk).fetchone() = Nothing).

Hope this helps! I know this isn't ArcPy, but if you have any other ideas, I'd love to hear them!

0 Kudos
JakeSkinner
Esri Esteemed Contributor

Yes, in your script you are connecting to the database as the gisadmin user.  So, you are trying to query table 'gisadmin.a<registration id>'.  If gisadmin is not the owner of the table, then this table does not exist.  You will need to connect as the feature class owner, or update the variable to include the data owner.

0 Kudos
emelinerenz1
New Contributor II

GO TIT figured out!

DOESN'T WORK:

    tableChk = "table=" + addsTable

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

WORKS:

if cursor.tables(table=addsTable).fetchone():

Thanks for the input, though, Jake!

0 Kudos