Contents


Connect to Apache Derby databases using Python

Use this step-by-step approach to obtain, install, and configure the necessary components

Comments

Overview

Apache Derby is a relational database written in 100% Pure Java. The project, which was originally called Cloudscape™, was contributed to the Apache foundation by IBM in August 2004, and is currently in its incubation phase. This means you have access to an easy-to-use relational database, available free of charge under the Apache Software License.

Although Derby is written in Java, there are times when programs written in other languages need to access a Derby database. Python is an interpreted, high-level, object-oriented dynamically-typed programming language. The fact that the interpreter can be used interactively, or iteratively, provides us with an interesting rapid-prototype language and development/test environment. When combined with a few existing utilities, Python lets you write high-level, dynamic, object-oriented scripts for manipulating Derby relational databases quite easily. This article explains how to do this.

This article is not meant to be an introduction to Python, Apache Derby, or SQL. It will, however, show how to set up a Windows® system so that you can access and manipulate your Derby databases using Python.

First steps

The first thing that you need to do is to install and configure Derby to function on a Windows system.

Table 1: Installing Derby
Action TakenDescriptionComment/Result
At an absolute minimum, you need a Java Runtime Environment (JRE)Install the 1.4.2 JRE from developerWorks:
  1. Select the Java technology section.
  2. Click the IBM Developer Kits link.
  3. Click the download link link.
  4. Click the ibm-java2-jre-ibm-142.exe file link
  5. Read the download terms and conditions.
  6. Click the I agree link.
  7. Save the file on disk.
  8. Scan the file with a virus checker.
  9. Execute the installation program (for example: ibm-java2-jre-ibm-142.exe).
Minimally, a JRE needs to be present. On the other hand, you may want, or have, a complete Java Development Environment, which includes a Java compiler and other utilities.
Verify your Java environmentOpen a new command prompt, and type:

java -version

If you see something that begins like:

java version '1.4.2'

your environment should be suitable. If not, you may need to reinstall or upgrade your JRE.
Install Apache Derby
  1. Go to the Apache Derby Project page.
  2. Click the Downloads link.
  3. Click the Official Release download link
  4. Select the appropriate download (for example: incubating-derby-10.0.2.1-bin.zip).
  5. Perform a virus scan of the downloaded file.
  6. Unzip the downloaded file.
  7. Copy or move the expanded directory structure to: C:\Derby
Result: All of the Derby files are under:

C:\Derby

Recommended: Install IBM Cloudscape
  1. Go to "IBM Cloudscape Version 10.0" (developerWorks, September 2004).
  2. Select the link appropriate for the platform version you want to download (for example: IBM Cloudscape Version 10.0 Windows installer).
  3. Register, if you don't already have a valid user ID and password.
  4. Download the installation program (for example 10.0-IBM-Cloudscape-Win32.exe).
  5. Perform a virus scan of the downloaded file.
  6. Execute the file.
  7. Copy or move the expanded directory structure to: C:\Cloudscape
Result: All of the Cloudscape files are under:

C:\Cloudscape

Recommended: Install sample files from this article
  1. Go to the download section (below).
  2. Select the download file (for example: Python2DerbySamples.zip).
  3. Save the file on your system.
  4. Scan the file with a virus checker.
  5. Expand the contents of the file.
  6. Copy or move the *.bat files to: C:\Derby\bin
Result: All of the sample files are under:

C:\Derby\bin

Why install both Derby and Cloudscape?

The main reason for installing Derby, as far as I can see, is that you can get access to the program source, and participate in the review and implementation of an open source project. There will probably be some differences between the "current" version of Cloudscape available from IBM, and the absolute "latest" version of Derby available from Apache. At this point, there may not be much of a difference. At some time, there may. So, you can decide whether to install Derby, or Cloudscape, or both.

There are a number of reasons for installing IBM Cloudscape. I find the most compelling to be the fact that IBM Cloudscape includes:

  • Documentation in PDF format (which makes for easier searching)
  • Sample programs
  • Sample databases
  • The necessary "IBM DB2 JDBC Universal Driver" (for example: db2jcc*.jar) jar files.
  • Command files for executing the utilities, for example:

    dblook
    Data Definition Language (DDL) Generation Utility (also called a schema dump tool)
    ij
    Interactive JDBC scripting tool for running scripts against a database
    sysinfo
    Utility which provides information about your version of Cloudscape and your environment
    *NetworkServer*.bat
    Command files for starting and stopping Cloudscape as a Network Server

After installing Derby, the directory structure should contain the following:

Derby directory structure & contents:
DirectoryContents
C:\DerbyThe COPYRIGHT, LICENSE, NOTICE, and README files from the Derby installation, and the following directories:
C:\Derby\binAll the *.bat files from either Python2DerbySamples.zip or C:\Cloudscape\frameworks\NetworkServer\bin
C:\Derby\databasesAll the database data from C:\Cloudscape\demo\databases (for example: the sample and toursDB directories)
C:\Derby\javadocAll of the Java documentation from the Derby installation
C:\Derby\libAll of the *.jar files from the Derby installation

Starting the Derby Network server

For background information about configuring and using a Derby Network Server, take a look at the Derby documentation. Those of you, like myself, who are interested in quickly getting things up and running, can use the Cloudscape script files as an example, and write your own simple Derby Server scripts that start and stop your Derby database.

To make things even easier, I've included all of the following script files in the sample download file.

Table 2: Contents of Python2DerbySamples.zip
FilenameDescription
setNetworkClientCP.batsets the CLASSPATH environment value for Derby clients
setNetworkServerCP.batsets the CLASSPATH environment value for Derby server
NetworkServerControl.batissues commands to the Derby Network Server program
startNetworkServer.batstarts the Derby Network Server program
stopNetworkServer.batstops the Derby Network Server program
dblook.batData Definition Language generation utility
ij.batInteractive JDBC scripting tool
sysinfo.batdisplays information about your Derby Network Server system

There are three ways to start the Derby Network Server program:

  1. Manually (for example: setting up the CLASSPATH environment variable, and invoking the Java code to execute the NetworkServerControl class file with a "start" parameter). For example:
    set CLASSPATH=C:\Derby\lib\derby.jar;C:\Derby\lib\derbytools.jar;C:\Derby\lib\derbynet.jar
    cd C:\Derby\databases
    java org.apache.derby.drda.NetworkServerControl start
  2. Using the NetworkServerControl.bat file, and specifying "start" as a parameter. For example:
    NetworkServerControl start
  3. Using the startNetworkServer.bat file. Please note that since everything is done by this script, you could easily have a shortcut to this script on your desktop, or your quick launch toolbar. So, you could either use the shortcut icon, or you could start the script from a command prompt. For example:
    startNetworkServer

One attribute that seems to be shared among programmers is the propensity to look for, write, and use "quick and easy" ways to do things. In a way, that is what this article is all about. Since Windows batch (*.bat) files are a form of scripting, it is easy to understand why people are willing to have all of the "little details" identified and handled by a script file. Not only is it easy to use, it is also easier to remember.

Listing 1: setNetworkServerCP.bat

This script file is invoked by other script files to add the appropriate Derby Java Archive (jar) files to the front of the CLASSPATH environment variable. Hence, the file name (for example: Set Network Server ClassPath):

set DERBY=C:\Derby

set DERBY_JARS=%DERBY%\lib\derby.jar
set DERBY_JARS=%DERBY_JARS%;%DERBY%\lib\derbytools.jar
set DERBY_JARS=%DERBY_JARS%;%DERBY%\lib\derbynet.jar

set CLASSPATH=%DERBY_JARS%;%CLASSPATH%

Listing 2: NetworkServerControl.bat

This script file executes the setNetworkServerContol program, passing any user-specified parameters. This allows us to have a common script file that can be used to issue any of the supported Network Control commands:

@ECHO OFF
setlocal
Title Derby: NetworkServerControl
cd C:\Derby\databases
call C:\Derby\bin\setNetworkServerCP.bat
java org.apache.derby.drda.NetworkServerControl %*
Title Windows command prompt
endlocal

Listing 3: startNetworkServer.bat

This specialized script file can only be used to start the Derby Network Server. Much like the preceding script file, the NetworkServerControl program is invoked, having "start" specified as the only parameter:

@ECHO OFF
setlocal
Title Derby Network Server
cd C:\Derby\databases
call C:\Derby\bin\setNetworkServerCP.bat
java org.apache.derby.drda.NetworkServerControl start
Title Windows command prompt
endlocal

Regardless of the technique used to start Derby as a Network Server, if it is able to successfully start, the program will wait for connection requests from clients. As each connection is received, a message indicating this will be displayed.

Stopping the Derby Network Server

There are three (reasonable) ways to stop the Derby Network Server program:

  1. Manually (for example: setting up the CLASSPATH environment variable, and invoking the Java code to execute the NetworkServerControl class file with a "shutdown" parameter). For example:
    set CLASSPATH=C:\Derby\lib\derby.jar;C:\Derby\lib\derbytools.jar;C:\Derby\lib\derbynet.jar
    java org.apache.derby.drda.NetworkServerControl shutdown
  2. Using the NetworkServerControl.bat file, and specifying "shutdown" as a parameter. For example:
    NetworkServerControl shutdown
  3. Using the stopNetworkServer.bat file. As with the startNetworkServer.bat, you could have a shortcut to it on your desktop, or your quick launch toolbar. So, you could either use the shortcut icon, or you could run the script from a command prompt. For example:
    stopNetworkServer

Note: Even though it is possible to terminate the Derby Network Server program or process window abnormally, doing this could possibly corrupt your database, and therefore should be highly discouraged.

To learn more about the actions that can be performed by NetworkServerControl program, you could look to the Derby documentation. Another way is to execute NetworkServerControl without specifying parameters. Doing this will cause it to display the usage information. As you can see in Figure 1, the parameter to use when requesting the Derby Network Server to stop is shutdown.

Figure 1: NetworkServerControl - usage information
C:\Derby\bin> NetworkServerControl
No arguments given.
Usage: NetworkServerControl <commands>
Commands:
start [-h <host>] [-p <portnumber>]
shutdown [-h <host>][-p <portnumber>]
ping [-h <host>][-p <portnumber>]
sysinfo [-h <host>][-p <portnumber>]
runtimeinfo [-h <host>][-p <portnumber>]
logconnections {on|off}[-h <host>][-p <portnumber>]
maxthreads <max>[-h <host>][-p <portnumber>]
timeslice <milliseconds>[-h <host>][-p <portnumber>]
trace {on|off} [-s <session id>][-h <host>][-p <portnumber>]
tracedirectory <traceDirectory>[-h <host>][-p <portnumber>]

C:\Derby\bin> NetworkServerControl start
Server is ready to accept connections on port 1527.

*** Execute the following in a separate command window ***

C:\Derby\bin> NetworkServerControl ping
Connection obtained for host: localhost, port number 1527.

C:\Derby\bin> NetworkServerControl shutdown
Shutdown successful.

Connecting to the Derby Network Server

As you saw in Figure 1, you can use NetworkServerControl for more than starting the Derby Network server. You can also use it to demonstrate that connectivity can be made (for example: via a "ping" request). Derby clients need to use a different interface than that used by the NetworkServerControl program. The interface provided for clients is a Java Database Connectivity (JDBC) interface. Before clients can connect to the Derby Network Server using this JDBC interface, you need to obtain a connectivity driver that supports this interface.

Support for this is provided by two JAR files:

  1. db2jcc.jar
  2. db2jcc_license_c.jar

Fortunately there are two places from which these files may be obtained.

  1. You can either retrieve them from developerWorks:
    • Go to the "IBM DB2 JDBC Universal Driver, for Apache Derby Network Server" article.
    • Click the db2jcc_for_derby.zip link.
    • Click the Released product: IBM Cloudscape (IBM DB2 JDBC Universal Driver, for Cloudscape/Derby) link.
    • Click Continue.
    • Register, if necessary.
    • Click Sign-in.
    • Fill in your user ID and password, then click Submit.
    • Read the license agreement, then click I agree.
    • Download the db2jcc_for_derby.zip to your machine.
    • Run a virus scan on the downloaded file.
    • Extract the file contents.
  2. Or, if you installed Cloudscape, you can simply copy the files from the C:\Cloudscape\lib directory.

In either case, the files (db2jcc.jar, and db2jcc_license_c.jar) need to be copied to the C:\Derby\lib directory.

Figure 2: Updated CLASSPATH
C:\Derby> lsPath classpath
CLASSPATH=
  C:\Derby\lib\derby.jar
  C:\Derby\lib\derbytools.jar
  C:\Derby\lib\derbynet.jar
  C:\Derby\lib\db2jcc.jar
  C:\Derby\lib\db2jcc_license_c.jar

Using the ij utility

Now you can use one of the other utilities provided with Derby -- specifically, the ij utility -- to do more than simply connect to the Derby Network Server. In order to figure out how to run the ij utility, you can again take advantage of the scripts that are provided with Cloudscape. In addition to reviewing and studying the ij script, I copied the sample databases from C:\Cloudscape\demo\databases to the corresponding location under C:\Derby. For example:

Figure 3: Copying Cloudscape sample databases
C:\> xcopy C:\Cloudscape\demo\databases C:\Derby\databases /s /e
Does C:\Derby\databases specify a file name
or directory name on the target
(F = file, D = directory)? d
...
135 File(s) copied

Note:It is easiest if you start the Derby Network Server using a hostname of localhost, so that passwords need not be authenticated. This means that the user ID will only be used to define the initial schema for the connection. Since connections to the database can only occur from the same machine, this shouldn't be too much of a security concern. Once you have stabilized your environment, you can use the Derby documentation to enable password authentication and connections from remote machines.

The schema identifies the tables within the database that will be accessed by default. This allows us to "SELECT * FROM ORG" instead of "SELECT * FROM SAMP.ORG".

Since you are accessing the Derby databases through the Network Server, it must be started before a connection can be established.

Figure 4: ij session
C:\Derby\bin> ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'jdbc:derby:net://localhost:1527/sample';
ij> select * from org;
DEPTN&|DEPTNAME      |MANAG&|DIVISION  |LOCATION
-----------------------------------------------------
10    |Head Office   |160   |Corporate |New York
15    |New England   |50    |Eastern   |Boston
20    |Mid Atlantic  |10    |Eastern   |Washington
38    |South Atlantic|30    |Eastern   |Atlanta
42    |Great Lakes   |100   |Midwest   |Chicago
51    |Plains        |140   |Midwest   |Dallas
66    |Pacific       |270   |Western   |San Francisco
84    |Mountain      |290   |Western   |Denver

8 rows selected
ij> disconnect all;
ij> exit;

The DB2 Runtime Client

The JDBC API that is provided by Derby is, not surprisingly, most useful for Java programs. If you want to connect to Derby using the JDBC Application Programming Interface (API), you should consider using Jython instead of Python.

Fortunately, there is an alternative. Susan Cline's developerWorks article, "Cloudscape and ODBC" (see Related topics) describes how to use the DB2 Runtime Client to connect to the Derby Network Server using the JDBC interface and to provide an Open DataBase Connectivity (ODBC) interface to other programs that support it. ODBC is an open database access method which defines how programs can access and manipulate information in a database.

This article contains a Windows downloads section. Use the "DB2 Run-Time Client Lite, Version 8.2" link to access the installation instructions (see Part 3 of the readme file). Since you have already installed Derby, and copied the sample databases from Cloudscape, and used the ij utility to verify the JDBC connectivity to the Derby Network Server, you are now ready to configure the DB2 Runtime client.

Table 2: Setting up the DB2 Runtime Client
StepAction Taken
1.If you don't have the Derby Network Server running, use the instructions in Starting the Derby Network Server above to get it started.
2.Start the DB2 Command Line Processor (CLP).

You can either use the Windows Start menu:

  • Start > Programs > IBM DB2 > Command Line Tools > Command Line Processor

Or you can:

  • Open a command prompt.
  • Change to the bin directory under the runtime client installation. For example:

    cd C:\Program Files\IBM\SQLLIB\bin

  • Initialize the DB2 environment using: db2cmd
  • Execute the DB2 CLP using: db2
3.Configure a name by which the DB2 code can access the Derby Network Server. For the sake of simplicity, I will use derby. Note that this is not a hostname, it is simply a name used within the DB2 configuration for identifying how to get to your Derby Network Server.
db2 => catalog tcpip node derby remote localhost server 1527
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
4.Configure the sample database as being on this "remote" node:
db2 => catalog db sample at node derby authentication server
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
5.While you're at it, also configure the toursDB database as being on the same "remote" node:
db2 => catalog db toursdb at node derby authentication server
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
6.Now you can verify connectivity to the database.
db2 => connect to sample user SAMP using SAMP

   Database Connection Information

 Database server        = Apache Derby CSS10000
 SQL authorization ID   = SAMP
 Local database alias   = SAMPLE
7.And perform the same query you did using ij in Figure 4, above.
db2 => select * from org

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
      10 Head Office        160 Corporate  New York
      15 New England         50 Eastern    Boston
      20 Mid Atlantic        10 Eastern    Washington
      38 South Atlantic      30 Eastern    Atlanta
      42 Great Lakes        100 Midwest    Chicago
      51 Plains             140 Midwest    Dallas
      66 Pacific            270 Western    San Francisco
      84 Mountain           290 Western    Denver

  8 record(s) selected.
8.Since you're done, you should disconnect from the database.
db2 => disconnect all
DB20000I  The SQL DISCONNECT command completed successfully.
9.And exit the command line processor.
db2 => quit
DB20000I  The QUIT command completed successfully.
10.If the window is still open, and you want to close it, all you have to do is:
C:\Program Files\IBM\SQLLIB\bin> exit

Note: If you choose User DSN, then the information that you define is only available to the user performing the configuration. On the other hand, if you use System DSN then the Data Source Name is a system wide value that is available to every user on the system.

Configuring ODBC

In order to access the DB2 configuration that you just defined, there is some additional ODBC configuration required.

  1. Open the Windows Control Panel, using either:
    • Start > Settings > Control Panel or
    • Start > Control Panel
  2. Select Administrative Tools, and open the ODBC Data Source Administrator:
  3. On the User DSN tab, click Add.
  4. Scroll down, and select the IBM DB2 ODBC DRIVER:
  5. Click Finish.
  6. In the IBM DB2 ODBC DRIVER - Add dialog box:
    1. Specify a Data source name (DSN) of sample.
    2. In the Database alias drop-down, select SAMPLE.
    3. Optionally specify a Description.
    4. Click OK.
  7. You can use these same steps to define a Data source name for the toursDB database as well (of course using the appropriate database alias).
  8. Click OK.

Installing Python

You were probably wondering how long it was going to take me to get to the point of getting Python set up. Well, it's finally time for that.

Table 3: Installing Python
Action TakenDescriptionComment/Result
Get and install Python for WindowsTo retrieve and install Python from the ActiveState site:
  1. Click the Download link.
  2. Optionally fill in the information, and click Next.
  3. Use the Windows MSI link to save the install executable.
  4. Scan the file with a virus checker.
  5. Using Windows Explorer, double-click on the installation program (for example: ActivePython-2.4.1-245-win32-ix86.msi).
Install Python to a reasonable location (for example: C:\Python24).
Verify your Python installationOpen a new command prompt and type:

python -V

You should see:

Python 2.4.1

A little something extra

Now that you have Python installed, you can use it write some interesting and very useful scripts. One of the commands that I find myself using all of the time on Unix™ is the which command. For those who are unfamiliar with this command, it is used to identify the program that would be executed if you were to type the command.

For example: which java would display the complete path to the java executable.

Unfortunately, the which command isn't something that is available on Windows. You could search for it on the Internet, there are probably lots of implementations of this utility that are readily available. Or, you can see what would be needed to implement it in Python.

Here is an uncommented version of that program, based upon one provided with the Python installation. The commented version is available in the download section below.

How it works

It uses the values from the PATH and PATHEXT environment variables to search for a file with the specified name that exists in one of the directories in the %PATH% that ends with one of the executable extensions specified in %PATHEXT%. If one is found, the complete path to that executable is displayed.

C:\> type C:\Python24\which.py

import sys, os
from stat import *

def msg( str ):
    sys.stderr.write( str + '\n' )

def main():
    pathlist = os.environ[ 'PATH' ].split( os.pathsep )
    pathext  = os.environ[ 'PATHEXT' ].split( os.pathsep )

    if pathlist[ 0 ] != '.' :
        pathlist.insert( 0, '.' )

    for prog in sys.argv[ 1: ] :
        try :
            for dir in pathlist :
                filename = os.path.join( dir, prog )
                for ext in pathext :
                    program = filename + ext
                    try:
                        st = os.stat( program )
                        raise Exception( program )
                    except os.error:
                        continue
            msg( prog + ': not found' )
        except Exception :
            msg( program )

if __name__ == '__main__':
    main()

Let's find out what program gets executed
when you type "java" at the command prompt.

C:\> which java
C:\WINDOWS\system32\java.exe

The missing link

At this point you have almost everything you need in order to access your Derby databases with Python scripts. The final piece that you need is a Python module that provides database support using the ODBC interface. The topic section on the Python Web site contains information about accessing databases using Python. The Database Modules link on this page includes that missing link (pun intended). The mxODBC package is a "... nearly 100% Python DB API compliant interface to databases that are accessible via the ODBC API."

Installing the mxODBC package

  1. From the eGenix.com mx Extensions for Python page, click the Download link for the Base Package.
  2. Click the Windows installer, Python 2.4 link.
  3. Save the download file (for example: egenix-mx-base-2.0.6.win32-py2.4.exe).
  4. Use your browser's back button, then select the Download link from the Commercial Package.
  5. Click the Windows installer, Python 2.4 link.
  6. Save the download file (for example: egenix-mx-commercial-2.0.7.win32-py2.4.exe).
  7. Scan the downloaded files for viruses.
  8. After a successful scan, execute each installer program.

On some systems, particularly Windows 2000 systems, you may see a warning during the installation of the mxODBC package telling you that the MDAC SDK software needs to be upgraded. If this is the case, you can use the following procedure to install the latest version of this code from Microsoft®.

  1. Access the Microsoft ODBC page.
  2. Select Downloads.
  3. Click the MDAC 2.8 link.
  4. Click the Continue button.
  5. Optionally, validate your system, and click the Continue button.
  6. Click the Download button.
  7. Save the install executable (for example: MDAC_TYP.exe) on your system.
  8. Run a virus scan of the install program.
  9. After a successful scan, execute the install program.
  10. Read the license agreement, select "I accept...", and click Next.
  11. Click Finish to begin installation.
  12. After the installation, reboot your machine.

Using Python to interactively connect to Derby

Now you can try the same thing with the Python interpreter that you did earlier using the ij utility, and the DB2 CLP. This will show you how easy it is to use Python to connect to a Derby database. Remember to start the network server first, or you may get an error message.

First, however, you'll need to know some parameters that are used in the connection request:

Table 4: Connection keyword parameters
DSNData Source Name, as you saw in Configuring ODBC above. This value identifies how the ODBC connection request will be associated with the DB2 Driver, which in turn will associate it with the Derby Network Server on the "remote" node.
UIDA valid user ID. This value will be sent to the ODBC driver, which will, based on the DB2 runtime client configuration, forward it to the Derby Network Server for authentication. Since you have not enabled user authentication within Derby, this will only be used to identify the initial SCHEMA to be used.
PWDThe password for the specified user ID.
Figure 5: Python session
C:\> python
ActivePython 2.4.1 Build 245 (ActiveState Corp.) based on
Python 2.4.1 (#65, Mar 30 2005, 09:33:37) [MSC v.1310 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import mx.ODBC
>>> import mx.ODBC.Windows
>>> db = mx.ODBC.Windows.DriverConnect( 'DSN=sample;UID=SAMP;PWD=SAMP' )
>>> cursor = db.cursor()
>>> cursor.execute('select * from org')
>>> mx.ODBC.print_resultset( cursor )
Column 1 | Column 2         | Column 3 | Column 4    | Column 5
----------------------------------------------------------------------
10       | 'Head Office'    | 160      | 'Corporate' | 'New York'
15       | 'New England'    | 50       | 'Eastern'   | 'Boston'
20       | 'Mid Atlantic'   | 10       | 'Eastern'   | 'Washington'
38       | 'South Atlantic' | 30       | 'Eastern'   | 'Atlanta'
42       | 'Great Lakes'    | 100      | 'Midwest'   | 'Chicago'
51       | 'Plains'         | 140      | 'Midwest'   | 'Dallas'
66       | 'Pacific'        | 270      | 'Western'   | 'San Francisco'
84       | 'Mountain'       | 290      | 'Western'   | 'Denver'
>>> cursor.close()
>>> db.close()
>>>

To exit the Python interpreter, press Ctrl-Z.

>>> ^Z

Now that you have verified that things work as expected, you can
easily copy these same statements that you just tested interactively
into a file, e.g., mxORGtable.py, and use the python interpreter
to execute the script with no extra effort.  For example:

C:\> type mxORGtable.py
import mx.ODBC
import mx.ODBC.Windows
db = mx.ODBC.Windows.DriverConnect( 'DSN=sample;UID=SAMP;PWD=SAMP' )
cursor = db.cursor()
cursor.execute('select * from org')
mx.ODBC.print_resultset( cursor )
cursor.close()
db.close()

C:\> mxORGtable
Column 1 | Column 2         | Column 3 | Column 4    | Column 5
----------------------------------------------------------------------
10       | 'Head Office'    | 160      | 'Corporate' | 'New York'
15       | 'New England'    | 50       | 'Eastern'   | 'Boston'
20       | 'Mid Atlantic'   | 10       | 'Eastern'   | 'Washington'
38       | 'South Atlantic' | 30       | 'Eastern'   | 'Atlanta'
42       | 'Great Lakes'    | 100      | 'Midwest'   | 'Chicago'
51       | 'Plains'         | 140      | 'Midwest'   | 'Dallas'
66       | 'Pacific'        | 270      | 'Western'   | 'San Francisco'
84       | 'Mountain'       | 290      | 'Western'   | 'Denver'

C:\>

Which is exactly where you want to be.

Summary

In this article, I have shown that with a bit of configuration work, you can set up a Windows environment that allows us to access Apache Derby, which is an easy to use, open-source relational database. In addition, you can develop high-level, dynamic, object-oriented programs in Python that allow us to connect to and manipulate a Derby.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=83550
ArticleTitle=Connect to Apache Derby databases using Python
publish-date=05192005