代码收藏家技术教程 2025-02-17

使用mysql-connector-python库，这是MySQL官方推荐的Python驱动，新增、更新、查询、导出到EXCEL

下面是一个使用Python操作MySQL数据库进行数据插入和更新的简单示例。我们将使用mysql-connector-python库，这是MySQL官方推荐的Python驱动。如果你还没有安装这个库，可以通过pip安装它：

pip install mysql-connector-python

以下是代码示例：

import mysql.connector

# 连接到MySQL数据库
def connect_to_db():
    try:
        cnx = mysql.connector.connect(
            user='your_username',  # 替换为你的用户名
            password='your_password',  # 替换为你的密码
            host='127.0.0.1',  # 或者你的数据库服务器地址
            database='your_database'  # 替换为你的数据库名
        )
        print("成功连接到数据库")
        return cnx
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return None

# 插入数据
def insert_data(cnx):
    cursor = cnx.cursor()
    add_record = ("INSERT INTO your_table (column1, column2) "
                  "VALUES (%s, %s)")
    data_to_insert = ('value1', 'value2')  # 替换为你要插入的值
    
    try:
        cursor.execute(add_record, data_to_insert)
        cnx.commit()  # 提交事务以保存更改
        print("数据插入成功")
    except mysql.connector.Error as err:
        print(f"插入失败: {err}")
    finally:
        if cursor:
            cursor.close()

# 更新数据
def update_data(cnx):
    cursor = cnx.cursor()
    update_query = ("UPDATE your_table SET column1 = %s WHERE id = %s")
    data_to_update = ('new_value', 1)  # 假设id=1的行需要更新
    
    try:
        cursor.execute(update_query, data_to_update)
        cnx.commit()
        print("数据更新成功")
    except mysql.connector.Error as err:
        print(f"更新失败: {err}")
    finally:
        if cursor:
            cursor.close()

# 主函数
def main():
    db_connection = connect_to_db()
    if db_connection:
        try:
            insert_data(db_connection)
            update_data(db_connection)
        finally:
            db_connection.close()  # 确保关闭数据库连接

if __name__ == "__main__":
    main()

关键部分解释:

连接: connect_to_db() 函数用于建立到MySQL数据库的连接。
插入: 使用 insert_data() 函数执行SQL插入语句，将指定的数据插入到指定的表中。
更新: 类似地，update_data() 用于更新表中的记录。你需要指定新的值以及基于哪个条件（例如，通过ID）来更新记录。
事务管理: 在插入或更新后，通过调用 cnx.commit() 来提交事务，以确保数据被持久化到数据库。
异常处理: 对所有数据库操作都进行了异常处理，以捕获并报告可能的错误。
资源清理: 最后，无论是否发生错误，都确保在 finally 块中关闭游标和数据库连接，以避免资源泄露。

请记得替换 'your_username', 'your_password', 'your_database', 'your_table', 以及相应的列名和值，以适应你的具体环境和需求。

要在这段代码中添加数据查询功能，我们可以定义一个新的函数 `query_data()`。下面是如何修改之前的代码来包含查询数据的功能：

import mysql.connector

def connect_to_db():
    try:
        cnx = mysql.connector.connect(
            user='your_username',
            password='your_password',
            host='127.0.0.1',
            database='your_database'
        )
        print("成功连接到数据库")
        return cnx
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return None

def insert_data(cnx):
    # ... [插入数据逻辑保持不变] ...

def update_data(cnx):
    # ... [更新数据逻辑保持不变] ...

def query_data(cnx):
    cursor = cnx.cursor()
    query = ("SELECT * FROM your_table WHERE condition_column = %s")  # 替换为你的查询条件
    data_to_query = ('some_value', )  # 根据条件替换为实际的值
    
    try:
        cursor.execute(query, data_to_query)
        result = cursor.fetchall()  # 获取所有匹配的结果
        for row in result:
            print(row)  # 打印每一行的数据
        print("
查询完成")
    except mysql.connector.Error as err:
        print(f"查询失败: {err}")
    finally:
        if cursor:
            cursor.close()

def main():
    db_connection = connect_to_db()
    if db_connection:
        try:
            insert_data(db_connection)
            update_data(db_connection)
            query_data(db_connection)  # 添加这一行以执行查询
        finally:
            db_connection.close()

if __name__ == "__main__":
    main()

**关键部分解释**:

– **查询数据**: 新增了 `query_data()` 函数，它接受数据库连接对象作为参数。在该函数内部，我们定义了一个SQL查询字符串，并使用 `%s` 占位符来防止SQL注入。然后，通过传递具体的值给占位符，我们安全地执行查询。

– **获取结果**: 使用 `cursor.fetchall()` 方法获取所有查询到的行，然后遍历这些行并打印出来。这一步骤展示了如何从数据库中检索数据并进行处理。

– **整合到主函数**: 在 `main()` 函数中，新增了对 `query_data(db_connection)` 的调用，使得在主流程中也能执行数据查询操作。

请根据你的实际需求调整查询语句中的表名、条件和数据。此外，对于大型数据集，你可能希望一次只获取一行或者使用其他方法来优化内存使用，而不是一次性获取所有数据。

为了将从MySQL数据库查询得到的数据导出到Excel文件中，我们可以使用 pandas 库来处理数据，以及 mysql-connector-python 和 openpyxl 或 xlsxwriter（取决于你的偏好，但这里我会推荐使用 openpyxl 因为它支持更丰富的格式化选项）。首先，请确保安装了所需的库：

pip install pandas mysql-connector-python openpyxl

接下来，是一个完整的示例代码，演示如何查询MySQL数据库并将数据导出到Excel文件：

import pandas as pd
import mysql.connector

def connect_to_db():
    try:
        cnx = mysql.connector.connect(
            user='your_username',
            password='your_password',
            host='127.0.0.1',
            database='your_database'
        )
        print("成功连接到数据库")
        return cnx
    except mysql.connector.Error as err:
        print(f"连接错误: {err}")
        return None

def query_and_export_to_excel(cnx):
    query = ("SELECT * FROM your_table")  # 替换为你的查询语句
    df = pd.read_sql(query, con=cnx)  # 使用pandas读取SQL查询结果
    filename = "output.xlsx"  # 导出的Excel文件名
    
    try:
        with pd.ExcelWriter(filename, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='Sheet1', index=False)  # 将DataFrame写入Excel
        print(f"数据已成功导出到 {filename}")
    except Exception as e:
        print(f"导出失败: {e}")

def main():
    db_connection = connect_to_db()
    if db_connection:
        try:
            query_and_export_to_excel(db_connection)
        finally:
            db_connection.close()

if __name__ == "__main__":
    main()

关键步骤解释:

连接到数据库: 使用 connect_to_db() 函数与MySQL数据库建立连接。
查询数据并使用Pandas处理: 通过 pd.read_sql() 函数执行SQL查询，该函数直接接收数据库连接和SQL查询语句，然后返回一个Pandas DataFrame，这是一种非常方便的数据结构，便于进一步分析和处理。
导出到Excel: 利用 pd.ExcelWriter() 创建一个Excel写入器对象，然后通过调用 DataFrame 的 .to_excel() 方法将其内容写入到Excel文件中。这里选择 ‘openpyxl’ 作为引擎，因为它提供了更多的格式化功能。
文件命名: 可以根据需要更改导出的Excel文件的名称。
异常处理: 在整个过程中，我们确保了异常会被捕捉并适当处理，以保证程序的健壮性。