sqlalchemy ORM

        orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

        在Python中，最有名的ORM框架是SQLAlchemy。

 

一、安装

pip3 install SQLAlchemy -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com

 

二、sqlalchemy基本使用

创建表

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8',echo=True)

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

class User2(Base):
    __tablename__ = 'user2'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

#创建表结构,这里会创建继承Base基类所有子类的表，这里是User()和User2(),同时创建两张表
Base.metadata.create_all(engine)

 

写入数据

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
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

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8',echo=True)

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

#创建表结构,这里会创建继承Base基类所有子类的表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

user_obj = User(name='root',password="mysql") #生成要创建的数据对象
user_obj2 = User(name='root123',password="mysql") #再创建一个对象
print(user_obj.name,user_obj.id) #现在还没有创建对象，可以打印下看看id还是none

#session.add_all([s1,s2,s3,s4])  ##可以同时添加好几条数据

Session.add(user_obj)

Session.add(user_obj2) #把要创建的数据对象添加到Session中，多条记录会统一创建

print(user_obj.name,user_obj.id) #此时依然还没有创建

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

查看表这两条数据已经插入到数据库了：

 

查询

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
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

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

#创建表结构,这里会创建继承Base基类所有子类的表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

#像这样查出的数据是一组数据，后面不加all(),是无法print查出的结果的
#data = Session.query(User).filter_by(name='root')
data = Session.query(User).filter_by(name='root').all()
print(data)

注意，这里print出来的结果是两条记录，但是这两条记录是一个列表对象：

[<__main__.User object at 0x1036d66d8>, <__main__.User object at 0x1036d6748>] 

 修改最后print命令为 ： print(data[0].password)  就可以找出某条数据的用户密码。

可以使用__repr__()来格式化结果数据，易于读取。怎么做呢，如下：

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
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

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<%s name:%s password:%s>" %(self.id,self.name,self.password)

#创建表结构,这里会创建继承Base基类所有子类的表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

#像这样查出的数据是一组数据，后面不加all(),是无法print查出的结果的
#data = Session.query(User).filter_by(name='root')

data = Session.query(User).filter_by(name='root').all()
print(data)

这样结果就变成了：

[<1 name:root password:mysql>, <3 name:root password:123>]    

把：data = Session.query(User).filter_by(name='root').all()

改为：data = Session.query(User).filter(User.id>2).all()   即可使用条件查询

filter判断等于是==，filter_by判断等于是=    两者区别以后再看吧。

当然也支持多条件查询：data = Session.query(User).filter(User.id>1).filter(User.id<3).all()

 

如果我要实现例如在select * from table limit 1 这种效果呢，把all()改成first()：

data = Session.query(User).filter(User.id>1).filter(User.id<4).first()

这样查询结果只会显示1条记录

 

修改

(1)修改一条记录多个值

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
# -*- coding:utf-8 -*-
# Author:Brownyangyang
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

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<%s name:%s password:%s>" %(self.id,self.name,self.password)

#创建表结构,这里会创建继承Base基类所有子类的表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

#像这样查出的数据是一组数据，后面不加all(),是无法print查出的结果的
#data = Session.query(User).filter_by(name='root')

#
data = Session.query(User).filter(User.id>1).filter(User.id<4).first()
print(data)
data.name = "Jack"
data.password = "123456"
Session.commit()
print(data)

 

（2）批量修改多个记录

未完待续

 

回滚

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
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

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

#创建表结构,这里会创建继承Base基类所有子类的表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

user_obj = User(name='kangshifu',password="kangshifu") #生成要创建的数据对象
Session.add(user_obj)
print(Session.query(User).filter(User.name.in_(["Jack","kangshifu"])).all())

Session.add(user_obj)
Session.rollback() 
#搜索出Jack或者kangshifu名称的字段
print(Session.query(User).filter(User.name.in_(["Jack","kangshifu"])).all())

 

统计

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
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

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

#创建表结构,这里会创建继承Base基类所有子类的表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

user_obj = User(name='kangshifu',password="kangshifu") #生成要创建的数据对象
Session.add(user_obj)

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

#查询含有Jack或者kangshifu的条目
print(Session.query(User).filter(User.name.in_(["Jack","kangshifu"])).count())

结果是2，和数据库数据是匹配的：

 

分组

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
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
from sqlalchemy import func  ##分组用

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

#创建表结构,这里会创建继承Base基类所有子类的表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

print(Session.query(func.count(User.name),User.name).group_by(User.name).all())

结果（root两个记录，其他一个记录）：

[(1, 'Jack'), (1, 'kangshifu'), (1, 'liyang'), (2, 'root')]

 

删除

Session.query(User).filter_by(name='Jack').delete()

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

关联查询（join）

需要准备两张表，用下面两张表来做join吧。

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
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

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class User(Base):
    __tablename__ = 'user1'   #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

    def __repr__(self):
        return "<%s,name:%s,password:%s>"%(self.id,self.name,self.password)

class Student (Base):
    __tablename__ = 'student'  # 表名
    stu_id = Column (Integer, primary_key=True)
    name = Column (String (32))
    age = Column (String (64))
    register_date = Column(String(32))

    #根据自己的表结构来写
    def __repr__(self):
        return "<%s,name:%s,age:%s,register_date:%s>"%(self.stu_id,self.name,self.age,self.register_date)

#创建表结构,这里会创建继承Base基类所有子类的表，这里是User()和User2(),同时创建两张表
Base.metadata.create_all(engine)

#创建与数据库的会话session class，注意，这里返回给session的是个class，不是实例
Session_class = sessionmaker(bind=engine)

#生成实例，类似cursor
Session = Session_class()

data = Session.query(User,Student).filter(User.id==Student.stu_id).all()
print(data)

 

结果（把id和stu_id相等的数据都匹配出来了）：

[(<1,name:root,password:mysql>, <1,name:liyang,age:22,register_date:2018-03-01>), (<3,name:root,password:123>, <3,name:liyang3,age:42,register_date:2018-12-01>), (<4,name:liyang,password:liyang>, <4,name:xiaohong,age:20,register_date:2017-04-14>)]

如果两个表之间有外键关联，也可以用下面的写法去查：

data = Session.query(User).join(Student).all()

 

实现外键关联

（1）建表

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey  ##外键
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE
from sqlalchemy.orm import sessionmaker

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8',echo=True)

Base = declarative_base()   #生成orm基类

class Student(Base):
    __tablename__ = "student01"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_data = Column(DATE,nullable=False)

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

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("student01.id"),nullable=False)  ##创建外键

    def __repr__(self):
        return "<%s day:%s>" % (self.id,self.day)

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

Session_class = sessionmaker(bind=engine)
session = Session_class() #生成session实例 #cursor

s1 = Student(name="beibei",register_data="2016-01-22")
s2 = Student(name="huanhuan",register_data="2016-02-04")
s3 = Student(name="jingjing",register_data="2016-03-23")
s4 = Student(name="lele",register_data="2016-04-11")

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)

#下面两条我是分开来执行的，因为有外键关联，在新增study_record表的stu_id字段时，
# 因为student01.id新增了但还没有commit，所以新增stu_id字段会找不到外键，所以会报错，我这里是分开执行的，注意一下
session.add_all([s1,s2,s3,s4])
session.add_all([study_obj1,study_obj2,study_obj3,study_obj4])

session.commit()

（2）查询

# -*- coding:utf-8 -*- 
# Author:Brownyangyang
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey  ##外键
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DATE
from sqlalchemy.orm import sessionmaker,relationship

#建立连接，echo=True是打印创建过程，root是用户，mysql是密码，liyang是表空间
# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://root:mysql@10.211.55.15/liyang",encoding='utf-8')

Base = declarative_base()   #生成orm基类

class Student(Base):
    __tablename__ = "student01"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_data = Column(DATE,nullable=False)

    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

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("student01.id"),nullable=False)  ##创建外键

    # stud是自定义的字段,在StudyRecord中和Student如下建立联系后，我就可以通过my_study_record查 StudyRecord的数据
    # 可以通过stud查询Student表，这个stud字段是在内存中创建的，并不在表中创建
    stud = relationship("Student",backref="my_study_record")

    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.id,self.day,self.status)

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

Session_class = sessionmaker(bind=engine)
session = Session_class() #生成session实例 #cursor


std_obj= session.query(Student).filter(Student.name=="beibei").first()
print(std_obj)  ##查出student01表的id和姓名
print(std_obj.my_study_record)  #

结果：

<1 name:beibei>
[<5 day:1 status:YES>, <6 day:2 status:NO>, <7 day:3 status:YES>]

第一行结果是Student表的内容（你要多展示字段就调下__repr__）,第二行结果就是通过my_study_record查StudyRecord的数据

首先通过Student表查到人，再通过外键关联，因为一个人的id唯一，对应着StudyRecord的stu_id，进而可以查到这个人的上课记录。 

甚至，我可以把上面的结果也加到下面的结果中，只要改一个地方，下面红色字体部分

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("student01.id"),nullable=False)  ##创建外键

    # stud是自定义的字段,在StudyRecord中和Student如下建立联系后，我就可以通过my_study_record查 StudyRecord的数据
    # 可以通过stud查询Student表
    stud = relationship("Student",backref="my_study_record")

    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.stud.name,self.day,self.status)

这里我就是通过stud这个参数去访问了Student表。结果：

[<beibei day:1 status:YES>, <beibei day:2 status:NO>, <beibei day:3 status:YES>]