Hosted on
Generating Python Sources With SqlJaTor
Since release 0.9 SqlJaTor has been refactored to make it possible to generate source files other than in Java. Python with cxOracle is the first language/driver pair supported. This release introduces also the pgsql/postgresql support for Python.
This page will explain how to use SqlJaTor, and the source files produced with it.
In this example you will set up your environment to use sqljator, create a little table on Oracle and generate a pyton source that queries this table. The query will have one input parameter. Some notes have been added also for postgresql users.
Requirements
SqlJaTor is a Java program, to execute it you must first install the JDK or JRE (at least release 1.5) an Apache Ant (at least release 1.7). Once installed them download the SPWrapper distribution zip an extract it somewhere on your hard disk.
You must also download a JDBC driver for your database. If you are using Oracle and have installed its client on your pc you should find it at this path c:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar. The postgresql jdbc driver is an optional download, its name is something like postgresql-8.2-504.jdbc3.jar. Download it from jdbc.postgresql.org
SqlJaTor is executable as an Ant extension, we will prepare an ant build script to transalte a simple SQL query into a Python source. Before starting Ant it will be necessary to set up correctly the Java environment.
First of all check if Java is correctly executable from the command line
C:\Documents and Settings\giovannibricconi>java -version java version "1.6.0_02" Java(TM) SE Runtime Environment (build 1.6.0_02-b06) Java HotSpot(TM) Client VM (build 1.6.0_02-b06, mixed mode)
If the version does not appers set correcly the execution path, example:
C:\Documents and Settings\giovannibricconi>set PATH=c:\Programmi\Java\jdk1.6.0_02\bin;%PATH%
Now let's add Ant to the PATH
C:\Documents and Settings\giovannibricconi>set PATH=c:\Programmi\ant1.7\bin;%PATH% C:\Documents and Settings\giovannibricconi>ant.bat Buildfile: build.xml does not exist! Build failed
Now ant starts but it complaints that it is missing a buil file to execute. Before writing it let's connect to the database and create a simple table and populate it with some test data. The following sql file is the python.sql provided in the documentation
create table simple(
person varchar2(80) not null,
boss varchar2(80),
salary number not null)
/
insert into simple(person,boss,salary)
values ('hug','bob',150)
/
insert into simple(person,boss,salary)
values ('bob','sara',250)
/
insert into simple(person,boss,salary)
values ('sara',null,350)
/
insert into simple(person,boss,salary)
values ('eve','sara',220)
/
commit
/
quit
/
To run this with postgresql simply replace varchar2 with varchar and niumber with decimal, then execute each statement and commit it with pgadminIII or similar tools.
To execute the script run the following command:
C:\Documents and Settings\giovannibricconi>sqlplus gio/facile@orcl @c:\gio\work\distribution\doc\python.sql SQL*Plus: Release 10.2.0.1.0 - Production on Mer Mar 5 15:05:40 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connesso a: Personal Oracle Database 10g Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Tabella creata. Creata 1 riga. Creata 1 riga. Creata 1 riga. Creata 1 riga. Commit completato. Disconnesso da Personal Oracle Database 10g Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Now that we have a table we can create a python source to find out the boss salaries in descending order. We will skip out all the bosses with a salary lower than a minimum. We will put this query inside an ant script so that SqlJaTor will create a python source for it. This is the python.xml file provided in the documentation
<?xml version='1.0'?>
<project name="simplebuild" default="main" basedir=".">
<path id="sqljator-path">
<pathelement path="c:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar" />
<pathelement location="c:\gio\work\distribution\spwrapper.jar" />
<pathelement path="C:\Programmi\PostgresqlJdbc\postgresql-8.2-504.jdbc3.jar" />
</path>
<property name='oracle-conn-string' value='jdbc:oracle:thin:@localhost:1521:orcl' />
<property name='pg-conn-string' value='jdbc:postgresql:demo' />
<taskdef name="sqljator"
classname="net.sourceforge.spwrapper.ant.SqlJaTorTask"
classpathref="sqljator-path" />
<target name='main'>
<sqljator language='python'
connectionstring="${oracle-conn-string}"
password="facile"
username="gio"
targetdirectory="."
filename='boss'>
<param name='minsal' constant='100' />
<![CDATA[select
from simple s1
where exists (select *
from simple s2
where s1.person=s2.boss)
and s1.salary>100
order by salary desc]]>
</sqljator>
</target>
</project>
This file first of all sets up the correct classpath to execute SqlJaTor. The classpath includes the JDBC driver to connecto to the database and the SPWrapper jar. Then the target main is defined; the target contains the quey and gives a name to a parameter. This name will be inserted as member of the generated python class. Parameters are automatically detected from the costants that appears in the SQL statement. To prenvent the constant 1 and 40 in "substr(person,1,40)" becoming parameters they have been protected inside a <const% section. To learn about parameters, datatypes and ovverriding them please refer to the SqlJaTor documentation.
If you whant to run this file for postgresql simply change the connectionstring to something like jdbc:postgresql:demo and replate the path to ojdbc.jar with the postgresql jdbc driver.
To execute it simply run this command. The warning messages that will appears are due to some missing jdbc driver. Since we are not using those databases we will simply ignore them.
C:\Documents and Settings\giovannibricconi>ant.bat -f c:\gio\work\distribution\doc\python.xml Buildfile: c:\gio\work\distribution\doc\python.xml main: [sqljator] 5-mar-2008 15.39.37 net.sourceforge.spwrapper.Introspector registerDriver [sqljator] AVVERTENZA: Driver not in classpath com.microsoft.sqlserver.jdbc.SQLServerDriver BUILD SUCCESSFUL Total time: 4 seconds
SqlJaTor has been execute and has produced a new python source, boss.py. Let's examine it.
NOTE the release 0.9 of SqlJaTor was too much Java oriented an created a lot of useles getter and setters methods. With the release 0.9.1 this behaviour has changed, the classes create now should be much more Python oriented.
# -*- coding: utf-8 -*-
#common imports
import logging
#driver specific module imports
import cx_Oracle
#inherits object to be compatible with the new typesystem
class boss(object):
def __init__(self,**args):
'''All the parameters are routed to the module "connect" method
of the dbapi module, unless the spwrapperConn parameter is provided.
In this later case the connection is not created but the one passed is used.
'''
if args.has_key('spwrapperConn'):
self.connection=args['spwrapperConn']
else:
self.connection = cx_Oracle.connect.__call__(**args)
self.autoclose=self.statementAutoclose=True
self.autoCommit=True
self.log = logging.getLogger('boss')
self.minsal=None
self.NAME=None
#common methods and settings
def get_autoclose(self):
if self.autoclose == None :
return True
return self.autoclose
def get_statementAutoclose(self):
if self.statementAutoclose == None :
return True
return self.statementAutoclose
def get_updateCount(self):
if self.updateCount==None:
return 0
return self.updateCount
def tryClose(self):
'safely closes cursor and connection'
try :
if self.statementAutoclose:
self.statement.close()
except:
self.log.warning("error while closing the cursor")
try :
if self.autoclose:
if self.autoCommit:
self.connection.commit()
self.connection.close()
except:
self.log.warning("error while committing or closing the connection")
def was_NAME_null(self):
return self.NAME==None
def run(self, arg_minsal):
self.minsal=arg_minsal
self.runCore()
def runCore(self):
self.statement=self.connection.cursor()
try:
params = {'minsal':self.minsal}
self.statement.execute('''select substr(s1.person,1,40) as name
from simple s1
where exists (select *
from simple s2 where s1.person=s2.boss)
and s1.salary>:minsal
order by salary desc ''', params )
self.updateCount = self.statement.rowcount
if False:
self.tryClose()
except:
self.log.error( "error executing statement" )
self.tryClose()
raise
def __iter__(self):
return self
def next(self):
'''read a new row from the cursor. if the result set is empty
closes the connection and the statement according to user
preferences'''
try:
row = self.statement.fetchone()
if row != None:
self.NAME=row[1-1]
return self
else:
self.tryClose()
except:
self.tryClose()
raise
raise StopIteration
def fromParams(user,password,db):
'''factory method with default values for all the dbapi2 drivers'''
return boss(user=user,password=password,dsn=db)
def fromConnection(connection):
'''factory method to be used when a connection already exists
remember to disable autoclose if you need it later'''
return boss(spwrapperConn=connection)
Quite a lot of source code for a simple query... a new "boss" class has been created, its constructor can be initialized with various parameters. If the argument "spwrapperConn" is provided then it must hold a valid open connection to the database. Otherwise all the provided parameters are routed to the connect method of the cx_Oracle module; in this way you can provide all the arguments you need.
All the member of the class are initialized, input and output parameters. The object is initialized to have an open connection and so that it will close its connection when all the data has been fetched.
Then some common methods are defined, they are documented in the source file. After these metods a run and runCore methods appears. The first is define to receive all the input parameters at once, and to start the query. The runCore instead expects that all the parameters have been set up before invoking it.
Below runCore, __iter__ and next are defined, these implements the usual Python iterator pattern so that you can iterate over the rows fetched from the database.
At the end of the file two methods are generated, these will be standard whatever database you choose, and will let you instantiate the class giving username and password or an open connection
Now we are ready to go on an use this class
C:\Documents and Settings\giovannibricconi>copy c:\gio\work\distribution\doc\boss.py .
1 file copiati.
C:\Documents and Settings\giovannibricconi>c:\Programmi\python2.5\python.exe
Python 2.5.1 (r251:54863, Apr 18 2007, 08:51:08) [MSC v.1310 32 bit (Intel)] on
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import boss
>>> b=boss.fromParams("gio","facile","orcl")
>>> b.run(300)
>>> for i in b:
... print b.NAME
...
sara
>>>
Here is the same example for postgresql:
>>> import boss
>>> b=boss.fromParams('gio','facile','localhost:demo')
>>> b.run(300)
>>> for i in b:
... print b.name
...
sara
As you can see the object memeber "name" has a different case for the two databases, to solve this simply tweak the as clause in the SQL statement.
Supported Datatypes
Refer to the Supported Datatypes section for details about the type you can access with SqlJaTor.