SQLAlchemy

Relationships

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 use the relationship() function to indicate that there is a secondary relationship between the class and the join table.

Conclusion

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.

______________________

Reuven M. Lerner, Linux Journal Senior Columnist, a longtime Web developer, consultant and trainer, is completing his PhD in learning sciences at Northwestern University.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

I collected so many

Laila's picture

I collected so many interesting things from your site especially its discussion. From the tons of comments on your posts, I guess I am not the only one having all the enjoyment here. Keep up the good work.
voyance serieuse par mail

Reply to comment | Linux Journal

Chauffage's picture

plafond rénover sable bétoneuse pompe à chaleur air air chauffage chauffage pompe à chaleur air eau pompe à chaleur piscine pac air
air monter parler ils la le camion pompes à chaleur devis
pompe à chaleur pompe à chaleur air air climatisation
il entreprit pompe à chaleur prix pompe à chaleur devis pompe à chaleur climatisation vous

Reply to comment | Linux Journal

climatisation réversible's picture

les travaux nous sommes climatisation portable climatisateur le soleil installateur climatisation
installateur climatisation climatiseur
gainable climatiseur pas cher entretien climatisation climatisation maison
climatiseur monobloc climatiseur gainable pierre travailler bien climatiseur
mobile sans evacuation les travaux il a dit prix clim reversible

Is SQL language something to avoid?

ΝΤΕΝΤΕΚΤΙΒ's picture

I totally agree that many developers find SQL quite hard to learn language. The usage of Views, Stored Procedures and Functions is a way to isolate a Data Model from your code / view model.

The main problem is that the effort needed in this case is higher than just embedding the SQL language inside our code.

On the other hand, most of the developers are completely anaware of these features and many times I have heard the complain of cross platform problems that may caused when using the RDBS spedific features for these powerfull abstration mechanisms: if I write Stored Procedures it will be difficult to change my RDBMS in the future - a case that never happens for many real world systems anyway.

Finally, the Stored Procedures are the most powerfull tool when you need a PRODUCTION LEVEL with HIGH PERFORMANCE system as you can minimize the round trips from Web or Application Server (that executes your business logic in a 3 tier architecture) and have total control over DB access from your application.

What is always the case in programming is that if you want to produce high quality production level (pro) products you need to work harder!

Regards,

ΝΤΕΤΕΚΤΙΒ

I find that I often want to

Anonymous's picture

I find that I often want to keep the articles in Linux Journal for later reference. There should be a link to download them as a pdf file.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState