SQLAlchemy增删改查、使用原生SQL以及使用事务

小果冻啦

已于 2024-07-29 10:56:13 修改

阅读量202

点赞数 7

文章标签：数据库 mysql python flask

于 2024-07-29 10:51:17 首次发布

本文链接：https://blog.csdn.net/2301_76868495/article/details/140763681

版权

数据表映射

如果库中没有此表则创建，有则变成映射关系

当然，如果存在已经创建好的表，只需要查询的操作，可是使用原生SQL进行查询

import datetime
from uuid import uuid4
from flask import Flask, jsonify
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, QueuePool, text
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, String, DateTime

class Test_sql(Base):
    __tablename__ = 'test_sql'  # 表名
    # 写字段
    uuid = Column(String(36), nullable=False, primary_key=True, default=str(uuid4()).replace('-', ''))
    name = Column(String(36), nullable=True, comment='姓名')
    age = Column(String(4), nullable=True, comment='年龄')
    sex = Column(String(4), comment='性别')  # String(32) == varchar(32)
    hobby = Column(String(64), comment='爱好')
    updated_time = Column(DateTime, default=datetime.datetime.now, nullable=True, comment='更新时间')

数据库连接，并使用连接池

engine = create_engine('mysql+pymysql://root:wq123456@127.0.0.1:3306/gf_test?charset=utf8mb4',
                       pool_size=10,  # 连接数10
                       pool_timeout=30,  # 超时30s
                       max_overflow=0,  # 连接池溢出时最大连接数
                       pool_pre_ping=True,  # 检测连接状态 如果有错误，监测为断开的状态，连接将被立即回收
                       pool_recycle=3600,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
                       # echo=True,  # 打印日志
                       poolclass=QueuePool)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

数据增加 - insert

session = Session()

# 添加
handsome = Test_sql(name='张三', age='18', sex='男', hobby='打球')
session.add(handsome)
session.commit() # 提交数据写入

# 多对象数据插入 注意：主键需要生成，否则会报错 - 一个主键多用
handsome = Test_sql(uuid=str(uuid4()).replace('-', ''), name='王五', age='25', sex='男', hobby='打球')
handsome1 = Test_sql(uuid=str(uuid4()).replace('-', ''), name='大能', age='50', sex='男', hobby='爱看美女')
session.add_all([handsome, handsome1])
session.commit()  # 提交数据写入

session.close()

数据修改 - update

session = Session()

session.query(Test_sql).filter_by(name='张三').update({'age': '20', 'sex': '男', 'hobby': '看美女'})

session.commit()

数据查询 - select

session = Session()

# first() 单条查询
res = session.query(Test_sql).filter_by(name='张三').first()
print(res)  # Query对象: <__main__.Test_sql object at 0x105b0f580>
print(res.hobby)  # 使用点语法取值 - hobby字段

# all() 返回全部数据
res1 = session.query(Test_sql).filter_by(sex='男').all()
print(res1)  # 列表套Query对象 [<__main__.Test_sql object at 0x110370d30>, <__main__.Test_sql object at 0x1103715a0>]
for i in res1:
    print(i.name)  # 循环对象使用点语法取name字段的值

session.close()

数据删除 - delete

session = Session()

# 方式一
session.query(Test_sql).filter_by(name='张三').delete()

# 方式二
del1 = session.query(Test_sql).filter_by(name='张三').delete()
del2 = session.query(Test_sql).filter_by(name='李四').delete()
session.delete(del1)  # 只能删除一个对象，不能用列表的形式删除多个对象
session.delete(del2)
session.commit()  # 删除操作需要提交

session.close()

使用原生SQL查询

session = Session()
# 原生SQL查询
sql = text('select name,age,hobby from test_sql ')
result = session.execute(sql).fetchall()
print(result)  # 列表套元组的形式，一个元组对应一行数据 [('张三', '18', '打球'), ('李四', '20', '打球')]

# 传入参数
sql = text('select name,age,hobby from test_sql where name=:name and sex=:sex')
result = session.execute(sql, {'name': '张三', 'sex': '男'}).fetchall()
print(result)  # 列表套元组，一个元组对应一行数据 [('张三', '18', '打球')]
session.close()

使用事务 - 原子性

session = Session()
try:
   session.begin()  # 开启事务

   handsome = Test_sql(uuid=str(uuid4()).replace('-', ''), name='王五', age='25', sex='男', hobby='打球',a='cc')
   handsome1 = Test_sql(uuid=str(uuid4()).replace('-', ''), name='大能', age='50', sex='男', hobby='爱看美女')
   session.add_all([handsome, handsome1])

   session.commit()
except exc.SQLAlchemyError as e:
   session.rollback()  # 事务回滚，恢复数据
   app.logger.error(f'添加失败：{e}', exc_info=False)  # 控制是否将异常信息（堆栈跟踪）作为日志消息的一部分输出，False不输出
except Exception as e:
   session.rollback()
   app.logger.error(f'添加失败：{e}', exc_info=False)
finally:
   session.close()  # 无论成功与否，都要关闭session

增加失败后，日志输出样式

完整代码：

import datetime
from uuid import uuid4
from flask import Flask, jsonify
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, QueuePool, text, exc
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, String, DateTime

app = Flask(__name__)
app.debug = True  # 调试使用，并默认使用日志输出，完全控制自定义日志记录，生产下删掉

Base = declarative_base()


class Test_sql(Base):
    __tablename__ = 'test_sql'  # 表名
    # 写字段
    uuid = Column(String(36), nullable=False, primary_key=True, default=str(uuid4()).replace('-', ''))
    name = Column(String(36), nullable=True, comment='姓名')
    age = Column(String(4), nullable=True, comment='年龄')
    sex = Column(String(4), comment='性别')  # String(32) == varchar(32)
    hobby = Column(String(64), comment='爱好')
    updated_time = Column(DateTime, default=datetime.datetime.now, nullable=True, comment='更新时间')


engine = create_engine('mysql+pymysql://root:wq123456@127.0.0.1:3306/gf_test?charset=utf8mb4',
                       pool_size=10,  # 连接数10
                       pool_timeout=30,  # 超时30s
                       max_overflow=0,  # 连接池溢出时最大连接数
                       pool_pre_ping=True,  # 检测连接状态 如果有错误，监测为断开的状态，连接将被立即回收
                       pool_recycle=3600,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
                       # echo=True,  # 打印日志
                       poolclass=QueuePool)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)


@app.route('/', methods=['GET', ])
def index():
    session = Session()
    # 添加 - insert
    # handsome = Test_sql(name='张三', age='18', sex='男', hobby='打球')
    # session.add(handsome)

    # 多对象数据插入 注意：主键需要生成，否则会报错
    # handsome = Test_sql(uuid=str(uuid4()).replace('-', ''), name='王五', age='25', sex='男', hobby='打球')
    # handsome1 = Test_sql(uuid=str(uuid4()).replace('-', ''), name='大能', age='50', sex='男', hobby='爱看美女')
    # session.add_all([handsome, handsome1])
    # session.commit()  # 提交数据写入

    # 修改 - update
    # session.query(Test_sql).filter_by(name='张三').update({'age': '20', 'sex': '男', 'hobby': '看美女'})
    # session.commit()

    # 查询 - select
    # res = session.query(Test_sql).filter_by(name='张三').first()
    # print(res)  # Query对象: <__main__.Test_sql object at 0x105b0f580>
    # print(res.hobby)  # 使用点语法取值 - hobby字段

    # res1 = session.query(Test_sql).filter_by(sex='男').all()
    # print(res1)  # 列表套Query对象 [<__main__.Test_sql object at 0x110370d30>, <__main__.Test_sql object at 0x1103715a0>]
    # for i in res1:
    #     print(i.name)  # 循环对象使用点语法取name字段的值

    # 删除 - delete
    # 方式一
    # session.query(Test_sql).filter_by(name='张三').delete()

    # 方式二
    # del1 = session.query(Test_sql).filter_by(name='张三').delete()
    # del2 = session.query(Test_sql).filter_by(name='李四').delete()
    # session.delete(del1)  # 只能删除一个对象，不能用列表的形式删除多个对象
    # session.delete(del2)
    # session.commit()

    # 原生SQL查询
    # sql = text('select name,age,hobby from test_sql ')
    # result = session.execute(sql).fetchall()
    # print(result)  # 列表套元组的形式，一个元组对应一行数据 [('张三', '18', '打球'), ('李四', '20', '打球')]

    # 传入参数
    # sql = text('select name,age,hobby from test_sql where name=:name and sex=:sex')
    # result = session.execute(sql, {'name': '张三', 'sex': '男'}).fetchall()
    # print(result)  # 列表套元组，一个元组对应一行数据 [('张三', '18', '打球')]

    # 使用事务 - 原子性
    try:
        session.begin()  # 开启事务

        handsome = Test_sql(uuid=str(uuid4()).replace('-', ''), name='王五', age='25', sex='男', hobby='打球',a='cc')
        handsome1 = Test_sql(uuid=str(uuid4()).replace('-', ''), name='大能', age='50', sex='男', hobby='爱看美女')
        session.add_all([handsome, handsome1])

        session.commit()
    except exc.SQLAlchemyError as e:
        session.rollback()  # 事务回滚，恢复数据
        app.logger.error(f'添加失败：{e}', exc_info=False)  # 控制是否将异常信息（堆栈跟踪）作为日志消息的一部分输出，False不输出
    except Exception as e:
        session.rollback()
        app.logger.error(f'添加失败：{e}', exc_info=False)
    finally:
        session.close()  # 无论成功与否，都要关闭session
    return jsonify({'code': 200, 'msg': 'ok'})


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8080)