json转sql|极客笔记

json转sql

概述

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于前端与后端之间的数据传输。而SQL（Structured Query Language）是一种用于管理关系型数据库的语言。在实际开发中，我们经常会遇到需要将JSON数据转换为SQL语句的情况，从而方便地将数据存储到数据库中。本文将详细介绍如何将JSON转换为SQL，并给出相应的示例代码。

背景

假设我们有一个包含学生信息的JSON对象，每个学生包括学号、姓名、年龄和性别等属性。我们希望将这些学生的信息保存到一个名为students的数据库表中。因此，我们需要将JSON对象转换为以下形式的SQL语句：

INSERT INTO students (id, name, age, gender) VALUES ('001', 'Alice', 18, 'female');
INSERT INTO students (id, name, age, gender) VALUES ('002', 'Bob', 20, 'male');
...

方法一：手动拼接SQL语句

最简单直接的方法是手动拼接SQL语句，通过遍历JSON对象获取每个学生的属性，并将属性值拼接到对应的SQL语句中。以下是一个示例代码：

import json

def json_to_sql(json_data):
    sql_statements = []

    for student in json_data['students']:
        id = student['id']
        name = student['name']
        age = student['age']
        gender = student['gender']

        sql = f"INSERT INTO students (id, name, age, gender) VALUES ('{id}', '{name}', {age}, '{gender}');"
        sql_statements.append(sql)

    return sql_statements

# 示例数据
data = {
    "students": [
        {
            "id": "001",
            "name": "Alice",
            "age": 18,
            "gender": "female"
        },
        {
            "id": "002",
            "name": "Bob",
            "age": 20,
            "gender": "male"
        }
    ]
}

sql_statements = json_to_sql(data)
for statement in sql_statements:
    print(statement)

输出：

INSERT INTO students (id, name, age, gender) VALUES ('001', 'Alice', 18, 'female');
INSERT INTO students (id, name, age, gender) VALUES ('002', 'Bob', 20, 'male');

上述代码中，我们首先定义了一个函数json_to_sql，该函数接收一个包含学生信息的JSON对象作为参数，并返回一个包含SQL语句的列表。然后，我们遍历JSON对象中的每个学生，获取学生的属性值，并将其拼接到SQL语句中，最后将SQL语句添加到列表中并返回。

这种方法简单直接，但存在一定的安全风险，因为直接将属性值拼接到SQL语句中可能会导致SQL注入攻击。为了避免这种风险，我们可以使用参数化查询（使用占位符）的方法来构建SQL语句。

方法二：使用参数化查询

参数化查询是一种安全的SQL构建方式，通过使用占位符来代替实际的属性值，从而防止SQL注入攻击。以下是一个使用参数化查询的示例代码：

import json
import sqlite3

def json_to_sql(json_data, table_name):
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()

    for student in json_data['students']:
        id = student['id']
        name = student['name']
        age = student['age']
        gender = student['gender']

        sql = f"INSERT INTO {table_name} (id, name, age, gender) VALUES (?, ?, ?, ?);"
        cursor.execute(sql, (id, name, age, gender))

    conn.commit()
    conn.close()

# 示例数据
data = {
    "students": [
        {
            "id": "001",
            "name": "Alice",
            "age": 18,
            "gender": "female"
        },
        {
            "id": "002",
            "name": "Bob",
            "age": 20,
            "gender": "male"
        }
    ]
}

json_to_sql(data, 'students')

上述代码中，我们首先导入了sqlite3模块，用于连接SQLite数据库。然后，定义了一个函数json_to_sql，该函数接收一个包含学生信息的JSON对象和表名作为参数，并将JSON数据插入到指定的数据库表中。

在函数中，我们首先建立了与数据库的连接，并创建了一个光标（cursor）。然后，遍历JSON对象中的每个学生，使用参数化查询的方法构建SQL语句，并通过光标执行该语句。最后，我们提交事务并关闭数据库连接。

通过使用参数化查询，我们避免了直接拼接属性值到SQL语句中的安全风险，提高了代码的安全性。