Essential SQLAlchemy

Chapter 3. Engines and MetaData

This chapter introduces SQLAlchemy’s Engine and MetaData classes. The Engine class provides database connectivity, including a connection pool with various strategies for acquiring connections from the pool. The MetaData class maintains information about your database schema, including any tables and indexes defined. In this chapter, you will learn how to define a new database schema using MetaData as well as how to connect a MetaData instance to an existing schema.

Engines and Connectables

The SQLAlchemy-provided Engine class is responsible for managing the connection to the database. It does this by incorporating a database connection pool and a database-specific Dialect layer to translate the SQL expression language (Chapter 5) into database-specific SQL.

To get started using an Engine, you use the create_engine() function:

# Create a connection to a SQLite in-memory database engine = create_engine('sqlite://') # Create a connection to a SQLite on-disk database "data.sqlite" engine = create_engine('sqlite:///data.sqlite') # Create a connection to a PostGreSQL database engine = create_engine('postgres://rick:foo@localhost:5432/pg_db') # Create a connection to a MySQL database engine = create_engine('mysql://localhost/mysql_db') # Create a connection to an Oracle database (via TNS) engine = create_engine('oracle://rick:foo@oracle_tns') # Create a connection to an Oracle database (without a TNS name) engine = ... create_engine('oracle://rick:foo@localhost:1521/oracle_sid') ...