ORM和SQLAlchemy - downtiser/python-one GitHub Wiki

ORM(object relational mapping),就是对象映射关系程序,简单来说是这样的:实现了将数据库实例化，使得用户在操作数据时无需使用数据库原生语句来操作数据库，而是通过ORM将原生语句进行封装后的方法，使得用户能够方便高效的管理操作数据库。

SQLAlchemy是python上最知名的ORM框架，在使用前要先安装SQLAlchemy模块

SQLAlchemy基本使用方法:

#Downtiser
import sqlalchemy
from sqlalchemy import create_engine   #导入连接生成方法
from sqlalchemy.ext.declarative import declarative_base  #导入基类生成方法
from sqlalchemy import Column, Integer, String  #导入数据类型
from sqlalchemy.orm import sessionmaker  #导入游标生成器
engine = create_engine('mysql+mysqlconnector://root:365225@localhost/test',encoding='utf-8', echo=True)
# 连接数据库，指定数据库+数据库驱动://用户名:密码@主机/数据库,并指定字符编码，echo指定是否返回创建过程

BaseClass = declarative_base() #生成ORM基类

class User(BaseClass): #继承基类
    __tablename__='user1'
    id = Column(Integer, primary_key=True) #生成字段并指定字段类型
    name = Column(String(32))
    password = Column(String(64))

# BaseClass.metadata.create_all(engine)  #创建表结构 基类下的metaclass.create_all会将继承基类的所有类全部创建
Session_class = sessionmaker(bind=engine)  #和连接实例绑定，生成一个游标类
session = Session_class() #生成游标实例，相当于mysql中的cursor
user_obj = User(name='downtiser',password='365225') #生成数据实例
user_obj2 = User(name='noob',password='123abc')
session.add(user_obj) #将要写入的数据添加到游标实例中
session.add(user_obj2)

session.commit() #将数据提交

• 查找及修改删除数据:

#Downtiser
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqlconnector://root:gu996080@localhost/test', encoding='utf-8',)

Basic = declarative_base()

class User(Basic):
    __tablename__ = 'user1'
    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)

#Basic.metadata.create_all(engine)
Session_class = sessionmaker()
session = Session_class(bind=engine)
res = session.query(User).filter_by(id=2).all() #查找filter_by(相当于where)指定的数据，并返回一个数据列表,里面存着数据实例,相等用'='
# res = session.query(User).filter_by().first() #也可以用.first()返回匹配到的第一个数据实例
res2 = session.query(User).filter(User.id<3).filter(User.name=='downtiser').all() # 可以用filter()进行多条件判断,注意等于要用'=='
res3 = session.query(User).filter(User.name.in_(['idiot', 'loser', 'noob'])).first()  #可以从指定范围中过滤,注意：MySQL不区分大小写
counter = session.query(User).filter(User.name.in_(['noob', 'idiot', 'downtiser'])).count()  #统计匹配到的数据量
session.query(User).filter(User.name=='lol').delete()  #删除匹配到的数据
print(res,)
print(res2)
print(res3)
print(counter)
# res3.name = 'idiot'
# res3.password = '65535'  #修改匹配到的数据
session.rollback()  #若进行回滚，则之前修改的数据都不会写入
session.commit()

• 分组及统计：

#Downtiser
import sqlalchemy
from sqlalchemy import create_engine, func #导入函数
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, CHAR, Date
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqlconnector://root:gu996080@localhost/test', encoding='utf-8',)

Basic = declarative_base()

class Student(Basic):  #映射数据库中的同名表
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(CHAR(32), nullable=False)
    age = Column(Integer, nullable=False)
    register_date = Column(Date, nullable=False)
    gender = Column(CHAR(32), nullable=False)
    def __repr__(self):
        return "<%s %s %s %s %s>"%(self.id, self.name, self.age,self.register_date, self.gender)

Session_class = sessionmaker()
session = Session_class(bind=engine)
res = session.query(Student.age, func.count('*')).group_by(Student.age).all()
# group_by()进行分组，同时调用func.count()聚合函数对每组数据量进行统计
print(res)

• 连表：

#Downtiser
from sqlalchemy import create_engine, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, CHAR, Date
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqlconnector://root:gu996080@localhost/test', encoding='utf-8',)

Basic = declarative_base()
class User(Basic):
    __tablename__ = 'user1'
    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)

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

Session_class = sessionmaker()
session = Session_class(bind=engine)
res = session.query(User, Student).filter(User.name == Student.name).all()   #按照匹配条件进行连表
# session.query(User).join(Student).all() 当两张表之间有外键关联时可以这样连表
print(res)

• 添加外键关联:

#Downtiser
from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, String, Date, CHAR, Column, ForeignKey #导入外键类
from  sqlalchemy.orm import sessionmaker, relationship #导入建立外键关系类
engine = create_engine('mysql+mysqlconnector://root:gu996080@localhost/test2', encoding='utf-8')
Basic = declarative_base()
class Student(Basic):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    age = Column(Integer, nullable=False)
    register_date = Column(Date, nullable=False)
    def __repr__(self): #调用print打印对象时,将生成对象的指定属性以字符串形式返回
        return "<%s %s %s %s>"%(self.id, self.name, self.age,self.register_date)

class StudentRecord(Basic):
    __tablename__ = 'record'
    id = Column(Integer, primary_key=True)
    day = Column(Integer, nullable=False)
    statue = Column(String(32), nullable=False, default='Yes')
    student_id = Column(Integer, ForeignKey('student.id'), nullable=False)   #建立外键
    student = relationship('Student', backref='my_study_record')
    # 相当于 session.query(Student).filter(StudentRecord.student_id==Student.id).first(),生成一个临时的数据对象，该对象能够调用与之关联的Student表的内容
    # 建立外键关联，StudentRecord可以通过student字段反查Student表的内容，设置backref使得Student表中的数据实例
    # 可以通过backref设置的字段反查StudentRecord表的内容
    def __repr__(self):
        return '<name:%s day:%s statue:%s>'%(self.student.name, self.day, self.statue)

# Basic.metadata.create_all(engine)

# stu1 = Student(name='downtiser', age=20, register_date='2018-8-13')
# stu2 = Student(name='downtiseme', age=22, register_date='2018-8-14')
# stu3 = Student(name='noob', age=25, register_date='2018-7-14')
# stu4 = Student(name='idiot', age=22, register_date='2018-8-17')
#
# record1 = StudentRecord(day=1,statue='Yes',student_id=1)
# record2 = StudentRecord(day=2,statue='Yes',student_id=1)
# record3 = StudentRecord(day=3,statue='No',student_id=1)
# record4 = StudentRecord(day=1,statue='Yes',student_id=2)
# record5 = StudentRecord(day=1,statue='Yes',student_id=3)
# record6 = StudentRecord(day=1,statue='No',student_id=4)
session_class = sessionmaker()
session = session_class(bind=engine)
# session.add_all([stu1,stu2,stu3,stu4,record1,record2,record3,record4,record5,record6]) #一次性添加
# session.commit()
stu = session.query(Student).filter(Student.name=='downtiser').first()
print(stu.my_study_record)

应该注意的是，通过relationship建立的外键关联仅存在于内存中，并不真实存在于数据库中，只有通过查找等方法返回一个数据实例后，才能通过这个实例的关联实例来查找对应表的数据，而在实例生成之前，这个关联实例并不能去查找与之关联的表的数据，诸如query(StudentRecord).filter(StudentRecord.student.name=='downtiser').first()这样在实例生成过程中就去调用关联实例的方法都是错误的，简而言之，关联实例是临时的.

添加一对多外键关联:

#Downtiser
from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, String, Date, CHAR, Column, ForeignKey #导入外键类
from  sqlalchemy.orm import sessionmaker, relationship #导入建立外键关系类
engine = create_engine('mysql+mysqlconnector://root:gu996080@localhost/test2?charset=utf8', encoding='utf-8')
Basic = declarative_base()

class Customer(Basic):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    bill_address_id = Column(Integer,ForeignKey('address.id'),nullable=False)
    package_address_id = Column(Integer, ForeignKey('address.id'),nullable=False)
    bill_address = relationship('Address',foreign_keys=[bill_address_id]) #要指定与之关联的外键
    package_address = relationship('Address',foreign_keys=[package_address_id])
    def __repr__(self):
        return 'name:[%s] bill_address:[st:%s city:%s prov:%s] package_address:[st:%s city:%s prov:%s]'%(self.name,
                                                                              self.bill_address.street,
                                                                              self.bill_address.city,
                                                                              self.bill_address.province,
                                                                              self.package_address.street,
                                                                              self.package_address.city,
                                                                              self.package_address.province
                                                                              )


class Address(Basic):
    __tablename__ = 'address'
    id = Column(Integer,primary_key=True)
    street = Column(String(64), nullable=False)
    city = Column(String(64), nullable=False)
    province = Column(String(64), nullable=False)

    def __repr__(self):
        return 'Province:[%s] City:[%s] Street:[%s]'%(self.province, self.city, self.street)

# Basic.metadata.create_all(engine)
session_class = sessionmaker()
session = session_class(bind=engine)


# a1 = Address(street='no1000',city='hangzhou',province='zhejiang')
# a2 = Address(street='no1001',city='wenzhou',province='zhejiang')
# a3 = Address(street='no1002',city='Beijing',province='Beijing')
# session.add_all([a1, a2, a3,])
# c1 = Customer(name='downtiser',bill_address=a1, package_address=a2)
# c2 = Customer(name='downtiseme',bill_address=a2, package_address=a3)
# c3 = Customer(name='noob',bill_address=a3, package_address=a1)
# session.add_all([c1,c2,c3])
# session.commit()
c = session.query(Customer).filter(Customer.name=='downtiser').first()
print(c)

建立多对多外键：

#Downtiser
from sqlalchemy import create_engine,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Integer, String, Date, CHAR, Column, ForeignKey #导入外键类
from  sqlalchemy.orm import sessionmaker, relationship #导入建立外键关系类
engine = create_engine('mysql+mysqlconnector://root:gu996080@localhost/test2', encoding='utf-8')
Basic = declarative_base()

book_2_author = Table('books_2_authors',Basic.metadata,
            Column('author_id',Integer,ForeignKey('authors.id')),
            Column('book_id', Integer, ForeignKey('books.id'))
)  #建立关联两个表的复合外键表

class Author(Basic):
    __tablename__ = 'authors'
    id = Column(Integer,primary_key=True)
    name = Column(String(32), nullable=False)
    books = relationship('Book', secondary=book_2_author, backref='authors') #secondary指定多对多外键中转表

    def __repr__(self):
        return 'name:[%s]'%(self.name)

class Book(Basic):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    pub_date = Column(Date, nullable=False)
    name = Column(String(32), nullable=False)

    def __repr__(self):
        return 'book_name:[%s] pub_date:[%s]'%(self.name, self.pub_date)

#Basic.metadata.create_all(engine)
# a1 = Author(name='downtiser')
# a2 = Author(name='noob')
# a3 = Author(name='idiot')
#
# b1 = Book(pub_date = '2018-8-13', name='the_book1')
# b2 = Book(pub_date = '2018-8-15', name='the_book2')
# b3 = Book(pub_date = '2018-7-15', name='the_book3')
#
# a1.books=[b1,b2,b3]  #对a1建立多个外键
# a2.books=[b1,b3]
# a3.books=[b2,b3]
#
session_class = sessionmaker()
session = session_class(bind=engine)
#session.add_all([a1,a2,a3,b1,b2,b3])

author_obj = session.query(Author).filter(Author.name=='downtiser').first()
print(author_obj.name,author_obj.books)
book_obj = session.query(Book).filter(Book.name=='the_book2').first()
print(book_obj.name,book_obj.authors)
#book_obj.authors.remove(author_obj) 可以直接移除关联数据而不必对中间表作修改，ORM可以自动对中间表做修改
#author_obj.books.append(book_obj)
#author_obj.books.remove(book_obj)

# session.delete(author_obj) 也可以直接删除数据
#session.commit()

当进行操作a1.books=[b1,b2,b3]时，一开始建立的多对多外键表会自动添加三行数据1 1,1 2,1 3,其中前一个值是与之建立外键的author_id,后一个值是与之建立外键的book_id,每次在authors表中通过关系实例books查询指定作者名下的书籍时，实际上是到多对多外键表中匹配author_id为指定作者id的几行数据，并返回每行数据的book_id，然后到books表中匹配对应id的数据，当通过查询得到的Author实例调用其下的books关系实例时，就会将前面在books表中匹配到的数据实例返回，在查询得到的Book实例中也可以通过调用在Author中建立外键关系时backref指定的关系实例auhors来返回authors表中的多个数据.其实是用两个一对多外键实现了建立多对多外键关系。

当使用原生sql语句对多对多外键关联表进行删改时，要先到中间表中移除有关数据，才能到原表中移除数据，很不方便，而通过QRM,对中间表的修改将交由ORM来处理，极大提高了操作数据库的便利.

如果要让ORM支持建立中文数据，需要在建立链接引擎时在写配置参数时在末尾加上?charset=utf8: engine = create_engine('mysql+mysqlconnector://root:gu996080@localhost/test2?charset=utf8', encoding='utf-8')