Pyodbc multiple insert statements

Question

I want to insert into a table that has a specific type in SQL using pyodbc in Python.

The code for the database is setup and basic queries are working , however when trying multiple inserts I keep on receiving the following error

Error :

ProgrammingError: ('The SQL contains 3 parameter markers, but 7 parameters were supplied', 'HY000')

code :

db_env = "My_env"
params = [(1, '2022-04-28', '2022-05-27'), (2, '2022-05-28', '2022-06-27'), (3, '2022-06-28', '2022-07-27'), (4, '2022-07-28', '2022-08-27'), (5, '2022-08-28', '2022-09-27'), (6, '2022-09-28', '2022-10-27'), (7, '2022-10-28', '2022-11-27')]
with cursor as curs:
 
 query = "SET NOCOUNT ON;"
 query += f'USE {db_env};\n'
 query += 'declare @input mytype;\n'
 #params = []
 
 query += 'insert into @input (ID, PeriodStart, PeriodEnd ) VALUES (?,?,?);\n'
 
 query += "select PeriodStart, PeriodEnd, cast(Distance / 1000.0 as varchar(30)) from myfunction(@input);" 
 curs.execute(query, params)
 result = curs.fetchall()

The error tells me that there is something wrong with the tuple but to me it looks fine?

I amended the code to :

params = [(1, '2022-04-28', '2022-05-27'), (2, '2022-05-28', '2022-06-27'), (3, '2022-06-28', '2022-07-27'), (4, '2022-07-28', '2022-08-27'), (5, '2022-08-28', '2022-09-27'), (6, '2022-09-28', '2022-10-27'), (7, '2022-10-28', '2022-11-27')]
with cursor as curs:
 curs.execute('declare @input mytype;\n')
 query = "SET NOCOUNT ON;"
 query += f'USE {db_env};\n'
 query += 'declare @input mytype;\n'
 
 curs.executemany('insert into @input (ID, PeriodStart, PeriodEnd ) VALUES (?,?,?);\n', params)

I get the following error :

ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Must declare the table variable "@input". (1087) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)')

Answer 1

It looks like you may be forgetting to loop through the params list & instead are loading that entire list. So when you go to insert, you're not inserting each tuple, but are trying to insert all 7 tuples in your list at one time.