topic Re: how to insert a row with arcpy (puthon2) in Database SQL Server in Python Questions

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

KamelAmellal — Wed, 10 May 2023 13:06:32 GMT

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’]

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

BlakeTerhune — Thu, 11 May 2023 16:10:03 GMT

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.

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

Anonymous User — Fri, 12 May 2023 12:29:54 GMT

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()

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

BlakeTerhune — Fri, 12 May 2023 14:19:19 GMT

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)