Databases
StackOverflow2013
Postsdbo
POInserting JSON into MySQL using Python
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserting JSON into MySQL using Python
    text
    copied!<p>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.</p> <p>At moment am getting errors and I believe it is due to the single quotes '' in the JSON Objects. </p> <p>How can I insert my JSON Object into MySQL using Python?</p> <p>Here is the error message I get:</p> <pre><code>error: uncaptured python exception, closing channel &lt;twitstream.twitasync.TwitterStreamPOST connected at 0x7ff68f91d7e8&gt; (&lt;class '_mysql_exceptions.ProgrammingError'&gt;: (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]) </code></pre> <p>Another error for reference</p> <pre><code>error: uncaptured python exception, closing channel &lt;twitstream.twitasync.TwitterStreamPOST connected at 0x7feb9d52b7e8&gt; (&lt;class '_mysql_exceptions.ProgrammingError'&gt;: (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]) </code></pre> <p>Here is a link to the code that I am using <a href="http://pastebin.com/q5QSfYLa" rel="noreferrer">http://pastebin.com/q5QSfYLa</a></p> <pre><code>#!/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) &lt; 1: args = ['Blackberry'] stream = twitstream.track(USER, PASS, callback, args, options.debug, engine=options.engine) # Loop forever on the streaming call: stream.run() </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload