使用 Python 将 JSON 插入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4251124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting JSON into MySQL using Python
提问by Aran
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.
我在 Python 中有一个 JSON 对象。我正在使用 Python DB-API 和 SimpleJson。我正在尝试将 json 插入到 MySQL 表中。
At moment am getting errors and I believe it is due to the single quotes '' in the JSON Objects.
目前出现错误，我相信这是由于 JSON 对象中的单引号 '' 造成的。
How can I insert my JSON Object into MySQL using Python?
如何使用 Python 将我的 JSON 对象插入 MySQL？
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
这是我正在使用的代码的链接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()
采纳答案by Mordi
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
使用 json.dumps(json_value) 将您的 json 对象（python 对象）转换为 json 字符串，您可以将其插入到 mysql 的文本字段中
回答by Aif
The error may be due to an overflow of the size of the field in which you try to insert your json. Without any code, it is hard to help you.
该错误可能是由于您尝试插入 json 的字段大小溢出。没有任何代码，很难帮助你。
Have you considerate a no-sql database system such as couchdb, which is a document oriented database relying on json format?
有没有考虑过couchdb这种依赖json格式的面向文档的数据库系统？
回答by Vince Spicer
You should be able to insert intyo a text or blob column easily
您应该能够轻松插入文本或 blob 列
db.execute("INSERT INTO json_col VALUES %s", json_value)
回答by nonot1
To expand on the other answers:
要扩展其他答案：
Basically you need make sure of two things:
基本上你需要确保两件事：
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.
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
您有足够的空间容纳要插入到要放置的字段中的全部数据。不同的数据库字段类型可以适应不同数量的数据。请参阅：MySQL 字符串数据类型。您可能需要“TEXT”或“BLOB”类型。
您正在安全地将数据传递到数据库。某些传递数据的方式可能会导致数据库“查看”数据，如果数据看起来像 SQL，它会感到困惑。这也是一个安全风险。请参阅：SQL 注入
The solution for #1 is to check that the database is designed with correct field type.
#1 的解决方案是检查数据库是否使用正确的字段类型设计。
The solution for #2 is use parameterized (bound) queries. For instance, instead of:
#2 的解决方案是使用参数化（绑定）查询。例如，而不是：
# 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.
如果您仍然遇到问题，那么我们将需要更仔细地检查您的语法。
回答by qneill
You need to get a look at the actual SQL string, try something like this:
您需要查看实际的 SQL 字符串，尝试如下操作：
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.
我想你会在那里找到一些杂散的引号、方括号或圆括号。
回答by kg3
@route('/shoes', method='POST')
def createorder():
cursor = db.cursor()
data = request.json
p_id = request.json['product_id']
p_desc = request.json['product_desc']
color = request.json['color']
price = request.json['price']
p_name = request.json['product_name']
q = request.json['quantity']
createDate = datetime.now().isoformat()
print (createDate)
response.content_type = 'application/json'
print(data)
if not data:
abort(400, 'No data received')
sql = "insert into productshoes (product_id, product_desc, color, price, product_name, quantity, createDate) values ('%s', '%s','%s','%d','%s','%d', '%s')" %(p_id, p_desc, color, price, p_name, q, createDate)
print (sql)
try:
# Execute dml and commit changes
cursor.execute(sql,data)
db.commit()
cursor.close()
except:
# Rollback changes
db.rollback()
return dumps(("OK"),default=json_util.default)
回答by Stefan
The most straightforward way to insert a python map into a MySQL JSON field...
将python映射插入MySQL JSON字段的最直接方法...
python_map = { "foo": "bar", [ "baz", "biz" ] }
sql = "INSERT INTO your_table (json_column_name) VALUES (%s)"
cursor.execute( sql, (json.dumps(python_map),) )
回答by Nitin Nain
Here's a quick tip, if you want to write some inline code, say for a small json value, without import json.
You can escape quotes in SQL by a double quoting, i.e. use ''or "", to enter 'or ".
这是一个快速提示，如果你想编写一些内联代码，比如一个小的 json 值，没有import json. 您可以通过双引号将 SQL 中的引号转义，即使用''或""，输入'或"。
Sample Python code (not tested):
示例 Python 代码（未测试）：
q = 'INSERT INTO `table`(`db_col`) VALUES ("{k:""some data"";}")'
db_connector.execute(q)
