Viewing file:  dbapi_transactions.py (3.44 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
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()
|