What is SQLAlchemy
SQLAlchemy is an open-source Python Database toolkit, which provides the following functionality:
- It maps relational databases into objects
- It manages an applications database connections
- It can create/alter a database layout if it is allowed to
The most powerful feature of SQLAlchemy is the first point: given a table description, it maps the data in tables into classes of objects, where each instance of an object is a row in the table, and can be worked with like a class or structure in code.
Example: Given a table called “Users”, with a FirstName and LastName column. Once the columns are described in the Python code, to add a row to the users table might look like this: joebruin = User() joebruin.FirstName = “Joe” joebruin.LastName = “Bruin” joebruin.save()
Given a table called “Addresses” with two columns called “Street” and “City”. It’s related to the “Users” table above using a column named User_Id, though SQLAlchemy will handle it for you. To add a row to the address table, and link it to the Users table, it might look like this: uclaaddress = Address() uclaaddress.Street = “405 Hilgard Ave.” uclaadress.City = “Los Angeles” joebruin.addresses.append(uclaaddress) uclaaddress.save() joebruin.save()
Note: Even though the id column is in the Addresses table, SQLAlchemy exposes it a collection on the Users table because of the relationship.
Some of the interesting features are things like eager-/lazy-loading, poly-morphic table layouts (in other words, having a table where the rows have a “type” column, then breaking the data elements into subclasses based on that column), caching, and database-platform independence.
More Reading: A Critical Examination of SQLAlchemy Performance