Python连接MySQL数据库的常规操作梳理

常用的数据库MySQL操作，一般包含对数据库的管理，表，数据，用户权限管理等；对数据表的操作包含创建表，查看表，修改表，删除表，合并表，备份及恢复表；对数据的操作包含查询数据，修改数据，插入数据，删除数据操作。

1.表中的数据查询操作

import pymysql
# 1. 创建连接connection = pymysql.connect(    host='localhost',    user='your_username',    password='your_password',    db='your_database',    charset='utf8mb4',  # 可选，设置字符集)
# 2. 创建游标对象cursor = connection.cursor()
# 3. 准备并执行SQL查询语句sql_query = "SELECT * FROM your_table WHERE condition"cursor.execute(sql_query)
# 4. 获取查询结果# fetchone() 方法获取第一条记录result_one = cursor.fetchone()if result_one:    print(result_one)
# fetchmany(size) 方法获取多条记录，默认size为None时返回剩余所有记录results_some = cursor.fetchmany(5)for row in results_some:    print(row)
# fetchall() 方法获取所有记录results_all = cursor.fetchall()for row in results_all:    print(row)
# 5. 关闭游标和连接cursor.close()connection.close()

2.表中的数据更新和删除操作

import pymysql
# 1. 创建连接connection = pymysql.connect(    host='localhost',    user='your_username',    password='your_password',    db='your_database',    charset='utf8mb4',  # 可选，设置字符集)
# 2. 创建游标对象cursor = connection.cursor()
# 3. 准备并执行SQL修改语句 - 更新操作示例# 假设我们有一个用户表users，并要更新某个用户的email地址update_query = "UPDATE users SET email=%s WHERE id=%s"update_data = ('new_email@example.com', 1)  # 新的邮件地址和要更新的用户ID
cursor.execute(update_query, update_data)
# 提交事务，确保更改生效（如果数据库支持事务）connection.commit()
# 也可以查看影响的行数affected_rows = cursor.rowcountprint(f"成功更新了{affected_rows}行")
# 删除操作示例delete_query = "DELETE FROM users WHERE id=%s"delete_data = (2,)  # 要删除的用户ID
cursor.execute(delete_query, delete_data)connection.commit()
# 检查删除操作的影响行数affected_rows = cursor.rowcountprint(f"成功删除了{affected_rows}行")
# 4. 关闭游标和连接cursor.close()connection.close()

3.表中的数据插入操作???????

import pymysql
# 1. 创建连接connection = pymysql.connect(    host='localhost',    user='your_username',    password='your_password',    db='your_database',    charset='utf8mb4',  # 可选，设置字符集)
# 2. 创建游标对象cursor = connection.cursor()
# 3. 准备并执行SQL插入语句 - 假设我们有一个users表，并要插入一条新用户记录insert_query = "INSERT INTO users (username, email, password) VALUES (%s, %s, %s)"new_user_data = ('new_username', 'new_email@example.com', 'hashed_password')  # 新用户的用户名、邮件地址和密码（这里假设已经进行了安全哈希处理）
cursor.execute(insert_query, new_user_data)
# 获取新插入行的ID（如果存在自增主键）# 对于支持LAST_INSERT_ID()的数据库（如MySQL），可以这样获取：new_id = cursor.lastrowidprint(f"新用户ID为：{new_id}")
# 提交事务，确保更改生效（如果数据库支持事务）connection.commit()
# 4. 关闭游标和连接cursor.close()connection.close()

4.数据库表的备份及恢复操作

备份：???????

import subprocessimport osfrom datetime import datetime
def backup_mysql_table(database, table, backup_path):    # 获取当前时间作为备份文件名的一部分    now = datetime.now()    timestamp = now.strftime('%Y%m%d_%H%M%S')        # 构建mysqldump命令    dump_cmd = [        'mysqldump',        '-u', 'your_username',  # 替换为你的MySQL用户名        '-p' + 'your_password',  # 替换为你的MySQL密码（注意这里的安全性）        database,        table,        '--result-file=' + os.path.join(backup_path, f'{database}_{table}_{timestamp}.sql'),    ]
    # 执行mysqldump命令并捕获输出    try:        subprocess.run(dump_cmd, check=True)        print(f"成功备份了{database}.{table}到{backup_path}")    except subprocess.CalledProcessError as e:        print(f"备份过程中发生错误: {e}")
# 使用函数backup_mysql_table('your_database',?'your_table',?'/path/to/backup/folder')

恢复：???????

def restore_mysql_table(database, backup_file):    # 构建mysql命令    restore_cmd = [        'mysql',        '-u', 'your_username',  # 替换为你的MySQL用户名        '-p' + 'your_password',  # 替换为你的MySQL密码（注意这里的安全性）        database,    ]        # 将备份文件路径添加到命令参数中    restore_cmd += ['<', backup_file]
    # 执行mysql命令并捕获输出    try:        subprocess.run(restore_cmd, input='', check=True, text=True, shell=True)        print(f"成功从{backup_file}恢复数据到{database}")    except subprocess.CalledProcessError as e:        print(f"恢复过程中发生错误: {e}")
# 使用函数restore_mysql_table('your_database', '/path/to/backup/folder/your_database_your_table_20240122_123456.sql')

注意：在实际应用中，为了安全起见，通常不会直接在命令行中写入明文密码，而是通过环境变量、配置文件或其他安全方法传递给脚本。

5.数据库表的合并操作

SQL语句???????

INSERT INTO new_table (id, column1, column2)SELECT id, column1, column2 FROM table1UNION ALLSELECT id, column1, column2 FROM table2;

Python中使用pandas库合并MySQL表???????

import pymysqlimport?pandas?as?pd# 连接到MySQL数据库connection = pymysql.connect(host='localhost',                             user='your_username',                             password='your_password',                             db='your_database',                             charset='utf8mb4')
# 读取两个表到DataFramedf1 = pd.read_sql_query("SELECT * FROM table1", connection)df2 = pd.read_sql_query("SELECT * FROM table2", connection)
# 根据共同的列（例如：'id'）合并两个DataFramemerged_df = pd.merge(df1, df2, on='id', how='outer')  # 'how'参数可以是inner、left、right或outer
# 如果需要，你可以将合并后的结果写回到MySQL数据库的一个新表中# 先创建新表create_table_query = """CREATE TABLE IF NOT EXISTS merged_table (    id INT PRIMARY KEY,    column1 VARCHAR(255),    column2 VARCHAR(255)...);"""cursor = connection.cursor()cursor.execute(create_table_query)
# 将DataFrame写入MySQLfor i, row in merged_df.iterrows():    insert_query = f"INSERT INTO merged_table (id, column1, column2) VALUES ({row['id']}, '{row['column1']}', '{row['column2']}')"    cursor.execute(insert_query)
# 提交事务，并关闭连接connection.commit()cursor.close()connection.close()

6.数据库中的索引创建，查询，删除操作

创建索引：???????

import pymysql
# 连接到MySQL数据库connection = pymysql.connect(host='localhost',                             user='your_username',                             password='your_password',                             db='your_database')
cursor = connection.cursor()
# 创建一个普通索引（假设表名为users，列名为username）create_index_query = """CREATE INDEX idx_username ON users (username);"""cursor.execute(create_index_query)
# 如果要创建唯一索引，可以这样：create_unique_index_query = """CREATE UNIQUE INDEX idx_unique_email ON users (email);"""cursor.execute(create_unique_index_query)
# 提交事务（如果数据库支持事务）connection.commit()
# 关闭游标和连接cursor.close()connection.close()

查询索引：???????

# 连接数据库并获取游标connection = pymysql.connect(...)cursor = connection.cursor()
# 查看指定表的所有索引show_indexes_query = "SHOW INDEX FROM your_table"cursor.execute(show_indexes_query)
# 获取所有索引信息for index_info in cursor.fetchall():    print(index_info)  # 输出每行索引详细信息
# 关闭资源cursor.close()connection.close()

删除索引：???????

# 连接数据库并获取游标connection = pymysql.connect(...)cursor = connection.cursor()
# 删除名为idx_username的索引drop_index_query = "DROP INDEX idx_username ON users"cursor.execute(drop_index_query)
# 提交事务connection.commit()
# 关闭游标和连接cursor.close()connection.close()

7.数据库中的索引操作???????

import pymysql
# 1. 创建数据库连接，注意要设置autocommit=False以启用手动提交事务connection = pymysql.connect(host='localhost',                             user='your_username',                             password='your_password',                             db='your_database',                             charset='utf8mb4',                             autocommit=False)
try:    # 2. 创建游标对象    cursor = connection.cursor()
    # 3. 开始一个事务（虽然在上面已经禁用了自动提交，但这里可以明确开始一个事务）    connection.begin()
    # 4. 执行一系列SQL操作    insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"    update_query = "UPDATE another_table SET column = %s WHERE condition = %s"
    data_to_insert = ('value1', 'value2')    cursor.execute(insert_query, data_to_insert)
    update_value = 'new_value'    condition = 'some_condition'    cursor.execute(update_query, (update_value, condition))
    # 5. 提交事务    connection.commit()
except pymysql.MySQLError as e:    # 6. 如果发生错误，回滚事务    print(f"Transaction failed: {e}")    connection.rollback()
finally:    # 7. 关闭游标和连接    cursor.close()    connection.close()

注意事项：

• 当创建连接时，通过将autocommit参数设置为False来开启手动事务管理。

• 使用connection.begin()方法开始一个新的事务。

• 在事务中执行的所有SQL语句都会被暂存起来，在调用connection.commit()之前不会真正应用到数据库。

• 如果在事务执行过程中出现任何异常（如pymysql.MySQLError），则捕获该异常并调用connection.rollback()方法回滚事务，这样所有未提交的操作都将被撤销。

• 最后无论是否发生异常，都确保关闭游标和连接.