Process list of tuples in SQL Server table using python [duplicate]

Question

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

See github.com/mkleehammer/pyodbc/wiki/… you can use a Table Valued Parameter — Charlieface
– Charlieface, Commented Aug 6, 2023 at 13:57

Hazik Arshad · Accepted Answer · 2023-08-06 08:01:47Z

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

edited Aug 6, 2023 at 8:01

answered Aug 6, 2023 at 6:15

Hazik Arshad

5262 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sekhar Over a year ago

Hi Hazik, Thank you. Is it not possible to handle with èxecute method to process list of tuples?

Collectives™ on Stack Overflow

Process list of tuples in SQL Server table using python [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related