zxJDBC |
Database Connectivity in JPython |
|
DocumentationGetting startedFirst, make sure both zxJDBC.jar and a valid JDBC driver are in your classpath. Then start JPython and import the zxJDBC connection factory. Supply the database, username, password and JDBC driver classname to the connect method. If your driver requires special arguments, pass them into theconnect method as
standard Python keyword arguments. You will be returned a
connection object. JPython 1.1+07 on java1.2.2 (JIT: symcjit) Copyright (C) 1997-1999 Corporation for National Research Initiatives >>> from com.ziclix.python.sql import zxJDBC >>> zxJDBC.autocommit = 0 >>> d, u, p, v = "database", "user", "password", "com.informix.jdbc.IfxDriver" >>> db = zxJDBC.connect(d, u, p, v) optionally >>> db = zxJDBC.connect(d, u, p, v, CHARSET='iso_1') >>> To access the database, create a cursor from the connection. To
execute a query simply provide the sql expression and call >>> c = db.cursor() >>> c.execute("select count(*) c from player") >>> c.description [('c', 3, 17, None, 15, 0, 1)] >>> for a in c.fetchall(): ... print a ... (13569,) >>>
>>> c.close() >>> db.close() >>> Standard extensions to the Python DB APIconnection.dbname
Datatype mapping callbacks through DataHandlerThe DataHandler interface has three methods for handling type mappings. They are called at two different times, one when fetching and the other when binding objects for use in a prepared statement. I have chosen this architecture for type binding because I noticed a number of discrepancies in how different JDBC drivers handled database types, in particular the additional types available in later JDBC versions.
It is simple to use these callbacks to achieve the desired result for your database driver. In the majority of cases nothing needs to be done to get the correct datatype mapping. However, in the cases where drivers differ from the spec or handle values differently, the DataHandler callbacks should provide the solution. Example DataHandler for Informix booleansOne such case where a driver needs a special mapping is Informix
booleans. The are represented as the characters 't' and 'f' in the
database and have their own type JPython 1.1+07 on java1.2.2 (JIT: symcjit) Copyright (C) 1997-1999 Corporation for National Research Initiatives >>> from com.ziclix.python.sql import zxJDBC >>> zxJDBC.autocommit = 0 >>> d, u, p, v = "database", "user", "password", "com.informix.jdbc.IfxDriver" >>> db = zxJDBC.connect(d, u, p, v) >>> c = db.cursor() >>> c.execute("create table g (a boolean)") >>> c.execute("insert into g values (?)", [1]) Traceback (innermost last): File "<console>", line 1, in ? Error: No cast from integer to boolean. [SQLCode: -9634] >>> from com.ziclix.python.sql.informix import InformixDataHandler >>> c.datahandler = InformixDataHandler(c.datahandler) >>> c.execute("insert into g values (?)", [1], {0:zxJDBC.OTHER}) >>> As you can see, the default handler fails to convert the Python
This functionality is also useful in handling the more advanced
JDBC 2.0 types You can also implement the DataHandler from within JPython as in this simple example: >>> class PyHandler(DataHandler): >>> def __init__(self, handler): >>> self.handler = handler >>> def getPyObject(self, set, col, datatype): >>> return self.handler.getPyObject(set, col, datatype) >>> def getJDBCObject(self, object, datatype): >>> print "handling prepared statement" >>> return self.handler.getJDBCObject(object, datatype) >>> >>> c.datahandler = PyHandler(c.datahandler) >>> c.execute("insert into g values (?)", [1]) handling prepared statement >>> JavadocYou can also look at the javadoc available here.Feel free to email me with questions or suggestions at bzimmer@ziclix.com |