How to insert and update using PyODBC?

Asked Oct 16 '20 at 12:26

Active Oct 16 '20 at 12:26

Viewed 202 times

How can i update or insert a SQL server table using pyodbc in Python.

I have two lists currently and this is how my code looks like:

column_name = ["ID","NAME","CLASS","SUBJECT"]
values = [123,"JOSEPH","ACCOUNT","BASICS"] # Values from one iteration

header_string = ', '.join('[{0}]'.format(w) for w in column_name)
var_string = ', '.join('?' * len(values))
query_string = 'INSERT INTO MY_TABLE(%s) values (%s);' % (header_string,var_string)
cursor.execute(query_string, values)

What i want to do is running the below SQL through cursor:

update test MY_TABLE NAME='JOHN' where ID=123
    IF @@ROWCOUNT=0
    insert into MY_TABLE ("ID","NAME","CLASS","SUBJECT") values(123,"JOSEPH","ACCOUNT","BASICS")

Can anyone suggest how can i do this ?

asked Oct 16 '20 at 12:26

Shivam Agrawal

2,053
4
26
42

What kind of error is happening? If the data is simply not appearing, try committing the data? – Jason Chia Oct 16 '20 at 12:28
I am only able to insert the data if record doesn't exists. I don't how what exact syntax to use is the record is already available . – Shivam Agrawal Oct 16 '20 at 12:33
Does this help? https://stackoverflow.com/questions/27952555/sql-update-statement-but-using-pyodbc – Jason Chia Oct 16 '20 at 12:37
Do you know about primary keys? – Wonka Oct 16 '20 at 12:47
No, The solution is only doing the update. I need to insert and update in single statement – Shivam Agrawal Oct 16 '20 at 12:48
@Wonka ID is the primary key – Shivam Agrawal Oct 16 '20 at 12:52
*"I need to insert and update in single statement"* - That is commonly known as an "upsert". A web search for that term should give you some clues. – Gord Thompson Oct 16 '20 at 13:46

How to insert and update using PyODBC?

0 Answers0