How do I fix my code for insert into SQL Server with Python 3.7

Question

I try to insert data into a SQL Server table with Python37 (pymssql), but I get an error as shown below. Do you have any suggestion?

These are the values to be inserted into the table

values = ['test','test', 'test', 'test', 'test', 'test','test','test','test','test','test','test','test','test']
      

SQL string in python code

insSQL = '''INSERT INTO [dbo].[S_LAZ_GET_ORDERS](ORDER_ID,
                                                   ORDER_NUMBER,
                                                   BILL_ADDRESS1,
                                                   BILL_ADDRESS2,
                                                   BILL_ADDRESS3,
                                                   BILL_ADDRESS4,
                                                   BILL_ADDRESS5,
                                                   BILL_CITY,
                                                   BILL_COUNTRY,
                                                   BILL_FIRST_NAME,
                                                   BILL_LAST_NAME,
                                                   BILL_PHONE,
                                                   BILL_PHONE2,
                                                   BILL_POST_CODE)
              VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''' 

  cur.executemany(insSQL, values)
  cur.commit()

This is the error I get:

Traceback (most recent call last):
File "src_mssql.pyx", line 1930, in _mssql._substitute_params
IndexError: tuple index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "D:\Python Project\Lazada API\getOrder.py", line 125, in getorders()
File "D:\Python Project\Lazada API\getOrder.py", line 109, in getorders
cur.executemany(insSQL, values)

File "src\pymssql.pyx", line 476, in pymssql.Cursor.executemany
File "src\pymssql.pyx", line 450, in pymssql.Cursor.execute
File "src_mssql.pyx", line 1070, in _mssql.MSSQLConnection.execute_query
File "src_mssql.pyx", line 1101, in _mssql.MSSQLConnection.execute_query
File "src_mssql.pyx", line 1218, in _mssql.MSSQLConnection.format_and_run_query
File "src_mssql.pyx", line 1240, in _mssql.MSSQLConnection.format_sql_command
File "src_mssql.pyx", line 1932, in _mssql._substitute_params

ValueError: more placeholders in SQL than params available

Answer 1

The list values should be a sequence of parameters (each element inside values is a single row to be inserted). So if you want to insert one row into your table where each cell in the row has the value 'test' then simply put one 14-element tuple inside values as follows:

values = [('test','test', 'test', 'test', 'test', 'test','test','test','test','test','test','test','test','test')]

If you want to add more rows, simply add more tuples to values.