1 files changed, 107 insertions, 0 deletions
diff --git a/examples/dbapi_transactions.py b/examples/dbapi_transactions.py
new file mode 100644
index 0000000..4e8f113
--- /dev/null
+++ b/examples/dbapi_transactions.py
@@ -0,0 +1,107 @@
+import sys
+import sqlite
+
+# The shared connection object
+cx = None
+
+def getCon():
+    # All code gets the connection object via this function
+    global cx
+    return cx
+
+def createSchema():
+    # Create the schema and make sure we're not accessing an old, incompatible schema
+    cu = getCon().cursor()
+    cu.execute("select tbl_name from sqlite_master where type='table' order by tbl_name")
+    tables = []
+    for row in cu.fetchall():
+        tables.append(row.tbl_name)
+    if tables != ["customer", "orders"]:
+        if tables == []:
+            # ok, database is empty
+            cu.execute("""
+                create table customer (
+                    cust_id integer primary key,
+                    cust_firstname text not null,
+                    cust_lastname text not null,
+                    cust_no text not null
+                )
+                """)
+            cu.execute("""
+                create table orders (
+                    ord_id integer primary key,
+                    ord_customer int,
+                    ord_item text not null,
+                    ord_quantity integer
+                )
+            """)
+            getCon().commit()
+        else:
+            print "We have an unknown schema here. Please fix manually."
+            sys.exit(1)
+
+def createCustomer(firstname, lastname, customerNo):
+    # Create a new customer and return the primary key id.
+    cu = getCon().cursor()
+    cu.execute("""
+        insert into customer(cust_firstname, cust_lastname, cust_no)
+            values (%s, %s, %s)
+            """, (firstname, lastname, customerNo))
+    getCon().commit()
+    return cu.lastrowid
+
+def createOrder(cust_id, ord_item, ord_quantity):
+    # Create a new order for the customer identified by cust_id and return the
+    # primary key of the created order row.
+    cu = getCon().cursor()
+    cu.execute("""
+        insert into orders (ord_customer, ord_item, ord_quantity)
+            values (%s, %s, %s)
+            """, (cust_id, ord_item, ord_quantity))
+    getCon().commit()
+    return cu.lastrowid
+
+def deleteOrder(ord_id):
+    # Delete an order.
+    cu = getCon().cursor()
+    cu.execute("delete from order where ord_id=%s", (ord_id,))
+    getCon().commit()
+
+def deleteCustomer(cust_id):
+    # Delete the customer identified by cust_id and all its orders (recursive
+    # delete).
+
+    # So now, finally, here we have an example where you *really* need
+    # transactions. We either want this to happen all or not at all. So all of
+    # these SQL statements need to be atomic, i. e. we need a transaction here.
+
+    # This will send the BEGIN to SQLite, as soon as the first non-SELECT is
+    # sent.
+    cu = getCon().cursor()
+
+    # So, before the next 'delete' statement, a 'BEGIN' is sent
+    cu.execute("delete from orders where ord_customer=%s", (cust_id,))
+    cu.execute("delete from customer where cust_id=%s", (cust_id,))
+
+    # This will send the "COMMIT" statement to the library.
+    getCon().commit()
+
+def main():
+    global cx
+    cx = sqlite.connect("customerdb")
+    createSchema()
+
+    # Create a customer
+    cust_id = createCustomer("Jane", "Doe", "JD0001")
+
+    # Create two orders for the customer
+    ord_id = createOrder(cust_id, "White Towel", 2)
+    ord_id = createOrder(cust_id, "Blue Cup", 5)
+
+    # Delete the customer, and all her orders.
+    deleteCustomer(cust_id)
+
+    cx.close()
+
+if __name__ == "__main__":
+    main()