Python how to update a sql server from a dictionary?

user_4443378 0
Viewed 50

I have an INSERT statement that looks like:

query = "INSERT TABLE ({columns}) VALUES ({value_placeholders})".format(
    columns=", ".join(my_dict.keys()),
    value_placeholders=", ".join(["?"] * len(my_dict)),
)

cursor.execute(query, list(my_dict.values()))

How do I turn it into an UPDATE statement?

I've got this so far...

query = "UPDATE TABLE SET ({columns}) ({value_placeholders}) WHERE ID = ({id})".format(
    columns=", ".join(my_dict.keys()),
    value_placeholders=", ".join(["?"] * len(my_dict)),
    id=id
)

cursor.execute(query, list(my_dict.values()))

Not sure how to join the key/value pairs in the dictionary to make it in the right format.

Edit:

my_dict ={
 'key1': '1',
 'key2': 'whatever',
 'key3': Timestamp('1970-01-01 00:27:34.883683354'),
 'key4': 'blabla',
 'key5': Timestamp('1970-01-01 00:27:34.883882086'),
 'ID': 11
}
sql python
1 Answers

here is the generated query string that you can use

# Imports
import pandas as pd
from pandas import Timestamp
from datetime import datetime

# Input dict
my_dict ={
 'key1': '1',
 'key2': 'whatever',
 'key3': Timestamp('1970-01-01 00:27:34.883683354'),
 'key4': 'blabla',
 'key5': Timestamp('1970-01-01 00:27:34.883882086'),
 'ID': 11
}
# generate key/pair array
arr = []
for key,value in my_dict.items():
    if type(value) is Timestamp: # Handle TimeStamps
        arr.append("{key} = {value}".format(key=key, value=value.timestamp()))
    elif type(value) is int: # Handle Integers
        arr.append("{key} = {value}".format(key=key, value=value))
    else: # Default Handler
        arr.append("{key} = '{value}'".format(key=key, value=value))
# generate string from key/pair array
data = ", ".join(arr)

# format SQL string
id = 1
table_name = 'users'
query = "UPDATE {table_name} SET {data} WHERE id = {id};".format(table_name=table_name, data=data, id=id)

# Call Query
cursor.execute(query)

Read More Here

user_66518400
Related