python操作mysql

Python Mysql开发有使用原生SQL语言和ORM框架两种方式。下面介绍下其中涉及到的知识点。

Python连接mysql的驱动包

python的mysql驱动包常用的有三个，分别是: pymysql, mysql-connector, MySQLdb。三个包都遵循PEP 249使用方式大同小异。

mysql-connector 是 MySQL 官方提供的驱动器, 它在Python中重新实现MySQL协议，它比较慢，但不需要C库，因此可移植性好。

MySQLdb是一个围绕mysql的瘦Python包装器，它使mysql与Python DB API接口兼容(v2.0)。其中mysql也是该作者开发的模块，它依赖C库，所以说MYSQLdb也是依赖C库的，因此它的可移植性不太好。但是由于是基于C库实现的，它的速度会快一些。一些开发者为了效率甚至直接使用mysql模块。

pymysql是由yutaka.matsubara开发维护的纯python实现的mysql模块。它相对于mysql.connector, MYSQLdb来说比较年轻。它的效率和可移植性和my-connector理论上是差不多的。

下面以pymysql为例，简单使用的代码如下：

import pymysql
#连接
conn = pymysql.connect(host='localhost',
                        user='root',
                        passwd='yourpasswd',
                        db='db',
                        charset='utf8mb4')
#查询
cursor = conn.cursor()
sql1 = "select * from main limit 10"
cursor.execute(sql)
result1 = cursor.fetchall()   # 取出所有数据
result2 = cursor.fetchone()   # 取出第一条数据
result3 = cursor.fetchmany(5) # 取出结果中的五条数据
cursor.close()
conn.close()
#增删改
conn = mysql.connector.connect(...)
cursor = conn.cursor()
sql1 = "insert into tablename values (%s, %s, %s)"
cursor.execute(sql1,(1,2,3))
cursor.executemany(sql1,[(1,2,3),(4,5,6)])
conn.commit()

sql2 = "delete from tablename where 
cursor.execute(sql2)
conn.commit()

sql3 = "update tablename set columnname = %s where 
cursor.execute(sql3, (0,1230))
cursor.executemany(sql, [(0,1030),(2,1230)])
conn.commit()

cursor.close()
conn.close()

增删改是会对数据库进行修改， 需要conn.commit()才会真正地生效。commit涉及事务概念。

SQLAlchemy–python ORM框架

面向对象的开发一般使用ORM框架，使用ORM框架能避免书写繁琐的SQL语句，使程序更加简洁统一，但ORM框架在处理一些复杂的问题时效率可能不如原生的SQL语句。下面首先介绍下SQLAlchemy中的几个概念，然后介绍框架的使用。

概念

engine

Engine 是访问数据库的入口，Engine引用Connection Pool和 Dialect实现了对数据库的访问, Dialect指定了具体的数据库类型 MYSQL, SQLSERVER等, 三者关系如图所示:

只有当调用connect(),execute()函数的时候，才会创建数据库的连接

from sqlalchemy import create_engine

scheme = 'mysql+pymysql://root:123456@localhost:3306/dev_shopping?charset=utf8'
engine = create_engine(scheme, pool_size=10 , max_overflow=5, pool_pre_ping=True, pool_recycle=1200)

scheme的格式是：dbname+driver://username:password@host:port/database

create_engine可选的参数很多，建议查看官方文档，其中pool_recycle的设置要小于mysql的wait_timeout，pool_pre_ping是在每次操作时检查连接是否可用。

在单进程中，建议在在初始化的模块的时候创建Engine, 使Engine成为全局变量， 而不是为每个调用Engine的对象或者函数中创建, Engine不同于connect, connect函数会创建数据库连接的资源，Engine是管理connect创建的连接资源

在多进程中，为每个子进程都创建各自的Engine, 因为进程之间是不能共享Engine。

connection

使用connect 创建连接数据库资源, 如上所说，即使创建了Engine, 还是没有创建对数据库的连接，调用connect才会创建真正的连接。

session

来自Stackoverflow的一个回答：

A session is just a result of a successful connection.
Any MySQL client requires some connection settings to establish a connection,
and after the connection has been established,
it acquires a connection id (thread id) and some context which is called session.

来自官方团队的描述：

Connections correspond to Sessions in SQL standard terminology.
A client connects to the MySQL Server and stays connected until it does a disconnect.

说到session，就需要了解事务的概念。

This section describes how to use transactions when working directly with Engine and Connection objects. When using the SQLAlchemy ORM, the public API for transaction control is via the Session object, which makes usage of the Transaction object internally. See Managing Transactions for further information

一般来说，session在需要访问数据库的时候创建，在session访问数据库的时候，准确来说，应该是“add/update/delete”数据库的时候，会开启database transaction, 假设没有修改autocommit的默认值(False), 那么，database transaction 一直会保持，只有等到session rolled back, committed, or closed的时候才结束，一般建议，当database transaction结束的时候，同时close session, 保证，每次发起请求，都创建一个新的session 。 session 不是线程安全的 。

示例代码

示例代码主要在tornado环境中使用

from sqlalchemy import Column, String, SmallInteger, DateTime, Text, Date, BigInteger, Integer
from tornado_sqlalchemy import SQLAlchemy, set_max_workers

db = SQLAlchemy()
db.configure(url, engine_options={"pool_pre_ping": True, "pool_recycle": 300})

class KA(db.Model):
    __tablename__ = 'ka'

    id = Column(BigInteger, primary_key=True)
    businessid = Column(String, nullable=True)
    kid = Column(String, nullable=True)

tornado SQLAlchemy

具体操作参考上面链接。

参考

Python：连接mysql数据库的三种方式，mysql.connector, pymysql, MYSQLdb

MySQL中的连接、实例、会话、数据库、线程之间的关系

https://sunnyingit.github.io/book/section_python/SQLalchemy-engine.html

MySQL基础知识：MySQL Connection和Session

https://stackoverflow.com/questions/62788690/is-there-a-solution-in-fixing-mysql-connection-not-available-for-very-second-r

深入研究sqlalchemy连接池

MySQL中interactive_timeout和wait_timeout的区别

tornado_sqlalchemy

imeout和wait_timeout的区别

tornado_sqlalchemy