Updating fields on a Microsoft SQL Table using pyodbc - Python

Question

I have a table on sql with the following data:

name  location
sarah   
dave     
bob     
rover
dave
bob
emma
john
prash

I have some data coming in that gives me location details of all these people. The locations could either be:

name_data  location_data
sarah          GB 
dave           US
bob            FR
rover          IN
dave           US
bob            FR
emma           ES
john           NI

How do I update the database so I can include the locations relative to the name? I tried the following, but it didn't seem to work:

cursor.execute("UPDATE "+table_name+"location) values (?)",location_data"WHERE name like" "'"name_data"'")

Judging by the (?) I'm guessing this uses sqlite syntax? That is a badly distorted string you're trying to build. If table_name needs to be dynamic then perhaps break it into two parts; first build a query= string in which you use %s for the parts that cannot be represented by (?) and then execute the query string and add the values covered by placeholders in the execution stage — roganjosh
– roganjosh, Commented Mar 14, 2016 at 18:38

M.Fazal · Accepted Answer · 2020-01-09 06:57:58Z

7

name=input("Enter name:")

loc = input("Enter Location:")  
cursor = cnxn.cursor() 
SQLCommand = ("UPDATE {table_name} SET location_data=?  WHERE name_data ="+ name+" ")
Location = [loc]
cursor.execute(SQLCommand,Location)

answered Jan 9, 2020 at 6:57

M.Fazal

932 silver badges6 bronze badges

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

Comments

Muposat · Accepted Answer · 2016-03-14 18:53:17Z

3

If you pack name/location data in list of tuples:

for name, location in name_location_data:
    sql = "UPDATE {tbl} SET location=? WHERE name=?".format(tbl=table_name)
    cursor.execute(sql, name, location)

answered Mar 14, 2016 at 18:53

Muposat

1,5061 gold badge12 silver badges25 bronze badges

Comments

semiflex · Accepted Answer · 2016-03-15 13:19:47Z

-1

I think the '(?)' representation for strings won't work when updating a table. I suggest you use '%s' and SET. It should look something like this:

cursor.execute("UPDATE table_name SET location='%s' WHERE name='%s'" % (location_data, name_data))

answered Mar 15, 2016 at 13:19

semiflex

1,2463 gold badges26 silver badges47 bronze badges

2 Comments

tylerl Over a year ago

You actually can use '?' with UPDATE - stackoverflow.com/a/28071336/706751

Jari Turkia Over a year ago

This is exactly something that must NEVER be done! Unescaped value in location_data or name_data opens your code for a SQL-injection attack. Solution is to use ?-binding.

Collectives™ on Stack Overflow

Updating fields on a Microsoft SQL Table using pyodbc - Python

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related