Databases

image

DB-API

The Python Database API (DB-API) defines a standard interface for Python database access modules. It"s documented in 249. Nearly all Python database modules such as [sqlite3], [psycopg], and [mysql-python] conform to this interface.

Tutorials that explain how to work with modules that conform to this interface can be found here and here.

SQLAlchemy

SQLAlchemy is a commonly used database toolkit. Unlike many database libraries it not only provides an ORM layer but also a generalized API for writing database-agnostic code without SQL.

$ pip install sqlalchemy

Records

Records is minimalist SQL library, designed for sending raw SQL queries to various databases. Data can be used programmatically or exported to a number of useful data formats.

$ pip install records

Also included is a command-line tool for exporting SQL data.

PugSQL

PugSQL is a simple Python interface for organizing and using parameterized, handwritten SQL. It is an anti-ORM that is philosophically lo-fi, but it still presents a clean interface in Python.

$ pip install pugsql

Django ORM

The Django ORM is the interface used by Django to provide database access.

It"s based on the idea of models, an abstraction that makes it easier to manipulate data in Python.

The basics:

  • Each model is a Python class that subclasses django.db.models.Model.
  • Each attribute of the model represents a database field.
  • Django gives you an automatically-generated database-access API; see Making queries.

peewee

peewee is another ORM with a focus on being lightweight with support for SQLite, MySQL, and PostgreSQL. It supports SQLite, MySQL, and PostgreSQL by default. The model layer is similar to that of the Django ORM and it has SQL-like methods to query data. While SQLite, MySQL, and PostgreSQL are supported out-of-the-box, there is a collection of add-ons available.

PonyORM

PonyORM is an ORM that takes a different approach to querying the database. Instead of writing an SQL-like language or boolean expressions, Python"s generator syntax is used. There"s also a graphical schema editor that can generate PonyORM entities for you. It can connect to SQLite, MySQL, PostgreSQL, and Oracle.

SQLObject

SQLObject is yet another ORM. It supports a wide variety of databases: common database systems like MySQL, PostgreSQL, and SQLite and more exotic systems like SAP DB, SyBase, and Microsoft SQL Server.