pymysql 插入 json类型的字段

原创

mob649e8155edc4 2024-01-23 10:16:18 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e8155edc4的原创作品，请联系作者获取转载授权，否则将追究法律责任

pymysql 插入 json类型的字段

在数据库中存储和操作 JSON 类型的数据非常方便。Python 中的 pymysql 库是一个流行的用于连接 MySQL 数据库的库，它也支持插入和查询 JSON 类型的字段。本文将介绍如何使用 pymysql 插入 JSON 类型的字段，并提供相应的代码示例。

准备工作

在开始之前，确保已经安装了 pymysql 库。如果没有安装，可以使用以下命令进行安装：

pip install pymysql

创建数据库和表

首先，我们需要创建一个示例数据库和表来存储 JSON 数据。以下是一个示例的 SQL 代码：

CREATE DATABASE travel_db;

USE travel_db;

CREATE TABLE travel (
  id INT AUTO_INCREMENT PRIMARY KEY,
  destination VARCHAR(255),
  details JSON
);

上述代码创建了一个名为 travel_db 的数据库，并在其中创建了一个名为 travel 的表。表中包含三个字段：id、destination 和 details。details 字段的类型为 JSON。

连接数据库

接下来，我们需要使用 pymysql 连接到数据库。首先，导入 pymysql 库，并使用以下代码连接到数据库：

import pymysql

connection = pymysql.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="travel_db"
)

在上述代码中，我们使用 pymysql 的 connect() 函数来连接到本地的 MySQL 数据库。你需要将 "your_username" 和 "your_password" 替换为你自己的数据库用户名和密码。

插入 JSON 数据

现在，我们可以开始插入 JSON 数据了。以下是一个示例代码，用于插入一条旅行数据到 travel 表中：

import json

destination = "Paris"
details = {
    "date": "2022-08-01",
    "duration": 7,
    "sights": ["Eiffel Tower", "Louvre Museum", "Notre-Dame Cathedral"]
}

# Convert details dictionary to JSON string
details_json = json.dumps(details)

# Create a cursor object
cursor = connection.cursor()

# Insert the data into the table
query = "INSERT INTO travel (destination, details) VALUES (%s, %s)"
cursor.execute(query, (destination, details_json))

# Commit the changes
connection.commit()

# Close the cursor and connection
cursor.close()
connection.close()

在上述代码中，我们首先定义了一个目的地（destination）和旅行详细信息（details）。然后，我们使用 json.dumps() 函数将 details 字典转换为 JSON 字符串。接下来，我们创建一个游标对象，并使用 execute() 方法执行插入数据的 SQL 语句。在 SQL 语句中，我们使用 %s 占位符来代表插入的值。最后，我们使用 commit() 方法提交更改，并关闭游标和连接。

查询 JSON 数据

要查询包含 JSON 类型字段的数据，我们可以使用 pymysql 的 fetchone() 或 fetchall() 方法。以下是一个示例代码，用于查询 travel 表中的所有旅行数据：

# Create a cursor object
cursor = connection.cursor()

# Select all data from the table
query = "SELECT * FROM travel"
cursor.execute(query)

# Fetch all rows
rows = cursor.fetchall()

# Iterate over the rows and print the data
for row in rows:
    destination = row[1]
    details_json = row[2]
    
    details = json.loads(details_json)
    
    print("Destination:", destination)
    print("Date:", details["date"])
    print("Duration:", details["duration"])
    print("Sights:", details["sights"])
    print()
    
# Close the cursor and connection
cursor.close()
connection.close()

在上述代码中，我们首先创建一个游标对象，并使用 execute() 方法执行查询语句。然后，我们使用 fetchall() 方法获取所有行的数据。接下来，我们使用 json.loads() 函数将 JSON 字符串转换为 Python 字典，并使用字典的键来访问详细信息。最后，我们打印出查询结果，并关闭游标和连接。