Update query in Python using Dictionary key Value pairs

user_5333128 0
Viewed 501

Im in my beginning days of learning python, my requirement is based on a column in Oracle Table im updating other column using dictionary key pair Values. My Dictionary looks like this

{'United States': 'American', 'France': 'European', 'Great Britain': 'European'}

So when Country column is United States the description column should be updated to American, after my database connection, here is my code 

query = "select country from details_table "
        cursor.execute(query)
        returnrows = cursor.fetchone()
        while returnrows is not None:
                pickedvalue=returnrows[0]

               mainvalue=file_dictionary[pickedvalue]


                updatequery = "update details_table set description='%s' where country='%s'"

                cursor.execute(updatequery %(mainvalue,pickedvalue))
                returnrows = cursor.fetchone()

When i execute this im getting Error "Not a query", so i tried changing the quotations im getting different Error each time

updatequery = "update details_table set description=%s where country=%s"
updatequery = "update details_table set description='%s' where country=%s"

for above two queries i got ORA-00933: SQL command not properly ended

updatequery = "update details_table set description=%s where country='%s'"

for this i got ORA-00904: "AMERICAN": invalid identifier

Can someone tell me which would be the correct query for this to work, i even tried like below with no luck

updatequery="update details_table set description={} where country='{}'"
sql=updatequery.format(main_value,pickedvalue)
cursor.execute(sql)
cx-oracle python-3.x dictionary oracle
2 Answers

I dont know what is the magic, it started working with this, i cant understand what is the logic difference between this and above code, someone please let me know

 query = "select country from details_table"
        cursor.execute(query)
        returnrows = cursor.fetchall()
        for rows in returnrows:
            for pickedvalue in rows:
                requiredvalue = file_dictionary[pickedvalue]

                print(requiredvalue)
                updatequery = "update details_table set description='%s' where country='%s'"
                cursor.execute(updatequery % (requiredvalue, pickedvalue))

        connection.commit()
user_53331280

Do not attempt to use string interpolation to update your query! Instead, please use bind variables as noted in the documentation.

Your example would look something like this:

query = "select country from details_table"
cursor.execute(query)
returnrows = cursor.fetchall()
for rows in returnrows:
    for pickedvalue in rows:
        requiredvalue = file_dictionary[pickedvalue]

        print(requiredvalue)
        updatequery = "update details_table set description=:1 where country=:2"
        cursor.execute(updatequery, [requiredvalue, pickedvalue])

        connection.commit()

Another way to do this with cursor iteration and tuple unpacking is as follows:

query = "select country from details_table"
cursor.execute(query)
updateCursor = connection.cursor()
for country, in cursor:
    requiredvalue = file_dictionary[country]

    print(requiredvalue)
    updatequery = "update details_table set description=:1 where country=:2"
    updateCursor.execute(updatequery, [requiredvalue, pickedvalue])

    connection.commit()
user_25103470
Related