sqlalchemy 3 异常和事务

原创已于 2022-12-06 19:32:52 修改 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

于 2021-04-28 12:03:46 首次发布

sqlalchemy 专栏收录该内容

13 篇文章

订阅专栏

本文探讨如何在内存数据库中重新创建用户表，介绍如何避免AttributeError和IntegrityError，使用try/except处理异常，并运用事务确保数据一致性。通过实例演示了建表、数据插入和事务管理，以提升数据库操作的健壮性。

重新造一个数据库和表。c3_manage.py

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Numeric, String, ForeignKey, Boolean
from sqlalchemy import CheckConstraint
from datetime import datetime
from sqlalchemy import DateTime

engine = create_engine('sqlite:///:memory:')
connection = engine.connect()

metadata = MetaData()

cookies = Table('cookies', metadata,
    Column('cookie_id', Integer(), primary_key=True),
    Column('cookie_name', String(50), index=True),
    Column('cookie_recipe_url', String(225)),
    Column('cookie_sku', String(55)),
    Column('quantity', Integer()),
    Column('unit_cost', Numeric(12,2)),  #长度12、精度2位小数
    CheckConstraint('quantity > 0', name='quantity_positive')
)

users = Table('users', metadata,
    Column('user_id', Integer(), primary_key=True),
    Column('username', String(15), nullable=False, unique=True),
    Column('email_address', String(255), nullable=False),
    Column('phone', String(20), nullable=False),
    Column('password', String(25), nullable=False),
    Column('created_on', DateTime(), default=datetime.now),
    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now),
    )

orders = Table('orders', metadata,
    Column('order_id', Integer(), primary_key=True),
    Column('user_id', ForeignKey('users.user_id')),
    Column('shipped', Boolean(), default=False),
    )

line_items = Table('line_items', metadata,
    Column('line_items_id', Integer(), primary_key=True),
    Column('order_id', ForeignKey('orders.order_id')),
    Column('cookie_id', ForeignKey('cookies.cookie_id')),
    Column('quantity', Integer()),
    Column('extended_cost', Numeric(12,2)),
    )

metadata.create_all(engine)

常见异常 AttributeError 和 IntegrityError。

AttributeError：当试图访问一个不存在的属性时，就会出现AttributeError 。比如要访问的列在ResultProxy中不存在。

from sqlalchemy import select, insert
from c3_manage import users, connection
ins = insert(users).values(
    username='cookiemon',
    email_address='mon@cookie.com',
    phone='111-111-1111',
    password='password'
    )
result = connection.execute(ins)

s = select([users.c.username])
result = connection.execute(s)
for r in result:
    print(r.username)    #这一行的还是会执行的
    print(r.password)    #select返回resultproxy里没有password列

#报错
cookiemonTraceback (most recent call last):

  File "D:\alchemy\c3_error.py", line 15, in <module>
    print(r.password)
AttributeError: Could not locate column in row for column 'password'

IntegrityError：当试图做一些违反列约束或表约束的事情时，就会出现IntegrityError。比如破坏唯一性约束。

from sqlalchemy import select, insert
from c3_manage import users, connection
ins = insert(users).values(    #之所以再次insert是因为这个数据库建在内存Memory上，之前的insert已经消失掉了
    username='cookiemon',
    email_address='mon@cookie.com',
    phone='111-111-1111',
    password='password'
    )
result = connection.execute(ins)

s = select([users.c.username])
print(connection.execute(s).fetchall())

ins = insert(users).values(
    username='cookiemon',    #重名了
    email_address='mon@cookie.com',
    phone='111-111-1111',
    password='password'
    )
result = connection.execute(ins)

#报错
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.username
[SQL: INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (?, ?, ?, ?, ?, ?)]
[parameters: ('cookiemon', 'mon@cookie.com', '111-111-1111', 'password', '2021-04-27 17:05:04.309756', '2021-04-27 17:05:04.309756')]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

处理错误 用 try/except

ins = insert(users).values(
    username='cookiemon',
    email_address='mon@cookie.com',
    phone='111-111-1111',
    password='password'
    )
try:
    result = connection.execute(ins)
except:
    print('error')

try/except 块中的代码越少越好，并且最好只用它来捕获特定类型的错误。因为不同的错误可能有不同的处理方式。

事务。事务可以看作是一个标志，标志着从begin到commit之间的所有语句，执行时要么全部成功，要么有一个失败就全都视为失效。

启动事务时，数据库系统先记录数据库当前状态，然后再执行SQL语句，

如果所有SQL语句执行成功，则提交commit，并丢弃之前的数据库状态；

但只要有一个语句执行失败，则回滚rollback到先前记录的那个状态。

准备案例环境

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey, Boolean
from sqlalchemy import CheckConstraint
from datetime import datetime
from sqlalchemy import DateTime
#建库、建表
engine = create_engine('sqlite:///:memory:')
connection = engine.connect()
metadata = MetaData()
cookies = Table('cookies', metadata,
    Column('cookie_id', Integer(), primary_key=True),
    Column('cookie_name', String(50), index=True),
    Column('cookie_recipe_url', String(225)),
    Column('cookie_sku', String(55)),
    Column('quantity', Integer()),
    Column('unit_cost', Numeric(12,2)),  #长度12、精度2位小数
    CheckConstraint('quantity > 0', name='quantity_positive')
)

users = Table('users', metadata,
    Column('user_id', Integer(), primary_key=True),
    Column('username', String(15), nullable=False, unique=True),
    Column('email_address', String(255), nullable=False),
    Column('phone', String(20), nullable=False),
    Column('password', String(25), nullable=False),
    Column('created_on', DateTime(), default=datetime.now),
    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now),
    )

orders = Table('orders', metadata,
    Column('order_id', Integer(), primary_key=True),
    Column('user_id', ForeignKey('users.user_id')),
    Column('shipped', Boolean(), default=False),
    )

line_items = Table('line_items', metadata,
    Column('line_items_id', Integer(), primary_key=True),
    Column('order_id', ForeignKey('orders.order_id')),
    Column('cookie_id', ForeignKey('cookies.cookie_id')),
    Column('quantity', Integer()),
    Column('extended_cost', Numeric(12,2)),
    )

metadata.create_all(engine)
#插入users、cookies数据
from sqlalchemy import select, insert, update
ins = insert(users).values(
    username='cookiemon',
    email_address='mon@cookie.com',
    phone='111-111-1111',
    password='password',
    )
result = connection.execute(ins)
ins = cookies.insert()
inventory_list = [
    {
        'cookie_name': 'chocolate chip',
        'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe.html',
        'cookie_sku': 'cc01',
        'quantity':'12',
        'unit_cost':'0.50',
    },
    {
        'cookie_name': 'dark chocolate chip',
        'cookie_recipe_url': 'http://some.aweso.me/cookie/recipe_dark.html',
        'cookie_sku': 'cc02',
        'quantity':'1',
        'unit_cost':'0.75', 
    }
]
result = connection.execute(ins, inventory_list)
#插入两份订单数据
ins = insert(orders).values(user_id=1, order_id='1')
result = connection.execute(ins)
ins = insert(line_items)
order_items = [
    {
        'order_id': 1,
        'cookie_id': 1,
        'quantity': 9,
        'extended_cost': 4.50,
    },
]
result = connection.execute(ins, order_items)
ins = insert(orders).values(user_id=1, order_id='2')
result = connection.execute(ins)
ins = insert(line_items)
order_items = [
    {
        'order_id': 2,
        'cookie_id': 1,
        'quantity': 4,
        'extended_cost': 1.50,
    },
        {
        'order_id': 2,
        'cookie_id': 2,
        'quantity': 1,
        'extended_cost': 4.50,
    },
]
result = connection.execute(ins, order_items)
#发货函数
def ship_it(order_id):
    s = select([line_items.c.cookie_id, line_items.c.quantity])
    s = s.where(line_items.c.order_id==order_id)
    cookies_to_ship = connection.execute(s)
    for cookie in cookies_to_ship:
        u = update(cookies).where(cookies.c.cookie_id == cookie.cookie_id)
        u = u.values(quantity = cookies.c.quantity-cookie.quantity)
        result = connection.execute(u)
    u = update(orders).where(orders.c.order_id == order_id)
    u = u.values(shipped = True)
    result = connection.execute(u)
    print('shipped order ID：'+ str(order_id))
#发第1单货
ship_it(1)
s = select([cookies.c.cookie_id, cookies.c.quantity])
print(connection.execute(s).fetchall())
#发第2单货
ship_it(2)
#此时报错IntegrityError，因为quantity<0
#这可能会导致部分数据被修改，而部分数据不变，但实际上货并没有ship出去

上面的这个问题可以用try/except解决，也可以用事务。事务型ship_it()

from sqlalchemy.exc import IntegrityError
def ship_it(order_id):
    s = select([line_items.c.cookie_id, line_items.c.quantity])
    s = s.where(line_items.c.order_id==order_id)
    transaction = connection.begin()    #启动事务，记录当前数据库状态
    cookies_to_ship = connection.execute(s).fetchall()
    try:
        for cookie in cookies_to_ship:
            u = update(cookies).where(cookies.c.cookie_id == cookie.cookie_id)
            u = u.values(quantity = cookies.c.quantity-cookie.quantity)
            result = connection.execute(u)
        u = update(orders).where(orders.c.order_id == order_id)
        u = u.values(shipped = True)
        result = connection.execute(u)
        print('shipped order ID：'+ str(order_id))
        transaction.commit()    #若无错误，提交事务
    except IntegrityError as error:
        transaction.rollback()    #若有错误，手动回滚
        print(error)

ship_it(1)
ship_it(2)