一、SQLAlchemy
1.1.ORM技术简介
所谓ORM(Object Relational Mapping),就是建立其由Python类到数据库表的映射关系:一个Python实例(instance)对应数据库中的一行(row)。这种映射包含两层含义,一是实现对象和与之关联的的行的状态同步,二是将涉及数据库的查询操作,表达为Python类的相互关系。数据库表结构
student:id,name,age,sex,address
定义一个模型类module,将其与数据库字段一一对应
示例
from sqlalchemy import create_engine
#创建数据库连接引擎,其中+pymysql是指将使用python3连接,如果是python2的话,可以不用指明。
#mysql://登录名:登录密码@IP/数据库名
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy')
print(engine)
#定义一个模型student与数据库表student字段一一对应
class Student():
def __init__(self,id,name,age,sex,address):
self.id=id
self.name=name
self.age=age
self.sex=sex
self.address=address
#通过moudel实例化数据表student中一行
student1=Student(1001,'liming',19,'F','zhenjiang')
student2=Student(1002,'qionghua',26,'G','zhenjiang')
#直接通过实例化,调用某个属性
print(student1.name)
1.2.ORM思想
数据库中每次查出来的数据都用一个类表示,这个类的属性和数据库中表的字段一一对应。多条数据,就是一个list,每一行数据都是一个类来表示,如下所示:class user(object):
def __init__(self,id,name):
self.id=id
self.name=name
User(1, “ling”),
User(2, “shang”),
User(3, “huo”),
]
当我们需要获得id,或者name的时候,只需要通过循环获取到对象,直接通过user1.id或者user1.name就可以获取到id和name的属性。并且使得数据的存取非常的规范,这样ORM架构应用而生。
1.3.SQLAlchemy
Python中最有名的ORM架构就是SQLAlchemy1.3.1.环境安装
pip install SQLAlchemy1.3.2.创建用户并授权
grant all privileges on *.* to 'admin'@'%' identified by '123456' with grant option;1.3.3.初始化连接
from sqlalchemy import create_engineengine = create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy',echo=True)
conn=engine.connect()
create_engine()返回一个Engine的实例,并且它表示通过数据库语法处理细节的核心接口,在这种情况下,数据库语法将会被解释成python的类方法。
mysql+pymysql://root:123456@127.0.0.1/sqlalchemy
mysql: 指定是哪种数据库连接
pymysql:pymysql是指将使用python3连接器,默认的是python2的MysqlDb
root:数据库登录名
123456:数据库登录密码
127.0.0.1:数据库的IP
sqlalchemy:连接的数据库名
conn=engine.connect():创建一个数据库连接,类似于游标cursor创建数据库连接
1.3.4.使用元数据MetaData创建表
MetaData类主要用于保存表结构,连接字符串等数据,是一个多表共享的对象示例
创建user表
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('mysql+pymysql://admin:123456@127.0.0.1/sqlalchemy')
metadata = MetaData(engine) # 绑定元信息
user=Table("user",metadata,
Column('id',Integer,primary_key=True),
Column('name',String(50)),
Column('age',Integer)
)
metadata = MetaData(engine) 绑定一个数据源的metadata
metadata.create_all(engine) 是来创建表,这个操作是安全的操作,会先判断表是否存在。
Table类
构造函数:
Table.__init__(self, name, metadata,*args, **kwargs)
name 表名
metadata 共享的元数据
*args Column 是列定义
下面是可变参数 **kwargs 定义
schema 此表的结构名称,默认None
autoload 自动从现有表中读入表结构,默认False
autoload_with 从其他engine读取结构,默认None
include_columns 如果autoload设置为True,则此项数组中的列明将被引用,没有写的列明将被忽略,None表示所有都列明都引用,默认None
mustexist 如果为True,表示这个表必须在其他的python应用中定义,必须是metadata的一部分,默认False
useexisting 如果为True,表示这个表必须被其他应用定义过,将忽略结构定义,默认False
owner 表所有者,用于Orcal,默认None
quote 设置为True,如果表明是SQL关键字,将强制转义,默认False
quote_schema 设置为True,如果列明是SQL关键字,将强制转义,默认False
mysql_engine mysql专用,可以设置'InnoDB'或'MyISAM'
Column类
Column.__init__(self, name, type_, *args, **kwargs)
1、name 列名
2、type_ 类型,更多类型 sqlalchemy.types
3、*args Constraint(约束), ForeignKey(外键), ColumnDefault(默认), Sequenceobjects(序列)定义
4、key 列名的别名,默认None
下面是可变参数 **kwargs
5、primary_key 如果为True,则是主键
6、nullable 是否可为Null,默认是True
7、default 默认值,默认是None
8、index 是否是索引,默认是True
9、unique 是否唯一键,默认是False
10、onupdate 指定一个更新时候的值,这个操作是定义在SQLAlchemy中,不是在数据库里的,当更新一条数据时设置,大部分用于updateTime这类字段
11、autoincrement 设置为整型自动增长,只有没有默认值,并且是Integer类型,默认是True
12、quote 如果列明是关键字,则强制转义,默认False
1.2.5.创建会话sessionmaker(插入数据)
说到数据库,就离不开Session。Session的主要目的是建立与数据库的会话,它维护你加载和关联的所有数据库对象。它是数据库查询(Query)的一个入口。
在Sqlalchemy中,数据库的查询操作是通过Query对象来实现的。而Session提供了创建Query对象的接口。
Query对象返回的结果是一组同一映射(Identity Map)对象组成的集合。事实上,集合中的一个对象,对应于数据库表中的一行(即一条记录)。所谓同一映射,是指每个对象有一个唯一的ID。如果两个对象(的引用)ID相同,则认为它们对应的是相同的对象。
要完成数据库查询,就需要建立与数据库的连接。这就需要用到Engine对象。一个Engine可能是关联一个Session对象,也可能关联一个数据库表。
当然Session最重要的功能还是实现原子操作。
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy')
DBSession=sessionmaker(bind=engine)
session=DBSession()
通过sessionmake方法创建一个Session工厂,然后在调用工厂的方法来实例化一个Session对象。
示例
向student表添加数据
from sqlalchemy import create_engine, Column, Integer, String, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
#创建了一个 BaseModel 类,这个类的子类可以自动与一个表关联。
base=declarative_base()
#定义实例化类
class Student(base):
__tablename__="student"
id=Column(Integer,primary_key=True,autoincrement=True)
name=Column(String(100))
age=Column(Integer)
sex=Column(Enum('F','G'))
address=Column(String(100))
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy')
DBSession=sessionmaker(bind=engine)
session=DBSession()
New_Student1=Student(id=1001,name='test01',age=23,sex='F',address='taizhou')
New_Student2=Student(id=1002,name='test02',age=16,sex='F',address='changzhou')
New_Student3=Student(id=1003,name='test03',age=33,sex='F',address='suzhou')
session.add(New_Student1)
session.add_all([New_Student2,New_Student3])
session.commit()
session.close()
1.2.5.查询
通过Session的query()方法创建一个查询对象。这个函数的参数数量是可变的,参数可以是任何类或者是类的描述的集合。1.2.5.1.filter_by()过滤
from sqlalchemy import create_engine, Column, Integer, String, Enumfrom sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
baseModel=declarative_base()
class Student(baseModel):
__tablename__="student"
id=Column(Integer,primary_key=True)
name=Column(String(100))
age = Column(Integer)
sex = Column(Enum('F', 'G'))
address = Column(String(100))
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy')
DBSession=sessionmaker(bind=engine)
session=DBSession()
stdinfo=session.query(Student).filter_by(name="test01").first()
#直接打印结果属性得出结果
print(stdinfo.id,stdinfo.name,stdinfo.age,stdinfo.sex,stdinfo.address)
1.2.5.2.filter()过滤
from sqlalchemy import create_engine, Column, Integer, String, Enumfrom sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
baseModel=declarative_base()
class Student(baseModel):
__tablename__="student"
id=Column(Integer,primary_key=True)
name=Column(String(100))
age = Column(Integer)
sex = Column(Enum('F', 'G'))
address = Column(String(100))
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy')
DBSession=sessionmaker(bind=engine)
session=DBSession()
filterInfo=session.query(Student).filter(Student.name.like("test%"))
print(filterInfo)
1.2.5.3.返回列表(List)和单项(Scalar)
1.all() 返回一个列表from sqlalchemy import create_engine, Column, Integer, String, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
baseModel=declarative_base()
class Student(baseModel):
__tablename__="student"
id=Column(Integer,primary_key=True)
name=Column(String(100))
age = Column(Integer)
sex = Column(Enum('F', 'G'))
address = Column(String(100))
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy')
DBSession=sessionmaker(bind=engine)
session=DBSession()
filterInfo=session.query(Student).filter(Student.name.like("test%")).all()
print(filterInfo)
结果
[<__main__.Student object at 0x00000000034DEFD0>, <__main__.Student object at 0x00000000034DED68>, <__main__.Student object at 0x00000000034F80B8>, <__main__.Student object at 0x00000000034F8240>]
可以通过遍历列表来获取每个对象
from sqlalchemy import create_engine, Column, Integer, String, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
baseModel=declarative_base()
class Student(baseModel):
__tablename__="student"
id=Column(Integer,primary_key=True)
name=Column(String(100))
age = Column(Integer)
sex = Column(Enum('F', 'G'))
address = Column(String(100))
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1/sqlalchemy')
DBSession=sessionmaker(bind=engine)
session=DBSession()
filterInfo=session.query(Student).filter(Student.name.like("test%")).all()
for x in filterInfo:
print(x.id)
print(x.name)
print(x.age)
print(x.sex)
print(x.address)
2.one()
返回且仅返回一个查询结果。当结果的 数量不足一个或者多于一个时会报错
如果将上面的示例中的all()改成one(),执行结果如下:
3.first()
返回至多一个结果,而且以单项形式,而不是只有一个元素的tuple形式返回这个结果
如果将上面的示例中的all()改成first(),执行结果如下:
1.2.5.4.filter()和filter_by()的区别
filter: 可以像写 sql 的 where 条件那样写 > < 等条件,但引用列名时,需要通过 类名.属性名 的方式。filter_by: 可以使用 python 的正常参数传递方法传递条件,指定列名时,不需要额外指定类名。,参数名对应名类中的属性名,但似乎不能使用 > < 等条件。
当使用filter的时候条件之间是使用“==",fitler_by使用的是"="。
user1 = session.query(User).filter_by(id=1).first()
user1 = session.query(User).filter(User.id==1).first()
filter不支持组合查询,只能连续调用filter来变相实现。
而filter_by的参数是**kwargs,直接支持组合查询。