Insert Tuple into SQL using Python

Question

I have created the Tuple as below. I want to insert the follwoing into the SQL database (using import pyodbc).

tmpData=[('PRP', 'PRP-000005', 'In Progress', 'UYR', 'uYT'), ('PRP', 'PRP-000006', 'In Progress', 'UYR', 'uYT'), ('PRP', 'PRP-000007', 'In Progress', 'UYR', None), ('PRP', 'PRP-000008', 'In Progress', 'UYR', 'TCP'), ('PRP', 'PRP-000009', 'In Progress', 'UYR', 'OTH'), ('PRP', 'PRP-000010', 'In Progress', 'UYR', None),]

conn = pyodbc.connect('Driver={SQL Server};'
                          'Server=Python;'
                          'Database=Test;'
                          'Trusted_Connection=yes;')
cursor = conn.cursor()
sql = "insert into [TBListformstables] values???????
cursor.execute(sql)

I am facing a problem in writing this to SQL. What would come at values so that Tuple can be passed

user5386938 · Accepted Answer · 2019-11-26 10:15:17Z

1

See executemany

Executes the same SQL statement for each set of parameters, returning None. The single params parameter must be a sequence of sequences, or a generator of sequences.

params = [ ('A', 1), ('B', 2) ]
cursor.executemany("insert into t(name, id) values (?, ?)", params)

So in your example, something like

sql = "insert into [TBListformstables] values (?, ?, ?, ?, ?)"
cursor.executemany(sql, tmpData)

answered Nov 26, 2019 at 10:15

user5386938

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

5 Comments

Andrew Over a year ago

Thanks a lot! This is what I was looking for.

Andrew Over a year ago

I have one variable like Companyname and want to insert in each row. how can I pass one variable name in above ? ... values (?, ?, ?, ?, ?,'Google'). it is working, but I want the company name as a variable to pass into SQL.I do not want to insert company name in each Tuple.

user5386938 Over a year ago

"I do not want to insert company name in each Tuple" -- Why not? It's easy: tmpData=[tup+(Companyname,) for tup in tmpData]

Andrew Over a year ago

Thanks. I want to insert some 5+ other variables like ( Dept, Auth, Class etc). so this might be cumbersome?? I can add this 5 times. Please suggest.

user5386938 Over a year ago

I don't see what's cumbersome about tmpData=[tup+(Companyname, Dept, Auth, Class, Foo, Bar) for tup in tmpData]. Just make sure you have the same number of ? in sql as the number of items in your tuples.

Juhil Somaiya · Accepted Answer · 2019-11-26 10:23:22Z

1

You can create a JSON object from your list and pass it to the insert query. Check below:

sql = insert into table values(json.dumps(tmpData))

answered Nov 26, 2019 at 10:23

Juhil Somaiya

9637 silver badges24 bronze badges

Collectives™ on Stack Overflow

Insert Tuple into SQL using Python

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related