Python学习日记--Flask的数据库操作(一)

知识点

1. SQLAlchemy连接数据库
2. ORM-增删改查
3. 常用的数据类型

涉及到的库

1. SQLAlchemy
2. pymysql
3. mysql-connector

• 2、3选其一，官方推荐使用3

SQLAlchemy连接数据库

from sqlalchemy import create_engine  #创建引擎


# 数据库配置
HOSTNAME = "127.0.0.1"  # 地址
PORT = "3306"  # 端口
DATABASE = "demo0417"  # 数据库
USERNAME = "root"  # 账号
PASSWORD = "root"  # 密码

# 创建数据库引擎
# dialect+driver://username:password@host:port/database?charset=utf8
# DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)  # 使用pymysql这个方法会有警告,官方不推荐
DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)  # 官方推荐使用mysqlconnector这种方式
engine = create_engine(DB_URL)

# 创建连接
with engine.connect() as conn:
    # 执行原生sql语句
    result = conn.execute("select * from student")
    print(result.fetchone())  # 查询第一条数据

• 如果使用mysql+pymysql，在运行的时候会警告（warning）提示，因此官方推荐使用mysql+mysqlconnector

SQLAlchemy–ORM

ORM介绍

随着项目越来越大,采用原生SQL的方式在代码中会出现大量的SQL语句,对项目的进展非常不利

• SQL语句重复利用率不高,越复杂的SQL语句条件越多,代码越长。会出现很多相近似的SQL语句
• 很多SQL语句是在业务逻辑中拼出来的,如果有数据库需要更改,就要去修改这些逻辑,很容易漏掉某些SQL语句的修改
• 写SQL时容易忽略web安全问题

ORM: Object Relationship Mapping,对象关系映射,通过ORM我们可以通过类的方式去操作数据库,而不用写原生的SQL语句。通过把表映射成类,把行作为实例,把字段作为属性,ORM在执行对象操作时候最终还是会把对应的操作转换为数据库原生语句
使用ORM的优点

• 易用性：使用ORM做数据库的开发可以有效的减少SQL语句,写出来的模型也更加直观
• 性能损耗小
• 设计灵活：可以轻松写出来复杂的查询
• 可移植性：SQLAlchemy封装了底层的数据库实现,支持多个关系型数据库,包括MySQL,SQLite

ORM创建数据库表

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base  # ORM操作数据库需要继承的基类
from sqlalchemy import Column, Integer, String  # 创建表用到的类

# 数据库配置
HOSTNAME = "127.0.0.1"  # 地址
PORT = "3306"  # 端口
DATABASE = "demo0417"  # 数据库
USERNAME = "root"  # 账号
PASSWORD = "root"  # 密码

# 创建数据库引擎
# dialect+driver://username:password@host:port/database?charset=utf8
DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)  # 官方推荐使用mysqlconnector这种方式
engine = create_engine(DB_URL)

# 要通过ORM操作数据库,都要要继承declarative_base函数生成的基类
Base = declarative_base(engine)

# ORM操作数据库
class Students(Base):
    __tablename__ = "students"  # 定义表的名字

    # 定义表字段
    # Coiumn 数据类型  数据约束
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    gender = Column(Integer, default=1, comment="1.男, 2.女")


# 数据库模型映射到数据库中
Base.metadata.create_all()

• Column类用于创建表字段
• Integer类对应数据库的int数据类型
• String类对应数据库的varchar数据类型

增删改查

首先先创建一个表

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer
from sqlalchemy.orm import sessionmaker

# 数据库配置
HOSTNAME = "localhost"
DATANAME = "demo0417"
PORT = 3306
USERNAME = "root"
PASSOWRD = "root"

DB_URL = "mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME, PASSOWRD, HOSTNAME, PORT, DATANAME)
engin = create_engine(DB_URL)

Base = declarative_base(engin)


class Art(Base):
    __tablename__ = "art"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(String(50))
    author = Column(String(50))


Base.metadata.create_all()
# 实例化sessionmaker类
Session = sessionmaker(bind=engin)  # 实例化sessionmaker
session = Session()  # 调用Session.__call__方法,相当于将类当做函数调用

• 创建完表之后可以把Base.metadata.create_all()注释了，因为只能用在创建表，不能用它修改表字段。
• 实例化sessionmaker类并调用其__call__方法, 后面的添加和提交需要用到它

增

def add_data():
    """增"""
    article = Art(title="Python", content="人生苦短", author="龟叔")
    session.add(article)  # 添加单条数据
    # session.add_all([art1, atr2]) # 添加多条数据, 需要传入一个列表
    session.commit()
    
if __name__ == '__main__':
    add_data()

查

def search_data():
    data = session.query(Art).all()  # 查询所有数据, 返回一个对象列表
    # print(data)
    for item in data:
        print(item.title)  # 打印title
        print(item.content)  # 打印content

由于返回是对象因此可以用item.XXX的方式，将id、title、content等打印出来

并且由于打印的是Art对象，因此可以在Art类中定义__str__()方法，将这些数据返回出去

class Art(Base):
    __tablename__ = "art"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(String(50))
    author = Column(String(50))

    def __str__(self):
        return "Art(id:{}, title:{}, content:{}, author:{})".format(self.id, self.title, self.content, self.author)

修改查询的函数

def search_data():
    data = session.query(Art).all()  # 查询所有数据, 返回一个列表
    # print(data)
    for item in data:
        print(item)

此时的输出结果

其余的查询方式

def search_data():
    # data = session.query(Art).all()  # 查询所有数据, 返回一个列表
    # # print(data)
    # for item in data:
    #     print(item)

    # 带条件查询, filter条件
    # data = session.query(Art).filter(Art.title == "python").all()
    # for item in data:
    #     print(item)

    # 还可以用filter_by, 与上面的区别是filter后面不再需要写模型类并且只需要一个"="
    # data = session.query(Art).filter_by(title="Java").all()
    # for item in data:
    #     print(item)

    # 查询第一条数据
    # data = session.query(Art).filter_by(title="Python").first()
    # print(data)

    # 查询第N条数据
    data = session.query(Art).get(2)
    print(data)

if __name__ == '__main__':
    search_data()

改

def updata_data():
    """要查询出数据才能修改"""
    art = session.query(Art).first()  # 查询出第一条数据
    art.title = "Python3.6"  # 修改title
    session.commit()  # 提交

if __name__ == '__main__':
    updata_data()

删

def del_data():
    """要查询出数据才能删除"""
    art = session.query(Art).first()  # 查询出第一条数据
    session.delete(art)  # 删除数据
    session.commit()  # 提交

if __name__ == '__main__':
    del_data()

回滚

只有在commit之前才能回滚

session.rollback()

常用的数据类型

字段类型

字段	描述
Integer	整型
String	字符类型，等同于varchar
Boolean	布尔值
DECIMAL	定点类型，要传入两个参数DECIMAL（20， 5）；第一个参数代表总共多少位，第二个参数代表保留多少个小数
Float	浮点类型
enum	枚举类型
Date	日期，传递datetime.date()进去
DateTime	日期+时间，传递datetime.datetime()进去
Time	时间，传递datetime.time()进去
Text	文本类型
LONGTEXT	长文本类型

• DECIMAL定点类型的精度要比Float浮点类型的精度高，因此如果要存储金额等高精度的数据，推荐使用DECIMAL定点类型

约束

约束	描述
primary_key	主键
autoincrement	自增
nullable	非空，False=不能为空，True=允许为空（默认是True）
default	默认值
unique	是否唯一
onupdate	数据更新的时候执行的函数，例如文章内容修改了，会触发onupdate更新最后修改时间
name	该属性在数据库中的字段映射，例如，一般情况下模型类创建了id字段，那么在数据库中的字段也叫id，但如果加上name=uid，那么数据库中就会变成uid

query的常用参数

聚合函数

使用聚合函数需要先导入func

from sqlalchemy import func  # 聚合函数

result1 = session.query(func.count(Art.id)).first()  # 求总行数
print(result1)

result2 = session.query(func.avg(Art.id)).first()  # 求平均值
print(result2)

result3 = session.query(func.max(Art.id)).first()  # 求最大值
print(result3)

result4 = session.query(func.min(Art.id)).first()  # 求最大值
print(result4)

result5 = session.query(func.sum(Art.id)).first()  # 求和
print(result5)