0

I keep running into an error and cannot seem to find a solution anywhere online. I currently have a SQL that has 36 columns and am using a delete statement then inserting new values each day via a CSV and python program. It is a basic Insert Into statement that is utilizing a python for loop to insert all rows from the CSV.

I just added a new column to the SQL table and to my insert statement within the python program (I have done this multiple times to this same table in the past), however whenever I the new insert statement program I get the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server 
Driver][SQL Server]Incorrect syntax near '@P26'. (102) (SQLExecDirectW); 
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could 
not be prepared. (8180)")

I assume the '@P26' is referring to some parameter, however I am not utilizing any SQL Parameters. Below is a snippet of my Python / SQL code:

SQL = """insert into example_table(   [Column_1],
                                      [Column_2],
                                      [Column_3],
                                      [Column_4],
                                      [Column_5],
                                      [Column_6],
                                      [Column_7],
                                      [Column_8],
                                      [Column_9],
                                      [Column_10],
                                       ....
                                      [Column_36],
                                      [New Column] 

                                             ) values (?,?,?,?,?,?,?,?,?,?,?,?,                                                            
                                                      ?,?,?,?,?,?,?,?,?,?,?,?,
                                                        ?,?,?,?,?,?,?,?,?,?,?,?,?)"""

cursor2.execute("delete example_table")
for row in csv_data:
   cursor2.execute(SQL, row)

Thanks so much for the help I am completely stuck. Sorry for the weird indents in the code. (NOTE: I know the syntax is correct as it works when I delete the new column and parameter marker and re-run).

ls101
  • 177
  • 5
  • 17
  • Most likely answer is that your string or generated SQL is being truncated at some point... are there string length limits anywhere in any of the code not pictured? Or in the underlying libraries/APIs you're calling? – pmbAustin May 04 '17 at 16:43
  • Austin thank you for the response. I actually thought about that so I set the varchar limit to max for the new column that I am inserting. With that said, the maximum number of characters that it will ever reach is 8. As far as the pyodbc library goes I have never run into any issue. In any case, a number of columns that already exist in the table have lengths that are far greater than the new column. – ls101 May 04 '17 at 16:55
  • No, I'm talking about the strings that are HOLDING your SQL that is being passed to SQL Server. The error message seems to indicate that the SQL string itself is getting truncated, thus causing the syntax error when it gets too long. – pmbAustin May 04 '17 at 16:57
  • Do you mean the python string itself? This thread seems to imply that the maximum for a python string is in the dozens or hundreds of gigabytes: http://stackoverflow.com/questions/1739913/what-is-the-max-length-of-a-python-string Unless it is being truncated by the ODBC driver? @ls101 does your query work in SSMS? – Jacob H May 04 '17 at 17:32
  • Could you check what happens the server using profiler? You might spot what's actually wrong with your SQL – James Z May 04 '17 at 17:35
  • All - thank you for your responses. Due to a time constraint, I found a column that was not currently being used and simply replaced that with my new column. I will keep this thread open to see if someone knows the answer outright, that way anyone on SO will be able to troubleshoot similar issues if they come across them. – ls101 May 04 '17 at 17:40

1 Answers1

1

This is a syntax issue, please check your "?" 26th value. This happen to me i has this syntax:

.... ?,?,?.?)

this was my 23th value and the error told me @P23. Source: exp.

Raul Salazar
  • 26
  • 1