SQLAlchemy
是一个ORM框架。
作用: 帮助我们使用类和对象快速实现数据库操作。
一、MySQLdb操作数据库
MySQLdb
操作数据库与pymysql
操作数据库用法是一样的。但是MySQLdb
只支持python2
。
1.1、插入数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('alex','usa'))
# reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'})
conn.commit()
cur.close()
conn.close()
print reCount
批量插入数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
li =[
('alex','usa'),
('sb','usa'),
]
reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li)
conn.commit()
cur.close()
conn.close()
print reCount
1.2、删除数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
reCount = cur.execute('delete from UserInfo')
conn.commit()
cur.close()
conn.close()
print reCount
1.3、修改数据
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
reCount = cur.execute('update UserInfo set Name = %s',('alin',))
conn.commit()
cur.close()
conn.close()
print reCount
1.4、查数据
# ############################## fetchone/fetchmany(num) ##############################
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
cur = conn.cursor()
reCount = cur.execute('select * from UserInfo')
print cur.fetchone()
print cur.fetchone()
cur.scroll(-1,mode='relative')
print cur.fetchone()
print cur.fetchone()
cur.scroll(0,mode='absolute')
print cur.fetchone()
print cur.fetchone()
cur.close()
conn.close()
print reCount
# ############################## fetchall ##############################
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cur = conn.cursor()
reCount = cur.execute('select Name,Address from UserInfo')
nRet = cur.fetchall()
cur.close()
conn.close()
print reCount
print nRet
for i in nRet:
print i[0],i[1]
二、SQLAlchemy的使用
安装:pip3 install sqlalchemy
2.1、单表基本的增删改查
models.py
:数据库需要自己创建
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
Base = declarative_base()
# 创建单表
class Users(Base):
# 表名
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True, nullable=False)
# email = Column(String(32), unique=True)
# ctime = Column(DateTime, default=datetime.datetime.now)
# extra = Column(Text, nullable=True)
__table_args__ = (
# UniqueConstraint('id', 'name', name='uix_id_name'),
# Index('ix_id_name', 'name', 'email'),
)
def init_db():
"""
根据类创建数据库表
:return:
"""
engine = create_engine(
"mysql+pymysql://用户名:密码@127.0.0.1:3306/库名?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置),-1表示不回收
)
Base.metadata.create_all(engine)
def drop_db():
"""
根据类删除数据库表
:return:
"""
engine = create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收(重置)
)
Base.metadata.drop_all(engine)
if __name__ == '__main__':
# drop_db()
init_db() # 右键运行即可创建
单表的基本增删改查
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from SQLAlchemy.models import Users
# 先创建连接
engine = create_engine(
"mysql+pymysql://root:123456@127.0.0.1:3306/fls?charset=utf8",
max_overflow=0,
pool_size=5,
pool_timeout=30,
pool_recycle=-1
)
SessionFactory = sessionmaker(bind=engine)
# 实例化
session = SessionFactory()
# 1、单条增加
obj = Users(name='lzp')
session.add(obj)
session.commit() # 提交
# 2、多条增加
session.add_all([
Users(name='lwb'),
Users(name='shj')
])
session.commit()
# 3、查询
# 查询所有
res = session.query(Users).all()
print(res) # 拿到的是对象列表
for row in res:
print(row.id, row.name)
# 过滤查询
res = session.query(Users).filter(Users.id > 2)
for i in res:
print(i.name)
# 只拿第一条的结果
res = session.query(Users).filter(Users.id >= 2).first()
print(res.name)
# 4、删
session.query(Users).filter(Users.id >= 2).delete()
session.commit()
# 5、改
session.query(Users).filter(Users.id == 4).update({Users.name: "沙雕"})
session.query(Users).filter(Users.id == 5).update({'name': "帅哥"})
# synchronize_session=False,如果不指定,默认会做数值的相加,导致报错
session.query(Users).filter(Users.id == 1).update({'name': Users.name+'_dsb'}, synchronize_session=False)
session.commit()
session.close()
2.2、SQLAlchemy常用的操作
all()
返回查询到的所有的结果。这个方法比较危险的地方是,如果数据量大且没有使用limit子句限制的话,所有的结果都会加载到内存中。它返回的是一个列表,如果查询不到任何结果,返回的是空列表。
first()
返回查询到的第一个结果,如果没有查询到结果,返回None。
.scalar()
这个方法与.one_or_none()的效果一样。 如果查询到很多结果,抛出sqlalchemy.orm.exc.MultipleResultsFound异常。如果只有一个结果,返回它,没有结果返回None。
one()
如果只能查询到一个结果,返回它,否则抛出异常。没有结果时抛sqlalchemy.orm.exc.NoResultFound,有超过一个结果时抛sqlalchemy.orm.exc.MultipleResultsFound。
one_or_none()
比起one()来,区别只是查询不到任何结果时不再抛出异常而是返回None。
get()
这是个比较特殊的方法。它用于根据主键来返回查询结果,因此它有个参数就是要查询的对象的主键。如果没有该主键的结果返回None,否则返回这个结果。
# 1、指定查询的列
res = session.query(Users.id, Users.name).all()
for i in res:
print(i) # (1, 'lzp_dsb')
print(i[0], i.id) # 1 1
# 2、默认条件是and
r = session.query(Users).filter(Users.id>1, Users.name=='ds').all()
print(r)
# 3、between……and,后面不加.all(),打印的结果就是执行的sql语句
r = session.query(Users).filter(Users.id.between(1,4)).all()
print(r)
# 4、id在列表[1,3,4]:in
r = session.query(Users).filter(Users.id.in_([1,3,4])).all()
for i in r:
print(i.name)
# 5、not in:~
r = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
# 6、子查询
r = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='帅哥'))).all()
# 7、and和or
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
or_(
Users.id < 2,
# and与or混用
and_(Users.name == 'eric', Users.id > 3),
Users.extra != ""
)).all()
# 8、filter_by:内部传的是参数,不像filter传表达式
session.query(Users).filter_by(name='shj').all()
# 9、通配符
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()
# 10、切片
ret = session.query(Users)[1:2]
# 11、排序
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
# 12、分组:group_by
from sqlalchemy.sql import func
# 按部门id分组
ret = session.query(Users).group_by(Users.depart_id).all() #如果有多条数据,这么指定不能拿到我们想要的数据,要用func.max()或其他指定
ret = session.query(
func.max(Users.id),
func.sum(Users.id),
func.min(Users.id)).group_by(Users.name).all()
ret = session.query(
func.max(Users.id),
func.sum(Users.id),
func.min(Users.id)).group_by(Users.depart_id).having(func.count(Users.id) >2).all() # having,部门人数大于2的数据
# 13、组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all() # 去重
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all() # 不去重
# 14、连表
ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
ret = session.query(Person).join(Favor).all()
ret = session.query(Person).join(Favor, isouter=True).all()