Foreign Key: Postgres
Explore how to handle foreign key constraints and back-population in Postgres using SQLAlchemy. Understand differences with SQLite, manage integrity errors properly, and learn how lazy loading affects ORM objects. This lesson helps you work effectively with database relationships and maintain data integrity in your applications.
We'll cover the following...
SQLAlchemy: Postgres vs. SQLite
When running on Postgres, the pragma command is not only unnecessary, but it also raises an error through SQLAlchemy. This is a shortcoming of SQLAlchemy. It’s supposed to provide a seamless interface that works across databases. Mostly, it is successful, but here the illusion breaks down. One reasonable option is to keep track of whether the connection is SQLite or Postgres, and run the pragma command only when it is SQLite. Another option is to always run the pragma but put it in a try-except clause and ignore any ProgrammingError exception.
Another difference between SQLite and Postgres is the explanatory text given when a foreign constraint violation is attempted. To compare in Postgres, we drop the pragma line and use the Postgres connection string.
connection_string = "postgresql://localhost:5432/books"
The new outcome still involves the IntegrityError, but the explanatory ...