Python SQLAlchemy ORM

A couple of days ago I implemented some Python classes which were supposed to map tables from an Oracle DB. In Java, we can use Hibernate for achieving this. In Python, I chose to use SQLAlchemy framework. In the beginning, I had found the syntax a little confusing but after I understood it everything went smoothly. In the following paragraphs, I'll show how a one-to-may relation can be implemented and also how to use composite keys in SQLAlchemy.
I assume that classes are implemented in da package.

1. In __init__.py file I instantiate sqlalchemy dependency objects.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

BaseEntity = declarative_base()
strDbURL = "oracle://db_tes:xxx@xe"
objDbEngine = create_engine(strDbURL, echo=False)

Session = sessionmaker(bind=objDbEngine)
Session.configure(bind=objDbEngine)

def getConnection():
return Session()

In this moment, we can start implementing the entities. Every entity can extend BaseEntity. Every time we need a database connection we use getConnection() method. A SQLAlchemy session is similar to a Hibernate session.

2. I define User class which has a composite chei(username and password ---- don't do this in practice: you can safely choose to use serial number as primary key). In this example, I want to show how composite keys are implemented.

from da import BaseEntity
from sqlalchemy import Column,Integer,String,Numeric,DateTime,ForeignKey,Sequence
from sqlalchemy.orm import relation, backref

class User(BaseEntity):
username = Column("username", String, primary_key=True)
password = Column("password", String, primary_key=True)
descriere = Column("descriere", String)

def __init__(self, strUsername, strPassword, strDesc):
self.username = strUsername
self.password = strPassword
self.descriere = strDesc

3. I implement the Address class.

from da import BaseEntity
from sqlalchemy import Column,Integer,String,Numeric,DateTime,ForeignKey,Sequence
from sqlalchemy.orm import relation, backref

class Adress(BaseEntity):
id = Column("id", Integer, Sequence("seq_xxx_id"), primary_key=True)
fk_username = Column("fk_username", String, ForeignKey("utilizatori.username"))
fk_password = Column("fk_password", String, ForeignKey("utilizatori.password"))
street = Column("street", String)
number = Column("number", String)

user = relation("User",
foreign_keys=[fk_username, fk_password],
primaryjoin="User.username == Address.fk_username and "
"User.password == Address.fk_password",
backref="adresses")

def __init__(self, strUser, strPasswd, strStreet, strNumber):
self.fk_username = strUser
self.fk_password = strPassword
self.street = strStreet
self.number = strNumber

Comments: in this example, I try to show the power and flexibility provided by SQLAlchemy. First of all, in every SQLAlchemy entity we'll define the mapped table structure to attributes of the class. Using relation function, we can implement links(foreign keys) to other entities:

• one-to-one relation
• one-to-many relation
• many-to-many relation

We can also use the backref keyword argument to indicate that we want a reference at the other end of relation.

4. After we implemented all the entities, we have to use them. In the folloing example, I show several lines of code that prove the entities functionality:

import da
from da import User, Address

# I select all user from the database
objSes = da.getConnection()
for objUsr, in objSes.query(User).all():
print(objUsr.addresses)

# filter that returns the user with a specific address
for objUsr in objSes.query(User, Address).filter(User.username == Address.fk_username and User.password == Address.fk_password).filter(Address.id == 1).all():
print(objUsr.username)

In conclusion, SQLAlchemy easily maps relational logic to object logic(the scope of any ORM) but it doesn't require a configuration file or annotations(like Hibernate). In addition, writting a query is extremely easy. What was not covered in this tutorial but it is intuitive is entity saving(persist). We use add method of an opened session for adding and updating an entity.