SQLAlchemy

1、简介

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

说明：

SQLAchemy 本身无法操作数据库，其本质上是依赖pymysql，MySQLdb，mssql等第三方插件。

Dialect用于和数据库API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作。

2、安装

pip3 install SQLAlchemy

3、连接

from sqlalchemy import create_engine
# MySQL-Python
create_engine('mysql+mysqldb://root:123456@192.168.100.101:3306/sqlalchemy')

# pymysql
engine =  create_engine('mysql+pymysql://root:123456@192.168.100.101/sqlalchemy[?<options>]')

# MySQL-Connector
engine =  create_engine('mysql+mysqlconnector://root:123456@192.168.100.101:3306/sqlalchemy')

# cx_Oracle
engine =  create_engine('oracle+cx_oracle://root:123456@192.168.100.101:3306/sqlalchemy[?key=value&key=value...')

• create_engine()返回一个Engine的实例，并且它表示通过数据库语法处理细节的核心接口，在这种情况下，数据库语法将会被解释成python的类方法。
• mysql+xxx: 指定是哪种数据库连接
• 第一个root： 用户名
• 123456： root用户对应的密码
• 192.168.100.101： 数据库的ip
• 3306: 数据端口
• sqlalchemy： 数据库需要连接库的名字

其中，mysql+pymysql方式中不需要指定数据库端口号，options 中可指定如下内容：

• max_overflow:最大连接数
• charset: 设定连接时，使用的字符集charset = utf8
• echo: 参数如果为 True，会显示每条执行的 SQL 语句，生产环境应该关闭

4、常用字段和数据类型

在 sqlalchemy.scherma 包里有数据库关系的描述，常用的有：

• Column: 字段
• Index: 索引
• Table: 表

数据类型在 sqlalchemy.types 包中，常用的有：

数据类型	Python 数据类型	说明
Integer	int	整型
String	str	字符串
Float	float	浮点型
DECIMAL	decimal.Decimal	定点型
Boolean	bool	布尔型
Date	datetime.date	日期
Datetime	datetime.datetime	日期和时间
Time	datetime.time	时间
Enum	str	枚举
Text	str	文本
LongText	str	长文本

5、常用操作

（1）连接数据库

#!/usr/bin/env python
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine

# mysql+pymysql 链接方式://用户名:密码@数据库链接地址:端口/链接的数据库名称
engine = create_engine('mysql+pymysql://root:123456@192.168.100.101/sqlalchemy?charset=urf8')

（2）创建表

通过 sql 语句创建表

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

sql = '''create table student(
    id int not null primary key,
    name varchar(50),
    age int,
    address varchar(100));
'''

engine = create_engine('mysql+pymysql://root:123456@192.168.100.101/sqlalchemy')
conn = engine.connect()
conn.execute(sql)
engine.connect() #表示获取到数据库连接。类似我们在MySQLdb中游标course的作用。

通过 ORM 方式创建表

# -*- coding: utf-8 -*-
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String

# 创建数据库连接
engine = create_engine('mysql+pymysql://root:123456@192.168.100.101/sqlalchemy')
# 获取元数据
metadata = MetaData(engine)

# 定义表
student = Table('student', metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50), ),
            Column('age', Integer),
            Column('address', String(10)),
)

# 创建数据表，如果数据表存在，则忽视
metadata.create_all(engine)

以上方式都可以创建数据库表

• MetaData类主要用于保存表结构，连接字符串等数据，是一个多表共享的对象
• metadata = MetaData(engine) 绑定一个数据源的metadata
• metadata.create_all(engine) 是来创建表，这个操作是安全的操作，会先判断表是否存在。

Column 类

构造函数

Column.__init__(self,  name,  type_,  *args,  **kwargs)

参数详解：

• name 列名
• type_ 类型，更多类型 sqlalchemy.types
• *args
  • Constraint（约束）
  • ForeignKey（外键）
  • ColumnDefault（默认）
  • Sequenceobjects（序列）定义
  • key 列名的别名，默认None
• **kwargs
  • primary_key 如果为True，则是主键
  • nullable 是否可为Null，默认是True
  • default 默认值，默认是None
  • index 是否是索引，默认是True
  • unique 是否唯一键，默认是False
  • onupdate 指定一个更新时候的值，这个操作是定义在SQLAlchemy中，不是在数据库里的，当更新一条数据时设置，大部分用于updateTime这类字段
  • autoincrement 设置为整型自动增长，只有没有默认值，并且是Integer类型，默认是True
  • quote 如果列明是关键字，则强制转义，默认False

Table 类

构造函数：

Table.__init__(self, name, metadata,*args, **kwargs)

• name 表名
• metadata 共享的元数据
• **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’

创建会话

说到数据库，就离不开Session。Session的主要目的是建立与数据库的会话，它维护你加载和关联的所有数据库对象。它是数据库查询（Query）的一个入口。
在Sqlalchemy中，数据库的查询操作是通过Query对象来实现的。而Session提供了创建Query对象的接口。
Query对象返回的结果是一组同一映射（Identity Map）对象组成的集合。事实上，集合中的一个对象，对应于数据库表中的一行（即一条记录）。所谓同一映射，是指每个对象有一个唯一的ID。如果两个对象（的引用）ID相同，则认为它们对应的是相同的对象。
要完成数据库查询，就需要建立与数据库的连接。这就需要用到Engine对象。一个Engine可能是关联一个Session对象，也可能关联一个数据库表。
当然Session最重要的功能还是实现原子操作。
ORM通过session与数据库建立连接进行通信，如下所示：

from sqlalchemy.orm import sessionmaker

DBSession = sessionmaker(bind=engine)
session = DBSession()

通过sessionmake方法创建一个Session工厂，然后在调用工厂的方法来实例化一个Session对象。

（3）添加数据

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


# 连接数据库，并创建会话
engine = create_engine('mysql+pymysql://fxq:123456@192.168.100.101/sqlalchemy')
# 创建会话
DBsession = sessionmaker(bind=engine)
session = DBsession()
# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.create_all(engine)
# 声明SQLORM基类
Base = declarative_base()

# 构建数据库表的模型类
class Student(Base):
    # 指定表名
    __tablename__ = 'student'
    # 指定表结构
    id = Column(Integer, primary_key=True)  #主键 autoincrement=True 自增
    name = Column(String(100))
    age = Column(Integer)
    address = Column(String(100))
    
    # 这个是可选的方法，这里自定义在显示Strudent对象时的格式
    def __repr__(self):
        return "<User(id='%s', name='%s', age='%s, address='%s')>" % (
                             self.id, self.name, self.age, self.address)

# 插入数据
student1 = Student(id=1001, name='ling', age=25, address="beijing")
student2 = Student(id=1002, name='molin', age=18, address="jiangxi")
student3 = Student(id=1003, name='karl', age=16, address="suzhou"