将Python列表数据高效写入SQL Server数据库表

花韻仙語

发布时间：2025-10-31 12:18:18

311人浏览过

来源于php中文网

原创

将Python列表数据高效写入SQL Server数据库表

本文详细介绍了如何将包含结构化数据的python列表高效且安全地写入sql server数据库表。核心方法包括使用`ast.literal_eval()`安全解析字符串形式的python列表数据，以及利用数据库游标的`executemany()`方法进行批量数据插入，从而避免了手动解析错误和提高了数据导入效率。

引言：Python数据与数据库交互的挑战

在数据处理和应用开发中，我们经常需要将Python程序中处理好的数据存储到关系型数据库中，例如SQL Server。当数据以Python列表的形式存在，尤其是当这些列表本身是从文件或其他源读取的字符串形式时，如何高效、安全地将其导入数据库表是一个常见需求。本教程将针对一种常见的列表结构——包含键值对的嵌套列表（例如 [['ID', '001'], ['Name', 'Jon']]），提供一套完整的解决方案。

数据解析：从字符串到Python对象

假设我们从文件中读取到的数据是字符串形式的Python列表，例如：

[['ID', '001'], ['Name', 'Jon'], ['Score', '90'], ['Semester', 'Fall']]
[['ID', '002'], ['Name', 'Ama'], ['Score', '87'], ['Semester', 'Fall']]

直接使用字符串分割（如split(':')）来解析这种复杂的嵌套结构是不可靠且容易出错的。Python标准库提供了一个更安全、更健壮的方法来处理这种情况：ast.literal_eval()。

ast.literal_eval()函数能够安全地解析包含Python字面量（字符串、数字、元组、列表、字典、布尔值和None）的字符串，并将其转换为对应的Python对象。这比使用eval()函数更安全，因为它只评估字面量，不会执行任意代码。

立即学习“Python免费学习笔记（深入）”；

以下是如何使用ast.literal_eval()从文件中读取并解析数据：

from ast import literal_eval

def parse_data_from_file(filepath):
    """
    从指定文件中读取每行数据，并使用ast.literal_eval解析为Python列表。
    同时将[['Field', 'Value']]格式转换为['Value']格式。

    Args:
        filepath (str): 数据文件的路径。

    Returns:
        list: 包含所有解析后的行数据的列表，每行是一个值的列表。
    """
    parsed_rows = []
    try:
        with open(filepath, 'r', encoding='utf-8') as file:
            for line in file:
                # 移除行尾的换行符并解析为Python列表对象
                line_data = literal_eval(line.strip())
                # 从[['Field', 'Value']]格式中提取'Value'
                row_values = [value for field, value in line_data]
                parsed_rows.append(row_values)
    except FileNotFoundError:
        print(f"错误：文件 '{filepath}' 未找到。")
    except ValueError as e:
        print(f"错误：解析文件内容时发生问题 - {e}。请检查文件格式是否正确。")
    except Exception as e:
        print(f"发生未知错误：{e}")
    return parsed_rows

# 示例使用
file_path = 'files.txt' # 假设你的数据存储在这个文件中
# 确保 files.txt 包含如下内容（每行一个列表的字符串表示）:
# [['ID', '001'], ['Name', 'Jon'], ['Score', '90'], ['Semester', 'Fall']]
# [['ID', '002'], ['Name', 'Ama'], ['Score', '87'], ['Semester', 'Fall']]

processed_data = parse_data_from_file(file_path)
print("解析后的数据：")
for row in processed_data:
    print(row)

# 预期输出:
# ['001', 'Jon', '90', 'Fall']
# ['002', 'Ama', '87', 'Fall']

经过这一步处理，processed_data将是一个包含多个列表的列表，每个内部列表都只包含需要插入数据库的字段值，且顺序与数据库表的列顺序一致。

XYCMS建站系统php版1.4

XYCMS建站系统PHP版非MVC框架,自己手写原生态普通代码，作为企业用，已经绰绰有余。软件运行效率中等，加入数据缓存后性能提高。假如用来学习，下载可以慢慢研究的，假如用来建站，可以选择购买商业版就行建站用。栏目类别：文章，人员信息，专题项目，招聘，下载，相册，单页【支持无限极分类】文章：可用作添加新闻，资讯，列表信息类栏目信息人员信息：可用作企业员工信息栏目内容添加或者维护专题项目：可用作企业

下载

数据库写入：使用 executemany() 进行批量插入

将解析后的数据写入数据库时，为了提高效率，强烈推荐使用数据库游标（cursor）对象的executemany()方法进行批量插入，而不是循环执行多次execute()。executemany()能够将多行数据一次性发送到数据库服务器，显著减少了网络往返次数和数据库操作开销。

以下是使用executemany()将数据插入SQL Server表的示例代码。请注意，你需要替换数据库连接和游标的获取方式，这取决于你使用的具体Python数据库驱动（例如pyodbc、pymssql等）。

import pyodbc # 以pyodbc为例，你需要安装它：pip install pyodbc

def insert_data_to_database(data_to_insert, connection_string, table_name, column_names):
    """
    将处理后的数据批量插入到SQL Server数据库表。

    Args:
        data_to_insert (list): 包含待插入数据的列表，每个元素是一个行的值的列表。
        connection_string (str): 数据库连接字符串。
        table_name (str): 目标数据库表的名称。
        column_names (list): 目标表的列名列表，顺序需与data_to_insert中的值顺序一致。
    """
    conn = None
    cursor = None
    try:
        # 建立数据库连接
        # 示例连接字符串，请根据你的SQL Server配置进行修改
        # conn_str = (
        #     "DRIVER={ODBC Driver 17 for SQL Server};"
        #     "SERVER=your_server_name;"
        #     "DATABASE=your_database_name;"
        #     "UID=your_username;"
        #     "PWD=your_password"
        # )
        conn = pyodbc.connect(connection_string)
        cursor = conn.cursor()

        # 构建INSERT语句
        # 使用问号 (?) 作为参数占位符，适用于许多ODBC驱动
        # 如果是其他驱动，占位符可能不同（例如psycopg2使用%s，sqlite3使用?）
        placeholders = ', '.join(['?' for _ in column_names])
        columns_str = ', '.join(column_names)
        insert_sql = f"INSERT INTO {table_name} ({columns_str}) VALUES ({placeholders})"

        # 执行批量插入
        cursor.executemany(insert_sql, data_to_insert)

        # 提交事务
        conn.commit()
        print(f"成功将 {len(data_to_insert)} 条数据插入到表 '{table_name}'。")

    except pyodbc.Error as ex:
        sqlstate = ex.args[0]
        print(f"数据库操作失败：SQLSTATE {sqlstate} - {ex}")
        if conn:
            conn.rollback() # 发生错误时回滚事务
    except Exception as e:
        print(f"发生未知错误：{e}")
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

# 示例使用
# 1. 定义你的数据库连接字符串
# 请替换为你的实际数据库连接信息
db_connection_string = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;" # 或者你的服务器IP/名称
    "DATABASE=TestDB;"  # 你的数据库名称
    "UID=sa;"           # 你的用户名
    "PWD=your_password;" # 你的密码
)

# 2. 定义目标表名和列名
target_table = "yourTable" # 替换为你的实际表名
# 确保列名顺序与 processed_data 中值的顺序一致
table_columns = ["ID", "Name", "Score", "Semester"]

# 3. 准备要插入的数据（使用之前解析好的数据）
# processed_data = [['001', 'Jon', '90', 'Fall'], ['002', 'Ama', '87', 'Fall']] # 假设这是从文件解析来的

# 4. 执行插入操作
insert_data_to_database(processed_data, db_connection_string, target_table, table_columns)

注意事项：

数据库驱动和连接字符串：示例中使用了pyodbc和SQL Server的ODBC驱动。根据你的数据库类型（如MySQL、PostgreSQL、SQLite）和Python驱动（如mysql-connector-python、psycopg2、sqlite3），连接字符串和驱动安装会有所不同。
SQL语句占位符：SQL语句中的参数占位符（?）是针对ODBC驱动的。其他数据库或驱动可能使用不同的占位符，例如%s（psycopg2）、:（cx_Oracle）或命名参数。
事务管理：在executemany()之后，务必调用conn.commit()来提交事务，使更改永久保存到数据库。如果发生错误，使用conn.rollback()可以撤销未提交的更改。
错误处理：示例代码包含了基本的try-except-finally块，用于处理文件操作和数据库操作中可能出现的错误，并确保资源（文件句柄、数据库连接和游标）被正确关闭。
数据类型匹配：确保Python列表中的数据类型与数据库表中对应列的数据类型兼容。例如，如果数据库列是整数类型，Python中的值也应该是整数或可转换为整数的字符串。

总结

通过本教程，我们学习了如何高效且安全地将Python中的结构化列表数据导入SQL Server数据库。关键步骤包括：

安全解析数据：利用ast.literal_eval()将从文件或其他源获取的字符串形式的Python列表转换为实际的Python对象，并提取出所需的值列表。
批量高效插入：使用数据库游标的executemany()方法进行批量数据插入，这比单条插入能显著提升性能。
完善的错误处理和资源管理：通过try-except-finally确保操作的健壮性。

遵循这些实践，可以有效地管理Python应用程序与关系型数据库之间的数据流，提高数据处理的效率和可靠性。

如何解决 MySQL 连接中密码含特殊字符（如 @）导致的 URL 解析错误

如何正确处理 MySQL 连接字符串中的特殊字符（如 @ 符号）

sqlalchemy 如何写“插入或更新”并返回受影响行数

sqlalchemy 如何写“存在则更新，不存在则插入”（upsert）

sqlalchemy 如何写批量插入或忽略（on conflict do nothing）