CODEWITHSUNDEEP
pythonpython-3.x
MEIYAPPAN
MEIYAPPAN

Reputation: 47

Write nested Python dictionary into SQL table

I think I have made some mistake in creating new table in SQL by mentioning its column also. Could anyone please review the code let me know your thoughts on this

db = conn.connect(
    host ="Localhost",
    user ="root",
    passwd ="admin",
    database = "EMPLOYEE_DETAILS_00"
    )

cursor = db.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS Details ( User_ID VARCHAR(255), Name VARCHAR(255), Age VARCHAR(255), Occupation VARCHAR(255), Department VARCHAR(255), Salary VARCHAR(255), Address VARCHAR(255) ) ")

I need to write a nested Python dictionary into a SQL table. I'm trying to do it by using a for loop but I'm getting the following error:

Error : Couldn't process parameters

Can anyone provide me with any suggestions on this?

This is the code I'm trying to run:

user_details = {}
create_user_ID = input(" Enter the user ID :  ")
user_details[create_user_ID] = {}
user_name = input(" Enter the user name : ")
user_details[create_user_ID]['Name'] = user_name
user_age = int(input(" Enter the Age : "))
user_details[create_user_ID]['Age'] = user_age

for v in user_details.values():
    cols = v.keys()
    vals = v.values()

    sql = "INSERT INTO Details ({}) VALUES ({})".format(
        ', '.join(cols),
        ', '.join(['%s'] * len(cols)));

    cursor.execute(sql, vals)

Upvotes: 0

Views: 816

Answers (2)

Bartłomiej Staszkiewicz
Bartłomiej Staszkiewicz

Reputation: 1

DON'T use the most obvious one (%s with %) in real code, it's open to attacks.

sql = ("INSERT INTO Details ? Values ?" ,(col, placeholders))
cursor.execute(sql,val)

Upvotes: 0

Eduardo
Eduardo

Reputation: 2704

I would say your problem is at the last line, when you try to do cursor.execute(sql,(val,)).

In Python 3 dict.keys() and dict.values() doesn't return lists, but some dictionary view objects wrapping the data, so what you're getting from (val,) is a single value tuple with one dict_values object.

Try using just val as @niteshbisht suggested or list(val) or tuple(val) if that still doesn't work.

Please see also Python nested dictionary with SQL insert, as it looks like you're trying to address the same problem.

Upvotes: 1

Related Questions