python mysql数据库连接池_Python操作MySQL、数据库连接池DBUtils

本篇对于Python操作MySQL主要使用两种方式：

原生模块 pymysql

ORM框架 SQLAchemy

pymysql

pymysql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同，但Python3不支持MySQLdb。

下载安装

Python2默认无pip命令

Python3默认自带pip3命令，若终端命令执行出错，执行python3-m pip install --upgrade pip

使用操作

import pymysql

username= input('请输入用户名：')

pwd= input('请输入密码：')

# 建立连接

conn=pymysql.connect(

host='localhost',user='root',

password="",database='db13',

port=3306,

charset='utf8')

# 创建游标

cur= conn.cursor()

sql= 'select * from userinfo where name="%s" and pwd="%s"'%(username,pwd)print(sql)

res= cur.execute(sql)print(res)

# 游标关闭 连接关闭

cur.close()

conn.close()ifres:print('登录成功')else:print('登录失败')

pymysql简单示例

注：上例会被sql注入

不能用字符串拼接，会被sql注入，一定不要用字符串拼接，要用参数传递

利用sql的注释来注入(--空格)例：

import pymysql

username = input('请输入用户名：')

pwd = input('请输入密码：')

conn = pymysql.connect(

host='localhost',

user='root',

password="",

database='db13',

port=3306,

charset='utf8'

)

cur = conn.cursor()

sql = 'select * from userinfo where name="%s" and pwd="%s"'%(username,pwd)

print(sql)

res = cur.execute(sql)

print(res)

cur.close()

conn.close()

if res: print('登录成功')

else : print('登录失败')

#数据库中为 alex 123

上例中，若用户输入的username为alexsb" or 1=1 -- asdf会将-- 后的东西全注释掉，则不需要密码甚至用户名错误也可登录成功或拿到数据，即使alexsb不是alex，也会由于or 1=1成立而登录成功

1、执行SQL(增删改一定要有conn.commit())

1 #!/usr/bin/env python

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

3 importpymysql4

5 #创建连接

6 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')7 #创建游标

8 cursor =conn.cursor()9

10 #执行SQL，并返回收影响行数

11 effect_row = cursor.execute("update hosts set host = '1.1.1.2'")12

13 #执行SQL，并返回受影响行数

14 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))

15

16 #执行SQL，并返回受影响行数

17 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

18

19

20 #提交，不然无法保存新建或者修改的数据

21 conn.commit()22

23 #关闭游标

24 cursor.close()25 #关闭连接

26 conn.close()

View Code

2、获取新创建数据自增ID

1 #!/usr/bin/env python

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

3 importpymysql4

5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')6 cursor =conn.cursor()7 cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])8 conn.commit()9 cursor.close()10 conn.close()11

12 #获取最新自增ID

13 new_id = cursor.lastrowid

View Code

3、获取查询数据

1 #!/usr/bin/env python

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

3 importpymysql4

5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')6 cursor =conn.cursor()7 cursor.execute("select * from hosts")8

9 #获取第一行数据

10 row_1 =cursor.fetchone()11

12 #获取前n行数据

13 #row_2 = cursor.fetchmany(3)

14 #获取所有数据

15 #row_3 = cursor.fetchall()

16

17 conn.commit()18 cursor.close()19 conn.close()

View Code

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode='relative')  # 相对当前位置移动

cursor.scroll(2,mode='absolute') # 相对绝对位置移动

4、fetch数据类型

关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

1 #!/usr/bin/env python

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

3 importpymysql4

5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')6

7 #游标设置为字典类型

8 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)9 r = cursor.execute("call p1()")10

11 result =cursor.fetchone()12

13 conn.commit()14 cursor.close()15 conn.close()

View Code

1 作业：2 参考表结构：3 用户类型4

5 用户信息6

7 权限8

9 用户类型&权限10 功能：11

12 #登陆、注册、找回密码

13 #用户管理

14 #用户类型

15 #权限管理

16 #分配权限

17

18 特别的：程序仅一个可执行文件

练习题

SQLAchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

安装：

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

一、内部处理

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

1 #!/usr/bin/env python

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

3 from sqlalchemy importcreate_engine4

5

6 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)7

8 #执行SQL

9 #cur = engine.execute(

10 #"INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"

11 #)

12

13 #新插入行自增ID

14 #cur.lastrowid

15

16 #执行SQL

17 #cur = engine.execute(

18 #"INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]

19 #)

20

21

22 #执行SQL

23 #cur = engine.execute(

24 #"INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",

25 #host='1.1.1.99', color_id=3

26 #)

27

28 #执行SQL

29 #cur = engine.execute('select * from hosts')

30 #获取第一行数据

31 #cur.fetchone()

32 #获取第n行数据

33 #cur.fetchmany(3)

34 #获取所有数据

35 #cur.fetchall()

View Code

二、ORM功能使用

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

1、创建表

1 #!/usr/bin/env python

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

3 from sqlalchemy.ext.declarative importdeclarative_base4 from sqlalchemy importColumn, Integer, String, ForeignKey, UniqueConstraint, Index5 from sqlalchemy.orm importsessionmaker, relationship6 from sqlalchemy importcreate_engine7

8 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)9

10 Base =declarative_base()11

12 #创建单表

13 classUsers(Base):14 __tablename__ = 'users'

15 id = Column(Integer, primary_key=True)16 name = Column(String(32))17 extra = Column(String(16))18

19 __table_args__ =(20 UniqueConstraint('id', 'name', name='uix_id_name'),21 Index('ix_id_name', 'name', 'extra'),22 )23

24

25 #一对多

26 classFavor(Base):27 __tablename__ = 'favor'

28 nid = Column(Integer, primary_key=True)29 caption = Column(String(50), default='red', unique=True)30

31

32 classPerson(Base):33 __tablename__ = 'person'

34 nid = Column(Integer, primary_key=True)35 name = Column(String(32), index=True, nullable=True)36 favor_id = Column(Integer, ForeignKey("favor.nid"))37

38

39 #多对多

40 classGroup(Base):41 __tablename__ = 'group'

42 id = Column(Integer, primary_key=True)43 name = Column(String(64), unique=True, nullable=False)44 port = Column(Integer, default=22)45

46

47 classServer(Base):48 __tablename__ = 'server'

49

50 id = Column(Integer, primary_key=True, autoincrement=True)51 hostname = Column(String(64), unique=True, nullable=False)52

53

54 classServerToGroup(Base):55 __tablename__ = 'servertogroup'

56 nid = Column(Integer, primary_key=True, autoincrement=True)57 server_id = Column(Integer, ForeignKey('server.id'))58 group_id = Column(Integer, ForeignKey('group.id'))59

60

61 definit_db():62 Base.metadata.create_all(engine)63

64

65 defdrop_db():66 Base.metadata.drop_all(engine)

View Code

注：设置外检的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])

2、操作表

1 #!/usr/bin/env python

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

3 from sqlalchemy.ext.declarative importdeclarative_base4 from sqlalchemy importColumn, Integer, String, ForeignKey, UniqueConstraint, Index5 from sqlalchemy.orm importsessionmaker, relationship6 from sqlalchemy importcreate_engine7

8 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)9

10 Base =declarative_base()11

12 #创建单表

13 classUsers(Base):14 __tablename__ = 'users'

15 id = Column(Integer, primary_key=True)16 name = Column(String(32))17 extra = Column(String(16))18

19 __table_args__ =(20 UniqueConstraint('id', 'name', name='uix_id_name'),21 Index('ix_id_name', 'name', 'extra'),22 )23

24 def __repr__(self):25 return "%s-%s" %(self.id, self.name)26

27 #一对多

28 classFavor(Base):29 __tablename__ = 'favor'

30 nid = Column(Integer, primary_key=True)31 caption = Column(String(50), default='red', unique=True)32

33 def __repr__(self):34 return "%s-%s" %(self.nid, self.caption)35

36 classPerson(Base):37 __tablename__ = 'person'

38 nid = Column(Integer, primary_key=True)39 name = Column(String(32), index=True, nullable=True)40 favor_id = Column(Integer, ForeignKey("favor.nid"))41 #与生成表结构无关，仅用于查询方便

42 favor = relationship("Favor", backref='pers')43

44 #多对多

45 classServerToGroup(Base):46 __tablename__ = 'servertogroup'

47 nid = Column(Integer, primary_key=True, autoincrement=True)48 server_id = Column(Integer, ForeignKey('server.id'))49 group_id = Column(Integer, ForeignKey('group.id'))50 group = relationship("Group", backref='s2g')51 server = relationship("Server", backref='s2g')52

53 classGroup(Base):54 __tablename__ = 'group'

55 id = Column(Integer, primary_key=True)56 name = Column(String(64), unique=True, nullable=False)57 port = Column(Integer, default=22)58 #group = relationship('Group',secondary=ServerToGroup,backref='host_list')

59

60

61 classServer(Base):62 __tablename__ = 'server'

63

64 id = Column(Integer, primary_key=True, autoincrement=True)65 hostname = Column(String(64), unique=True, nullable=False)66

67

68

69

70 definit_db():71 Base.metadata.create_all(engine)72

73

74 defdrop_db():75 Base.metadata.drop_all(engine)76

77

78 Session = sessionmaker(bind=engine)79 session = Session()

表结构 + 数据库连接

增

1 obj = Users(name="alex0", extra='sb')2 session.add(obj)3 session.add_all([4 Users(name="alex1", extra='sb'),5 Users(name="alex2", extra='sb'),6 ])7 session.commit()

View Code

删

session.query(Users).filter(Users.id > 2).delete()

session.commit()

View Code

改

1 session.query(Users).filter(Users.id > 2).update({"name" : "099"})2 session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)3 session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")4 session.commit()

View Code

查

1 ret =session.query(Users).all()2 ret =session.query(Users.name, Users.extra).all()3 ret = session.query(Users).filter_by(name='alex').all()4 ret = session.query(Users).filter_by(name='alex').first()5

6 ret = session.query(Users).filter(text("id<:value and name=':name")).params(value=224,'>

8 ret = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()

View Code

其他

1 #条件

2 ret = session.query(Users).filter_by(name='alex').all()3 ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()4 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()5 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()6 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()7 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()8 from sqlalchemy importand_, or_9 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()10 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()11 ret =session.query(Users).filter(12 or_(13 Users.id < 2,14 and_(Users.name == 'eric', Users.id > 3),15 Users.extra != ""

16 )).all()17

18

19 #通配符

20 ret = session.query(Users).filter(Users.name.like('e%')).all()21 ret = session.query(Users).filter(~Users.name.like('e%')).all()22

23 #限制

24 ret = session.query(Users)[1:2]25

26 #排序

27 ret =session.query(Users).order_by(Users.name.desc()).all()28 ret =session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()29

30 #分组

31 from sqlalchemy.sql importfunc32

33 ret =session.query(Users).group_by(Users.extra).all()34 ret =session.query(35 func.max(Users.id),36 func.sum(Users.id),37 func.min(Users.id)).group_by(Users.name).all()38

39 ret =session.query(40 func.max(Users.id),41 func.sum(Users.id),42 func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()43

44 #连表

45

46 ret = session.query(Users, Favor).filter(Users.id ==Favor.nid).all()47

48 ret =session.query(Person).join(Favor).all()49

50 ret = session.query(Person).join(Favor, isouter=True).all()51

52

53 #组合

54 q1 = session.query(Users.name).filter(Users.id > 2)55 q2 = session.query(Favor.caption).filter(Favor.nid < 2)56 ret =q1.union(q2).all()57

58 q1 = session.query(Users.name).filter(Users.id > 2)59 q2 = session.query(Favor.caption).filter(Favor.nid < 2)60 ret = q1.union_all(q2).all()

View Code

更多功能参见文档，猛击这里下载PDF

importpymysqlfrom DBUtils.PooledDB importPooledDB

POOL=PooledDB(

creator=pymysql, #使用链接数据库的模块

maxconnections=6, #连接池允许的最大连接数，0和None表示不限制连接数

mincached=2, #初始化时，链接池中至少创建的空闲的链接，0表示不创建

maxcached=5, #链接池中最多闲置的链接，0和None不限制

maxshared=3, #链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。

blocking=True, #连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错

maxusage=None, #一个链接最多被重复使用的次数，None表示无限制

setsession=[], #开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]

ping=0,#ping MySQL服务端，检查是否服务可用。

#如：0 = None = never,

#1 = default = whenever it is requested,

#2 = when a cursor is created,

#4 = when a query is executed,

#7 = always

host="127.0.0.1",

port=3306,

user="root",

password="",

charset="utf8",

db="day115")

conn= POOL.connection() #pymysql - conn

cur = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql= "select * from users WHERE name='jwb' and age=73"res=cur.execute(sql)print(cur.fetchall())

conn.close()

DBUtils 的简单使用

importpymysqlfrom DBUtils.PooledDB importPooledDB

POOL=PooledDB(

creator=pymysql, #使用链接数据库的模块

maxconnections=6, #连接池允许的最大连接数，0和None表示不限制连接数

mincached=2, #初始化时，链接池中至少创建的空闲的链接，0表示不创建

maxcached=5, #链接池中最多闲置的链接，0和None不限制

maxshared=3, #链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。

blocking=True, #连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错

maxusage=None, #一个链接最多被重复使用的次数，None表示无限制

setsession=[], #开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]

ping=0,#ping MySQL服务端，检查是否服务可用。

#如：0 = None = never,

#1 = default = whenever it is requested,

#2 = when a cursor is created,

#4 = when a query is executed,

#7 = always

host="127.0.0.1",

port=3306,

user="root",

password="",

charset="utf8",

db="day115")defcreate_conn():

conn=POOL.connection()

cursor= conn.cursor(cursor=pymysql.cursors.DictCursor)returnconn,cursordefclose_conn(conn,cursor):

cursor.close()

conn.close()definsert(sql,args):

conn,cursor=create_conn()

res=cursor.execute(sql,args)

conn.commit()

close_conn(conn,cursor)returnresdeffetch_one(sql,args):

conn,cursor=create_conn()

cursor.execute(sql,args)

res=cursor.fetchone()

close_conn(conn,cursor)returnresdeffetch_all(sql,args):

conn,cursor=create_conn()

cursor.execute(sql,args)

res=cursor.fetchall()

close_conn(conn,cursor)returnres#sql = "insert into users(name,age) VALUES (%s, %s)"

#insert(sql,("mjj",9))

sql= "select * from users where name=%s and age=%s"

print(fetch_one(sql,("mjj",9)))

自制sqlhelper 简单示例

pip install DBUtils

创建数据库连接池:

importtimeimportpymysqlimportthreadingfrom DBUtils.PooledDB importPooledDB, SharedDBConnection

POOL=PooledDB(

creator=pymysql, #使用链接数据库的模块

maxconnections=6, #连接池允许的最大连接数，0和None表示不限制连接数

mincached=2, #初始化时，链接池中至少创建的空闲的链接，0表示不创建

maxcached=5, #链接池中最多闲置的链接，0和None不限制

maxshared=3, #链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。

blocking=True, #连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错

maxusage=None, #一个链接最多被重复使用的次数，None表示无限制

setsession=[], #开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]

ping=0,#ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always

host='127.0.0.1',

port=3306,

user='root',

password='123',

database='pooldb',

charset='utf8')

创建数据库连接池

使用数据库连接池:

deffunc():#检测当前正在运行连接数的是否小于最大链接数，如果不小于则：等待或报raise TooManyConnections异常

#否则

#则优先去初始化时创建的链接中获取链接 SteadyDBConnection。

#然后将SteadyDBConnection对象封装到PooledDedicatedDBConnection中并返回。

#如果最开始创建的链接没有链接，则去创建一个SteadyDBConnection对象，再封装到PooledDedicatedDBConnection中并返回。

#一旦关闭链接后，连接就返回到连接池让后续线程继续使用。

conn =POOL.connection()

cursor=conn.cursor()

cursor.execute('select * from tb1')

result=cursor.fetchall()

conn.close()

使用数据库连接池中的链接

自制sqlhelper

classMySQLhelper(object):def __init__(self, host, port, dbuser, password, database):

self.pool=PooledDB(

creator=pymysql, #使用链接数据库的模块

maxconnections=6, #连接池允许的最大连接数，0和None表示不限制连接数

mincached=2, #初始化时，链接池中至少创建的空闲的链接，0表示不创建

maxcached=5, #链接池中最多闲置的链接，0和None不限制

maxshared=3,#链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。

blocking=True, #连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错

maxusage=None, #一个链接最多被重复使用的次数，None表示无限制

setsession=[], #开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]

ping=0,#ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always

host=host,

port=int(port),

user=dbuser,

password=password,

database=database,

charset='utf8')defcreate_conn_cursor(self):

conn=self.pool.connection()

cursor=conn.cursor(pymysql.cursors.DictCursor)returnconn,cursordeffetch_all(self, sql, args):

conn,cursor=self.create_conn_cursor()

cursor.execute(sql,args)

result=cursor.fetchall()

cursor.close()

conn.close()returnresultdefinsert_one(self,sql,args):

conn,cursor=self.create_conn_cursor()

res=cursor.execute(sql,args)

conn.commit()print(res)

conn.close()returnresdefupdate(self,sql,args):

conn,cursor=self.create_conn_cursor()

res=cursor.execute(sql,args)

conn.commit()print(res)

conn.close()returnres

sqlhelper= MySQLhelper("127.0.0.1", 3306, "root", "1233121234567", "dragon")#sqlhelper.fetch_all("select * from user where id=%s",(1))

#sqlhelper.insert_one("insert into user VALUES (%s,%s)",("jinwangba",4))

#sqlhelper.update("update user SET name=%s WHERE id=%s",("yinwangba",1))

自制sqlhelper