If SQLAlchemy could only do this, it still would be a nice library, simplifying your queries. But the real power of SQLAlchemy occurs when you define relationships between tables. For example, let's assume that I have an Appointments table, indicating when I'm meeting with various people:
CREATE TABLE Appointments ( id SERIAL PRIMARY KEY, person_id INTEGER NOT NULL REFERENCES People, meeting_at TIMESTAMP NOT NULL, notes TEXT );
Let's also add some appointments:
INSERT INTO Appointments (person_id, meeting_at, notes) VALUES (2, '1-jan-2013', 'New Year meeting'), (2, '1-feb-2013', 'Monthly update');
Now I need to create a Python class that represents appointments:
class Appointment(Base): __tablename__ = 'appointments' id = Column(Integer, primary_key=True) person_id = Column(Integer) meeting_at = Column(DateTime) notes = Column(String)
Now, this class will work just fine. However, there's no relationship, according to Python, between the Person class and the Appointment class. To make this work, you'll need to change each of these table definitions. In the case of Appointment, you'll need to indicate that the person_id column doesn't just contain an integer, but that it is a foreign key that points to the "id" column on the People table:
person_id = Column(Integer, ForeignKey('people.id'))
On the Person table, you'll need to add a line to the class attributes, after describing all of the columns:
appointments = relationship("Appointment", backref="person")
Thanks to these two lines, you get an "appointments" attribute on your Person model. But thanks to the "backref" parameter, you also get a "person" reference on the appointment. This means you can do something like this:
for a in session.query(Appointment): print a.person for p in session.query(Person): print p.appointments
Note that the assumption is that you'll have multiple appointments per person, representing a one-to-many relationship.
Let's say, however, that you want to have a many-to-many relationship
between people and appointments, such that you can meet with more than
one person at a time, and you can have more than one appointment with a
particular person. In order to do that, you need to modify your
database table and code somewhat, adding a third (association) table.
SQLAlchemy makes it easy to do that. Although I don't have space to show
it here, the basic idea is that you create the third table, and you
relationship() function to indicate that there is a secondary
relationship between the class and the join table.
SQLAlchemy is packed with features. In addition to the introductory examples I showed here, it handles everything from joins to connection pooling, to dynamically calculated column values, to creating Python classes based on an existing database table. There is no doubt that it's a powerful system, one that I expect to use in some of the Python projects on which I work.
That said, I found SQLAlchemy to be a bit overwhelming for the newcomer. Perhaps it's because I have long used the Active Record model in Ruby, which has minimal configuration and syntax, but I found the syntax for SQLAlchemy to be a bit overly verbose. Then again, Python has long preferred things be explicit, and there's no doubt that SQLAlchemy provides a clear and explicit ORM, without much magic and with obvious ramifications for every function call and parameter.
The other thing that might throw off newcomers to SQLAlchemy is that the documentation is complete, but not particularly friendly. Once you start to use the system, I expect that you (like me) will be able to understand the documentation and make good use of it. But I found that even the tutorial documents were a bit formal, trying to tell you too much before moving ahead with actual code. Hopefully, this article can help some more people become interested in SQLAlchemy.
In conclusion, SQLAlchemy is a great Python module, one that deserves its sterling reputation and broad popularity. If you're interested in working with databases from Python programs, you definitely should take a look at SQLAlchemy.
The home page for SQLAlchemy is http://sqlalchemy.org, and the documentation is at http://docs.sqlalchemy.org. The Python language is at http://python.org. I suggest that you read through the introductory section and then the ORM documentation, rather than look at the document sequentially.
There are a number of on-line tutorials for SQLAlchemy. Two that I enjoyed, which are freely available to the public, are https://www.youtube.com/watch?v=399c-ycBvo4 and https://www.youtube.com/watch?v=PKAdehPHOMo.
Finally, Rick Copeland's book, Essential SQLAlchemy, published by O'Reilly in 2008, is a good introduction, particularly if you look at the ORM section. The rest is a bit dry and technical, even if the examples are well written. This book is not completely up to date, and there are several items in it that reflect the fact that it was published several years ago. Nevertheless, having an additional reference can be quite handy and can provide examples for certain features that aren't otherwise obvious.
Reuven M. Lerner, Linux Journal Senior Columnist, a longtime Web developer, consultant and trainer, is completing his PhD in learning sciences at Northwestern University.
Pick up any e-commerce web or mobile app today, and you’ll be holding a mashup of interconnected applications and services from a variety of different providers. For instance, when you connect to Amazon’s e-commerce app, cookies, tags and pixels that are monitored by solutions like Exact Target, BazaarVoice, Bing, Shopzilla, Liveramp and Google Tag Manager track every action you take. You’re presented with special offers and coupons based on your viewing and buying patterns. If you find something you want for your birthday, a third party manages your wish list, which you can share through multiple social- media outlets or email to a friend. When you select something to buy, you find yourself presented with similar items as kind suggestions. And when you finally check out, you’re offered the ability to pay with promo codes, gifts cards, PayPal or a variety of credit cards.Get the Guide
|Free Today: September Issue of Linux Journal (Retail value: $5.99)||Sep 27, 2016|
|nginx||Sep 27, 2016|
|Epiq Solutions' Sidekiq M.2||Sep 26, 2016|
|Nativ Disc||Sep 23, 2016|
|Android Browser Security--What You Haven't Been Told||Sep 22, 2016|
|The Many Paths to a Solution||Sep 21, 2016|
- Free Today: September Issue of Linux Journal (Retail value: $5.99)
- Android Browser Security--What You Haven't Been Told
- Readers' Choice Awards 2013
- Epiq Solutions' Sidekiq M.2
- The Many Paths to a Solution
- Nativ Disc
- Downloading an Entire Web Site with wget
- Securing the Programmer
- Returning Values from Bash Functions