python操作json到mysql_使用Python将JSON插入MySQL

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

0x7ff68f91d7e8> (:

(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

0x7feb9d52b7e8> (:

(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()

解决方案

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