Sqlalchemy使用
1.创建数据库
新建一个文件create_tale.py，此文件为基础文件，写入如下：
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer,ForeignKey
Base=declarative_base()#定义基类
class user(Base):
__tablename__='user'
id=Column(Integer,primary_key=True,nullable=False,index=True)
name=Column(String(50),primary_key=True,nullable=False)
article=Column(Integer,ForeignKey('article.id'))
class article(Base):
__tablename__='article'
id = Column(Integer, primary_key=True, nullable=False, index=True)
name = Column(String(50), primary_key=True, nullable=False)
engine=create_engine('mysql+pymysql://root:@127.0.0.1:3306/test1')#连接数据库
Base.metadata.create_all(engine)#将所有继承基类的类生成到数据库中2.数据库基本操作
新建一个py文件,可执行增、删、改、查操作
1)新增
from create_table import engine
from sqlalchemy.orm import sessionmaker
session_window=sessionmaker(engine)
db_session=session_window()#构建执行sql的窗口
#单个数据新增
from create_table import user
user_obj=user(name='zhanghuan')#构建sql语句
db_session.add(user_obj)#将sql语句添加到执行窗口
db_session.commit()#提交
db_session.close()#关闭窗口
#批量新增
obj_list=[user(name='abc'),user(name='abc1'),user(name='abc2')]
db_session.add_all(obj_list)
db_session.commit()
db_session.close()2)删除
db_session.query(user).filter_by(id=2).delete()
db_session.commit()
db_session.close()3)修改
（1）字典形式
db_session.query(user).filter_by(id=2).update({'name':'zhanghuan'})
db_session.commit()
db_session.close()（2）对象形式
db_session.query(Student).filter(Student.id==7).update({Student.name:'mafeifei'},synchronize_session=False)
db_session.commit()
db_session.close()4)查看
（1）filter和filter_by
res1=db_session.query(user).filter_by(id=2)
print(res1)
res2=db_session.query(user).filter(user.id==2)
print(res2)
#返回结果时sql语句，推荐使用第二种结果
SELECT user.id AS user_id, user.name AS user_name
FROM user
WHERE user.id = %(id_1)s
SELECT user.id AS user_id, user.name AS user_name
FROM user
WHERE user.id = %(id_1)s
Process finished with exit code 0(2)filter()参数详解
参数必须是以对象取出的属性为基础的判断条件，即"对象.属性……",如student.id>1
一般条件
如student.id>1,student.id<100,student.id==20
带范围条件
使用前需要导入from sqlalchemy.sql import and_,or_
如and_(student.id>1,student.name='zhanghuan')或者or_(student.id>1,student.name='zhanghuan')
模糊查询条件
如user.name.like('z%')或~user.name.like('z%')
(3)first()和all()
res3=db_session.query(user).filter(user.id==2).first()#这个返回的是单个数据
print(type(res3))
res4=db_session.query(user).filter(user.id==2).all()#这个返回的是列表
print(type(res4))结果：
<class 'create_table.user'>
<class 'list'>
Process finished with exit code 0(4)order_by()
结果排序，asc()正序，desc()倒序
res5=db_session.query(user).filter(user.name.like('a%')).order_by(user.id.desc()).all()
for item in res5:
print(item.id)结果
10
9
8
7
6
5
4
3
Process finished with exit code 0（5）offset()
res5=db_session.query(user).offset(5).all()#偏移前5个数据，数据id从2开始
for item in res5:
print(item.id)结果
7
8
9
10
Process finished with exit code 0(6)聚合函数
max(),sum(),min(),avg()
from sqlalchemy.sql import func
res5=db_session.query(func.avg(user.id)).group_by(user.group_id).all()
print(res5)结果
[(Decimal('2.0000'),), (Decimal('4.5000'),)]
Process finished with exit code 0(7)text()
可传参数的筛选判断条件
from sqlalchemy.sql import text res=session.query(Student).filter(text('id<:id and name=:name')).params(id=4,name='zhanghuan').all()
for row in res:
print(row.id,row.name)结果
1 zhanghuan
Process finished with exit code 0(8)from_statement()
原生sql语句查询
res=session.query(Student).from_statement(text('select * from Student where id>1')).all()
for row in res:
print(row.id,row.name)结果
2 周杰伦
3 林俊杰
4 卢本伟
Process finished with exit code 0另外，execute()也可以
3.一对多操作
models文件
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship
Base=declarative_base()
engine=create_engine('sqlite:////home/zhanghuan/Desktop/test.db')
class Student(Base):
__tablename__='student'
id=Column(Integer,primary_key=True,autoincrement=True)
name=Column(String(20))
grade_id=Column(Integer,ForeignKey('grade.id'))
std2gra=relationship('Grade',backref='gra2stu')
class Grade(Base):
__tablename__='grade'
id=Column(Integer,primary_key=True,autoincrement=True)
name=Column(String(20))
Base.metadata.create_all(engine)views文件
from models import engine
from sqlalchemy.orm import sessionmaker
from models import Student,Grade
session_window=sessionmaker(engine)
session=session_window() 1.新增数据
1）向本表和外键关联的表中同时新增数据
新增一条数据，外键通过relationship添加，不走grade_id，
结果为在student表中新增一条数据zhanghuan，同时新增一条数据grade7到grade里，并且与新增u的zhanghuan进行关联
现实中没有这样的应用场景
std_obj=Student(name='zhanghua',std2gra=Grade(name='grade7'))
session.add(std_obj)
session.commit() 2）向本表中新增一条数据
std_obj=Student(name='zhanghua',grade_id=1)
session.add(std_obj)
session.commit() 2.查找数据
1)正向查找
result=session.query(Student).filter(Student.id>1).all()
for row in result:
print(row.id,row.name,row.std2gra.name) 结果
2 zhanghua grade5
3 zhanghua grade6
4 zhanghua grade1
5 zhanghua grade1
Process finished with exit code 0 2）反向查找
result=session.query(Grade).filter(Grade.id==1).first()
print(result.id,result.name,result.gra2std)
for item in result.gra2std:
print(item.id,item.name,item.grade_id) 结果
1 grade1 [<models.Student object at 0x7fdbdda63a20>, <models.Student object at 0x7fdbdda63a90>]
4 zhanghua 1
5 zhanghua 1
Process finished with exit code 0 4.多对多操作
1）models文件
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship
Base=declarative_base()
engine=create_engine('sqlite:////home/zhanghuan/Desktop/test1.db')
class Student(Base):
__tablename__='student'
id=Column(Integer,primary_key=True,autoincrement=True)
name=Column(String(20))
stu2cou=relationship('Course',backref='cou2stu',secondary='st2cou')
class Course(Base):
__tablename__='course'
id=Column(Integer,primary_key=True,autoincrement=True)
name=Column(String(20))
class St2cou(Base):
__tablename__='st2cou'
id=Column(Integer,primary_key=True,autoincrement=True)
student_id=Column(Integer,ForeignKey('student.id'))
course_id=Column(Integer,ForeignKey('course.id'))
Base.metadata.create_all(engine)2）views文件
from models import engine
from sqlalchemy.orm import sessionmaker
from models import Student,Course,St2cou
from sqlalchemy.sql import text
session_window=sessionmaker(engine)
session=session_window() (1)新增
1）往student和course里分别增加一条数据zhanghuan,语文，并产生关联
obj=Student(name='zhanghuan',stu2cou=[Course(name='语文'),])
session.add(obj)
session.commit() 2）在student里新增一条数据,并与已有course进行关联
obj1=Student(name='马飞飞2')
obj2=session.query(Course).filter(Course.id==1).first()
obj3=session.query(Course).filter(Course.id==2).first()
obj4=session.query(Course).filter(Course.id==3).first()
obj1.stu2cou.append(obj2)
obj1.stu2cou.append(obj3)
obj1.stu2cou.append(obj4)
session.add(obj1)
result=session.commit()
print(result) (2)删除
直接在关系表中删除即可
session.query(St2cou).filter(St2cou.student_id==7).delete()
session.commit() (3)更新
1.关系表中删除相关关系
2.重新向关系表中新增关系
(4)查询
1)正向查询
res=session.query(Student).filter(Student.id==7).first().stu2cou
for row in res:
print(row.id,row.name) 1 语文
2 数学
3 物理
4 化学
Process finished with exit code 0
2)反向查询
res=session.query(Course).filter(Course.id==1).first().cou2stu
for row in res:
print(row.id,row.name) 1 zhanghuan
6 马飞飞1
7 mafeifei
Process finished with exit code 0 还没有评论，来说两句吧