{"id":4147,"date":"2024-07-24T10:35:01","date_gmt":"2024-07-24T03:35:01","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4147"},"modified":"2024-07-24T19:49:31","modified_gmt":"2024-07-24T12:49:31","slug":"python-sql-server-transaction","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-transaction\/","title":{"rendered":"Python SQL Server: Transaction"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to perform an SQL Server transaction in Python.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0Deleting data from a table tutorial<\/a> left off.<\/p>\n\n\n\n

Setting up sample data #<\/a><\/h2>\n\n\n\n

Step 1. Launch the SQL Server Management Studio (SSMS) and connect to the SQL Server.<\/p>\n\n\n\n

Step 2. Create a query and execute the following statements to insert data into the Books<\/code>, BookAuthors<\/code>, and Inventories<\/code> tables:<\/p>\n\n\n

INSERT<\/span> INTO<\/span> Books (Title, Publisher, ISBN, PublishedDate) VALUES<\/span>\n('Mastering SQL: A Comprehensive Guide'<\/span>, 'Tech Books Publishing'<\/span>, '978-1234567890'<\/span>, '2022-01-15'<\/span>),\n('The Art of Database Design'<\/span>, 'Expert Press'<\/span>, '978-0987654321'<\/span>, '2021-06-10'<\/span>),\n('SQL Queries for Mere Mortals'<\/span>, 'Practical SQL Publishing'<\/span>, '978-1122334455'<\/span>, '2023-03-21'<\/span>),\n('Advanced SQL Programming Techniques'<\/span>, 'Pro Code Press'<\/span>, '978-6677889900'<\/span>, '2020-09-30'<\/span>),\n('Database Systems: Theory and Practice'<\/span>, 'Academic Press'<\/span>, '978-5566778899'<\/span>, '2022-11-05'<\/span>);\n\nDECLARE<\/span> @BookID1 INT<\/span>, @BookID2 INT<\/span>, @BookID3 INT<\/span>, @BookID4 INT<\/span>, @BookID5 INT<\/span>;\n\nSELECT<\/span> @BookID1 = BookID FROM<\/span> Books WHERE<\/span> ISBN = '978-1234567890'<\/span>;\nSELECT<\/span> @BookID2 = BookID FROM<\/span> Books WHERE<\/span> ISBN = '978-0987654321'<\/span>;\nSELECT<\/span> @BookID3 = BookID FROM<\/span> Books WHERE<\/span> ISBN = '978-1122334455'<\/span>;\nSELECT<\/span> @BookID4 = BookID FROM<\/span> Books WHERE<\/span> ISBN = '978-6677889900'<\/span>;\nSELECT<\/span> @BookID5 = BookID FROM<\/span> Books WHERE<\/span> ISBN = '978-5566778899'<\/span>;\n\n-- Insert records into the BookAuthors table<\/span>\nINSERT<\/span> INTO<\/span> BookAuthors (BookID, AuthorID)\nVALUES<\/span> (@BookID1, 1<\/span>),\n       (@BookID2, 1<\/span>),\n       (@BookID3, 1<\/span>),\n       (@BookID4, 1<\/span>),\n       (@BookID5, 1<\/span>);\n\nINSERT<\/span> INTO<\/span> Inventories (BookID, Qty)\nSELECT<\/span> BookID, ABS<\/span>(CHECKSUM<\/span>(NEWID()) % 101<\/span>) + 100<\/span>\nFROM<\/span> Books;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Performing a transaction #<\/a><\/h2>\n\n\n\n
Step 1. Create a new module transaction.py<\/code> within the project directory with the following code:<\/p>\n\n\n
import<\/span> logging\nfrom<\/span> connect import<\/span> create_connection\n\n\ndef<\/span> create_order<\/span>(customer_id:int , book_id:int, quantity:int, price:float, order_date:str)<\/span> -> bool:<\/span>\n    # connect to the SQL Server<\/span>\n    conn = create_connection()\n    \n    if<\/span> conn is<\/span> None<\/span>:\n        return<\/span> False<\/span>\n\n    with<\/span> (conn, conn.cursor() as<\/span> cursor):\n        try<\/span>:\n            # check inventory<\/span>\n            cursor.execute(\"SELECT Qty FROM Inventories WHERE BookId = %s\"<\/span>, (book_id,))\n            row = cursor.fetchone()\n            if<\/span> row is<\/span> None<\/span> or<\/span> row[0<\/span>] < quantity:\n                raise<\/span> Exception(\"Insufficient inventory\"<\/span>)\n\n            # insert into orders<\/span>\n            cursor.execute(\"INSERT INTO Orders (OrderDate, CustomerId, TotalAmount) VALUES (%s, %s, %s)\"<\/span>, (order_date, customer_id, price*quantity))\n            order_id = cursor.lastrowid\n\n            # insert into orderDetails<\/span>\n            cursor.execute(\"INSERT INTO OrderDetails (OrderId, BookId, Quantity, Price) VALUES (%s, %s, %s, %s)\"<\/span>, (order_id, book_id, quantity, price))\n\n            # update inventories<\/span>\n            cursor.execute(\"UPDATE Inventories SET Qty = Qty - %s WHERE BookId = %s\"<\/span>, (quantity, book_id))\n            \n            conn.commit()        \n            return<\/span> True<\/span>\n        except<\/span> Exception as<\/span> e:\n            logging.error(f\"Error creating order: {e}<\/span>\"<\/span>)\n            conn.rollback()\n\n    return<\/span> False<\/span> <\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the built-in logging<\/code> module:<\/p>\n\n\n
import<\/span> logging<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
from<\/span> connect import<\/span> create_connection<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, define the create_order()<\/code> function that creates a new order:<\/p>\n\n\n
def<\/span> create_order<\/span>(customer_id:int , book_id:int, quantity:int, price:float, order_date:str)<\/span> -> bool:<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, connect to SQL Server by calling the create_connection()<\/code> function and return 0 if the connection fails:<\/p>\n\n\n
conn = create_connection()\nif<\/span> conn is<\/span> None<\/span>:\n    return<\/span> 0<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, manage the Connection<\/code> and Cursor<\/code> objects using the with<\/code> statement:<\/p>\n\n\n
with<\/span> (conn, conn.cursor() as<\/span> cursor):<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, check the ordered quantity with the inventory quantity of the book:<\/p>\n\n\n
cursor.execute(\"SELECT Qty FROM Inventories WHERE BookId = %s\"<\/span>, (book_id,))\nrow = cursor.fetchone()\nif<\/span> row is<\/span> None<\/span> or<\/span> row[0<\/span>] < quantity:\n    raise<\/span> Exception(\"Insufficient inventory\"<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, insert a new row into the Orders<\/code> table, get the inserted order ID, and use it to insert a new row into the OrderDetails<\/code> table:<\/p>\n\n\n
# insert into orders<\/span>\ncursor.execute(\"INSERT INTO Orders (OrderDate, CustomerId, TotalAmount) VALUES (%s, %s, %s)\"<\/span>, (order_date, customer_id, price*quantity))\norder_id = cursor.lastrowid\n\n# insert into orderDetails<\/span>\ncursor.execute(\"INSERT INTO OrderDetails (OrderId, BookId, Quantity, Price) VALUES (%s, %s, %s, %s)\"<\/span>, (order_id, book_id, quantity, price))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Eighth, update the inventory by reducing the inventory quantity by the order quantity:<\/p>\n\n\n
cursor.execute(\"UPDATE Inventories SET Qty = Qty - %s WHERE BookId = %s\"<\/span>, (quantity, book_id))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Ninth, commit the transaction:<\/p>\n\n\n
conn.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Tenth, return True if the order was created successfully:<\/p>\n\n\n
return<\/span> True<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Eleventh, if an error occurred during order creation, log the error details and roll back the transaction:<\/p>\n\n\n
logging.error(f\"Error creating order: {e}<\/span>\"<\/span>)\nconn.rollback()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the main.py<\/code> to use the create_order<\/code> function:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> transaction import<\/span> create_order\n\n# config logging to console<\/span>\nlogging.basicConfig(\n    stream=sys.stdout, \n    encoding='utf-8'<\/span>, \n    format='%(levelname)s:%(message)s'<\/span>,\n    level=logging.DEBUG\n)\n\n# create an order<\/span>\ntry<\/span>:\n    create_order(\n        customer_id=10<\/span>,\n        book_id=1<\/span>,\n        quantity=15<\/span>, \n        price=15.99<\/span>,\n        order_date='2024-07-24'<\/span>\n    );\nexcept<\/span> Exception as<\/span> e:\n    logging.error(f\"Error fetching customers: {e}<\/span>\"<\/span>)       <\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the main.py<\/code> file:<\/p>\n\n\n
python main.py<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Call the commit()<\/code> method of the Connection<\/code> object to commit a transaction.<\/li>\n\n\n\n
  • Use the rollback()<\/code> method of the Connection<\/code> object to roll back a transaction.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, import the create_connection<\/code> function from the connect<\/a> module:<\/p>\n\n\n