Database abstraction with Python: SQLAlchemy

The SQLAlchemy SQL Toolkit and Object Relational Mapper is a comprehensive set of tools for working with databases and Python. It has several distinct areas of functionality which can be used individually or combined together. Its major API components, all public-facing, are illustrated below:

+-----------------------------------------------------------+
|             Object Relational Mapper (ORM)                |
+-----------------------------------------------------------+
+---------+ +------------------------------------+ +--------+
|         | |       SQL Expression Language      | |        |
|         | +------------------------------------+ |        |
|         +-----------------------+ +--------------+        |
|        Dialect/Execution        | |    Schema Management  |
+---------------------------------+ +-----------------------+
+----------------------+ +----------------------------------+
|  Connection Pooling  | |              Types               |
+----------------------+ +----------------------------------+

Above, the two most significant front-facing portions of SQLAlchemy are the Object Relational Mapper and the SQL Expression Language. These are two separate toolkits, one building off the other. SQL Expressions can be used independently of the ORM. When using the ORM, the SQL Expression language is used to establish object-relational configurations as well as in querying.

Capabilities

  • Supported Dialects
    • PostgreSQL: psycopg2
    • SQLite: sqlite3 (included in Python 2.5 or greater), pysqlite
    • MySQL: MySQLDB (a.k.a. mysql-python)
    • Oracle: cx_Oracle
    • Firebird: kinterbasdb
    • MS-SQL, MSAccess: pyodbc (recommended) adodbapi pymssql
  • Experimental Dialects
    • MSAccess: pyodbc
    • Informix: informixdb
    • Sybase: TODO
    • MAXDB: TODO
  • Third Party Dialects
    • DB2/Informix IDS: ibm-db

Tutorials

resources

Comments