Servlet NewJavaServlet Test

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
250 
 
Listing 12-23. 
>>> import sqlalchemy 
>>> sqlalchemy.__version__ 
'0.6beta1' 
>>> 
After we’ve ensured that the installation was a success, it is time to begin working with SqlAlchemy 
via the terminal. However, we have one step left before we can begin. Jython uses zxJDBC to implement 
the Python database API in Java. The end result is that most of the dialects that are available for use with 
SqlAlchemy will not work with Jython out of the box. This is because the dialects need to be rewritten to 
implement zxJDBC. At the time of this writing, we could only find one completed dialect, zxoracle, that 
was rewritten to use zxJDBC, and we’ll be showing you some examples based upon zxoracle in the next 
sections. However, other dialects are in the works including SQL Server and MySQL. The bad news is 
that SqlAlchemy will not yet work with every database available, on the other hand, Oracle is a very good 
start and implementing a new dialect is not very difficult. You can find the zxoracle.py dialect included 
in the source for this book. Browse through it and you will find that it may not be too difficult to 
implement a similar dialect for the database of your choice. You can either place zxoracle somewhere on 
your Jython path, or place it into the Lib directory in your Jython installation. 
Lastly, we will need to ensure that our database JDBC driver is somewhere on our path so that 
Jython can access it. Once you’ve performed the procedures included in this section, start up Jython and 
practice some basic SqlAlchemy using the information from the next couple of sections. 
Using SqlAlchemy 
We can work directly with SqlAlchemy via the terminal or command line. There is a relatively basic set of 
steps you’ll need to follow in order to work with it. First, import the necessary modules for the tasks you 
plan to perform. Second, create an engine to use while accessing your database. Third, create your 
database tables if you have not yet done so, and map them to Python classes using a SqlAlchemy 
mapper. Lastly, begin to work with the database. 
Now there are a couple of different ways to do things in this technology, just like any other. For 
instance, you can either follow a very granular process for table creation, class creation, and mapping 
that involves separate steps for each, or you can use what is known as a declarative procedure and 
perform all of these tasks at the same time. We will show you how to do each of these in this chapter, 
along with performing basic database activities using SqlAlchemy. If you are new to SqlAlchemy, we 
suggest reading through this section and then going to sqlalchemy.org and reading through some of the 
large library of documentation available there. However, if you’re already familiar with SqlAlchemy, you 
can move on if you wish because the rest of this section is a basic tutorial of the ORM solution itself. 
Our first step is to create an engine that can be used with our database. Once we’ve got an engine 
created then we can begin to perform database tasks making use of it. Type the following lines of code 
(Listing 12-24) in your terminal, replacing database specific information with the details of your 
development database. 
Listing 12-24. Creating a Database Engine and Performing Database Tasks 
>>> import zxoracle 
>>> from sqlalchemy import create_engine 
>>> db = create_engine('zxoracle://schema:password@hostname:port/database) 
 
Next, we’ll create the metadata that is necessary to create our database table using SqlAlchemy 
(Listing 12-25). You can create one or more tables via metadata, and they are not actually created until 
after the metadata is applied to your database engine using a create_all() call on the metadata. In this 
www.it-ebooks.info

---

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
251 
 
example, we are going to walk you through the creation of a table named Player that will be used in an 
application example in the next section. 
Listing 12-25. Creating a Database Table 
>>>player = Table('player', metadata, 
...     Column('id', Integer, primary_key=True), 
...     Column('first', String(50)), 
...     Column('last', String(50)), 
...     Column('position', String(30))) 
>>> metadata.create_all(engine) 
Our table should now exist in the database and the next step is to create a Python class to use for 
accessing this table. See Listing 12-26. 
Listing 12-26. Creating a Python Class to Access a Database Table 
class Player(object): 
    def __init__(self, first, last, position): 
        self.first = first 
        self.last = last 
        self.position = position 
 
    def __repr__(self): 
        return "
" %(self.first, self.last, self.position) 
 
The next step is to create a mapper to correlate the Player python object and the player database 
table. To do this, we use the mapper() function to create a new Mapper object binding the class and 
table together (Listing 12-27). The mapper function then stores the object away for future reference. 
Listing 12-27. Create a Mapper to Correlate the Python Object and the Database Table 
>>> from sqlalchemy.orm import mapper 
>>> mapper(Player, player) 
 
 
Creating the mapper is the last step in the process of setting up the environment to work with our 
table. Now, let’s go back and take a quick look at performing all of these steps in an easier way. If we 
want to create a table, class, and mapper all at once, then we can do this declaratively. Please note that 
with the Oracle dialect, we need to use a sequence to generate the auto-incremented id column for the 
table. To do so, import the sqlalchemy.schema.Sequence object and pass it to the id column when 
creating. You must ensure that you’ve manually created this sequence in your Oracle database or this 
will not work. See Listing 12-28. 
Listing 12-28. Creating a Table, Class and Mapper at Once 
SQL> create sequence id_seq 
  2  start with 1 
  3  increment by 1; 
 
Sequence created. 
www.it-ebooks.info

---