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

创建指定的数据库表：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,create_engine

#创建一个orm模型基类
Base=declarative_base()

class User(Base):
    __tablename__="user"
    id=Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(32),index=True)

#创建数据库连接
engine=create_engine("mysql+pymysql://root:1234@127.0.0.1:3306/lgweb?charset=utf8")

#调用engine 创建所有继承base类的数据表类
Base.metadata.create_all(engine)

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

from creat_table import engine,User
from sqlalchemy.orm.session import sessionmaker

#创建指定数据库会话
Session=sessionmaker(engine)
#打开会话窗口
db_session=Session()

# user_obj=User(name="tom")
# user1=User(name="lisa")

#相当于insert into
# db_session.add(user_obj)
# db_session.add(user1)
db_session.add_all([
    User(name="婉君"),User(name="小芬"),User(name="程洁"),User(name="思宇")
])
#提交数据
db_session.commit()

db_session.close()

 单表查询：

from creat_table import engine,User
from sqlalchemy.orm.session import sessionmaker

Session=sessionmaker(engine)
db_session=Session()

# user_list=db_session.query(User).filter(User.id>=4).all()
user_list=db_session.query(User).filter_by(id>=4).all()
for user in user_list:
    print(user.id,user.name)

 更新数据：

from creat_table import engine,User
from sqlalchemy.orm.session import sessionmaker

Session=sessionmaker(engine)
db_session=Session()

db_session.query(User).filter_by(name="小芬").update({"name":"晓晓"})
db_session.commit()

删除数据：

from creat_table import engine,User
from sqlalchemy.orm.session import sessionmaker

Session=sessionmaker(engine)
db_session=Session()

ret=db_session.query(User).filter_by(id="2").delete()
print(ret)
db_session.commit()
db_session.close()

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

多表关联：主外键关联

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import Session
from sqlalchemy.orm import relationship
from sqlalchemy import Column,Integer,String,ForeignKey

#创建一个数据表的继承基类
Base=declarative_base()
#创建一个数据库连接
engine=create_engine("mysql+pymysql://root:root1234@127.0.0.1:3306/db_unv?charset=utf8")
#创建一个会话
db_session=Session(engine)



class Student(Base):
    __tablename__="student"
    id=Column(Integer,primary_key=True)
    name=Column(String(32),index=True)
    school_id=Column(Integer,ForeignKey("school.id"))   #外键
    stu2sch=relationship("School",backref='sch2stu')    # school反向解析student关键字


class School(Base):
    __tablename__="school"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)

Base.metadata.create_all(engine)

 多表的数据添加：

from creat_table import engine,School,Student
from sqlalchemy.orm.session import sessionmaker

session=sessionmaker(engine)
db_session=session()

# stu_obj=Student(name="凯文",stu2sch=School(name="麻省理工"))         #反向添加数据
sch_obj=School(name="常青藤大学",sch2stu=[Student(name="胡柯"),Student(name="徐素")])    #正向添加


# db_session.add(stu_obj)
db_session.add(sch_obj)
db_session.commit()
db_session.close()

 多表数据的更新和删除：

from creat_table import School,Student,engine
from sqlalchemy.orm.session import sessionmaker

Session=sessionmaker(engine)
db_session=Session()

# stu_obj=db_session.query(Student).filter(Student.name=="徐菲菲").first()
# print(stu_obj.school_id)
#
# db_session.query(Student).filter(Student.id==stu_obj.id).update({"school_id":2})
sch_obj=db_session.query(School).filter(School.name=="麻省理工").first()
# ret=db_session.query(Student).filter(Student.school_id==sch_obj.id).update({"school_id":3})
db_session.query(Student).filter(Student.school_id==sch_obj.id).delete()

db_session.commit()

db_session.close()