Python——MySQL远程控制

目录

MySQL运程控制

1. 准备工作

2. 连接MySQL数据库

使用mysql-connector

使用PyMySQL

3. 基本CRUD操作

创建表

插入数据

查询数据

更新数据

删除数据

4. 高级操作

事务处理

使用ORM框架 – SQLAlchemy

5. 最佳实践

6. 常见错误处理

连接池

一、连接池的作用

二、优势与劣势

三、部署与使用

1. 常用库及安装

2. 基础使用示例

3. 生产环境建议

四、性能优化技巧

事务管理 

一、事务核心概念

二、典型问题场景

三、选型建议

四、Python配置示例

MySQL运程控制

MySQL是最流行的关系型数据库之一，Python通过多种方式可以与MySQL进行交互。下面我将详细介绍Python操作MySQL的常用方法和最佳实践。

1. 准备工作

在开始之前，你需要：

推荐使用mysql-connector-python或PyMySQL库：

pip install mysql-connector-python
# 或
pip install pymysql

2. 连接MySQL数据库

使用mysql-connector

import mysql.connector

# 创建连接
conn = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="your_database"
)

# 创建游标
cursor = conn.cursor()

# 执行SQL查询
cursor.execute("SELECT * FROM your_table")

# 获取结果
results = cursor.fetchall()
for row in results:
print(row)

# 关闭连接
cursor.close()
conn.close()

使用PyMySQL

import pymysql

# 创建连接
conn = pymysql.connect(
host='localhost',
user='your_username',
password='your_password',
db='your_database',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)

# 使用上下文管理器自动管理连接
with conn:
  with conn.cursor() as cursor:
  # 执行SQL查询
  sql = "SELECT * FROM your_table"
  cursor.execute(sql)

  # 获取结果
  results = cursor.fetchall()
  for row in results:
  print(row)

3. 基本CRUD操作

创建表

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")

插入数据

# 单条插入
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = ("John Doe", "john@example.com")
cursor.execute(sql, val)

# 多条插入
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = [
  ("Jane Smith", "jane@example.com"),
  ("Bob Johnson", "bob@example.com")
]
cursor.executemany(sql, val)

# 提交事务
conn.commit()

查询数据

# 查询所有记录
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# 查询单条记录
cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
row = cursor.fetchone()

# 带条件的查询
cursor.execute("SELECT name, email FROM users WHERE name LIKE %s", ("%John%",))
rows = cursor.fetchall()

更新数据

sql = "UPDATE users SET name = %s WHERE id = %s"
val = ("John Smith", 1)
cursor.execute(sql, val)
conn.commit()

删除数据

sql = "DELETE FROM users WHERE id = %s"
val = (1,)
cursor.execute(sql, val)
conn.commit()

4. 高级操作

事务处理

try:
# 开始事务
conn.start_transaction()

# 执行多个SQL操作
cursor.execute(sql1, val1)
cursor.execute(sql2, val2)

# 提交事务 conn.commit()
except Exception as e:
# 发生错误时回滚
conn.rollback()
print(f"Transaction failed: {e}")

使用ORM框架 – SQLAlchemy

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建引擎
engine = create_engine('mysql+pymysql://user:password@localhost/dbname')

# 声明基类
Base = declarative_base()

# 定义模型
class User(Base):
__tablename__ = 'users'
 
 id = Column(Integer, primary_key=True)
name = Column(String(255))
email = Column(String(255), unique=True)

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 添加新用户
new_user = User(name='Alice', email='alice@example.com')
session.add(new_user)
session.commit()

# 查询用户
users = session.query(User).filter_by(name='Alice').all()
for user in users:
print(user.name, user.email)

5. 最佳实践

6. 常见错误处理

try:
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM non_existent_table")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally: if 'conn' in locals() and conn.is_connected():
cursor.close()
conn.close()

连接池

一、连接池的作用

    数据库连接池是一种预先创建并管理数据库连接的技术，主要解决频繁创建/销毁连接的性能损耗问题。其核心思想是连接复用，应用程序从池中获取连接，使用后归还而非直接关闭。

二、优势与劣势

优势：

劣势：

三、部署与使用

1. 常用库及安装

# SQLAlchemy（支持多种数据库）
pip install sqlalchemy

# DBUtils（通用连接池）
pip install dbutils

# Psycopg2（PostgreSQL专用）
pip install psycopg2-binary

2. 基础使用示例

SQLAlchemy连接池配置：

from sqlalchemy import create_engine

# 带连接池的配置（连接池大小5-10）
engine = create_engine(
"mysql+pymysql://user:pass@host/db",
pool_size=5,
max_overflow=5,
pool_recycle=3600
)

DBUtils连接池示例：

from dbutils.pooled_db import PooledDB
import pymysql

pool = PooledDB(
creator=pymysql,
maxconnections=10,
host='localhost',
user='root',
database='test'
)
conn = pool.connection() # 获取连接

3. 生产环境建议

四、性能优化技巧

事务管理 

一、事务核心概念

四大隔离级别对比

*注：MySQL的InnoDB通过MVCC机制在REPEATABLE READ下可避免幻读

二、典型问题场景

三、选型建议

四、Python配置示例

# PostgreSQL设置隔离级别
import psycopg2
conn = psycopg2.connect(dsn)
conn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ )

不同数据库对隔离级别的实现存在差异，如Oracle默认READ COMMITTED而MySQL默认REPEATABLE READ38，实际开发需结合具体数据库特性调整