ORM初学代码

代码阐述：利用sqlalchemy创建表与数据库表关联，实现添加数据/删除单条数据/修改数据/回滚/分组计数/连表查询这些简易的MySQL功能

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://goddess:gadesi@192.168.0.101:3306/meserdb", encoding = 'utf-8',echo = True)
#echo = True显示操作细节
Base = declarative_base()
 
# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))
 
    # __table_args__ = (
    # UniqueConstraint('id', 'name', name='uix_id_name'),
    #     Index('ix_id_name', 'name', 'extra'),
    # )
    def __repr__(self):
        return "<%s name:%s>"%(self.id,self.name)

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key = True)
    name = Column(String(32),nullable = False)
    register_date = Column(nullable = False)
    gender = Column(nullable = False)
    def __repr__(self):
        return "<%s name:%s>"%(self.id,self.name)


Base.metadata.create_all(engine) #创建表结构

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
 
##添加数据
# user_obj = Users(name = "goddess",extra = "gadesi") #生成你要创建的数据对象
# print(user_obj.name,user_obj.id)  #此时未创建对象，打印id结果是None
# student_obj = Student(name = 'N4',register_date = '2020-10-04',gender = 'M')
# Session.add(student_obj)
# Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建
# print(user_obj.name,user_obj.id) #此时也依然还没创建，在commit后才创建


##删除数据
#data = Session.query(Users).filter_by().all()
# data = Session.query(Users).filter(Users.name == 'Rain').filter(Users.id<15).first() 
#filter→参数含类（表）名，使用'==';  filter_by→参数不含类（表）名，使用'='
# print("匹配数据：%s"%data)
# Session.delete(data)
##修改数据
# data.name = "Jack Liu"
# data.extra = "shit"

##回滚
#fake_user = Users(name = 'Rain',extra = '12345')
#Session.add(fake_user)
# print(Session.query(Users).filter(Users.name.in_(['Jack Liu','rain'])).count())
# Session.rollback()
# print(Session.query(Users).filter(Users.name.in_(['Jack Liu','rain'])).all())

##分组计数
# from sqlalchemy import func
# print(Session.query(func.count(Users.name),Users.name).group_by(Users.name).all())

##连表查询（无外键）
print(Session.query(Users,Student).filter(Users.id==Student.id).all())
##连表查询（有外键）
#print(Session.query(User).join(Student).all())

Session.commit() #统一提交，创建数据

参考博客：https://www.cnblogs.com/alex3714/articles/5978329.html