Insert in a Database in python

Question

I want to insert a dictionary a = {'key': 602L} into DB in python. It does not allows me to insert it because of the quotes'' in key.

Here is my query

self.__db_conn.cursor.execute("INSERT INTO TABLE VALUES ('%s')" % (str(a)))

If I manually try to insert it(without quotes), it does work.

self.__db_conn.cursor.execute("INSERT INTO TABLE VALUES ('%s')" % ("{key: 602L}"))

Note the missing quotes in key.

How do I insert a dictionary into a table.?

• user2665694: Why do you want to store a dict this way into a database? In doubt: serialize or deserialize using JSON a dict to a string and back but not this way.
• Grijesh Chauhan: Read: stackoverflow.com/questions/8519599/…

Answer 1

Serialize to JSON

self.__db_conn.cursor.execute("INSERT INTO TABLE VALUES ('%s')" % json.dumps(your_dict)

Unserialize from JSON

row = db.conn.fetchone(.....)
your_dict = json.loads(row[...])

• Praful Bagai: @GrijeshChauhan - Yes I knew it before. For complete explanation, refer to another answer provide.

Answer 2

str(a) is "{'key': 602L}". Replacing this into the SQL statement gives:

INSERT INTO TABLE VALUES ('{'key': 602L}')

Note the mismatch of single quotes. Look into escaping the single quotes.

EDIT

As user2799617 mentioned, you can try the following:

import json
a_json = json.dumps(str(a))
self.__db_conn.cursor.execute("INSERT INTO TABLE VALUES ('%s')" % a_json)

When you get the value back from the database, you can use json.loads.

• Praful Bagai: Yaa I know. That is my concern. How do I store the full dictionary with quotes in DB?
• user2665694: Please read answers and seralize/deserialize your dict from and to JSON