topic Re: working around versioning with PYODBC? in Geoprocessing Questions

working around versioning with PYODBC?

emelinerenz1 — Tue, 19 Aug 2014 15:22:44 GMT

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.

Re: working around versioning with PYODBC?

JakeSkinner — Tue, 19 Aug 2014 16:36:03 GMT

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.

Re: working around versioning with PYODBC?

emelinerenz1 — Tue, 19 Aug 2014 16:54:21 GMT

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!

Re: working around versioning with PYODBC?

JakeSkinner — Tue, 19 Aug 2014 16:59:45 GMT

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.

Re: working around versioning with PYODBC?

emelinerenz1 — Tue, 19 Aug 2014 17:36:57 GMT

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!