how to insert a row with arcpy (puthon2) in Database SQL Server

Question

I'm currently having a problem when using ArcPy (Python 2) to insert a row into a table using a SQL server database., Since this table contains a "Order" field that is a keyword in SQL., here is an example in the names of the fields to insert:

Fields = [‘Id’, ‘Nom’, ‘TypeLayer’, ‘Order’]

And I used arcpy.da.InsertCursor(table_path, Fields) to insert the row.

When I run the script, I got the following error:

RuntimeError : Invalid SQL syntax [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Syntaxe incorrecte vers le mot clé ‘Order’]

Then, after researching on the net, I found solutions that say to put the keyword 'Order' in brackets.

Fields = [‘Id’, ‘Nom’, ‘TypeLayer’, ‘[Order]’]

I always had the same error:

RuntimeError : Invalid SQL syntax [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Syntaxe incorrecte vers le mot clé ‘Order’]

blake.terhune · Answer

Keep in mind you'll need to use the versioned view if you're editing versioned data with SQL. It's more complicated if you're working in a named version rather than the Default version.

Edit versioned data in SQL Server using SQL—ArcMap | Documentation (arcgis.com)

Anonymous User · Answer

You could also try straight sql:

import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=servername\SQLEXPRESS;'
                      'Database=db;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

for item in ItemsToAddList:
    cursor.execute("""INSERT INTO table (Id, Nom, TypeLayer, [Order]) VALUES ({0},{1},{2},{3})""".format(item[0], item[1], item[2], item[3]))
    # or if the table only consists of these four fields and you insert 4 items, you can maybe drop the field assignment: cursor.execute("""INSERT INTO table VALUES ({0},{1}, {2},{3})""".format(item[0], item[1], item[2], item[3]))
conn.commit()

blake.terhune · Answer

Ha! That's a very unfortunate situation. Can you insert the row if you don't use the Order field? If so, do that, then update the row later with your Order value. Not efficient, but that is the first thing that comes to mind. Or if this feature class has little or no dependencies, then maybe consider renaming the field.