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.?

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. — user2665694
– user2665694, Commented Sep 25, 2013 at 4:43

user2665694 · Accepted Answer · 2013-09-25 04:52:33Z

2

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[...])

answered Sep 25, 2013 at 4:52

user2665694

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

1 Comment

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

dparpyani · Accepted Answer · 2013-09-25 05:01:32Z

1

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.

answered Sep 25, 2013 at 4:44

dparpyani

2,5032 gold badges16 silver badges16 bronze badges

Yaa I know. That is my concern. How do I store the full dictionary with quotes in DB?

Please read answers and seralize/deserialize your dict from and to JSON