Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
dbexts
Another extension available with the zxJDBC package is dbexts. dbexts is a Python module that adds another layer of abstraction around the Python DB API 2.0. With DBexts, you can specify connection information in a configuration file, and then use the higher-lever dbexts methods on the connections defined. In order to use dbexts, the dbexts.py module that comes with zxJDBC must be added to the sys.path.
The configuration file can be any file, but the default is a file named dbexts.ini that resides in the same directory as the dbexts.py file. To use another file, include the filename in the dbexts constructor. Listing 1 is a configuration file that defines connections to the MySQL and PostgreSQL database systems.
Listing 1—Sample dbexts Configuration
[default] name=mysqltest [jdbc] name=mysqltest url=jdbc:mysql://localhost/test user=jyuser pwd=beans driver=org.gjt.mm.mysql.Driver [jdbc] name=pstest url=jdbc:postgresql://localhost/test user=jyuser pwd=beans driver=org.postgresql.Driver
Once the configuration file is defined, you can connect to the database by instantiating the dbexts class. In the following example, the dbexts.ini file is placed in the current working directory. You can alternatively place it in the directory on the sys.path where you placed the dbexts.py module:
>>> from dbexts import dbexts
>>> mysqlcon = dbexts("mysqltest", "dbexts.ini")
>>> psgrscon = dbexts("pstest", "dbexts.ini")
>>>
>>> # execute raw sql and get a list of headers and results
>>> psgrscon.raw("SELECT * from random")
([('letter', 1, 1, None, None, None, 1), ('number', 4, 11, None, 10, 0,
1)], [('A', 4), ('f', 6), ('a', 1)])
>>>
>>> # execute interactive sql
>>> psgrscon.isql("select * from random")
LETTER | NUMBER
---------------
A | 4
f | 6
a | 1
3 rows affected
>>>
>>> # Display schema- this works with postgresql, not MySQL
>>> psgrscon.schema("random")
Table
random
Primary Keys
Imported (Foreign) Keys
Columns
letter bpchar(1), nullable
number int4(4), nullable
Indices
>>>
>>> # show tables in MySQL 'test' database
>>> mysqlcon.table()
TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS
-----------------------------------------------------------
| | random | TABLE |
1 row affected
Table 3 lists the primary dbexts methods. Some of the methods have additional optional arguments and there are some additional methods, but they are a bit beyond the scope of this article. For more details, consult the excellent documentation that comes with zxJDBC.
Table 3—dbexts Methods
|
Method |
Description |
|
__init__(dbname, cfg, resultformatter, autocommit) |
The dbexts constructor. All parameters have default values, so they are all optional. The dbname is the name you specified for the connection in the dbexts.ini file. The cfg is the location of the dbexts.ini file if it does not reside in the same directory as the dbexts.py file. The resultformatter is a callable object that accepts a list of rows as one argument and optionally accepts a list of headers. The resultformatter object is called to display data. The autocommit argument is set to 1, or true, by default, but can be set to 0 if included in the call to the constructor. |
|
isql(sql, params, bindings, maxrows) |
The isql method interactively executes SQL statements. Interactive means that results are displayed with the resultformatter immediately after executing the statement, much like the MySQL and pSQL client programs. All parameters have default values except for the sql statement. The sql parameter is the SQL statement itself. The params parameter is a tuple of value parameters used to replace ? in SQL statements. The bindings parameter allows you to bind a datahandler. See the documentation on zxJDBC for more about datahandlers. The maxrows specifies the maximum number of rows to return, where zero or None means no limit. |
|
raw(sql, params, bindings) |
Executes the sql statement and returns a tuple containing headers and results. The params and bindings arguments are the same as for the isql method. |
|
schema(table, full, sort) |
Displays a table's indices, foreign keys, primary keys, and columns. If the full parameter is nonzero, the results include the table's referenced keys. A nonzero sort value means the table's column names will be sorted. |
|
table(table) |
The table parameter is optional. Without a table argument, this method displays a list of all tables. Otherwise, the specified table's columns are displayed. |
|
proc(proc) |
The proc parameter is optional. Without it, all procedures are listed. With it, the parameters for the specified procedure are displayed. |
|
bcp(src, table, where='(1=1)') |
This method copies data from specified database and table (src and table) to the current instance's database. |
|
begin(self) |
Creates a new cursor. |
|
rollback(self) |
Rolls back statements executed since the creation of a new cursor. |
|
commit(self, cursor=None, maxrows=None) |
Commits statements executed since the creation of a new cursor. |
|
display(self) |
Displays the results using the current formatter. |