Python MySQL

DRAFT, 8/24/01 Chapter 11 If you are not fa initely want to that combines th lends itself to a Associates, Inc. provides an exce a working under In order to follo install the MySQ reference section DB-API Like Java and Pe API. This databa called the Datab opers interested is a very small, problem is that plex features da good because it The Databa The entry point database enginebook Learning Python, 2nd Edi llent introduction into Python p standing of the Python languag w the content of this chapter Ldb, the MySQL version of DB et/andy/python/MySQLdb. Cha includes directions on how to rl, Python has developed a un se API was developed by a P ase SIG. The Database SIG is a Python access to various datab simple API. On the negative it is very small and thus does tabase programmers expect in realistically does not enable tru se Connection into DB-API is really the only . By convention, all modules sCopyright © 2001 O’Reiside, it isn’t very good. Part of its not support a lot of the more com- a database API. It is also not very e database independence. part of the API tied to a particular upporting DB-API are named afterified API for database access—DB- ython Special Interest Group (SIG) group of influential Python devel- ases. On the positive side, DB-API11 11.Python miliar with Python and you do a lot of Perl programming, you def- take a look at it. Python is an object-oriented scripting language e strengths of languages like Perl and Tcl with a clear syntax that pplications that are easy to maintain and extend. The O’Reilly & tion by Mark Lutz and David Asher rogramming. This chapter assumes e. , you will need to download and -API. You can find the module at pter 23, The Python DB-API in the install MySQLdb.192 lly & Associates, Inc. DRAFT, 8/24/01 the database the thus called MyS cledb and the S API should con object. This meth import MySQL conn = MySQL The above exam MySQL database arguments, you use for the conn All arguments m above. The API for a co to cursor objects the connection: conn.close() Cursors Cursors represen vides your appli cursor = con This cursor is t cute() method plest form of dat conn = MySQL db='test'); cursor = con cursor.execu print "Affec In this example, sor generated by the number of r 1. Query processin method to sendy support with a "db" extension. The MySQL implementation is QLdb. Similarly, the Oracle implementation would be called ora- ybase implementation sybasedb. The module implementing DB- tain a connect() method that returns a DB-API connection od returns an object that has the same name as the module: db; db.connect(host='carthage', user='test', passwd='test', db='test'); ple connects using the user name/password pair ’test’/’test’ to the ’test’ hosted on the machine ’carthage’. In addition to these four can also specify a custom port, the location of a UNIX socket to ection, and finally an integer representing client connection flags. ust be passed to connect() as keyword/value pairs in the example nnection object is very simple. You basically use it to gain access and manage transactions. When you are done, you should close ; t SQL statements and their results. The connection object pro- cation with a cursor via the cursor() method: n.cursor(); he center of your Python database access. Through the exe- , you send SQL to the database and process any results. The sim- abase access is of course a simple insert: db.connect(host='carthage', user='test', passwd='test', n.cursor(); te('INSERT INTO test (test_id, test_char) VALUES (1, 'test')"); ted rows: ", cursor.rowcount; the application inserts a new row into the database using the cur- the MySQL connection. It then verifies the insert by printing out ows affected by the insert. For inserts, this value should always be g is a little more complex. Again, you use the execute() SQL to the database. Instead of checking the affected rows, how-Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 ever, you grab Example 11-1 sh The cursor objec many(), and fe Python tuple. In results from the methods, will th Of course, fetch sets. You can in The fetchone( represents a col rows of the resu The final fetch m and fetchall rows at once. Yo or instead rely o Parameteriz DB-API includes execute() me many(). Param can pass argume cute() is a SQ parameters spec cursor.execu Example 11-1. A S import MySQLdb; connection = Non try: connection = cursor = con cursor.execu for row in c print "K print "V connection. except: if connectio connectithe results from the cursor using one of many fetch methods. ows a Python program processing a simple query. t actually provides several fetch methods: fetchone(), fetch- tchall(). For each of these methods, a row is represented by a the above example, the fetchall() method fetches all of the query into a list of Python tuples This method, like all of the fetch row an exception if the SQL was not a query. ing all of the rows at once can be very inefficient for large result stead fetch each row one by one using the fetchone() method. ) method returns a single row as a tuple where each element umn in the returned row. If you have already fetched all of the lt set, fetchone() will return None. ethod, fetchmany(), is middle ground between fetchone() (). It enables an application to fetch a pre-defined number of u can either pass in the number of rows you wish to see returned n the value of cursor.arraysize to provide a default value. ed SQL a mechanism for executing pseudo-prepared statements using the thod as well as a more complex method called execute- eterized SQL is a SQL statement with placeholders to which you nts. As with a simple SQL execution, the first argument to exe- L string. Unlike the simple form, this SQL has place holders for ified by the second argument. A simple example is: te('INSERT INTO COLORS (COLOR, ABBR) VALUES (%s, %s)', ('BLUE', 'BL')); imple Query e; MySQLdb.connect(host="carthage", user="user", passwd="pass", db="test"); nection.cursor(); te("SELECT test_id, test_val FROM test ORDER BY test_id"); ursor.fetchall(): ey: ", row[0]; alue: ", row[1]; close(); n: on.close();Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 In this example, as the second ar and the second DB-API actually format you wish MySQLdb.para MySQLdb when the set of place value for MySQL you pass a Pytho DB-API actually them. This lack in database appl The utility of pa executemany( statement with m pet that adds thr cursor.execu cursor.execu cursor.execu That same functi cursor.execu As you can see, values in place Python in batch MyS ing sion shou Other Objec DB-API provide types so that th many() and re databases. Thes%s is placed in the SQL as placeholders for values that are passed gument. The first %s matches the first value in the paramter tuple %s matches the second value in the tuple. has several ways of marking SQL parameters. You can specify the to use by setting MySQLdb.paramstyle. The above example is mstyle = "format". The "format" value is the default for a tuple of parameters is passed to execute() and is basically holders from the ANSI C printf() function. Another possible db.paramstyle is "pyformat". This value is the default when n mapping as the second argument. allows several other formats, but MySQLdb does not support of support is particularly unfortunate since it is common practice ications in other languages to mark placeholders with a ?. rameterized SQL actually becomes appearant when you use the ) method. This method enables you to execute the same SQL ultiple sets of parameters. For example, consider this code snip- ee rows to the database using execute(): te("INSERT INTO COLOR (COLOR, ABBREV) VALUES ('BLUE', 'BL')"); te("INSERT INTO COLOR (COLOR, ABBREV) VALUES ('PURPLE', 'PPL')"); te("INSERT INTO COLOR (COLOR, ABBREV) VALUES ('ORANGE', 'ORN')"); onality using executemany() looks like this: temany("INSERT INTO COLOR ( COLOR, ABBREV ) VALUES (%s, %s )", (("BLUE", "BL"), ("PURPLE", "PPL"), ("ORANGE", "ORN"))); this one line executes the same SQL three times using different of the placeholders. This can be extremely useful if you are using processing. QLdb treats all values as string values, even when their underly- database type is BIGINT, DOUBLE, DATE, etc. Thus, all conver- parameters should be %s even though you might think they ld be a %d or %f. ts s a host of other objects to help encapulate common SQL data ey may be passed as parameters to execute() and execute- lieve developers of the burden of formatting them for different e objects include Date, Time, Timestamp, and Binary. MySQLdbCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 supports these o ters, it converts The Timestamp siders this illegal updates the data import time; d = MySQLdb. cursor.execu It is important t Time(), and Ti have to use th FromTicks() each of these me Proprieta In general, you database code in need access to the MySQL C A ability is particu database. Basically, MySQ cessing since cu shows a trivial methods from th the MySQL serve not run this app Example 11-2. . A import MySQLdb; conn = None; try: conn = MySQL for db in co for tbl curs curs prin curs except:bjects up to a point. Specifically, when MySQLdb binds parame- each paramter to a string (via __str__) and places it in the SQL. object, in particular, includes fractional seconds and MySQL con- input. The following code creates a Date for the current time and base: DateFromTicks(time.time()); te("UPDATE test SET test_date = %s WHERE test_id = 1", (d,)); o note that MySQLdb does not properly implement the Date(), mestamp() constructors for their respective objects. You instead e DateFromTicks(), TimeFromTicks(), and Timestamp- methods to get a reference to the desired object. The argument for thods is the number of seconds since the epoch. ry Operations should stick to the published DB-API specification when writing Python. There will be some instances, however, where you may MySQL-specific functionality.MySQLdb is actually built on top of PI, and it exposes that API to programs that wish to use it. This larly useful for applications that want meta-data about the MySQL Ldb exposes most C methods save those governing result set pro- rsors are a better interface for that functionality. Example 11-2 application that uses the list_dbs() and list_tables() e C API to loop through all of the tables in all of the databases on r and print out the first row from each table. Needless to say, do lication against a production machine. Python Application Using Proprietary Functionality db.connect(host="carthage", user="test", passwd="test", db="test"); nn.list_dbs(): in conn.list_tables(db[0]): or = conn.cursor(); or.execute("SELECT * FROM " + tbl[0]); t cursor.fetchone(); or.close();Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Chapter 23, The Applied D So far, we have tionality. As a co and does not co should therefore through a practic Our example is builds an XML f the order inform ated XML file. if conn: conn.clo Example 11-3. . An <?xml version="1 <order orderID=" <customer cust Wibble 1818 H Osse MN< U <postalCod <lineItem quan <unitCost cu <product pro Wibb <lineItem quan <unitCost cu <product pro Wibb Example 11-2. . A Python DB-API lists the proprietary APIs exposed by MySQLdb. B-API walked you through the DB-API and showed you its basic func- mprehensive database access API, it still leaves a lot to be desired mpete with more mature APIs like Perl DBI and Java JDBC. You except significant change in this API over time. Now, we will go al example of a Python database application using the DB-API. a batch routine that pulls stale orders from an order database and ile. Business partners can then download this XML file and import ation into their database. Example 11-3 shows a sample gener- se(); XML File Containing Order Information for a Fictitious Manufacturer .0"?> 101" date="2000" salesRepID="102"> omerID="100"> Retail armonika Rd. o /state> S e>55369 tity="2"> rrency="USD">12.99 ductID="104"> le Scarf tity="1"> rrency="USD">24.95 ductID="105"> le Hat Python Application Using Proprietary FunctionalityCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 The XML enable out having to kn runs to look for such orders are into XML. The Python scrip import sys, import trace import MySQL Much of the scr the database. Ex Figure 11-1. . The Example 11-4. class Address: def __init__ self.lin self.lin self.cit self.sta self.cou self.pos def toXML(se xml = (' xml = (' if self. xml if self. xml xml = (' if self. xml if self. xml if self. xml if self. xml xml = (' return x class Customer: def __init__s the our business partners to trade information about orders with- ow anything about our data model. Every night, a Python script orders that have not been converted to XML in the last day. Any then read from the database in Figure 11-1 and then converted t, xmlgen.py, starts with a few simple imports: os; back; db; ipt defines Python objects that encapsulate the business objects in ample 11-4 contains the code for these business objects. FIGURE11-1.BMP Data Model for the Manufacturing Database (self, l1, l2, cty, st, ctry, zip): e1 = l1; e2 = l2; y = cty; te = st; ntry = ctry; talCode = zip; lf, ind): %s\r\n' % ind); %s%s \r\n' % (xml, ind)); line1: = ('%s%s \r\n%s %s\r\n%s \r\n' % (xml, ind, ind, self.line1, ind)); line2: = ('%s%s \r\n%s %s\r\n%s \r\n' % (xml, ind, ind, self.line2, ind)); %s%s \r\n' % (xml, ind)); city: = ('%s%s %s\r\n' % (xml, ind, self.city)); state: = ('%s%s %s\r\n' % (xml, ind, self.state)); country: = ('%s%s %s\r\n' % (xml,ind,self.country)); postalCode: = ('%s%s %s\r\n' % (xml, ind, self.postalCode)); %s%s\r\n' % (xml, ind)); ml; (self, cid, nom, addr):Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 self.cus self.nam self.add def toXML(se xml = (' if self. xml if self. xml xml = (' return x class LineItem: def __init__ self.pro self.qua self.uni def toXML(se xml = (' xml = (' ( xml = (' xml = (' return x class Order: def __init__ self.ord self.ord self.sal self.cus self.ite def toXML(se xml = (' ( xml = (' for item xml xml = (' return x class Product: def __init__ self.pro self.nam def toXML(se xml = (' xml = (' Example 11-4.tomerID = cid; e = nom; ress = addr; lf, ind): %s\r\n' % (ind, self.customerID)); name: = ('%s%s %s\r\n' % (xml, ind, self.name)); address: = ('%s%s' % (xml, self.address.toXML(ind + ' '))); %s%s\r\n' % (xml, ind)); ml; (self, prd, qty, cost): duct = prd; ntity = qty; tCost = cost; lf, ind): %s\r\n' % (ind, self.quantity)); %s%s %s\r\n' % xml, ind, self.unitCost)); %s%s' % (xml, self.product.toXML(ind + ' '))); %s%s\r\n' % (xml, ind)); ml; (self, oid, date, rep, cust): erID = oid; erDate = date; esRep = rep; tomer = cust; ms = []; lf, ind): %s\r\n' % ind, self.orderID, self.orderDate, self.salesRep)); %s%s' % (xml, self.customer.toXML(ind + ' '))); in self.items: = ('%s%s' % (xml, item.toXML(ind + ' '))); %s%s\r\n' % (xml, ind)); ml; (self, pid, nom): ductID = pid; e = nom; lf, ind): %s\r\n' % (ind, self.productID)); %s%s %s\r\n' % (xml, ind, self.name));Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Each business o nothing more th toXML(), conve access separate good database p All of the datab The purpose of load them from orders and send shows the exec xml = (' return x Example 11-5. . D def executeBatch try: cursor = cursor.e orders = cursor.c except: print "E tracebac conn.clo exit(0); for row in o oid = ro try: curs curs row curs addr Example 11-4.bject defines two basic methods. The first, the constructor, does an assign values to the object’s attributes. The second method, rts the business object to XML. So far, we have kept all database from our business objects. This is a very critical design element of rogramming. ase access comes in a module method called executeBatch(). this method is to find out which orders need XML generated and the database into business objects. It then takes those loaded s the return value of toXML() to an XML file. Example 11-5 uteBatch() method. %s%s\r\n' % (xml, ind)); ml; atabase Access for the XML Generator (conn): conn.cursor(); xecute("SELECT ORDER_ID FROM ORDER_EXPORT " + "WHERE LAST_EXPORT CURRENT_DATE()"); cursor.fetchall(); lose(); rror retrieving orders."; k.print_exc(); se(); rders: w[0]; or = conn.cursor(); or.execute("SELECT CUST_ORDER.ORDER_DATE, " + "CUST_ORDER.SALES_REP_ID, " + "CUSTOMER.CUSTOMER_ID, " + "CUSTOMER.NAME, " + "CUSTOMER.ADDRESS1, " + "CUSTOMER.ADDRESS2, " + "CUSTOMER.CITY, " + "CUSTOMER.STATE, " + "CUSTOMER.COUNTRY, " + "CUSTOMER.POSTAL_CODE " + "FROM CUST_ORDER, CUSTOMER " + "WHERE CUST_ORDER.ORDER_ID = %s " + "AND CUST_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID", ( oid ) ); = cursor.fetchone(); or.close(); = Address(row[4], row[5], row[6], row[7], row[8], row[9]);Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 The first try/exc have not had XM bails completely Each row return The script theref the order repres cust orde curs curs for except: prin trac exit try: curs except: prin trac try: fnam xmlf xmlf xmlf xmlf except: prin trac try: curs curs except: prin trac Example 11-5. . Dept block looks in the ORDER_EXPORT table for all orders that L generated in the last day. If that fails for any reason, the script . ed from fetchall() represents an order in need of exporting. ore loops through each of the rows ands loads all of the data for ented by the row. Inside the for loop, the script executes SQL to = Customer(row[2], row[3], addr); r = Order(oid, row[0], row[1], cust); or = conn.cursor(); or.execute("SELECT LINE_ITEM.PRODUCT_ID, " + "LINE_ITEM.QUANTITY, " + "LINE_ITEM.UNIT_COST, " + "PRODUCT.NAME " + "FROM LINE_ITEM, PRODUCT " + "WHERE LINE_ITEM.ORDER_ID = %s " + "AND LINE_ITEM.PRODUCT_ID = PRODUCT.PRODUCT_ID", oid); row in cursor.fetchall(): prd = Product(row[0], row[3]); order.items.append(LineItem(prd, row[1], row[2])); t "Failed to load order: ", oid; eback.print_exc(); (0); or.close(); t "Error closing cursor, continuing..."; eback.print_exc(); e = ('%d.xml' % oid); ile = open(fname, "w"); ile.write('\r\n\r\n'); ile.write(order.toXML('')); ile.close(); t ("Failed to write XML file: %s" % fname); eback.print_exc(); or = conn.cursor(); or.execute("UPDATE ORDER_EXPORT " + "SET LAST_EXPORT = CURRENT_DATE() " + "WHERE ORDER_ID = %s", ( oid )); t "Failed to update ORDER_EXPORT table, continuing"; eback.print_exc(); atabase Access for the XML GeneratorCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 pull order and c columns in han order, its associ LINE_ITEM is a LineItem objects current order ID With all of the d out their XML co back to the data The script then g more orders. The last part mis if __name__ try: conn except: prin trac exit executeB This script as w able with the resustomer data from the ORDER and CUSTOMER tables. With those d, it can construct Order, Customer, and Address objects for the ated customer, and the customer’s address. Because ORDER to one-to-many relationship, we need a separate query to load the . The next query looks for all of the line items associated with the and loads business objects for them. ata loaded into business objects, the script opens a file and writes nversion to that file. Once the write is successful, the script goes base to note that the XML is now up-to-date with the database. oes to the next order and continues processing until there are no sing from the script is the functionality to call executeBatch(): == '__main__': = MySQLdb.connect(host='carthage', user='test', passwd='test', db='Test'); t "Error connecting to MySQL:"; eback.print_exc(); (0); atch(conn); ell as the SQL to generate the tables and data behind it are avail- t of the examples from this book at the O’Reilly Web site.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01Copyright © 2001 O’Reilly & Associates, Inc.