SQLAlchemy对数据库的增删改查操作

创建指定的数据库表：

1from sqlalchemy.ext.declarative import declarative_base
2from sqlalchemy import Column,Integer,String,create_engine
3
4#创建一个orm模型基类
5Base=declarative_base()
6
7class User(Base):
8    __tablename__="user"
9    id=Column(Integer,primary_key=True,autoincrement=True)
10    name=Column(String(32),index=True)
11
12#创建数据库连接
13engine=create_engine("mysql+pymysql://root:1234@127.0.0.1:3306/lgweb?charset=utf8")
14
15#调用engine 创建所有继承base类的数据表类
16Base.metadata.create_all(engine)

对数据库中表数据的添加及批量添加操作：

1from creat_table import engine,User
2from sqlalchemy.orm.session import sessionmaker
3
4#创建指定数据库会话
5Session=sessionmaker(engine)
6#打开会话窗口
7db_session=Session()
8
9# user_obj=User(name="tom")
10# user1=User(name="lisa")
11
12#相当于insert into
13# db_session.add(user_obj)
14# db_session.add(user1)
15db_session.add_all([
16    User(name="婉君"),User(name="小芬"),User(name="程洁"),User(name="思宇")
17])
18#提交数据
19db_session.commit()
20
21db_session.close()

单表查询：

1from creat_table import engine,User
2from sqlalchemy.orm.session import sessionmaker
3
4Session=sessionmaker(engine)
5db_session=Session()
6
7# user_list=db_session.query(User).filter(User.id>=4).all()
8user_list=db_session.query(User).filter_by(id>=4).all()
9for user in user_list:
10    print(user.id,user.name)

更新数据：

1from creat_table import engine,User
2from sqlalchemy.orm.session import sessionmaker
3
4Session=sessionmaker(engine)
5db_session=Session()
6
7db_session.query(User).filter_by(name="小芬").update({"name":"晓晓"})
8db_session.commit()

删除数据：

1from creat_table import engine,User
2from sqlalchemy.orm.session import sessionmaker
3
4Session=sessionmaker(engine)
5db_session=Session()
6
7ret=db_session.query(User).filter_by(id="2").delete()
8print(ret)
9db_session.commit()
10db_session.close()

************************************************************************************************************************

多表关联：主外键关联

1from sqlalchemy.ext.declarative import declarative_base
2from sqlalchemy.engine import create_engine
3from sqlalchemy.orm.session import Session
4from sqlalchemy.orm import relationship
5from sqlalchemy import Column,Integer,String,ForeignKey
6
7#创建一个数据表的继承基类
8Base=declarative_base()
9#创建一个数据库连接
10engine=create_engine("mysql+pymysql://root:root1234@127.0.0.1:3306/db_unv?charset=utf8")
11#创建一个会话
12db_session=Session(engine)
13
14
15
16class Student(Base):
17    __tablename__="student"
18    id=Column(Integer,primary_key=True)
19    name=Column(String(32),index=True)
20    school_id=Column(Integer,ForeignKey("school.id"))   #外键
21    stu2sch=relationship("School",backref='sch2stu')    # school反向解析student关键字
22
23
24class School(Base):
25    __tablename__="school"
26    id = Column(Integer, primary_key=True)
27    name = Column(String(32), index=True)
28
29Base.metadata.create_all(engine)

多表的数据添加：

1from creat_table import engine,School,Student
2from sqlalchemy.orm.session import sessionmaker
3
4session=sessionmaker(engine)
5db_session=session()
6
7# stu_obj=Student(name="凯文",stu2sch=School(name="麻省理工"))         #反向添加数据
8sch_obj=School(name="常青藤大学",sch2stu=[Student(name="胡柯"),Student(name="徐素")])    #正向添加
9
10
11# db_session.add(stu_obj)
12db_session.add(sch_obj)
13db_session.commit()
14db_session.close()

多表数据的更新和删除：

1from creat_table import School,Student,engine
2from sqlalchemy.orm.session import sessionmaker
3
4Session=sessionmaker(engine)
5db_session=Session()
6
7# stu_obj=db_session.query(Student).filter(Student.name=="徐菲菲").first()
8# print(stu_obj.school_id)
9#
10# db_session.query(Student).filter(Student.id==stu_obj.id).update({"school_id":2})
11sch_obj=db_session.query(School).filter(School.name=="麻省理工").first()
12# ret=db_session.query(Student).filter(Student.school_id==sch_obj.id).update({"school_id":3})
13db_session.query(Student).filter(Student.school_id==sch_obj.id).delete()
14
15db_session.commit()
16
17db_session.close()