Python Cookbook 2Nd Edition Jun 1002005 [Electronic resources] نسخه متنی

Recipe 7.19. Using ODBC to Get Excel Data with Jython

Credit: Zabil CM

Problem

Your Jython script needs to extract
data from a Microsoft Excel file.

Solution

Jython, just like Java, can access ODBC through the JDBC-ODBC Bridge,
and Microsoft Excel can in turn be queried via ODBC:

from java import lang, sql
lang.Class.forName('sun.jdbc.odbc.JdbcOdbcDriver')
excel_file = 'values.xls'
connection = sql.DriverManager.getConnection(
'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};
DBQ=%s;READONLY=true}' %
excelfile, '', '')
# Sheet1 is the name of the Excel workbook we want.
  The field names for the
# query are implicitly set by the values for
 each column in the first row.
record_set = connection.createStatement( ).executeQuery(
'SELECT * FROM [Sheet1$]')
# print the first-column field of every record (==row)
while record_set.next( ):
print record_set.getString(1)
# we're done, close the connection and recordset
record_set.close( )
connection.close( )

Discussion

This recipe is most easily used on Microsoft Windows, where
installing and configuring ODBC, and the Microsoft Excel ODBC driver
in particular, is best supported. However, with suitable commercial
products, you can equally well use the recipe on an Apple Macintosh
or just about any other Unix version on the planet.

Using ODBC rather than alternate ways to access Microsoft Excel has
one substantial advantage that is not displayed in this recipe: with
ODBC, you can use a broad subset of SQL. For example, you can easily
extract a subset of a workbook's row by adding a
WHERE clause, such as:

SELECT * FROM [Sheet1$] WHERE DEPARTMENT=9

Since all of the selection logic can be easily expressed in the SQL
string you pass to the executeQuery method, this
approach lends itself particularly well to being encapsulated in a
simple reusable function.

If you're coding in Classic Python (CPython) rather
than Jython, you can't use JDBC, but you can use
ODBC directly (typically in the DB API-compliant way supported by
mxODBC, http://www.egenix.com//image/library/english/10241_python/mxODBCl)
to perform this recipe's task in a similar way.

See Also

The Jython site (http://www.jython.org);
JDBC's home page (http://java.sun.com/products/jdbc); Recipe 12.7, for another way to
access Excel data (by parsing the XML file that Excel can be asked to
output).