本篇对于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