1. 进行mysql数据库的创建，如果已经存在，就相当于进行数据库的连接操作
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Date from sqlalchemy.orm import sessionmaker # 创建连接 engine = create_engine('mysql+pymysql://root:123456@localhost/oldboydb', encoding='utf-8') Base = declarative_base() # 生成orm基类 class User(Base): __tablename__ = 'user' # table的名字 id = Column(Integer, primary_key=True) # 创建id属性 name = Column(String(32)) # 创建name属性 password = Column(String(64)) # 创建密码属性 def __repr__(self): # 用于进行查找时的数据返回 return '<%s name :%s>' %(self.id, self.name)
Base.metadata.create_all(engine) # 进行指令的调用，即生成table
2. 进行数据的查询，包括条件查询和多条件查询
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Date from sqlalchemy.orm import sessionmaker # 创建连接 engine = create_engine('mysql+pymysql://root:123456@localhost/oldboydb', encoding='utf-8') Base = declarative_base() # 生成orm基类 class User(Base): # User继承了上述的操作 __tablename__ = 'student_user' id = Column(Integer, primary_key=True) name = Column(String(32)) password = Column(String(64)) def __repr__(self): # 返回查询到的信息 return '<id: %s name: %s password: %s>' %(self.id, self.name, self.password) # 执行上述的操作 Base.metadata.create_all(engine) Session_class = sessionmaker(bind=engine) # 进行数据库的连接 Session = Session_class() # 生成session 实例 # 进行数据查询 .all()输出所有的检索结果，.first() 输出第一条的结果 data = Session.query(User).filter_by(name='alex').first() print(data) data = Session.query(User).filter(User.name == 'rain').first() print(data) # 多条件查询 data = Session.query(User).filter(User.name=='rain').filter(User.id > 1).first() print(data)
3. 进行用户属性的修改
# 进行数据的条件修改 data = Session.query(User).filter(User.name=='rain').first() # 查找出数据的类 data.name = 'Tom' # 对类属性进行修改 Session.commit() # 结果的提交
print(data.id) # 打印查找结果的id信息
4. 添加新的用户信息
fake_user = User(name='Jack', password='123456') # 设置用户的名字和密码 Session.add(fake_user) # 将数据添加到表内
5. 进行用户信息的删除
data = Session.query(User).filter(User.name=='Jack').first() # 查找用户信息 Session.delete(data) # 删除用户 Session.commit() # 提交结果
6. 数据的回滚操作
data = Session.query(User).filter(User.name=='Jack').first() # 查找符合条件的数据 Session.delete(data) # 数据删除 Session.rollback() # 数据回滚，取消上述操作 Session.commit() # 提交结果
7. 数据统计
print(Session.query(User).filter(User.name.in_(['alex', 'Jack'])).count()) # 统计名字是'alex'或者'Jack'的次数
# 2
8. 根据名字进行数据分组
from sqlalchemy import func data = Session.query(User.name, func.count(User.name)).group_by(User.name).all() print(data) # [('alex', 1), ('Tom', 1), ('Jack', 1)]
9. 定义数据库的student表，进行连表查询
# 定义Student类关联student table class Student(Base): __tablename__ = 'student' id = Column(Integer, primary_key=True) name = Column(String(32)) register_data = Column(Date) sex = Column(String(32)) def __repr__(self): # 返回查询到的信息 return '<id: %s name: %s register_data: %s>' % (self.id, self.name, self.register_data) data = Session.query(User, Student).filter(User.name==Student.name).all() print(data)
10. 将两个表的属性进行串联, 这里将student的id属性，有上课记录study_record的stu_id 属性进行关联，构造一个学生的上课记录
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Date, ForeignKey from sqlalchemy.orm import sessionmaker, relationship # 创建连接 engine = create_engine('mysql+pymysql://root:123456@localhost/oldboydb', encoding='utf-8') Base = declarative_base() # 生成orm 基类 # 构造student的类 class Student(Base): __tablename__ = 'student' id = Column(Integer, primary_key=True) name = Column(String(32), nullable=False) register_data = Column(Date, nullable=False) sex = Column(String(32)) def __repr__(self): return '<id: %s name: %s sex: %s>'%(self.id, self.name, self.sex) class StudyRecord(Base): __tablename__ = 'study_record' id = Column(Integer, primary_key=True) day = Column(Integer, nullable=False) status = Column(String(32), nullable=False) stu_id = Column(Integer, ForeignKey('student.id')) # 构建外键 student = relationship('Student', backref='my_study_record') def __repr__(self): return '<%s day:%s status:%s>'%(self.student.name, self.day, self.status) Base.metadata.create_all(engine) Session_class = sessionmaker(bind=engine) session = Session_class() s1 = Student(name='Alex', register_data='2015-04-21') s2 = Student(name='Jack', register_data='2016-04-21') s3 = Student(name='Rain', register_data='2017-04-21') s4 = Student(name='Eric', register_data='2018-04-21') study_obj1 = StudyRecord(day=1, status='YES', stu_id=1) study_obj2 = StudyRecord(day=2, status='NO', stu_id=1) study_obj3 = StudyRecord(day=3, status='YES', stu_id=1) study_obj4 = StudyRecord(day=1, status='YES', stu_id=2) # 进行数据的批量插入 session.add_all([s1, s2, s3, s4, study_obj1, study_obj2, study_obj3, study_obj4]) stu_obj = session.query(Student).filter(Student.name=='alex').first() print(stu_obj.my_study_record) # 找出student='alex' 对应的学习记录
11. 一张表里的两个属性对应一张表的属性，就好比商品的收获地址和发货地址都对应的是地址数据库
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Date, ForeignKey from sqlalchemy.orm import sessionmaker, relationship # 创建连接 engine = create_engine('mysql+pymysql://root:123456@localhost/oldboydb', encoding='utf-8') Base = declarative_base() class Customer(Base): __tablename__ = 'customer' id = Column(Integer, primary_key=True) name = Column(String(64)) billing_address_id = Column(Integer, ForeignKey('address.id')) shipping_address_id = Column(Integer, ForeignKey('address.id')) billing_address = relationship('Address', foreign_keys = [billing_address_id]) shipping_address = relationship('Address', foreign_keys = [shipping_address_id]) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) street = Column(String(64)) city = Column(String(64)) state = Column(String(64)) def __repr__(self): return self.street Base.metadata.create_all(engine) Session_class = sessionmaker(bind=engine) session = Session_class() stu_obj = session.query(Customer).filter(Customer.name=='computer').first() print(stu_obj.shipping_address) # 找出student='alex' 对应的学习记录
12. 构建多对多的表格类型，即一本书可以对应多个作者，一个作者可以对应多本书
orm_m2m.py
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Date, ForeignKey, Table from sqlalchemy.orm import sessionmaker, relationship # 创建连接 engine = create_engine('mysql+pymysql://root:lishentao22@localhost/oldboydb?charset=utf8', ) Base = declarative_base() book_m2m_author = Table('book_m2m_author', Base.metadata, Column('book_id', ForeignKey('books.id')), Column('author_id', ForeignKey('authors.id'))) class Book(Base): __tablename__ = 'books' id = Column(Integer, primary_key=True) name = Column(String(64)) pub_date = Column(Date) authors = relationship('Author', secondary=book_m2m_author, backref='books') def __repr__(self): return self.name class Author(Base): __tablename__ = 'authors' id = Column(Integer, primary_key=True) name = Column(String(32)) def __repr__(self): return self.name Base.metadata.create_all(engine)
orm_m2m_api.py
from day12 import orm_m2m from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind=orm_m2m.engine) session = Session_class() # b1 = orm_m2m.Book(name='learn python with Alex', pub_date='2014-06-02') # b2 = orm_m2m.Book(name='learn Zhangbility with Alex', pub_date='2015-02-06') # b3 = orm_m2m.Book(name='go alex to taiguo', pub_date='2014-02-05') # # a1 = orm_m2m.Author(name='Alex') # a2 = orm_m2m.Author(name='Jack') # a3 = orm_m2m.Author(name='Rain') # # b1.authors = [a1, a3] # b3.authors = [a1, a2, a3] # # session.add_all([b1, b2, b3, a1, a2, a3]) # session.commit() author_obj = session.query(orm_m2m.Author).filter(orm_m2m.Author.name=='alex').first() # session.delete(author_obj) # 删除这个作者 book_obj = session.query(orm_m2m.Book).filter(orm_m2m.Book.id==14).first() book_obj.authors.remove(author_obj) session.commit()
