SQLAlchemy

The Tropofy Platform leverages the SQLAlchemy toolkit for interacting with databases using Python. SQLAlchemy has several distinct areas of functionality, however the Tropofy Platform predominantly uses the Object Relational Mapper (ORM). The ORM associates user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. A simple example has been provided below,

Table Definition

SQLAlchemy user defined Python class:

class User(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

roughly maps to the following SQL statement:

CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    password VARCHAR,
    PRIMARY KEY (id)
)

Insert, Update, and Delete

The SQLAlchemy ORM transparently synchronizes all changes in state between objects and their related rows. This includes, creating objects, deleting objects, and updating objects. A simple example has been provided below,

SQLAlchemy object creation of User:

ed_user = User('ed', 'Ed Jones', 'edspassword')
session.add(ed_user)

roughly maps to the following SQL statement:

INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('ed', 'Ed Jones', 'edspassword')

Note

Use of session.add is not required in a Tropofy app. All changes to database objects are automatically commited to the database at the end of each server request.

Querying

In addition, the SQLAlchemy ORM provides a system for expressing database queries in terms of the user defined classes. This removes the need to write and maintain complex SQL statements. A simple example has been provided below,

SQLAlchemy ORM query, list of User objects:

for instance in session.query(User).order_by(User.id):
    print instance.name, instance.fullname

roughly maps to the following SQL statement:

SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users ORDER BY users.id

Compared to traditional techniques of exchange between an object-oriented language and a relational database, the ORM helps reduce the development time when writting applications. For more information on SQLAlchemy, please refer to their documentation.

Note

Use of session.query is not required in a Tropofy app. Instead use data_set.query. This ensures all queries query only objects in the active data_set.