Write nested Python dictionary into SQL table

Question

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)

Fix spelling, improve readability, remove unnecessary closing. — Eduardo
– Eduardo, Commented Oct 5, 2019 at 8:15
Possible duplicate of Python nested dictionary with SQL insert — Eduardo
– Eduardo, Commented Oct 5, 2019 at 9:09
Please don't edit your questions by overwriting the original text @Vishal MuraliKrishnan, it makes very difficult to follow the problem and keep track of what you're tried or not. It's much better if you add the additional information under some EDIT heading at the end. — Eduardo
– Eduardo, Commented Oct 7, 2019 at 21:46

Eduardo · Accepted Answer · 2019-10-05 09:13:11Z

1

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.

answered Oct 5, 2019 at 9:13

Eduardo

2,70421 silver badges17 bronze badges

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

5 Comments

MEIYAPPAN Over a year ago

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

MEIYAPPAN Over a year ago

And I've edited the for loop based on your suggestion also still also I'm not getting the answer. Error : Couldn't process parameters. Thank you for your time in advance

MEIYAPPAN Over a year ago

I have updated the query, Could you please review the code and let me know your thoughts !!

Eduardo Over a year ago

I've tried the new code you posted @Vishal MuraliKrishnan and it works for me on Python 3.7 using cursor.execute(sql, list(vals)) or cursor.execute(sql, tuple(vals)) instead of cursor.execute(sql, vals)

MEIYAPPAN Over a year ago

Thanks Eduardo, Thank you soo much, Code is getting excuted without any issues

Bartłomiej Staszkiewicz · Accepted Answer · 2019-10-05 09:14:30Z

0

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)

answered Oct 5, 2019 at 9:14

Bartłomiej Staszkiewicz

1

Collectives™ on Stack Overflow

Write nested Python dictionary into SQL table

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related