SQLAlchemy can be downloaded from http://www.sqlalchemy.org/. Only the 0.6 version and higher is supported on Python 3, and at the time of writing, the only underlying databases that are supported are SQLite and PostgresSQL.

This isn't a huge deal, as SQLAlchemy provides an abstraction over database APIs, it is (theoretically) possible to write SQLAlchemy code that works on one database system and later use the exact same (or only slightly modified) code on another one. So if you're looking for MySQL support, you could begin to write your code to use SQLAlchemy with SQLite as a backend first, and port it to MySQL when that backend is eventually supported.

SQLAlchemy is a very large and robust library; it allows us to do almost anything imaginable with a database. We'll only be able to touch on the basics in this section.

The idea behind SQLAlchemy, and ORMs in general is to interact with objects that automatically modify and update database tables in the background. SQLAlchemy provides multiple ways to map objects to tables; we'll be using the modern inheritance-based solution. If you need to connect to a legacy database, SQLAlchemy provides an alternative method that allows arbitrary object classes to be explicitly mapped to database tables, but we won't have room to cover that here.

The first thing we need to do is connect to a database. The sqlalchemy.create_engine function provides a single point of access for connecting to a database. It takes a huge number of arguments to customize or tune access. The most important one is a string URL defining the kind of backend database to be connected to, the specific database backend to make the connection, the name of the database, the host the database system is running on, and a username and/or password to authenticate with. The basic form of the URL resembles a web URL: driver://user:password@host/dbname.

If we want to use a simple SQLite database, which does not require username, password, or host; we can simply specify the filename for the database, as we'll see in the next example.

Then we'll need to create a class that allows objects to store their data in the database, while optionally supplying behavior as methods on the object. Each instance of the object will be stored in a separate row in the database, identified by its primary key (it is usually a good idea to make the primary key a single integer identifier, but SQLAlchemy does not require this).

Each table in the database is normally represented by a separate class, and special attributes on each class map to table columns. When we access these attributes on an object, we get database values, and when we update and save the object, the database is modified. Here's a simple example for our pets database:

	import sqlalchemy as sqa
	from sqlalchemy.ext.declarative import declarative_base
	
	Base = declarative_base()

	class Pet(Base):
		__tablename__ = "pet"
		id = sqa.Column(sqa.Integer, primary_key=True)
		type = sqa.Column(sqa.String(16))
		breed = sqa.Column(sqa.String(32))
		gender = sqa.Column(sqa.Enum("male", "female"))
		name = sqa.Column(sqa.String(64))
		
	engine = sqa.create_engine('sqlite:///mydata.db')
	Base.metadata.create_all(engine)

SQLAlchemy first asks us to set up a Base class by calling a function called declarative_base. This function returns a class, which we are able to extend in our declaration. The subclass needs a special attribute named __tablename__ to specify the name of the table in the database.

This is followed by several column declarations. We add Column objects whose first argument is a type object (example Integer or String), and subsequent arguments depend on the type. All of these type objects are provided in the sqlalchemy package. I generally import this package with the alias sqa to make it easier to reference the many classes in the package. Some people suggest using from sqlalchemy import * syntax, so all the objects are available, but as we discussed in Chapter 2, this can make code very confusing to maintain.

After defining one or more mapped classes that extend the Base object, we connect to a specific database (in this case, an SQLite file) using the create_engine function. The Base.metadata.create_all call ensures that all the tables associated with that Base class exist. It would typically issue some sort of CREATE TABLE call to the underlying database.

	pet = Pet()
	pet.id = 1
	pet.type = "dog"
	pet.breed = "spaniel"
	pet.gender = "female"
	pet.name = "Esme"

	Session = sqa.orm.sessionmaker(bind=engine)
	session = Session()

	session.add(pet)
	session.commit()

	session.query(Pet).filter_by(name="Esme").one()