Inserting JSON into MySQL using Python

Question

I have a JSON object in Python. I am Using Python DB-API and SimpleJson. I am trying to insert the json into a MySQL table.

At moment am getting errors and I believe it is due to the single quotes '' in the JSON Objects.

How can I insert my JSON Object into MySQL using Python?

Here is the error message I get:

error: uncaptured python exception, closing channel 
<twitstream.twitasync.TwitterStreamPOST connected at 
0x7ff68f91d7e8> (<class '_mysql_exceptions.ProgrammingError'>:
(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 ''favorited': '0', 
'in_reply_to_user_id': '52063869', 'contributors': 
'NULL', 'tr' at line 1") 
[/usr/lib/python2.5/asyncore.py|read|68] 
[/usr/lib/python2.5/asyncore.py|handle_read_event|390] 
[/usr/lib/python2.5/asynchat.py|handle_read|137] 
[/usr/lib/python2.5/site-packages/twitstream-0.1-py2.5.egg/
twitstream/twitasync.py|found_terminator|55] [twitter.py|callback|26] 
[build/bdist.linux-x86_64/egg/MySQLdb/cursors.py|execute|166] 
[build/bdist.linux-x86_64/egg/MySQLdb/connections.py|defaulterrorhandler|35])

Another error for reference

error: uncaptured python exception, closing channel 
<twitstream.twitasync.TwitterStreamPOST connected at 
0x7feb9d52b7e8> (<class '_mysql_exceptions.ProgrammingError'>:
(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 'RT @tweetmeme The Best BlackBerry Pearl 
Cell Phone Covers http://bit.ly/9WtwUO''' at line 1") 
[/usr/lib/python2.5/asyncore.py|read|68] 
[/usr/lib/python2.5/asyncore.py|handle_read_event|390] 
[/usr/lib/python2.5/asynchat.py|handle_read|137] 
[/usr/lib/python2.5/site-packages/twitstream-0.1-
py2.5.egg/twitstream/twitasync.py|found_terminator|55] 
[twitter.py|callback|28] [build/bdist.linux-
x86_64/egg/MySQLdb/cursors.py|execute|166] [build/bdist.linux-
x86_64/egg/MySQLdb/connections.py|defaulterrorhandler|35])

Here is a link to the code that I am using http://pastebin.com/q5QSfYLa

#!/usr/bin/env python

try:
        import json as simplejson
except ImportError:
        import simplejson

import twitstream
import MySQLdb

USER = ''
PASS = ''

USAGE = """%prog"""


conn = MySQLdb.connect(host = "",
                       user = "",
                       passwd = "",
                       db = "")

# Define a function/callable to be called on every status:
def callback(status):

    twitdb = conn.cursor ()
    twitdb.execute ("INSERT INTO tweets_unprocessed (text, created_at, twitter_id, user_id, user_screen_name, json) VALUES (%s,%s,%s,%s,%s,%s)",(status.get('text'), status.get('created_at'), status.get('id'), status.get('user', {}).get('id'), status.get('user', {}).get('screen_name'), status))

   # print status
     #print "%s:\t%s\n" % (status.get('user', {}).get('screen_name'), status.get('text'))

if __name__ == '__main__':
    # Call a specific API method from the twitstream module:
    # stream = twitstream.spritzer(USER, PASS, callback)

    twitstream.parser.usage = USAGE
    (options, args) = twitstream.parser.parse_args()

    if len(args) < 1:
        args = ['Blackberry']

    stream = twitstream.track(USER, PASS, callback, args, options.debug, engine=options.engine)

    # Loop forever on the streaming call:
    stream.run()

Solution 1

use json.dumps(json_value) to convert your json object(python object) in a json string that you can insert in a text field in mysql

http://docs.python.org/library/json.html

Solution 2

To expand on the other answers:

Basically you need make sure of two things:

1. That you have room for the full amount of data that you want to insert in the field that you are trying to place it. Different database field types can fit different amounts of data. See: MySQL String Datatypes. You probably want the "TEXT" or "BLOB" types.

2. That you are safely passing the data to database. Some ways of passing data can cause the database to "look" at the data and it will get confused if the data looks like SQL. It's also a security risk. See: SQL Injection

The solution for #1 is to check that the database is designed with correct field type.

The solution for #2 is use parameterized (bound) queries. For instance, instead of:

# Simple, but naive, method.
# Notice that you are passing in 1 large argument to db.execute()
db.execute("INSERT INTO json_col VALUES (" + json_value + ")")

Better, use:

# Correct method. Uses parameter/bind variables.
# Notice that you are passing in 2 arguments to db.execute()
db.execute("INSERT INTO json_col VALUES %s", json_value)

Hope this helps. If so, let me know. :-)

If you are still having a problem, then we will need to examine your syntax more closely.

Solution 3

The most straightforward way to insert a python map into a MySQL JSON field...

python_map = { "foo": "bar", [ "baz", "biz" ] }

sql = "INSERT INTO your_table (json_column_name) VALUES (%s)"
cursor.execute( sql, (json.dumps(python_map),) )

Solution 4

You should be able to insert intyo a text or blob column easily

db.execute("INSERT INTO json_col VALUES %s", json_value)

Solution 5

You need to get a look at the actual SQL string, try something like this:

sqlstr = "INSERT INTO tweets_unprocessed (text, created_at, twitter_id, user_id, user_screen_name, json) VALUES (%s,%s,%s,%s,%s,%s)", (status.get('text'), status.get('created_at'), status.get('id'), status.get('user', {}).get('id'), status.get('user', {}).get('screen_name'), status)
print "about to execute(%s)" % sqlstr
twitdb.execute(sqlstr)

I imagine you are going to find some stray quotes, brackets or parenthesis in there.

View more solutions

Comments

[Gerrat]

Both errors shown by OP contain: "You have an error in your SQL syntax;...". This has nothing to do with an overflow of the size of the field.

[nonot1]

@Aran Did my answer help? If so vote up and mark answered.. thanks. :)

[Aran]

I have just uploaded my code and still seem to be getting errors am trying various ways to write the SQL query...

[Aran]

Ok if I have stray quotes, brackets or parenthesis - how can I prepare the string for use with SQL is there a function in Python DB-API?

[qneill]

For an error in the SQL syntax, "preparing the string for use with SQL" would mean fixing the syntax... probably not an API call for that :) Post the output from the "about to execute..." statement.

[Fr0zenFyr]

str(json.dumps(json_value)) worked for me when data type of column is text. Without str() there was no error but the column was empty

[dimButTries]

many thanks this worked great when updating a row via a custom command-line interface by wrapping the nested json object with json.dumps