The zxJDBC package provides a nearly 100% Python DB API 2.0 compliant interface for database connectivity in Jython. It is implemented entirely in Java and makes use of the JDBC API. This means any database capable of being accessed through JDBC, either directly or using the JDBC-ODBC bridge, can be manipulated using zxJDBC.
First, make sure a valid JDBC driver is in your classpath. Then start Jython and import the zxJDBC connection factory.
The most common way to establish a connection is through a Driver.
Simply supply the database, username, password and JDBC driver
classname to the connect method. If your driver requires special
arguments, pass them into the connect method as
standard Python keyword arguments. You will be returned a
connection object.
Jython 2.1b1 on java1.4.0-beta3 (JIT: null) Type "copyright", "credits" or "license" for more information. >>> from com.ziclix.python.sql import zxJDBC >>> d, u, p, v = "jdbc:mysql://localhost/test", None, None, "org.gjt.mm.mysql.Driver" >>> db = zxJDBC.connect(d, u, p, v) optionally >>> db = zxJDBC.connect(d, u, p, v, CHARSET='iso_1') >>>
The only required argument is the fully-qualified classname of the DataSource, all keywords will use JavaBeans reflection to set properties on the DataSource.
Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from com.ziclix.python.sql import zxJDBC
>>> params = {}
>>> params['serverName'] = 'localhost'
>>> params['databaseName'] = 'ziclix'
>>> params['user'] = None
>>> params['password'] = None
>>> params['port'] = 3306
>>> db = zxJDBC.connectx("org.gjt.mm.mysql.MysqlDataSource", **params)
>>>
It is possible for zxJDBC to use a Connection found through a JNDI lookup. This
is particularly useful in an application server (such as when using PyServlet). The
bound object can be either a String, Connection, DataSource or ConnectionPooledDataSource.
The lookup will figure out the instance type and access the Connection
accordingly,
The only required argument is the JNDI lookup name. All keyword arguments will be converted to their proper Context field value if the keyword matches one of the constants. If a field name does not exist for the keyword, it will passed as declared. The resulting environment will be used to build the InitialContext.
This example uses the simple Sun FileSystem JNDI reference implementation. Please consult the JNDI implementation you intend to use for the InitialContextFactory classname as well as the connection URL.
Jython 2.1b1 on java1.4.0-beta3 (JIT: null) Type "copyright", "credits" or "license" for more information. >>> from com.ziclix.python.sql import zxJDBC >>> jndiName = "/temp/jdbc/mysqldb" >>> factory = "com.sun.jndi.fscontext.RefFSContextFactory" >>> db = zxJDBC.lookup(jndiName, INITIAL_CONTEXT_FACTORY=factory) >>>
In order execute any operation, a cursor is required from the connection. There are two different kinds of cursors: static and dynamic.
The primary difference between the two is the way they manage the underlying ResultSet.
In the static version, the entire ResultSet is iterated immediately, the data
converted and stored with the cursor and the ResultSet closed. This allows the cursor
to know the rowcount (not available otherwise within JDBC) and set the .rowcount
attribute properly. The major disadvantage to this approach is the space/time constraints
might be extraordinary.
The solution to the problem are dynamic cursors which keep a handle to the open ResultSet
and iterate as required. This drastically decreases memory consumption and increases
perceived response time because no work is done until asked. The drawback is the .rowcount
attribute can never be accurately set.
To execute a query simply provide the SQL expression and call
execute. The cursor now has a description
attribute detailing the column information. To navigate the result
set, call one of the fetch methods and a list of
tuples will be returned.
>>> c = db.cursor() # this gets a static cursor
or
>>> c = db.cursor(1) # this gets a dynamic 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,)
>>>
When finished, close the connections.
>>> c.close() >>> db.close() >>>
To call a stored procedure or function provide the name and any params to callproc.
The database engine must support stored procedures. The examples below have been tested with
Oracle, SQLServer and Informix. Refer to the Python DP API spec for how OUT and INOUT parameters
work.
NOTE: The name of the stored procedure can either be a string or tuple. This is NOT portable to other DB API implementations.
SQL Server
>>> c = db.cursor() # open the database as in the examples above
>>> c.execute("use northwind")
>>> c.callproc(("northwind", "dbo", "SalesByCategory"), ["Seafood", "1998"], maxrows=2)
>>> for a in c.description:
... print a
...
('ProductName', -9, 40, None, None, None, 0)
('TotalPurchase', 3, 17, None, 38, 2, 1)
>>> for a in c.fetchall():
... print a
...
('Boston Crab Meat', 5318.0)
('Carnarvon Tigers', 8497.0)
>>> c.nextset()
1
>>> print c.fetchall()
[(0,)]
>>> print c.description
[('@RETURN_VALUE', 4, -1, 4, 10, 0, 0)]
>>>
Oracle
>>> c = db.cursor() # open the database as in the examples above
>>> c.execute("create or replace function funcout (y out varchar2) return varchar2 is begin y := 'tested'; return 'returned'; end;")
>>> params = [None]
>>> c.callproc("funcout", params)
>>> print params
['tested']
>>> print c.description
[(None, 12.0, -1, None, None, None, 1)]
>>> print c.fetchall()
[('returned',)]
>>>
When finished, close the connections.
>>> c.close() >>> db.close() >>>
connection.dbname
connection.dbversion
cursor.updatecount
cursor.lastrowid
cursor.tables(qualifier,owner,table,type)
cursor.columns(qualifier,owner,table,column)
cursor.foreignkeys(primary_qualifier,primary_owner,pimary_table,
foreign_qualifier,foreign_owner,foreign_table)
cursor.primarykeys(qualifier,owner,table)
cursor.procedures(qualifier,owner,procedure)
cursor.procedurecolumns(qualifier,owner,procedure,column)
cursor.statistics(qualifier,owner,table,unique,accuracy)
The 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.
public void preExecute(Statement stmt)
throws SQLException;
execute method), all the parameters will have been set.public void postExecute(Statement stmt)
throws SQLException;
public String getMetaDataName(String name);
public PyObject getRowId(Statement stmt) throws SQLException;
public Object getJDBCObject(PyObject
object, int type);
execute method. When the parameters
are being bound to the statement, the DataHandler gets a callback
to map the type. This is only called if type bindings are present.public Object getJDBCObject(PyObject
object);
public PyObject
getPyObject(ResultSet set, int col, int type);
public PyObject getPyObject(CallableStatement stmt, int col,
int type) throws SQLException;
public void registerOut(CallableStatement statement, int
index, int colType, int dataType, String dataTypeName) throws SQLException;
public String getProcedureName(PyObject catalog, PyObject
schema, PyObject name);
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.
One 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 boolean. You can see
from the example below, without the special DataHandler, the
boolean type mapping fails.
Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> 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
1 into an Informix boolean because the IfxDriver
treats booleans as JDBC type OTHER. The
InformixDataHandler is intimately aware of the IfxDriver mappings
and understands how to interpret Python values as booleans when the
JDBC type is OTHER.
This functionality is also useful in handling the more advanced
JDBC 2.0 types CLOB, BLOB and
Array.
You can also implement the DataHandler from within Jython 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
>>>
dbexts is a wrapper around DB API 2.0 compliant database modules. It currently supports zxJDBC and mxODBC but could easily be modified to support others. It allows developers to write scripts without knowledge of the implementation language of Python (either C or Java). It also greatly eases the burden of database coding as much of the functionality of the Python API is exposed through easier to use methods.
dbexts needs a configuration file in order to create a connection. The configuration file has the following format:
[default] name=mysql [jdbc] name=mysql url=jdbc:mysql://localhost/ziclix user= pwd= driver=org.gjt.mm.mysql.Driver datahandler=com.ziclix.python.sql.handler.MySQLDataHandler [jdbc] name=ora url=jdbc:oracle:thin:@localhost:1521:ziclix user=ziclix pwd=ziclix driver=oracle.jdbc.driver.OracleDriver datahandler=com.ziclix.python.sql.handler.OracleDataHandler
cfg attribute.
__init__(self, dbname=None, cfg=None,
resultformatter=format_resultset, autocommit=1)
dbname
is None, the default connection, as specified in the cfg file
will be used.
isql(self, sql, params=None, bindings=None,
maxrows=None)
maxrows is specified, only maxrows are displayed.
raw(self, sql, params=None, bindings=None,
delim=None, comments=comments)
schema(table, full=0, sort=1)
full is true, also compute the exported (or
referenced) keys. If sort is true (the default), sort the column names.
>>> d.schema("store")
Table
store
Primary Keys
store_id {store_3}
Imported (Foreign) Keys
location (city.city_id) {store_7}
Exported (Referenced) Keys
store_id (site_store.store_id) {site_store_8}
Columns
location int(4), non-nullable
store_id serial(4), non-nullable
store_name varchar(32), non-nullable
Indices
unique index {523_8115} on (store_id)
unique index {store_ix_1} on (store_name)
>>>
table(table=None, types=("TABLE",), owner=None, schema=None)
proc(self, proc=None, owner=None, schema=None)
bcp(src, table, where='(1=1)', parameters=[], selective=[], ignorelist=[], autobatch=0)
where clause can narrow the number of rows to be copied.
The following are generally not called since isql and raw can handle almost all cases.
begin(self)
rollback(self)
commit(self, cursor=None, maxrows=None)
display(self)
Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from dbexts import dbexts
>>> d = dbexts()
>>> d.isql("create table store (store_id int, store_name varchar(32), location int)")
>>> d.isql("insert into store values (?, ?, ?)", [(1, "amazon.com", 3), (2, "egghead.com", 4)])
>>> d.isql("insert into store values (?, ?, ?)", [(15, "800.com", 1), (19, "fogdog.com", 3)])
>>> d.isql("insert into store values (?, ?, ?)", [(5, "nike.com", 4)])
>>> d.isql("select * from store order by store_name")
STORE_ID | STORE_NAME | LOCATION
---------------------------------
15 | 800.com | 1
1 | amazon.com | 3
2 | egghead.com | 4
19 | fogdog.com | 3
5 | nike.com | 4
5 rows affected
>>>