SQLAlchemy的使用
1 # ORM中的数据表是什么呢? 2 # Object Relation Mapping 3 # Object - Table 通过 Object 去操纵数据表 4 # 从而引出了我们的第一步创建数据表 - 创建Object 5 # 1. 创建Object 6 # class User(object): 7 # pass 8 9 # 2. 让Object与数据表产生某种关系 也就是让Object与数据表格式极度相似 10 # 导入官宣基础模型 11 from sqlalchemy.ext.declarative import declarative_base 12 # 实例化官宣模型 - Base 就是 ORM 模型 13 Base = declarative_base() 14 # 当前的这个Object继承了Base也就是代表了Object继承了ORM的模型 15 class User(Base): # 相当于 Django Models中的 Model 16 # 为Table创建名称 17 __tablename__ = "user" 18 # 创建ID数据字段 , 那么ID是不是一个数据列呢? 也就是说创建ID字段 == 创建ID数据列 19 from sqlalchemy import Column,Integer,String 20 # id = Column(数据类型,索引,主键,外键,等等) 21 # int == Integer 22 id = Column(Integer,primary_key=True,autoincrement=True) 23 # str == char(长度) == String(长度) 24 name = Column(String(32),index=True) 25 26 # 3.去数据库中创建数据表? or 先连接数据库? 27 # 3.去连接数据库 创建数据引擎 28 from sqlalchemy import create_engine 29 # 创建的数据库引擎 30 engine = create_engine("mysql+pymysql://root:DragonFire@127.0.0.1:3306/dragon?charset=utf8") 31 32 # Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表 33 Base.metadata.create_all(engine)
创建数据表
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String # declarative_base 声明基类 Base = declarative_base() # models.model class User(Base): __tablename__ = "users" id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(16),nullable=False) #创建数据库引擎 from sqlalchemy import engine my_engine = engine.create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s17db?charset=utf8") Base.metadata.create_all(my_engine)
增删改查
from create_table import User, my_engine from sqlalchemy.orm import sessionmaker Session = sessionmaker(my_engine) # 选择需要创建查询窗口的数据库 db_session = Session() # 新建查询窗口 # 增加数据 # user = User(name="DragonFire") # insert into #编写sql语句 # user1 = User(name="DragonFire") # insert into #编写sql语句 # user2 = User(name="DragonFire") # insert into #编写sql语句 # id name # 1 DragonFire # db_session.add(user) # 把sql语句放在查询窗口中 # db_session.add_all([user1,user2]) # db_session.commit() # 执行sql语句 提交 # db_session.close() # 查看数据 # res = db_session.query(User).all() # for i in res: # print(i.id,i.name) # res = db_session.query(User).first() # print(res.id,res.name) # 简单条件的查询语句 # res = db_session.query(User).filter(User.id == 2).first() # print(res.id) # res = db_session.query(User).filter(User.id >= 2).all() # for i in res: # print(i.id,i.name) # 复杂条件查询 并列条件查询 # res = db_session.query(User).filter(User.id >= 2, User.id<=4).all() # for i in res: # print(i.id,i.name) # or条件查询 # from sqlalchemy import or_,and_ # res = db_session.query(User).filter(or_(User.id == 1,User.id == 2)) # print(res) # for i in res: # print(i.id,i.name) # 修改数据 # res = db_session.query(User).filter(User.id>=2).update({"name":"Alexander.DSB.Li"}) # db_session.commit() # db_session.close() # 删除数据 # res = db_session.query(User).filter(User.name == "Alexander.DSB.Li").delete() # db_session.commit() # db_session.close()
创建数据表
from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.orm import relationship class Student(Base): __tablename__ = "student" id = Column(Integer, primary_key=True) name = Column(String(16), nullable=False) sch_id = Column(Integer, ForeignKey("school.id")) # relationship stu2sch = relationship("School",backref="sch2stu") class School(Base): __tablename__ = "school" id = Column(Integer, primary_key=True) name = Column(String(32), nullable=False) from sqlalchemy.engine import create_engine my_engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s17db?charset=utf8") Base.metadata.create_all(my_engine)
增删改查
from create_ForeignKey import Student,School,my_engine from sqlalchemy.orm import sessionmaker Session = sessionmaker(my_engine) db_session = Session() # 增加数据 relationship正向版 # stu = Student(name="DragonFire",stu2sch=School(name="OldBoyEDU BeiJing")) # db_session.add(stu) # db_session.commit() # 增加数据 relationship反向版 # sch = School(name="OldBoyEDU ShangHai") # sch.sch2stu = [Student(name="LinHaiFeng"),Student(name="YangJun")] # db_session.add(sch) # db_session.commit() # 查询数据 relationship正向版 # res = db_session.query(Student).filter(Student.id==2).first() # print(res.id,res.name,res.stu2sch.name) # res = db_session.query(Student).all() # for i in res: # print(i.id,i.name,i.stu2sch.name) # 查询数据 relationship反向版 # res = db_session.query(School).all() # for i in res: # print(i.id,i.name,len(i.sch2stu)) # # for stu in i.sch2stu: # # print(i.id,i.name,stu.name) # 删除数据 # res = db_session.query(Student).filter(Student.sch_id == 1).delete() # db_session.commit() # print(res)
创建数据表
from sqlalchemy import Column,String,Integer,ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship Base = declarative_base() class Girl(Base): __tablename__ = "girl" id = Column(Integer,primary_key=True) name = Column(String(16),nullable=False) #relationship g2b = relationship("Boy",backref="b2g",secondary="hotel") class Boy(Base): __tablename__ = "boy" id = Column(Integer,primary_key=True) name = Column(String(16),nullable=False) class Hotel(Base): __tablename__ = "hotel" id = Column(Integer,primary_key=True) boy_id = Column(Integer,ForeignKey("boy.id")) girl_id = Column(Integer,ForeignKey("girl.id")) from sqlalchemy.engine import create_engine my_engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s17db?charset=utf8") Base.metadata.create_all(my_engine)
增删改查
from create_M2M import Girl,Boy,Hotel,my_engine from sqlalchemy.orm import sessionmaker Session = sessionmaker(my_engine) db_session = Session() # 增加数据 Relationship 正向版 # g = Girl(name="赵丽颖") # g.g2b = [Boy(name="冯绍峰"),Boy(name="DragonFire")] # db_session.add(g) # db_session.commit() # 增加数据 Relationship 反向版 # b = Boy(name="李杰") # b.b2g = [Girl(name="娟儿"),Girl(name="罗玉凤")] # db_session.add(b) # db_session.commit() # 查询数据 Relationship 反向 # res = db_session.query(Boy).all() # for i in res: # for g in i.b2g: # print(i.name,g.name) # 查询数据 Relationship 正向 # res = db_session.query(Girl).all() # for i in res: # for b in i.g2b: # print(i.name,b.name) # h = Hotel(boy_id=1,girl_id=3) # db_session.add(h) # db_session.commit()
