From 79d16d72cd530acbee682ebee44d5b1d2010c661 Mon Sep 17 00:00:00 2001 From: Micah Anderson Date: Sun, 17 Nov 2013 17:25:01 -0500 Subject: initial import of debian package to build with autobuilder --- examples/converters.py | 39 +++++++++++++++ examples/dbapi_transactions.py | 107 ++++++++++++++++++++++++++++++++++++++++ examples/manual_transactions.py | 102 ++++++++++++++++++++++++++++++++++++++ 3 files changed, 248 insertions(+) create mode 100644 examples/converters.py create mode 100644 examples/dbapi_transactions.py create mode 100644 examples/manual_transactions.py (limited to 'examples') diff --git a/examples/converters.py b/examples/converters.py new file mode 100644 index 0000000..7a34cb7 --- /dev/null +++ b/examples/converters.py @@ -0,0 +1,39 @@ +import os +import sqlite + +# Ok, let's define a user-defined type we can use with the SQLite database +class Point: + def __init__(self, x, y): + self.x, self.y = x, y + + # The _quote function is currently the way a PySQLite user-defined type + # returns its string representation to write to the database. + def _quote(self): + return "'%f,%f'" % (self.x, self.y) + + def __str__(self): + return "Point(%f, %f)" % (self.x, self.y) + +# The conversion callable needs to accept a string, parse it and return an +# instance of your user-defined type. +def pointConverter(s): + x, y = s.split(",") + return Point(float(x), float(y)) + +# Ensure we have an empty database +if os.path.exists("db"): os.remove("db") + +cx = sqlite.connect("db", converters={"point": pointConverter}) +cu = cx.cursor() +cu.execute("create table test(p point, n int)") +cu.execute("insert into test(p, n) values (%s, %s)", (Point(-3.2, 4.5), 25)) + +# For user-defined types, and for statements which return anything but direct +# columns, you need to use the "-- types" feature of PySQLite: +cu.execute("-- types point, int") +cu.execute("select p, n from test") +row = cu.fetchone() + +print "p:", row.p # .columnname instead of [0] is a PySQLite +print "n:", row.n # extension to the DB-API! +cx.close() 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() diff --git a/examples/manual_transactions.py b/examples/manual_transactions.py new file mode 100644 index 0000000..114ecbf --- /dev/null +++ b/examples/manual_transactions.py @@ -0,0 +1,102 @@ +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("begin") + 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 + ) + """) + cu.execute("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)) + 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)) + return cu.lastrowid + +def deleteOrder(ord_id): + # Delete an order. + cu = getCon().cursor() + cu.execute("delete from order where ord_id=%s", (ord_id,)) + +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. + cu = getCon().cursor() + + cu.execute("begin") + cu.execute("delete from orders where ord_customer=%s", (cust_id,)) + cu.execute("delete from customer where cust_id=%s", (cust_id,)) + cu.execute("commit") + +def main(): + global cx + # Open the connection in autocommit mode, because we believe we have reason + # to :-/ + cx = sqlite.connect("customerdb", autocommit=1) + 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() -- cgit v1.2.3