0

Using python, json, and pyodbc, is there a python function to store a json object in a Text field in a SQL database?

For example:

cnxn = pyodbc.connect( MY CONNECTION STRING )
cursor = cnxn.cursor()
cursor.execute("UPDATE TABLE1 SET FIELD1 = '" + json.dumps(pythonDictionary) + "' WHERE FIELD2 = 'some value'")

I always get syntax error because i am not properly escaping the special symbols in the json object, i simply want that json dump to be converted to proper sql string, is there a function in python to do that?!

BLang
  • 930
  • 3
  • 16
  • 35
  • Have you considered an object storage platform like CouchDB or Amazon DynamoDB? You shouldn't store JSON as string in a traditional DMBS because you can't really then query for anything in a meaningful manner. – Anand Mar 17 '17 at 19:18
  • Here is more context - http://stackoverflow.com/questions/42585892 – Anand Mar 17 '17 at 19:21
  • Other data is stored for query purposes, but there is a lot of data we simply want to store and not use. – BLang Mar 17 '17 at 19:22
  • Even if you don't use it, it is better to store in NoSQL document storage platforms. Heck, I would even dump it out in files rather than deal with string storage. Not to mention, why bloat your dbms for large chunks of unused data? – Anand Mar 17 '17 at 19:28
  • 2
    Sometimes you have to work with what you're given, do any string stripping libraries exist in python? – BLang Mar 17 '17 at 19:33
  • Have you tried using JSON.stringify()? – tw1742 Mar 17 '17 at 19:43
  • i get that there is no stringify attribute found in the json library, is that in python? – BLang Mar 17 '17 at 19:53
  • My bad, I see now you did ask for a Python function. What are some of the special symbols that cause issues after using json.dumps(pythonDictionary)? – tw1742 Mar 17 '17 at 20:17
  • @Ehz, I would suggest it without knowing the full database schema. On the other hand, I would not suggest it if I knew the schema was what you have to work with. Which is when I stopped; how do you know these JSON objects are small and not humongous? Without full knowledge, I tend to offer suggestions that are more aligned with ideal state. Of course we all understand when you have to work with the cards you are dealt with. – Anand Mar 17 '17 at 20:31
  • Yeah, i appreciate the suggestions @Anand , and these json objects are guaranteed less than 500 characters even with the additional symbols of the json object itself. The reason I want to store a json into a single column is because for most entries, more than half the values in the json will be null, and its wasteful to create 8 extra columns in a db when 80 percent of the entries are going to be null for those fields. I know you did not know that fact, but i just wanted to share the reason for my madness ;) – BLang Mar 22 '17 at 22:11

1 Answers1

2

Instead of escaping yourself, you should just use parameterized values:

cursor.execute("UPDATE TABLE1 SET FIELD1 = ? WHERE FIELD2 = 'some value'", (json.dumps(pythonDictionary)) )

How to use variables in SQL statement in Python?

Community
  • 1
  • 1
Ehz
  • 2,027
  • 1
  • 12
  • 11
  • Wow i did not know that would do the escaping for me, I will test tomorrow and up vote/ star this as the answer if it works!! If you have any other additional resources on secure interactions between python and SQL database, that would be appreciated! Thanks friend! – BLang Mar 22 '17 at 21:51