如何从PYTHON使用mysql-connector将MySQL数据库导出到json？

i7uaboj4 于 2023-10-21 发布在 Python

关注(0)|答案(2)|浏览(115)

我想使用python中的mysql-connector和json库将一个带有一个表的mysql数据库导出到json中。

我尝试的内容：

import mysql-connector, json

cursor.execute("SELECT * FROM records")
data=cursor.fetchall()
for e in data:
  print(json.dumps(data, sort_keys=True, indent=4, separators=(',', ': ')))

回溯错误：

Traceback (most recent call last):
  File "bsentor.py", line 238, in <module>
    print(json.dumps(data))
  File "C:\Users\Justo Gregorio\AppData\Local\Programs\Python\Python37\lib\json\__init__.py", line 231, in dumps
    return _default_encoder.encode(obj)
  File "C:\Users\Justo Gregorio\AppData\Local\Programs\Python\Python37\lib\json\encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "C:\Users\Justo Gregorio\AppData\Local\Programs\Python\Python37\lib\json\encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "C:\Users\Justo Gregorio\AppData\Local\Programs\Python\Python37\lib\json\encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type date is not JSON serializable

JSON

来源：https://stackoverflow.com/questions/57068524/how-to-export-a-mysql-database-to-json-using-mysql-connector-from-python

2条答案

按热度按时间

ev7lccsx1#

如果您有无法序列化为JSON的对象，则可以在json.dumps上使用default=str

cursor.execute("SELECT * FROM registros")
data=cursor.fetchall()
for e in data:
  print(json.dumps(data, sort_keys=True, indent=4, separators=(',', ': '), default=str))

赞(0）回复(0）举报 2023-10-21

zbdgwd5y2#

我将表的排序规则更新为utf8mb3_unicode_ci

alter table MyTable convert to character set utf8mb3 collate utf8mb3_unicode_ci;

我用这个python代码得到了json结果

@app.route("/records")
def get_records():
    mydb = mysql.connector.connect(host="localhost",user="me",password="xxx",database="mydb")
    cursor = mydb.cursor(dictionary=True)
    query = "SELECT * FROM records"
    cursor.execute(query)
    result = cursor.fetchall()
    return result

赞(0）回复(0）举报 2023-10-21

我来回答

如何从PYTHON使用mysql-connector将MySQL数据库导出到json？

2条答案

相关问题

热门标签

最新问答