Python SQL Server: транзакция

В этом руководстве вы узнаете, как выполнить транзакцию SQL Server на Python.

Это руководство начинается там, где закончилось руководство по удалению данных из таблицы.

Настройка выборочных данных

Шаг 1. Запустите SQL Server Management Studio(SSMS) и подключитесь к SQL Server.

Шаг 2. Создайте запрос и выполните следующие операторы для вставки данных в таблицы Books, BookAuthors и Inventories:

INSERT INTO Books(Title, Publisher, ISBN, PublishedDate) VALUES
('Mastering SQL: A Comprehensive Guide', 'Tech Books Publishing', '978-1234567890', '2022-01-15'),
('The Art of Database Design', 'Expert Press', '978-0987654321', '2021-06-10'),
('SQL Queries for Mere Mortals', 'Practical SQL Publishing', '978-1122334455', '2023-03-21'),
('Advanced SQL Programming Techniques', 'Pro Code Press', '978-6677889900', '2020-09-30'),
('Database Systems: Theory and Practice', 'Academic Press', '978-5566778899', '2022-11-05');
DECLARE @BookID1 INT, @BookID2 INT, @BookID3 INT, @BookID4 INT, @BookID5 INT;
SELECT @BookID1 = BookID FROM Books WHERE ISBN = '978-1234567890';
SELECT @BookID2 = BookID FROM Books WHERE ISBN = '978-0987654321';
SELECT @BookID3 = BookID FROM Books WHERE ISBN = '978-1122334455';
SELECT @BookID4 = BookID FROM Books WHERE ISBN = '978-6677889900';
SELECT @BookID5 = BookID FROM Books WHERE ISBN = '978-5566778899';
-- Insert records into the BookAuthors table
INSERT INTO BookAuthors(BookID, AuthorID)
VALUES(@BookID1, 1),
      (@BookID2, 1),
      (@BookID3, 1),
      (@BookID4, 1),
      (@BookID5, 1);
INSERT INTO Inventories(BookID, Qty)
SELECT BookID, ABS(CHECKSUM(NEWID()) % 101) + 100
FROM Books;

Выполнение транзакции

Шаг 1. Создайте новый модуль transaction.py в каталоге проекта со следующим кодом:

import logging
from connect import create_connection
def create_order(customer_id:int , book_id:int, quantity:int, price:float, order_date:str) -> bool:
    # connect to the SQL Server
    conn = create_connection()
    if conn is None:
        return False
    with(conn, conn.cursor() as cursor):
        try:
            # check inventory
            cursor.execute("SELECT Qty FROM Inventories WHERE BookId = %s",(book_id,))
            row = cursor.fetchone()
            if row is None or row[0] < quantity:
                raise Exception("Insufficient inventory")
            # insert into orders
            cursor.execute("INSERT INTO Orders(OrderDate, CustomerId, TotalAmount) VALUES(%s, %s, %s)",(order_date, customer_id, price*quantity))
            order_id = cursor.lastrowid
            # insert into orderDetails
            cursor.execute("INSERT INTO OrderDetails(OrderId, BookId, Quantity, Price) VALUES(%s, %s, %s, %s)",(order_id, book_id, quantity, price))
            # update inventories
            cursor.execute("UPDATE Inventories SET Qty = Qty - %s WHERE BookId = %s",(quantity, book_id))
            conn.commit()        
            return True
        except Exception as e:
            logging.error(f"Error creating order: {e}")
            conn.rollback()
    return False 

Как это работает.

Сначала импортируем встроенный модуль ведения журнала:

import logging

Во-вторых, импортируйте функцию create_connection из модуля connect :

from connect import create_connection

В-третьих, определите функцию create_order(), которая создает новый заказ:

def create_order(customer_id:int , book_id:int, quantity:int, price:float, order_date:str) -> bool:

В-четвертых, подключитесь к SQL Server, вызвав функцию create_connection(), и верните 0, если соединение не удалось:

conn = create_connection()
if conn is None:
    return 0

В-пятых, управляйте объектами Connection и Cursor с помощью оператора with:

with(conn, conn.cursor() as cursor):

В-шестых, сверьте заказанное количество с имеющимся на складе количеством книги:

cursor.execute("SELECT Qty FROM Inventories WHERE BookId = %s",(book_id,))
row = cursor.fetchone()
if row is None or row[0] < quantity:
    raise Exception("Insufficient inventory")

В-седьмых, вставьте новую строку в таблицу Orders, получите идентификатор вставленного заказа и используйте его для вставки новой строки в таблицу OrderDetails:

# insert into orders
cursor.execute("INSERT INTO Orders(OrderDate, CustomerId, TotalAmount) VALUES(%s, %s, %s)",(order_date, customer_id, price*quantity))
order_id = cursor.lastrowid
# insert into orderDetails
cursor.execute("INSERT INTO OrderDetails(OrderId, BookId, Quantity, Price) VALUES(%s, %s, %s, %s)",(order_id, book_id, quantity, price))

В-восьмых, обновите запасы, уменьшив объем запасов на объем заказа:

cursor.execute("UPDATE Inventories SET Qty = Qty - %s WHERE BookId = %s",(quantity, book_id))

Девятое, зафиксируйте транзакцию:

conn.commit()

В-десятых, возвращаем True, если заказ был создан успешно:

return True

Одиннадцатое. Если при создании заказа произошла ошибка, запишите сведения об ошибке и откатите транзакцию:

logging.error(f"Error creating order: {e}")
conn.rollback()

Шаг 2. Измените main.py, чтобы использовать функцию create_order:

import logging, sys
from transaction import create_order
# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)
# create an order
try:
    create_order(
        customer_id=10,
        book_id=1,
        quantity=15, 
        price=15.99,
        order_date='2024-07-24'
    );
except Exception as e:
    logging.error(f"Error fetching customers: {e}")       

Шаг 3. Откройте терминал и запустите файл main.py:

python main.py

Загрузите исходный код проекта

Загрузите исходный код проекта

Краткое содержание

• Вызовите метод commit() объекта Connection для фиксации транзакции.
• Для отката транзакции используйте метод rollback() объекта Connection.