4、SQLAlchemy基础使用（sqlalchemy详解）

sqlalchemy

ORM模型

sqlalchemy连接数据库

连接语法

自定义ORM模型

增加数据

查询数据

修改和删除数据

Column常用参数

SQLAlchemy常用数据类型

sqlalchemy

数据库是一个网站的基础，在“Flask”中可以自由使用"MySQL"、“PostgreSQL”、“Redis”、“Mongodb”来写原生的语句实现功能，也可以使用更高级别的数据库抽象方式，如"SQLAlchemy"或“MongoEngine”这杨的“ORM”；

ORM(Object Relationship Mepping):即Object-Relationl Mapping，它的作用是在关系型数据库和对象之间作一个映射，这样，我们在具体的操作数据库的时候，就不需要再去和复杂的SQL语句打交道，只要像平时操作对象一样操作它就可以了 。

官网：
https://docs.sqlalchemy.org

ORM模型

ORM，全称Object Relational Mapping，中文叫做对象关系映射，通过ORM我们可以通过类的方式去操作数据库，而不用再写原生的SQL语句。通过把表映射成类，把行作实例，把字段做属性，ORM在执行对象操作的时候最终还是会把对应的操作转换为数据库的原生语句。使用ORM有许多优点：

随着项目越来越大，采用写原生sql的方式在代码中会出现大量的SQL语句，那么问题就出现了。

1、SQL语句重复利用率不高， 越复杂的SQL语句条件越多，代码越长，会出现很多相近的SQL语句；

2、很多SQL语句是在业务逻辑中拼出来的，如果有数据库需要更改，就要去修改这些逻辑，这会很容易漏掉对某些SQL语句的修改；

3、写SQL时容易忽略web安全问题，给未来造成隐患。SQL注入；

优点：

易用性：使用ORM做数据库的开发可以有效的减少重复SQL语句的概率，写出来的模型也更加直观、清晰。

性能损耗小：ORM转换成底层数据库操作指令确实会有一些开销。但从实际的情况来看，这种性能损耗很小，只要不是对性能有严苛的要求，综合考虑开发效率，代码的阅读性，带来的好处要远远大于性能损耗，而项目越大作用越明显。

设计灵活：可以轻松的写出复杂的查询。

可移植性：Django封装了底层的数据库实现，支持多个关系数据库引擎。可以非常轻松的切换数据库。

sqlalchemy连接数据库

通过sqlalchemy连接数据库并执行原生SQL

连接语法

mysqldb的连接:mysql+mysqldb://<username>:<password>@<host>[:<port>]/<dbname>

pymysql的连接:mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>,echo=True # 是否打印执行的语句

# dialect+driver://username:password@host:port/database

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False) # 拿到一个连接对象

conn = engine.connect() # 创建一个连接

result = conn.execute('select 1') # 写原生SQL

print(result.fetchone())

conn.close()

自定义ORM模型

自定义ORM模型，创建表，首先使用declarative_base根据engine来创建一个ORM基类，然后使用这个基类来定制自己的ORM类，并且创建类属性来映射到表中的字段，所有需要映射到表中的字段都应该为Column类型；

__tablename__：指定表名

from sqlalchemy import create_engine

from
sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String # 每一个字段都需要用到Column来定义，并且指定类型

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base()

# 创建ORM模型

class User(Base):

__tablename__ = 'users' # 表名

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(50),comment='姓名')

age = Column(Integer,comment='年龄')

# 对应

# CREATE TABLE `users` (

# `id` int(11) NOT NULL AUTO_INCREMENT,

# `name` varchar(50) DEFAULT NULL COMMENT '姓名',

# `age` int(11) DEFAULT NULL COMMENT '年龄',

# PRIMARY KEY (`id`)

#) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#

# 映射到数据库

Base.metadata.create_all(engine) # 创建基类，传入一个bind对象，这个bind对象可以绑定数据库，会得到一个类，这个类可以创建ORM的能力

增加数据

现在准备开始与数据库交谈，需要使用一个引擎的实例来创建一个 Session类的实例。

from sqlalchemy import create_engine

from
sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String # 每一个字段都需要用到Column来定义，并且指定类型

from sqlalchemy.orm import sessionmaker # 导入一个session模块

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base()

class User(Base):

__tablename__ = 'users' # 表名

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(50), comment='姓名')

age = Column(Integer, comment='年龄')

Base.metadata.create_all(engine)

Session = sessionmaker(engine) # 基于engine引擎创建一个session，# 把当前的引擎绑定给这个会话

session = Session() # 拿到一个session对象

# 添加一条数据

u1 = User(name='cce', age=18)

session.add(u1)

# 添加多条数据

lst = [User(name='cce', age=18),

User(name='cce', age=18),

User(name='cce', age=18)]

session.add_all(lst)

session.commit()

# session.rollback() # 回滚

查询数据

from sqlalchemy import create_engine

from
sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String # 每一个字段都需要用到Column来定义，并且指定类型

from sqlalchemy.orm import sessionmaker # 导入一个session模块

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base()

class User(Base):

__tablename__ = 'users' # 表名

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(50), comment='姓名')

age = Column(Integer, comment='年龄')

Base.metadata.create_all(engine)

Session = sessionmaker(engine) # 基于engine引擎创建一个session

session = Session() # 拿到一个session对象

data=session.query(User).all()

for i in data:

print(i.id)

print(i.name)

print(i.age)

break

修改和删除数据

from sqlalchemy import create_engine

from
sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String # 每一个字段都需要用到Column来定义，并且指定类型

from sqlalchemy.orm import sessionmaker # 导入一个session模块

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base()

class User(Base):

__tablename__ = 'users' # 表名

id = Column(Integer, primary_key=True, autoincrement=True)

name = Column(String(50), comment='姓名')

age = Column(Integer, comment='年龄')

Base.metadata.create_all(engine)

Session = sessionmaker(engine) # 基于engine引擎创建一个session

session = Session() # 拿到一个session对象

# 查询出一条数据

data=session.query(User).filter_by(name='cce').first()

# 修改查询出来的数据

data.name='caichangen'

# 删除数据

session.delete(data)

# 提交

session.commit()

Column常用参数

default：设置某个字段的默认值。在发表时间这些字段上面经常用;

nullable：指定某个字段是否为空。默认值是True，就是可以为空；

primary_key：设置某个字段为主键；

unique：是否唯一；

autoincrement：设置这个字段为自动增长的；

name：指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字；

onupdate：在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是`update_time`（每次更新数据的时候都要更新的值）

SQLAlchemy常用数据类型

Integer：整形，映射到数据库中是int类型。

Float：浮点类型，映射到数据库中是float类型。它占据的32位。

Double：双精度浮点类型，映射到数据库中是double类型，占据64位。

String：可变字符类型，映射到数据库中是varchar类型。

Boolean：布尔类型，映射到数据库中的是tinyint类型。

DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位。

Enum：枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举。

Date：存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用`datetime.date`来指定。

DateTime：存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用`datetime.datetime`来指定。

Time：存储时间，可以存储时分秒。映射到数据库中也是time类型。在Python代码中，可以使用`datetime.time`来指定。

Text：存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型。

LONGTEXT：长文本类型，映射到数据库中是longtext类型。

// 简单示例

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True, autoincrement=True)

price = Column(Float)

is_active = Column(Boolean)

sex = Column(Enum('男', '女'), default='男')

date = Column(Date())

datetime = Column(DateTime())