MySQL之操作大全

合集 - 数据库应用(2)

1.VirtualBox下Ubuntu中安装Grafana03-11

2.MySQL之操作大全03-11

收起

目录

• 我常用的
  • 建立标准配置，将dataframe导入mysql
  • 从dataframe创建mysql数据库
  • 把dataframe里的数据插入到mysql数据库
  • 两个位置不同的数据库实现同步
• ER图构建
• 参考文档

我常用的

建立标准配置，将dataframe导入mysql

特点：使用 dotenv 从 .env 文件中加载环境变量

以下是一个完整的示例，展示如何从环境变量中获取数据库配置：

1. 安装 python-dotenv 库
   首先，确保你已经安装了 python-dotenv 库。如果没有安装，可以通过以下命令安装：
   pip install python-dotenv

2. 创建 .env 文件
   在项目根目录下创建一个 .env 文件，并将数据库配置写入该文件。例如：

# .env 文件内容
DB_USER=your_username
DB_PASSWORD=your_password
DB_HOST=your_host
DB_DATABASE=your_database

3. 在代码中加载环境变量
   在代码中使用 dotenv 加载 .env 文件中的环境变量，并从中获取数据库配置。以下是完整的代码示例：

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
import pandas as pd
import logging

# 配置日志记录
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 加载 .env 文件中的环境变量
load_dotenv()

# 从环境变量中获取数据库配置
db_config = {
    'user': os.getenv('DB_USER'),          # 数据库用户名
    'password': os.getenv('DB_PASSWORD'),  # 数据库密码
    'host': os.getenv('DB_HOST'),          # 数据库主机地址
    'database': os.getenv('DB_DATABASE')   # 数据库名称
}

def create_db_engine(db_config):
    """创建SQLAlchemy引擎"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://{db_config['user']}:{db_config['password']}@"
            f"{db_config['host']}/{db_config['database']}"
        )
        logger.info("数据库引擎创建成功")
        return engine
    except Exception as e:
        logger.error(f"创建数据库引擎失败: {e}")
        raise

def read_excel_file(file_path, sheet_name):
    """读取Excel文件"""
    try:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        logger.info(f"成功读取文件: {file_path}")
        return df
    except Exception as e:
        logger.error(f"读取文件失败: {e}")
        raise

def write_to_mysql(engine, df, table_name, if_exists='replace'):
    """将数据写入MySQL"""
    try:
        df.to_sql(table_name, con=engine, if_exists=if_exists, index=False)
        logger.info(f"数据成功写入表: {table_name}")
    except Exception as e:
        logger.error(f"写入数据失败: {e}")
        raise

def main():
    # 创建数据库引擎
    engine = create_db_engine(db_config)

    # 读取Excel文件
    file_A = 'path_to_excel_file_A.xlsx'
    df_A = read_excel_file(file_A, sheet_name='Sheet0')

    # 将数据写入MySQL
    write_to_mysql(engine, df_A, 'table_A')

if __name__ == "__main__":
    main()

关键点说明：

• .env 文件：该文件存储了数据库的敏感信息（如用户名、密码等）。确保 .env 文件不会被提交到版本控制系统（如 Git）中。可以在 .gitignore 文件中添加 .env，以避免泄露敏感信息。
• load_dotenv()：该函数会从 .env 文件中加载环境变量，并将其添加到 os.environ 中。之后可以通过 os.getenv() 获取这些环境变量。
• 从环境变量中获取配置：使用 os.getenv('KEY') 获取环境变量的值。如果环境变量不存在，os.getenv() 会返回 None，因此可以设置默认值，例如：
  db_user = os.getenv('DB_USER', 'default_user')
• 安全性：通过环境变量管理敏感信息，可以避免将这些信息硬编码在代码中。在生产环境中，可以直接在操作系统的环境变量中设置这些值，而不需要 .env 文件。
• 生产环境中的环境变量设置：在生产环境中，通常不会使用 .env 文件，而是直接在操作系统的环境变量中设置这些值。

例如：Linux/MacOS：
在终端中运行以下命令：

export DB_USER=your_username
export DB_PASSWORD=your_password
export DB_HOST=your_host
export DB_DATABASE=your_database

Windows：
在命令提示符中运行以下命令：

cmd

set DB_USER=your_username
set DB_PASSWORD=your_password
set DB_HOST=your_host
set DB_DATABASE=your_database

然后在代码中直接使用 os.getenv() 获取这些值即可。

总结
通过使用环境变量管理数据库配置，可以提高代码的安全性和可维护性。dotenv 是一个方便的工具，可以在开发和测试环境中使用 .env 文件，而在生产环境中则可以直接使用操作系统的环境变量。

从dataframe创建mysql数据库

# -*- coding: utf-8 -*-

import pandas as pd
from sqlalchemy import create_engine
import pymysql as py

def create_mysql_table(df, table_name, primary_key=None):
    # 检查DataFrame是否为空
    if df.empty:
        raise ValueError("DataFrame 不能为空")

    # 获取DataFrame的列名和数据类型
    columns = df.dtypes.index.tolist()
    dtypes = df.dtypes.values.tolist()

    # 创建表的SQL语句
    create_table_query = f"CREATE TABLE {table_name} ("

    # 构建列的定义
    for col, dtype in zip(columns, dtypes):
        if pd.api.types.is_object_dtype(dtype) or pd.api.types.is_string_dtype(dtype):
            max_length = df[col].astype(str).apply(len).max()  # 获取列数据的最大长度
            varchar_length = min(max_length, 255)  # 限制VARCHAR长度最大为255
            create_table_query += f"{col} VARCHAR({varchar_length}), "
        elif pd.api.types.is_datetime64_any_dtype(dtype):
            create_table_query += f"{col} DATETIME, "  # 使用DATETIME而不是DATE
        elif pd.api.types.is_float_dtype(dtype):
            max_decimals = df[col].apply(lambda x: len(str(x).split('.')[-1])).max()  # 获取浮点数小数点后的最大位数
            float_length = min(max_decimals + 4, 38)  # 限制FLOAT小数点后数据长度最大为38
            create_table_query += f"{col} FLOAT({float_length}), "
        elif pd.api.types.is_integer_dtype(dtype):
            create_table_query += f"{col} INT, "
        elif pd.api.types.is_bool_dtype(dtype):
            create_table_query += f"{col} TINYINT, "
        elif pd.api.types.is_decimal_dtype(dtype):
            create_table_query += f"{col} DECIMAL(10, 2), "  # 假设DECIMAL类型，精度和小数位数可根据需要调整
        elif pd.api.types.is_categorical_dtype(dtype):
            create_table_query += f"{col} VARCHAR(255), "  # 将分类数据视为VARCHAR
        else:
            create_table_query += f"{col} TEXT, "

    # 添加主键定义
    if primary_key:
        create_table_query += f"PRIMARY KEY ({primary_key}), "

    create_table_query = create_table_query.rstrip(', ') + ") CHARACTER SET utf8mb4;" #设置默认字符集为utf8mb4以支持中文

    # 返回创建表的SQL语句
    return create_table_query

把dataframe里的数据插入到mysql数据库

import pandas as pd
import mysql.connector
from mysql.connector import Error
from datetime import datetime

def insert_dataframe_to_mysql(df, table_name, db_config):
    """
    将 DataFrame 数据插入到 MySQL 数据库的指定表中
    :param df: 要插入的 DataFrame
    :param table_name: 目标表名
    :param db_config: 数据库配置信息
    """
    try:
        # 建立数据库连接
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()

        # 创建表（如果不存在）
        columns = ', '.join([f"`{col}` VARCHAR(255)" for col in df.columns])
        create_table_query = f"CREATE TABLE IF NOT EXISTS `{table_name}` ({columns})"
        cursor.execute(create_table_query)

        # 插入数据
        columns_str = ', '.join([f"`{col}`" for col in df.columns])
        placeholders = ', '.join(['%s'] * len(df.columns))
        insert_query = f"INSERT INTO `{table_name}` ({columns_str}) VALUES ({placeholders})"
        for row in df.values.tolist():
            cursor.execute(insert_query, row)

        # 提交更改
        connection.commit()
        print(f"数据已成功插入到表 {table_name} 中")

        # 更新记录表
        update_record_table(table_name, df, db_config)

    except Error as e:
        print(f"插入数据时发生错误: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def update_record_table(table_name, df, db_config):
    """
    更新记录表，记录数据库中表格的更新情况
    :param table_name: 要记录的表名
    :param df: 插入的数据 DataFrame
    :param db_config: 数据库配置信息
    """
    try:
        # 建立数据库连接
        connection = mysql.connector.connect(**db_config)
        cursor = connection.cursor()

        # 创建记录表（如果不存在）
        create_record_table_query = """
        CREATE TABLE IF NOT EXISTS `record_table` (
            `table_name` VARCHAR(255) PRIMARY KEY,
            `purpose` VARCHAR(255),
            `creation_date` DATETIME,
            `update_date` DATETIME,
            `total_records` INT,
            `updated_records` INT,
            `update_count` INT
        )
        """
        cursor.execute(create_record_table_query)

        # 获取当前日期时间
        now = datetime.now()

        # 查询记录表中是否已有该表的记录
        select_query = f"SELECT * FROM `record_table` WHERE `table_name` = '{table_name}'"
        cursor.execute(select_query)
        existing_record = cursor.fetchone()

        if existing_record:
            # 如果已有记录，更新记录
            total_records = existing_record[4] + len(df)
            updated_records = len(df)
            update_count = existing_record[6] + 1
            update_query = f"""
            UPDATE `record_table`
            SET `update_date` = '{now}',
                `total_records` = {total_records},
                `updated_records` = {updated_records},
                `update_count` = {update_count}
            WHERE `table_name` = '{table_name}'
            """
        else:
            # 如果没有记录，插入新记录
            purpose = "示例用途"  # 这里可以根据实际情况修改
            creation_date = now
            total_records = len(df)
            updated_records = len(df)
            update_count = 1
            insert_query = f"""
            INSERT INTO `record_table` (`table_name`, `purpose`, `creation_date`, `update_date`, `total_records`, `updated_records`, `update_count`)
            VALUES ('{table_name}', '{purpose}', '{creation_date}', '{now}', {total_records}, {updated_records}, {update_count})
            """
            cursor.execute(insert_query)

        if existing_record:
            cursor.execute(update_query)

        # 提交更改
        connection.commit()
        print(f"记录表已更新，表名: {table_name}")

    except Error as e:
        print(f"更新记录表时发生错误: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# 示例使用
if __name__ == "__main__":
    # 示例 DataFrame
    data = {
        'Name': ['Alice', 'Bob', 'Charlie'],
        'Age': [25, 30, 35]
    }
    df = pd.DataFrame(data)

    # 数据库配置
    db_config = {
        'host': 'localhost',
        'database': 'your_database_name',
        'user': 'your_username',
        'password': 'your_password'
    }

    # 要插入的表名
    table_name = 'your_table_name'

    # 插入数据并更新记录表
    insert_dataframe_to_mysql(df, table_name, db_config)

两个位置不同的数据库实现同步

考虑以下几个关键点：

• 同步方向：支持单向同步（从数据库 A 到数据库 B，或从数据库 B 到数据库 A）和双向同步。
• 冲突解决：在双向同步中，如果两个数据库的同一行数据都被修改，需要定义冲突解决策略（例如，以时间戳最新的为准，或优先使用某个数据库的数据）。
• 性能优化：对于大数据量的同步，需要考虑分块读取和写入，避免内存溢出。
• 日志记录：记录同步过程中的关键信息，便于调试和监控。
• 异常处理：捕获并处理同步过程中可能出现的异常。

以下是一个实现数据库同步的函数示例：

import pandas as pd
from sqlalchemy import create_engine
import logging
from datetime import datetime

# 配置日志记录
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def create_db_engine(db_config):
    """创建SQLAlchemy引擎"""
    try:
        engine = create_engine(
            f"mysql+mysqlconnector://{db_config['user']}:{db_config['password']}@"
            f"{db_config['host']}/{db_config['database']}"
        )
        logger.info(f"数据库引擎创建成功: {db_config['database']}")
        return engine
    except Exception as e:
        logger.error(f"创建数据库引擎失败: {e}")
        raise

def sync_databases(engine_A, engine_B, table_name, sync_direction='both', conflict_resolution='latest'):
    """
    同步两个数据库中的表数据

    参数:
    - engine_A: 数据库A的SQLAlchemy引擎
    - engine_B: 数据库B的SQLAlchemy引擎
    - table_name: 需要同步的表名
    - sync_direction: 同步方向，可选值为 'A_to_B', 'B_to_A', 'both'（默认）
    - conflict_resolution: 冲突解决策略，可选值为 'latest'（默认，以最新修改时间为准）, 'A', 'B'
    """
    try:
        logger.info(f"开始同步表: {table_name}")

        # 读取表A和表B的数据
        df_A = pd.read_sql_table(table_name, con=engine_A)
        df_B = pd.read_sql_table(table_name, con=engine_B)

        # 添加时间戳列，用于冲突解决
        if 'last_modified' not in df_A.columns:
            df_A['last_modified'] = datetime.now()
        if 'last_modified' not in df_B.columns:
            df_B['last_modified'] = datetime.now()

        # 同步逻辑
        if sync_direction == 'A_to_B':
            # 单向同步：A -> B
            df_B = df_A
            df_B.to_sql(table_name, con=engine_B, if_exists='replace', index=False)
            logger.info(f"单向同步完成: A -> B")

        elif sync_direction == 'B_to_A':
            # 单向同步：B -> A
            df_A = df_B
            df_A.to_sql(table_name, con=engine_A, if_exists='replace', index=False)
            logger.info(f"单向同步完成: B -> A")

        elif sync_direction == 'both':
            # 双向同步
            # 合并两个表的数据
            merged_df = pd.concat([df_A, df_B])

            # 根据冲突解决策略处理重复数据
            if conflict_resolution == 'latest':
                # 以最新修改时间为准
                merged_df = merged_df.sort_values(by='last_modified', ascending=False)
                merged_df = merged_df.drop_duplicates(subset=merged_df.columns.difference(['last_modified']), keep='first')
            elif conflict_resolution == 'A':
                # 优先使用A的数据
                merged_df = merged_df.drop_duplicates(subset=merged_df.columns.difference(['last_modified']), keep='first')
            elif conflict_resolution == 'B':
                # 优先使用B的数据
                merged_df = merged_df.drop_duplicates(subset=merged_df.columns.difference(['last_modified']), keep='last')

            # 将合并后的数据写回两个数据库
            merged_df.to_sql(table_name, con=engine_A, if_exists='replace', index=False)
            merged_df.to_sql(table_name, con=engine_B, if_exists='replace', index=False)
            logger.info(f"双向同步完成: A <-> B")

        else:
            raise ValueError(f"无效的同步方向: {sync_direction}")

        logger.info(f"表 {table_name} 同步完成")

    except Exception as e:
        logger.error(f"同步失败: {e}")
        raise

def main():
    # 数据库A的配置
    db_config_A = {
        'user': 'user_A',
        'password': 'password_A',
        'host': 'host_A',
        'database': 'database_A'
    }

    # 数据库B的配置
    db_config_B = {
        'user': 'user_B',
        'password': 'password_B',
        'host': 'host_B',
        'database': 'database_B'
    }

    # 创建数据库引擎
    engine_A = create_db_engine(db_config_A)
    engine_B = create_db_engine(db_config_B)

    # 同步表数据
    table_name = 'your_table_name'
    sync_databases(engine_A, engine_B, table_name, sync_direction='both', conflict_resolution='latest')

if __name__ == "__main__":
    main()

关键点说明
同步方向 (sync_direction)：

• A_to_B：将数据库 A 的数据同步到数据库 B。
• B_to_A：将数据库 B 的数据同步到数据库 A。
• both：双向同步，合并两个数据库的数据。

冲突解决策略 (conflict_resolution)：

• latest：以最新修改时间为准（默认）。
• A：优先使用数据库 A 的数据。
• B：优先使用数据库 B 的数据。

时间戳列 (last_modified)：在双向同步中，添加 last_modified 列来记录数据的修改时间，用于冲突解决。

性能优化：
对于大数据量的表，可以使用分块读取和写入的方式，避免内存溢出。例如：

chunksize = 10000
for chunk in pd.read_sql_table(table_name, con=engine_A, chunksize=chunksize):
    chunk.to_sql(table_name, con=engine_B, if_exists='append', index=False)

日志记录：
使用 logging 模块记录同步过程中的关键信息，便于调试和监控。
异常处理：捕获并处理同步过程中可能出现的异常，确保程序的健壮性。

示例运行
假设有两个数据库 database_A 和 database_B，它们都有一个表 your_table_name。运行以下代码可以将这两个表的数据进行双向同步：

if __name__ == "__main__":
    main()

总结
通过这个函数，可以实现两个数据库之间的灵活同步，支持单向和双向同步，并提供冲突解决策略。在实际应用中，可以根据需求进一步扩展功能，例如支持更多冲突解决策略、增量同步等。

ER图构建

参考文档

MySQL Workbench构建ER图(实体关系图)
如何使用 MySQL Workbench 自动生成 ER 图、同步更新远程数据库 - MySQL Workbench 使用教程