It wasn’t literally this week, but I recently wrote my first Python command at work. The command was part of a slack integration which allows non-developers to safely edit a table in our database to alter functionality for their clients. Since we were dealing with a DB, we needed a way to run SQL commands, which gave me a bit of anxiety because I’m not great at Python or SQL. Fortunately for me, we use SQLAlchemy at work, which is a toolkit that makes updating our databases with Python a breeze via ORM. I had learned about one ORM, ActiveRecord, in boot camp, but never really understood it at the time. So this week, we’ll examine SQLAlchemy and see if we can get some idea of how it works.
What is an ORM?
ORM stands for Object Relational Mapper. They are libraries that execute the principle of object-relational mapping which associates (or maps) databases tables to objects. What exactly does that mean? If I had a School database that contained a Students table, I might try to get information on all of my students by writing this SQL query:
SELECT * FROM students;And if I wanted to find a certain student, I could refine my search:
SELECT * FROM students WHERE id=13;Students take classes, so if I want to know all the classes that student 13 is taking…
SELECT * FROM classes WHERE student_id=13;And if I want to assign this student a new class:
INSERT INTO classes (student_id, name, abbreviation) VAUES (13, 'Biology 301', 'BI301');Things are getting a little tricky and, as our needs change, our queries will become more complex. Add into the mix that we should consider efficiency when programming queries and things become even more difficult. If you’re like me, you’re probably a lot more comfortable avoiding the SQL and writing something like this:
students.13.classes.append({'name': 'Biology 301', 'abbreviation': 'BI301'})It’s important to understand that table fields are not part of objects. They are scalar values that don’t carry the same type of relationship under the hood. But through object-relational mapping, we can transform these relationships into a language that’s easier to understand. Even better, we can count on an ORM to perform efficient queries and provide helpful methods to simplify our database processes. How does it work?
SQLAlchemy
The SQLAlchemy documentation declines to simply describe the platform as an ORM, instead opting to classify it first as a Python SQL toolkit. SQLAlchemy distinguishes itself by actually limiting abstraction. Again referring to the documentation:
SQLAlchemy’s overall approach to these problems is entirely different from that of most other SQL / ORM tools, rooted in a so-called complimentarity- oriented approach; instead of hiding away SQL and object relational details behind a wall of automation, all processes are fully exposed within a series of composable, transparent tools.
Another way of putting this is to say that we can use the SQLAlchemy toolkit to create an ORM interface, but it’ll be our responsibility to make certain decisions that other ORMs would take for granted. In the SQLAlchemy chapter of The Architecture of Open Source Applications, this separation of responsibilities is referred to as The Core/ORM Dichotomy. While the Core includes the basic functions of Python-DB interaction and schema management, the ORM built on top of it is fully customizable, giving the programmer more power and autonomy.
Here’s an example of this custom mapping at work from The Architecture of Open Source Applications:
user_table = Table("user", metadata,
Column('id', Integer, primary_key=True),
)class User(object):
passmapper(User, user_table, properties={
'related':relationship(Address)
})
The first action is the creation of a user_table, which is a table we are building with SQLAlchemy syntax. SQLAlchemy offers Table and Column constructs on the Core side with the intention being to be able to build a schema without worrying about object-relational concepts. This introduces a separation of concerns and also gives the developer the opportunity to choose their own ORM if they desire.
Table and Column models fall under the scope of another model: MetaData. As far as I can tell from the documentation and this Stack Overflow response, the MetaData class helps define these models, potentially providing information about how they are able to connect to an actual SQL database. Finally, we see that Table itself is provided with a Column construct which looks strikingly similar to a SQL command. We’re adding a column called ‘id’, whose data type is integer and is our primary key.
While user_class is the simple definition of our table, User provides the ORM power to create and assign special features and functionality. The example uses pass to indicate that this class can be populated with whatever methods or attributes we choose. Object is passed in as an argument to show that User may inherit attributes from a superclass.
If we’re opting for SQLAlchemy’s ORM functionality, User is going to be the object that we invoke in our code. User gains the functionality of user_table not through traditional inheritance, but through the use of a mapper function, which can also help relate User to another class, Address, which has not been defined in this example.
The example uses Classical mapping to better illustrate the steps SQLAlchemy takes to map a defined class to a Table object, but we’re more likely to see Declarative syntax used in modern applications. Here’s another example from the documentation:
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)SQLAlchemy’s Declarative extension runs a series of operations each time a new class is declared to automatically generate a Table object. Upon initialization, the User class is assigned an id attribute, but the abstracted mapper function patches its own id attribute, that of a Column which has been added to a Table, onto the class.
The Eternal Struggle
The end result of this mapping can be the beauty of an ORM. If we write an add method for our User class, we can create a new row as easily as:
User.add(13)This happened to be a shorter blog entry because it was not easy understanding the building blocks behind this powerful framework, especially since it boasts so much customization. I considered changing topics several times as I worked through the dense documentation. And maybe I would have learned more or understood better if I had considered something more within my scope of understanding, but it’s also important to work through difficult problems when they arise. Though this likely won’t serve as the ideal Beginner’s Guide to SQLAlchemy, it was a helpful exercise in the models behind complex abstractions.
Sources
- Object-relational mapping on Wikipedia
- SQLAlchemy.org
- How does Base.metadata work in Sqlalchemy? on Stack Overflow