CODEWITHSUNDEEP
pythondatabasesql-insert
Praful Bagai
Praful Bagai

Reputation: 17372

Insert in a Database in python

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

Upvotes: 1

Views: 397

Answers (2)

dparpyani
dparpyani

Reputation: 2493

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.

Upvotes: 1

user2665694
user2665694

Reputation:

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

Upvotes: 2

Related Questions