前戏:
? 不用怀疑,你肯定用过Django中的orm,这个orm框架是django框架中自己封装的,在Django中配置和使用较为简单,但是并不适用于其他web框架,而今天说的sqlalchemy是兼容python语言的orm框架,相信你已经明白谁牛逼!
? 下面,接下来....
? 还有比案例更好的教程吗,那下面请您享用为您准备好的大餐...
1.单表操作
1.1创建表
导入sqlachemy资源包
-
案例 from sqlalchemy.ext.declarative import declarative_base
BaseModel = declarative_base()
# 创建 Class / Table
from sqlalchemy import Column,Integer,String
class User(BaseModel):
__tablename__ = "user" # 创建Table时名字
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(32),nullable=False,index=True,unique=True)
# Column 定义数据列
# int string 数据类型
# 数据库引擎的创建:
from sqlalchemy.engine import create_engine
engine = create_engine("mysql+pymysql://root:[email?protected]:3306/dbname?charset=utf8") # 数据库连接驱动语句
#利用 User 去数据库创建 user Table
BaseModel.metadata.create_all(engine) # 数据库引擎
# 数据库呢? 数据库服务器地址呢?
# 数据库连接呢?
1.2CURD(增删改查)
-
案例 # 模拟 navcat 操作
# 1.选择数据库
from sqlalchemy.engine import create_engine
engine = create_engine("mysql+pymysql://root:[email?protected]:3306/s21?charset=utf8")
# 2.选择表
# 3.创建查询窗口
from sqlalchemy.orm import sessionmaker
select_db = sessionmaker(engine) # 选中数据库
db_session = select_db() # 已经打开查询窗口
# 4.写入SQL语句
user = User(name="Alexander.DSB.Li") # == insert into user(`name`) value ("Alexander.DSB.Li")
user_list = [User(name="Alex's Father"),User(name="李杰")]
# 放入查询窗口
db_session.add(user)
db_session.add_all(user_list)
# 5.提交sql语句
db_session.commit()
# 6.关闭查询窗口
db_session.close()
# 简单无条件查询
# """
# select * from user table_user == class_User
# """
res = db_session.query(User).all() # 查询全部符合条件的数据
res = db_session.query(User).first() # 查询符合条件的第一条数据
print(res.id,res.name)
# 简单条件查询
# """
# select * from user where id=3
# """
res = db_session.query(User).filter(User.id==3).all()
print(res[0].id,res[0].name)
res = db_session.query(User).filter_by(id=3).all()
res = db_session.query(User).filter(User.id==3,User.name == "123").all()
print(res)
#
is_true_or_false = User.id==3 and User.name == "123"
# 修改数据 update
res = db_session.query(User).filter(User.id == 1).update({"name":"李亚历山大"})
db_session.commit()
db_session.close()
# 删除数据
res = db_session.query(User).filter(User.id == 2).delete()
db_session.commit()
db_session.close()
2.一对多
2.1创建表
-
案例 from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,ForeignKey
from sqlalchemy.engine import create_engine
#ORM精髓 relationship
from sqlalchemy.orm import relationship
engine = create_engine("mysql+pymysql://root:[email?protected]:3306/s21?charset=utf8")
BaseModel = declarative_base()
# 一对多
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,nullable=False)
sch_id = Column(Integer,ForeignKey("school.id"))
# 关系映射
stu2sch = relationship("School",backref="sch2stu")
BaseModel.metadata.create_all(engine)
2.2CURD
-
使用案例 from sqlalchemy.orm import sessionmaker
from app01.static.上午.createForeignKey import engine
select_db = sessionmaker(engine)
db_session = select_db()
# 增加数据
# 先建立一个学校 再查询这个学校的id 利用这个ID 再去创建学生添加sch_id
# relationship 正向添加 relationship字段出现在哪个类
# stu = Student(name="DragonFire",stu2sch=School(name="OldBoyBeijing"))
# stu sql 语句
# db_session.add(stu)
# db_session.commit()
# db_session.close()
# relationship 反向添加
# sch = School(name="OldBoyShanghai")
# sch.sch2stu = [
# Student(name="赵丽颖"),# Student(name="冯绍峰")
# ]
#
# db_session.add(sch)
# db_session.commit()
# db_session.close()
# 查询 relationship 正向
# 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:
# # print(sch.name,len(sch.sch2stu)) 学校里面有多少学生
# for stu in sch.sch2stu:
# print(sch.name,stu.name)
3.多对多
3.1 创建表
-
案例 from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,ForeignKey
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import relationship
BaseModel = declarative_base()
engine = create_engine("mysql+pymysql://root:[email?protected]:3306/s21?charset=utf8")
class Girl(BaseModel):
__tablename__ = "girl"
id = Column(Integer,nullable=False)
gyb = relationship("Boy",backref="byg",secondary="hotel") # secondary="hotel" 数据表中的数据才能证明两者关系
class Boy(BaseModel):
__tablename__ = "boy"
id = Column(Integer,nullable=False)
class Hotel(BaseModel):
__tablename__ = "hotel"
id = Column(Integer,primary_key=True)
bid = Column(Integer,ForeignKey("boy.id"))
gid = Column(Integer,ForeignKey("girl.id"))
BaseModel.metadata.create_all(engine)
3.2CURD
-
案例 from sqlalchemy.orm import sessionmaker
from app01.static.上午.createM2M import engine
select_db = sessionmaker(engine)
db_session = select_db()
# 增加数据 relationship 正向添加
# g = Girl(name="赵丽颖",gyb=[Boy(name="DragonFire"),Boy(name="冯绍峰")])
# db_session.add(g)
# db_session.commit()
# db_session.close()
# 增加数据 relationship 反向添加
# b = Boy(name="李杰")
# b.byg = [
# Girl(name="罗玉凤"),# Girl(name="朱利安"),# Girl(name="乔碧萝")
# ]
#
# db_session.add(b)
# db_session.commit()
# db_session.close()
# 查询 relationship 正向
# res = db_session.query(Girl).all()
# for g in res:
# print(g.name,len(g.gyb))
# 查询 relationship 反向
# res = db_session.query(Boy).all()
# for b in res:
# print(b.name,len(b.byg))
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|