How to insert the keys and values of python dict in mysql using python

Question

I have a python dict like this:

my_dict = {'find': ['http://time.com', 'http://find.com'], 'time': ['http://any.com', 'http://www.tim.com', 'http://mine.in']...}

I want to insert keys and values of dictionary my_dict in two different columns of a mysql table. The columns names are term and urls respectively. I have three columns for now: id, term, urls and I want to insert each keys and values pairs in different rows.

I want to store the links in urls as seperated by commas. The website links in the values of my_dict must be stored seperated by commas like http://time.com, http://mine.com. I tried to insert in the following manner

for i in my_dict.items():   
    keys = i[0]
    values = i[1]
    sql = """INSERT INTO index_table (term, urls) VALUES (%s, %s)"""
    cursor.execute(sql, (keys, values))

But it shows the following error:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1")

Answer 1

Your values in this instance are lists which you are attempting to pass to the SQL query and that is causing you issues.

If you want a separate record for each url in the values section (assuming you are going to always have a list for the values portion)

for i in my_dict.items():
    term = i[0]
    for url in i[1]:
        sql = """INSERT INTO index_table (term, urls) VALUES (%s, %s)"""
        cursor.execute(sql, (term, url))

Or if you want to store the urls together in one record, you would need to pickle the list or convert to a format like json. Would require you to handle the data appropriately when pulling the data out from the database

import json
for i in my_dict.items():
    term = i[0]
    urls = json.dumps(i[1])
    sql = """INSERT INTO index_table (term, urls) VALUES (%s, %s)"""
    cursor.execute(sql, (term, urls))

To store the urls in comma separated format instead;

for i in my_dict.items():
    term = i[0]
    urls = ', '.join(i[1])
    sql = """INSERT INTO index_table (term, urls) VALUES (%s, %s)"""
    cursor.execute(sql, (term, urls))