SQLAlchemy

Introduction

SQLAlchemy is a Python package for abstracting connections to databases. SQLAlchemy is so good that I don't see any reason to learn anything else, period. SQLAlchemy competes with SQLObject and every DB module out there, even though it uses the DB modules.

Overview

Big picture stuff here.

• Engine: The engine is the connection to an actual, living, breathing database. You can skip the metadata and talk to the databse directly if you really want to (but you don't.)
• Metadata: The metadata is the description of the tables in a database that may not exist yet. There is enough info here to create any type of query you can imagine given the schema. To run the query, you need a session.
• Session: A session is where an engine and a metadata meet. This is where you can start doing real work.

Recommendations:

• Read the docs! It will be the best, most valuable read of your life. At the end, not only will you know SQLAlchemy, but you'll be miles ahead of the vast majority of other developers who write code that interacts with DB.
• Kiss SQL goodbye. It was nice knowing you, but it's time to move on to new relationships. (Get the pun there?)
• Let SQLAlchemy make your tables for you. It does a good job at this. Let it do it.
• ORM is your friend. Forget all the bad experiences you've had with other ORM systems. This one actually works and you don't need to fall back to the table abstractions.
• Keep the engine out of your app. Put it in only when you have a real instance running, and then only when configured by parameters. But with SQLAlchemy, this is not only easy, but natural.
• Don't write code that only works on one DB. Trust me, there are times when a man has to use SQLite, and there are times when they have to use PostgreSQL. Being able to jump between the two on a whim makes life easy.
• Write more unit tests with your spare time.

Open questions:

• How do you do upgrades? My answer right now: Don't. If you need to rework a table, then make a new table and then write code that handles both the old and the new tables simultaneously. Don't worry if you have version numbers in your table names or even in your module names.