python里面将接口返回的json格式数据写入到数据库的两种方案及其局限性

概要

方案一：
使用MySQLdb或pymysql等Python MySQL数据库连接库将数据插入到MySQL数据库

方案二：
使用pandas库将JSON数据转换为DataFrame对象，然后使用to_sql()方法将数据存入MySQL数据库**

方案一详解：

适用的场景

对整体的数据格式支持自定义处理，能处理较为复杂的数据格式

首先，我们使用json.load()函数将"data.json"文件中的JSON数据解析为Python对象。然后，我们使用pymysql库连接到MySQL数据库，并使用循环插入数据到数据库表中。在这个例子中，我们假设数据是一个列表，每个元素都是一个包含"name"、"age"和"city"字段的字典。您需要根据您的数据结构来调整代码。

import json
import pymysql

# 解析JSON数据
with open("data.json", "r") as f:
    data = json.load(f)

# 连接MySQL数据库
conn = pymysql.connect(host="localhost", user="your_username", password="your_password", database="your_database")

# 插入数据
cursor = conn.cursor()
for record in data:
    name = record["name"]
    age = record["age"]
    city = record["city"]
    cursor.execute("INSERT INTO your_table (name, age, city) VALUES (%s, %s, %s)", (name, age, city))

# 提交更改并关闭连接
conn.commit()
cursor.close()
conn.close()

方案二详解：

适用的场景

对整体的数据格式无特别的需求，比如json数据里面无字典、列表等特殊的数据格式，使用该方案处理更为方便、快捷

首先，我们使用json.load()函数将"data.json"文件中的JSON数据解析为Python对象。然后，我们使用pandas库将数据转换为DataFrame对象。最后，我们使用SQLAlchemy库连接到MySQL数据库，并使用DataFrame对象的to_sql()方法将数据存入数据库表中。

这种方法不仅简单，而且还能够自动处理数据类型和索引等问题。您可以根据需要调整参数，例如更改if_exists参数的值以指定数据插入方式（“replace”、“append"或"fail”）

import json
import pandas as pd
from sqlalchemy import create_engine

# 解析JSON数据
with open("data.json", "r") as f:
    data = json.load(f)

# 转换为DataFrame对象
df = pd.DataFrame(data)

# 连接MySQL数据库
engine = create_engine("mysql+pymysql://{user}:{password}@{host}/{database}".format(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
))

# 存入数据库
df.to_sql(name='your_table', con=engine, if_exists='replace', index=False)

优势

方便快捷，无需创建数据库表，也需要对单个字段做推送

局限性

场景一：从api接口里面获取过来的数据同时存在：字符串和字典格式的数据,也就是字典里面嵌套字典，如下数据格式：

{objectId': '7917a4cc-ac9f-4cfc-8d85-a92a15468e1d', 'customProperties': None, 'translateProperties': {'xxModifiedByText': '任文轩(renwenxuan608029@xx.com)', 'StdIsDeletedText': '否', 'IsVirtualOrgText': '否', 'BroadTypeText': '机构', 'ModifiedByText': '任文轩(renwenxuan608029@xx.com)', 'IsCurrentRecordText': '是', 'POIdOrgAdmin_TreePathText': 'xx', 'IsLegalCompanyText': '否', 'POIdOrgAdminNameTreePathText': 'xx', 'IsDeletedText': '否', 'StatusText': '启用', 'CreatedByText': '系统(Administrator@beisen.com)'}, 'createdBy': 10000, 'createdTime': '2023-04-21T14:41:58'}

此时，没有经过处理的数据，是无法写入数据库。
推荐的处理方案：
思路1【已验证可行】：从DataFrame里面将字典的列过滤掉（前提是该列无重要的信息），如实例数据里面的：translateProperties 列
处理代码：

df.drop(columns=['translateProperties'], inplace=True)

思路2【暂未通过】：将该列由字典列，通过函数将其转为字符串，然后存入系统中：df.astype(‘str’)
不知道是代码的问题，还是其他的问题，暂时没有转换成功