JPype DBAPI2 Guide

Introduction

One common use of JPype is to provide access to databases used JDBC. The JDBC API is well established, very capable, and supports most databases. JPype can be used to access JDBC both directly or through the use of the Python DBAPI2 as layed (see PEP-0249). Unfortunately, the Python API leaves a lot of behaviors undefined.

The JPype dbapi2 module provides our implementation of this Python API. Normally the Python API has to deal with two different type systems, Python and SQL. When using JDBC, we have the added complexity that Java types are used to communicate with the driver. We have introduced concepts appropriate to handle this addition complexity.

Module Interface

Constructors

Access to the database is made available through connection objects. The module provides the following constructor for connections:

jpype.dbapi2.connect

Globals

JPype dbapi2 defines several globals that define the module behavior. These values are constants.

apilevel: The apilevel for the module is "2.0".

threadsafety

The threadsafety level is 2 meaning "Threads may share the module and connections". But the actual threading level depends on the driver implementation that JDBC is connected to. Connections for many databases are synchronized so they can be shared, but threads must execute statement in series. Connections in the module are implemented in Python and have per object resources that cannot be shared. Attempting to use a connection with a thread other than the thread that created it will raise an Error.

Sharing in the above context means that two threads may use a resource without wrapping it using a mutex semaphore to implement resource locking. Note that you cannot always make external resources thread safe by managing access using a mutex: the resource may rely on global variables or other external sources that are beyond your control.

paramstyle

The parameter style for JPype dbapi2 module is qmark

paramstyle	Meaning
`qmark`	Question mark style, e.g. `...WHERE name=?`

Exceptions

The dbapi2 module exposes error information using the following exceptions:

jpype.dbapi2.Warning

jpype.dbapi2.Error

jpype.dbapi2.InterfaceError

jpype.dbapi2.DatabaseError

jpype.dbapi2.DataError

jpype.dbapi2.OperationalError

jpype.dbapi2.IntegrityError

jpype.dbapi2.InternalError

jpype.dbapi2.ProgrammingError

jpype.dbapi2.NotSupportedError

Python exceptions are more fine grain than JDBC exceptions. Whereever possible we have redirected the Java exception to the nearest Python exception. However, there are cases in which the Java exception may appear. Those exceptions inherit from :py:class:jpype.dbapi2.Error. This is the exception inheritance layout:

Exception
|__Warning
|__Error
   |__InterfaceError
   |__java.sql.SQLError
   |  |__java.sql.BatchUpdateException
   |  |__java.sql.RowSetWarning
   |  |__java.sql.SerialException
   |  |__java.sql.SQLClientInfoException
   |  |__java.sql.SQLNonTransientException
   |  |__java.sql.SQLRecoverableException
   |  |__java.sql.SQLTransientException
   |  |__java.sql.SQLWarning
   |  |__java.sql.SyncFactoryException
   |  |__java.sql.SyncProviderException
   |
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError

Type Access

JPype dbapi2 provides two different maps which serve to convert data between Python and SQL types. When setting parameters and fetching results, Java types are used. The connection provides to maps for converting the types of parameters. An adapter is used to translate from a Python type into a Java type when setting a parameter. Once a result is produced, a converter can be used to translate the Java type back into a Python type.

There are two lookup functions that select the behavior to decide how a column or parameter should be treated. These are getters and setters.

adapters

Whenever a Python type is passed to a statement, it must first be converted to the appropriate Java type. This can be accomplished in a few ways. The user can manually convert to the correct type by constructing a Java object or applying the JPype casting operator. Some Java types have built in implicit conversions from the corresponding type. For all other conversions, an adapter. An adapter is defined as a type to convert from and a conversion function which takes a single argument that returns a Java object.

The adapter maps are stored in the connection. The adapter map can be supplied when calling connect , or added to the map later through the adapters property.

setters

A setter transfers the Java type into a SQL parameter. There are multiple types can an individual parameter may accept. The type of setter is determined by the JDBC type. Each individual JDBC type can have its own setter. Not every database supports the same setter. There is a default setter may that would work for most purposes. Setters can also be set individually using the types argument to the .execute*() methods. The setter is a function which processes the database metadata into a type.

Setters can supplied as a map to connect or by accessing the setter property on a Connection.

jpype.dbapi2.SETTERS_BY_META

jpype.dbapi2.SETTERS_BY_TYPE

converters

When a result is fetched the database, it is returned as Jave type. This Java type then has a converter applied. Converters are stored in a map holding the type as key and a converter function that takes one argument and returns the desired type. The default converter map will convert all types to Python. This can be disabled by setting the converters to None.

The converter map can be passed in to the connect function, or set on the Connection using the converters property. It can be supplied as a list or a map to the .fetch*() methods.

getters

JDBA provides more than one way to access data returned from a result. In the native JDBC, each executed statement returns a result set which acts as a cursor for the statement. It is possible to access each column using a different get method. The default map will attempt to fetch according to the most general type. The getter is a configurable function that uses the metadata to find the most appropriate type.

jpype.dbapi2.GETTERS_BY_TYPE

jpype.dbapi2.GETTERS_BY_NAME

Connection Objects

A Connection object can be created using the using connect function. Once a connection is established the resulting Connection contains the following.

jpype.dbapi2.Connection

Cursor Objects

These objects represent a database cursor, which is used to manage the context of a fetch operation. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors. Cursors created from different connections may or may not be isolated, depending on how the transaction support is implemented (see also the connection's rollback and commit methods).

jpype.dbapi2.Cursor

Cursors can act as an iterator. So to get the contents of table one could use code like:

with connection.cursor() as cur:
    cur.execute("select * from table")
    for row in cur:
       print(row)

SQL Type Constructors

Many databases need to have the input in a particular format for binding to an operation's input parameters. For example, if an input is destined for a DATE column, then it must be bound to the database in a particular string format. Similar problems exist for "Row ID" columns or large binary items (e.g. blobs or RAW columns). This presents problems for Python since the parameters to the .execute*() method are untyped. When the database module sees a Python string object, it doesn't know if it should be bound as a simple CHAR column, as a raw BINARY item, or as a DATE.

This is less of a problem in JPype dbapi2 than in a typically dbapi driver as we have strong typing backing the connection, but we are still required to supply methods to construct individual SQL types. These functions are:

jpype.dbapi2.Date

jpype.dbapi2.Time

jpype.dbapi2.Timestamp

jpype.dbapi2.DateFromTicks

jpype.dbapi2.TimeFromTicks

jpype.dbapi2.TimestampFromTicks

jpype.dbapi2.Binary

For the most part these constructors are largely redundant as adapters can provide the same functionality and Java types can directly use to communicate type information.

JDBC Types

In the Python DBAPI2, the SQL type system is normally reduced to a subset of the SQL types by mapping multiple types together for example STRING covers the types STRING, CHAR, NCHAR , NVARCHAR , VARCHAR, and OTHER. JPype dbapi2 supports both the recommended Python types and the fine grain JDBC types. Each type is represented by an object of type JBDCType.

jpype.dbapi2.JDBCType

The following types are defined with the correspond Python grouping, the default setter, getter, and Python type. For types that support more than one type of getter, the special getter can be applied as the converter for the type. For example, the defaulf configuration has getter[BLOB] = BINARY.get, to get the Blob type use getter[BLOB] = BLOB.get or specify it when calling use.

Group	JDBC Type	Default Getter	Default Setter	PyTypes	Special Getter
DATE DATETIME TIME	DATE TIMESTAMP TIME	getDate getTimestamp getTime	setDate setTimestamp setTime	datetime.datetime datetime.datetime datetime.datetime
--------DECIMAL DECIMAL	------------------------DECIMAL NUMERIC	-------------------getBigDecimal getBigDecimal	--------------setBigDecimal setBigDecimal	-----------------decimal.Decimal decimal.Decimal
--------FLOAT FLOAT FLOAT	------------------------FLOAT DOUBLE REAL	-------------------getDouble getDouble getFloat	--------------setDouble getDouble setFloat	-----------------float float float
--------NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER	------------------------BOOLEAN BIT TINYINT (0..255) SMALLINT (-2^15..2^15) INTEGER (-2^31..2^31) BIGINT (-2^63..2^63)	-------------------getBoolean getBoolean getShort getShort getInt getLong	--------------setBoolean setBoolean setShort getShort getInt getLong	-----------------bool bool int int int int
--------BINARY	------------------------BINARY	-------------------getBytes	--------------setBytes	-----------------bytes
BINARY BINARY BINARY	BLOB LONGVARBINARY VARBINARY	getBytes getBytes getBytes	setBytes setBytes setBytes	bytes bytes bytes	getBlob
--------	------------------------	-------------------	--------------	-----------------	---------------
TEXT TEXT TEXT	CLOB LONGNVARCHAR LONGVARCHAR	getString getString getString	setString setString setString	str str str	getClob
TEXT	NCLOB	getString	setString	str	getNClob
TEXT	SQLXML	getString	setString	str	getSQLXML
--------STRING STRING STRING STRING	------------------------NVARCHAR CHAR NCHAR VARCHAR	-------------------getString getString getString getString	--------------setString setString setString setString	-----------------str str str str
	------------------------ARRAY OBJECT NULL REF ROWID RESULTSET TIME_WITH_TIMEZONE TIMESTAMP_WITH_TIMEZONE	-------------------getObject getObject getObject getObject getObject getObject getObject getObject			---------------getArray getObject getObject getRef getRowId getObject getTime getTimeStamp
-------- * * *	------------------------ASCII_STREAM BINARY_STREAM CHARACTER_STREAM ASCII_STREAM BINARY_STREAM CHARACTER_STREAM NCHARACTER_STREAM URL	-------------------getAsciiStream getBinaryStream getCharacterStream getAsciiStream getBinaryStream getCharacterStream getNCharacterStream getURL

Some of these types never correpond to a SQL type but are used only to specify getters and setters for a particular parameter or column.

Other

The default getter will attempt to look for the column type by name if the type is OTHER. This allows for user defined types to be added if supported by the database.

User defined types

A user can declare a new type using JDBCType. The arguments are the name of new type which must match a SQL typename. Use typeinfo on the connection to get the list of available types.

It may necessary to define a custom getter function which defining a new type so that the custom return type accurately reflects the column type.

class JSONType(dbapi2.JDBCType):
   def get(self, *args):
       rc = JDBCType.get(self, *args)
       # Custom return converter here
       return rc
JSON = JSONType("JSON")

Conclusion

This wraps up the JPype dbapi2 module. Because JDBC supports many different dataase drivers, not every behavior is defined on every driver. Consult the driver specific information to determine what is available.

The dbapi2 does not fully cover all of the capabilities of the JDBC driver. To access functions that are not defined in DBAPI2, the JDBC native objects can be accessed on both the connection and the cursor objects.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

dbapi2.rst

dbapi2.rst

JPype DBAPI2 Guide

Introduction

Module Interface

Constructors

Globals

Exceptions

Type Access

adapters

setters

converters

getters

Connection Objects

Cursor Objects

SQL Type Constructors

JDBC Types

*

*

Other

User defined types

Conclusion

Files

dbapi2.rst

Latest commit

History

dbapi2.rst

File metadata and controls

JPype DBAPI2 Guide

Introduction

Module Interface

Constructors

Globals

Exceptions

Type Access

adapters

setters

converters

getters

Connection Objects

Cursor Objects

SQL Type Constructors

JDBC Types

*

*

Other

User defined types

Conclusion