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, Mar 14 '16 at 18:38

score 7 · Answer 1 · answered Jan 09 '20 at 06:57

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 09 '20 at 06:57

M.Fazal

93
2
6

score 3 · Answer 2 · answered Mar 14 '16 at 18:53

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 '16 at 18:53

Muposat

1,476
1
11
24

score 0 · Accepted Answer · answered Mar 15 '16 at 13:19

0

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 '16 at 13:19

semiflex

1,176
3
25
44

You actually can use '?' with `UPDATE` - https://stackoverflow.com/a/28071336/706751 – tylerl Jun 29 '17 at 14:15

Updating fields on a Microsoft SQL Table using pyodbc - Python

3 Answers3