0

I would like to process list of tuple values into SQL server using python. I think we can try with loop to handle along with cursor.execute, but that is not my requirement. I would like to process list of tuples with parametrized way as part of optimization purpose.

I tried like this but it is not working.

lt = [('a','temp_a','001'),('b','temp_b','002')]

sql = '''
EXECUTE [dbo].[mystrored_proc] 
@table = %(parm)s
'''
conn = pymssql.connect(
server=server, 
user=user, 
password=password, 
database=database)

cursor = conn.cursor()
cursor.execute(query, {'parm': lt})
RF1991
  • 2,037
  • 4
  • 8
  • 17
Sekhar
  • 627
  • 4
  • 14
  • 34
  • See https://github.com/mkleehammer/pyodbc/wiki/Working-with-Table-Valued-Parameters-(TVPs) you can use a Table Valued Parameter – Charlieface Aug 06 '23 at 13:57

1 Answers1

1

You can't pass a list of tuples to cursor.execute(). You need to use a Table from pymssql.

import pymssql

lt = [('a', 'temp_a', '001'), ('b', 'temp_b', '002')]

# Define stored procedure with parameter @table
sql = '''
DECLARE @table AS dbo.MyTableType;
INSERT INTO @table (Column1, Column2, Column3) VALUES (%s, %s, %s);
EXEC [dbo].[mystrored_proc] @table;
'''

cursor.executemany(sql, lt)

# Commit and close the connection
Hazik Arshad
  • 456
  • 2
  • 8
  • Hi Hazik, Thank you. Is it not possible to handle with `èxecute` method to process list of tuples? – Sekhar Aug 06 '23 at 08:19