本文主要是介绍Python远程控制MySQL的完整指南,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
《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) cjsursor.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(Strijsng(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. 最佳实践
使用参数化查询:防止SQL注入攻击
使用上下文管理器:确保连接和游标正确关闭
处理异常:捕获并处理数据库操作中的异常
连接池:在高并发应用中使用连接池
索引优化:为常用查询字段添加索引
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.连接池的作用
数据库连接池是一种预先创建并管理数据库连接的技术,主要解决频繁创建/销毁连接的性能损耗问题。其核心思想是连接复用,应用程序从池中获取连接,使用后归还而非直接关闭。
2.优势与劣势
优势:
性能提升:减少连接创建/销毁的TCP三次握手和认证开销,降低延迟
资源控制:通过max_connections限制最大连接数,防止数据库过载
响应加速:初始化时预建连接,业务请求可直接使用
泄漏防护:超时回收机制避免连接长期占用
劣势:
需要合理配置参数(如最大/最小连接数)
连接状态维护增加复杂度
不适用于超短生命周期应用
3.部署与使用
1. 常用库及安装
# SQLAlchemy(支持多种数据库) pip install sqlalchemy # DBUtils(通用连接池) pip install dbutils # Psycopg2(PostgreSQL专用) pip install psycopg2-binary
2. 基础使用示例
SQLAlchemy连接池配置:
from sqlalchemy import crea编程te_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. 生产环境建议
根据QPS设置pool_size(建议=平均并发量×1.2)
启用pool_pre_ping自动检测失效连接
使用with语句确保连接归还
监控连接池使用率(如SQLAlchemy的pool.status())
4.性能优化技巧
不同业务使用独立连接池隔离资源
动态调整连接数(如SQLAlchemy的pool_events)
配合连接池使用ORM的Session缓存机制
事务管理
事务核心概念
ACID特性
- 原子性(Atomicity):事务是不可分割的工作单元
- 一致性(Consistency):事务前后数据库状态保持一致
- 隔离性(Isolation):并发事务互不干扰
- 持久性(Durability):事务提交后结果永久生效
隔离级别
- READ_UNCOMMITTED(可能读取未提交数据)
- READ_COMMITTED(避免脏读)
- REPEATABLE_READ(避免不可重复读)
- SERIALIZABLE(完全串行化)
四大隔离级别对比
隔离级别 | 脏读 | 不可重复读 | 幻读 | 锁机制特点 |
---|---|---|---|---|
READ UNCOMMITTED | ✓ | ✓ | ✓ | 无读锁,仅写锁冲突 |
READ COMMITTED | ✗ | ✓ | ✓ | 读后立即编程释放共享锁 |
REPEATABLE READ | ✗ | ✗ | ✓* | 持有读锁至事务结束 |
SERIALIZABLE | ✗ | ✗ | ✗ | 范围锁防止幻读 |
*注:MySQL的InnoDB通过MVCC机制在REPEATABLE READ下可避免幻读
典型问题场景
脏读:事务A读取事务B未提交的修改,B回滚导致A获得无效数据
不可重复读:事务A两次读取同记录,因事务B提交修改导致结果不一致
幻读:事务A按条件查询,事务B新增符合条件记录导致A两次结果集不同
选型建议
实时分析系统:READ UNCOMMITTED(容忍脏读换取性能)
支付系统:REPEATABLE READ(保证金额一致性)
票务系统:SERIALIZABLE(杜绝超卖风险)
常规OLTP:READ COMMITTED(平衡性能与一致性)
Python配置示例
# PostgreSQL设置隔离级别 import psycopg2 conn = psycopg2.connect(dsn) conn.set_isolation_level( psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ )
不同数据库对隔离级别的实现存在差异,如oracle默认READ COMMITTED而MySQL默认REPEATABLE READ38,实际开发需结合具体数据库特性调整
到此这篇关于Python远程控制MySQLjavascript的完整指南的文章就介绍到这了,更多相关Python远程控制MySQL内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)!
这篇关于Python远程控制MySQL的完整指南的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!