SQLAlchemy 的增删改查

1.创建数据表

ORM中的数据表是什么呢?

Object Relation Mappin

create_table.py

# 导入官宣基础模型
from sqlalchemy.ext.declarative import declarative_base
# 实例化官宣模型 - Base 就是 ORM 模型
BaseModel = declarative_base()
# 当前的这个Object继承了Base也就是代表了Object继承了ORM的模型
from sqlalchemy import Column,INT,Integer,INTEGER,CHAR,VARBINARY,NCHAR,NUMERIC,String
class User(BaseModel):  # 相当于 Django Models中的 Model
    __tablename__ = "user"   # 为Table创建名称
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=False,unique=True,index=True)
from sqlalchemy.engine import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemys18?charset=utf8") #root表示数据库账号,123表示数据库密码,没有密码就不用填
# Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表
BaseModel.metadata.create_all(engine)

 2.单表增删改查

CRUD.py

开窗口
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import create_engine
from create_table import User

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemys18?charset=utf8")

#选择数据库
Session = sessionmaker(engine)

#新建查询窗口
db_session = Session()

#写SQL
# insert data
u = User(name="李杰")
db_session.add(u)
u_list = [User(name="alex"),User(name='小钱')]
db_session.add_all(u_list)
db_session.commit()
db_session.close()

# 查询数据
res = db_session.query(User).all() #查询所有数据
print(res[0].name,res[0].id)
for row in res:
    print(row.id,row.name)

res= db_session.query(User).first() #查询第一条数据
print(res.id,res.name)

#带条件的查询
res = db_session.query(User).filter(User.id == 1).first()
print(res)
print(res.id,res.name)

# 并列条件
res = db_session.query(User).filter(User.id == 3,User.name == "小钱").first()
print(res.id,res.name)

# 修改 update
res = db_session.query(User).filter(User.id ==1).update({"name":"李杰DSB"})
print(res)
db_session.commit()

# 删除 delete
res = db_session.query(User).filter(User.id ==1).delete()
print(res)
db_session.commit()

from sqlalchemy.sql import and_,or_

ret = db_session.query(User).filter(and_(User.id <=3,User.name=="小钱")).all()
print(ret)
ret = db_session.query(User).filter(or_(User.id ==3, User.name=="小钱")).all()
print(ret)
ret = db_session.query(User).filter(or_(User.id ==3, User.name=="小钱",and_(User.id <=3,User.name=="小钱"))).all()
print(ret)

r4 = db_session.query(User).filter_by(name='小钱')
print(r4)

user_list = db_session.query(User).order_by(User.id).all()
user_list = db_session.query(User).order_by(User.id.desc()).all()
for i in  user_list:
    print(i.id)

ret = db_session.query(User).filter(User.id.in_([2,3,4])).all()
ret = db_session.query(User).filter(~User.id.in_([1])).all()
print(ret)

3.一对多的操作:ForeignKey

create_table_ForeignKey.py

from sqlalchemy.ext.declarative import declarative_base
BaseModel = declarative_base()
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship

class School(BaseModel):
    __tablename__ = "school"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

class Student(BaseModel):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    sch_id = Column(Integer,ForeignKey('school.id'))
    # ORM精髓
    stu2sch = relationship("School",backref='sch2stu')

from sqlalchemy.engine import create_engine
engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sqlalchemys18?charset=utf8')
BaseModel.metadata.create_all(engine)

create_table_ForeignKey.py

chemy.orm import sessionmaker

from create_table_ForeignKey import Student, engine, School

Session = sessionmaker(engine)
db_session = Session()

# # 1.增加数据 relationship 正向添加数据
s = Student(name="DragonFire",stu2sch=School(name="OldBoyBeiJing"))
db_session.add(s)
db_session.commit()

#增加数据 relationship 反向添加数据
sch = School(name="OldBoyShangHai")
sch.sch2stu = [
    Student(name='Egon.Lin'),
    Student(name='Jun.Yang')
]

db_session.add(sch)
db_session.commit()

#查询数据 relationship 正向
res = db_session.query(Student).first()
print(res.id,res.name,res.stu2sch.name)
res = db_session.query(Student).all()
for stu in res:
    print(stu.name,stu.stu2sch.name)

#查询数据 relationship 反向
res = db_session.query(School).all()
for sch in res:
    for stu in sch.sch2stu:
        print(sch.id,sch.name,stu.name)

#更新数据
class_info = db_session.query(School).filter(School.name=="OldBoyShangHai").first()
db_session.query(Student).filter(class_info.id == Student.sch_id).update({"name":"alex"})
db_session.commit()

#删除数据
class_info = db_session.query(School).filter(School.name=="OldBoyShangHai").first()
db_session.query(Student).filter(class_info.id == Student.sch_id).delete()
db_session.commit()

4.多对多:ManyToMany

create_table_M2M.py

from sqlalchemy.ext.declarative import declarative_base
BaseModel = declarative_base()
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import relationship

class Boy(BaseModel):
    __tablename__ = "boy"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)

class Girl(BaseModel):
    __tablename__ = "girl"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    gyb = relationship("Boy",backref="byg",secondary="hotel")

class Hoter(BaseModel):
    __tablename__ = "hotel"
    id = Column(Integer,primary_key=True)
    b_id = Column(Integer,ForeignKey("boy.id"))
    g_id = Column(Integer,ForeignKey("girl.id"))

from sqlalchemy.engine import create_engine
engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sqlalchemys18?charset=utf8')
BaseModel.metadata.create_all(engine)

CRUD_M2M.py

from sqlalchemy.orm import sessionmaker
from create_table_M2M import Boy,Girl,engine

Session = sessionmaker(engine)
db_session = Session()

#增加数据 relationship 正向添加
g = Girl(name="赵丽颖",gyb=[Boy(name="冯绍峰"),Boy(name="DragonFire")])
db_session.add(g)
db_session.commit()

#增加数据 relationship 反向添加
b = Boy(name="AlexDSB")
b.byg = [Girl(name="娟儿"),Girl(name="罗玉凤")]
db_session.add(b)
db_session.commit()

# 查询数据 relationship 正向
res = db_session.query(Girl).first()
for b in res.gyb:
    print(res.name,b.name)

# 查询数据 relationship 反向
res = db_session.query(Boy).all()
for b in res:
    for g in b.byg:
        print(b.name,g.name)

 

详情 : https://www.cnblogs.com/DragonFire/p/10166527.html