developers' network - quick start guides to bootstrap examples

(Perfect for people with a short attention span, needing to get work done!)


SQLAlchemy provides "a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language". SQLAlchemy's philosophy is that SQL databases behave less and less like object collections the more size and performance start to matter, while object collections behave less and less like tables and rows the more abstraction starts to matter. For this reason it has adopted the data mapper pattern (like Hibernate for Java) rather than the active record pattern used by a number of other object-relational mappers. However, optional plugins allow users to develop using declarative syntax.

"SQLAlchemy", 2015-07-11 11:42:37 UTC


Notable Modules

  • sqlacodegen - Automatic model code generator for SQLAlchemy.
    • pip install sqlacodegen
  • configobj - Config file reading, writing and validation.
    • pip install configobj
  • MySQL - "SQL parser written in yacc, but it uses a home-brewed lexical analyzer"

Getting Started

There are three most important components in writing SQLAlchemy code:

  1. A Table that represents a table in a database.
  2. A mapper that maps a Python class to a table in a database.
  3. A class object that defines how a database record maps to a normal Python object.

Instead of having to write code for Table, mapper and the class object at different places, SQLAlchemy's declarative allows a Table, a mapper and a class object to be defined at once in one class definition.

Python Central, "Introductory Tutorial of Python's SQLAlchemy", 2015-07-12 08:52:21 UTC

Database Structure

To expose a database to your Python programs, you will need to define a Python module which serves the purpose of declarative definitions. You have two ways to go about doing this:

  1. Generating your declarative definitions file from a pre-existing database.
  2. Creating a declarative definitions file, which can also setup your database and tables.

Both options are explored below, and beyond connectivity, we will explore basic CRUD operations, including with ACID, when applicable.

Generating declarative definitions

If you already have a pre-existing database (or are used to scaffolding in CakePHP, and want to perform similar from MySQL Workbench, phpMyAdmin, SQLPro for MySQL, SQLPro for Postgres, Adminer etc.), you can use the sqlacodegen Python module to build mapper files for SQLAlchemy.

At the minimum, you have to give sqlacodegen a database URL. The URL is passed directly to SQLAlchemy’s create_engine() method so please refer to SQLAlchemy’s documentation for instructions on how to construct a proper URL.

sqlacodegen, Python Package Index, version 1.1.6

WARNING: You should use a temporary password for accessing your database via sqlacodegen, as your password can be viewed through both the history and/or process list; even if you try and redirect the password into the process. You can try and store the password in an environment variable, but nothing is guaranteed. 1) 2)

  • Open your command line, and change to your project directory to activate your pyenv-based virtualenv, as mentioned in aforementioned setup instructions.
    • cd ~/your/project/directory
  • Use a proper connection URL for your database.
  • PostgreSQL
    • sqlacodegen postgresql:///some_local_db
  • MySQL
    • Host connection format
      • sqlacodegen mysql://user:password@hostname/dbname
        • Note: If you use "localhost" instead of (e.g. for tunneling) or a hostname; by default, sqlacodegen will look for a socket file at /tmp/mysql.sock
    • Unix socket file format
      • sqlacodegen mysql://user:password@localhost/dbname?unix_socket=/path/mysqld.sock
      • Examples
        • Ubuntu 14
          • sqlacodegen mysql://user:password@localhost/dbname?unix_socket=/var/run/mysqld/mysqld.sock
        • MAMP on OSX
          • sqlacodegen mysql://user:password@localhost/dbname?unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock
  • SQLite
    • sqlacodegen sqlite:///database.db

(To see the full list of options: sqlacodegen --help)

If you have prior experience with CakePHP and several other frameworks, you'll most likely be familiar with base concepts of inflection and relationship logic. For sqlacodegen to understand relationships between database tables, you will have to set foreign keys in the database itself. More information about this can be read on sqlacodegen's PyPi page, and key excerpts are as follows:

Model class naming logic

The table name (which is assumed to be in English) is converted to singular form using the "inflect" library. Then, every underscore is removed while transforming the next letter to upper case. For example, sales_invoices becomes SalesInvoice.

sqlacodegen, Python Package Index, version 1.1.6

Relationship detection logic

Relationships are detected based on existing foreign key constraints as follows:

  • many-to-one: a foreign key constraint exists on the table
  • one-to-one: same as many-to-one, but a unique constraint exists on the column(s) involved
  • many-to-many: an association table is found to exist between two tables

A table is considered an association table if it satisfies all of the following conditions:

  1. has exactly two foreign key constraints
  2. all its columns are involved in said constraints

sqlacodegen, Python Package Index, version 1.1.6

Relationship naming logic

Relationships are typically named based on the opposite class name. For example, if an Employee class has a column named employer which has a foreign key to, the relationship is named company.

A special case for single column many-to-one and one-to-one relationships, however, is if the column is named like employer_id. Then the relationship is named employer due to that _id suffix.

If more than one relationship would be created with the same name, the latter ones are appended numeric suffixes, starting from 1.

sqlacodegen, Python Package Index, version 1.1.6

Run-time Examples

As previously mentioned, the standard sqlacodegen argument consists of an URL in the format of CONNECTOR://ARGUMENTS which is passed directly to the create_engine() method in SQLAlchemy:

sqlacodegen %%CONNECTOR://ARGUMENTS%%

If you use the INI File method as described in the "Adding engine connection parameters" section below, you can execute sqlacodegen using the following (and very hideous) CLI command, in place of CONNECTOR://ARGUMENTS:

sqlacodegen `python -c "\
import re;\
from configobj import ConfigObj;\
config = ConfigObj('conf/auth.ini');\
sqlaurl = dict();\
[sqlaurl.update({'%'+k+'%':v}) for k,v in config['db']['NICKNAME'].items() if k not in ('sqlaurl')];\
print(re.sub('|'.join([re.escape(x) for x in sqlaurl.keys()]),lambda m: sqlaurl[],config['db']['NICKNAME']['sqlaurl']))"`

As found in the help message for sqlacodegen (sqlacodegen –help), there is an optional argument for saving to a file instead of the default behavior of writing to STDOUT. Or if you prefer, you can always use output direction to a file or pipe (e.g. for use with tee):

  • sqlacodegen CONNECTOR://ARGUMENTS --outfile OUTFILE
  • sqlacodegen CONNECTOR://ARGUMENTS | tee OUTFILE

Creating declarative definitions


  • ForeignKey columns for a table don't properly create on the database, and it errors out with "sqlalchemy.exc.CompileError: (in table 'TABLE', column 'COLUMN'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?". Perhaps this is a SQLAlchemy bug? 8-O


The core of a declarative definition is as shown below, and where:

  • COLUMNTYPE are imported and define database columns.
  • TABLE is defined as a class.
    • __tablename__ is as it sounds: it's the table name as found in the database.
    • COLUMNNAMEVAR is the name of the variable representing the column.
    • COLUMNTYPE defines the type of the column, followed by length.
    • A relationship may be defined between the database table represented by the Python class, and another database table represented by another Python class.

#!/usr/bin/env python
# coding: utf-8
from sqlalchemy import COLUMNTYPE1, COLUMNTYPE2, COLUMNTYPE3
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class TABLE1(Base):
    __tablename__ = 'TABLE'
    COLUMNNAMEVAR = Column(COLUMNTYPE(#), primary_key=True)
    # Relationship definition, if applicable:
    # TABLEMODELVAR = relationship('TABLE2', primaryjoin='TABLE1.COLUMN == TABLE2.COLUMN')

Example: Logging Trips

Please note: at present, this example is incomplete.

Clear as dirt, right? Let's start by putting together a "basic" example for logging trip data with a smartphone, and what we'll refer to in subsequent guides here on this site; due in part to an exercise in functionality (and certainly, complexity):

  • UUID as the primary key in each table.
  • Relationships between three tables using UUID as foreign keys:
    1. Categories
    2. Trips
    3. Users
#!/usr/bin/env python
# coding: utf-8
from sqlalchemy import BINARY, BigInteger, Column, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class Category(Base):
    __tablename__ = 'categories'
    id = Column(BINARY(16), primary_key=True)
    created = Column(DateTime)
    modified = Column(DateTime)
    utc_offset = Column(Integer)
class Trip(Base):
    __tablename__ = 'trips'
    id = Column(BINARY(16), primary_key=True)
    created = Column(DateTime)
    modified = Column(DateTime)
    utc_offset = Column(Integer)
class User(Base):
    __tablename__ = 'users'
    id = Column(BINARY(16), primary_key=True)
    created = Column(DateTime)
    modified = Column(DateTime)
    utc_offset = Column(Integer)


Adding engine connection parameters

Now that the core of your declarative definitions module has been defined for your database structure using one of the above methods (Generate or Create), there's one more piece of code that you'll need to add at the bottom of the module:

# Import SQLAlchemy's engine module for connecting
from sqlalchemy import create_engine
# Create an engine that stores data.
engine = create_engine('CONNECTOR://ARGUMENTS')
# Create all tables in the engine.
# This is equivalent to "Create Table" statements in raw SQL.
except Exception as e:
	print( 'A database error has occurred: %s' % e )

The above code allows you to create the schema on the database server and/or import your declarative definitions module into your program, and as shown in the Connections section below.

INI File

For concerns of security, as well as maintenance; I don't like scattering authentication credentials around, and prefer a central location, in a format which can easily be parsed by any number of languages, including Bash, if so desired 3) 4).

To resolve this, I use an INI file that I parse with the configobj Python module, so my file reads slightly differently, with a reciprocal auth.ini file, and examples with remote and localhost-socket connections using MySQL are as follows:

connector	=	'mysql'
user		=	'USER'
pass		=	'PASS'
host		=	'HOST'
port		=	3306
db		=	'myNewDatabase'
sqlaurl		=	'%connector%://%user%:%pass%@%host%:%port%/%db%'
connector	=	'mysql'
user		=	'USER'
pass		=	'PASS'
host		=	'localhost'
port		=	3306
db		=	'myNewDatabase'
socket		=	'/var/run/mysqld/mysqld.sock'
sqlaurl		=	'%connector%://%user%:%pass%@%host%:%port%/%db%?unix_socket=%socket%'
def sqlaConnectURL( iniFile, sectionPath ):
	# Six: Python 2 and 3 Compatibility Library
	import six
	# Parse authentication information
	from configobj import ConfigObj
	container	=	ConfigObj( iniFile )
	for s,item in enumerate(sectionPath):
		if ( s < ( len(sectionPath) - 1 ) ):
			container	=	container[ item ]
	# Build a replacement dict
	sqlaurl	=	dict()
	[sqlaurl.update({'%'+k+'%':v}) for k,v in container.items() if k not in (sectionPath[-1])]
	# String replacement and returning our connection URL
	import re
	return re.sub("|".join([re.escape(x) for x in sqlaurl.keys()]),lambda m: sqlaurl[],container[sectionPath[-1]])
# Import SQLAlchemy's engine module for connecting
from sqlalchemy import create_engine
# Create an engine that stores data.
engine	=	create_engine( sqlaConnectURL( 'conf/auth.ini', ['db','NICKNAME','sqlaurl'] ) )
# Create all tables in the engine.
# This is equivalent to "Create Table" statements in raw SQL.
except Exception as e:
	print( 'A database error has occurred: %s' % e )


If you opted for Creating declarative definitions or if you simply want to test your connection arguments, you can execute your declarative definitions module at the command line:

[user@host ~/your/project/directory$] python

If everything is working, you will see no errors; and for that matter, nothing at all. "No news is good news." ;-) You might also want to check out the Enabling SQL-level debugging section of this guide's Addendum.


Upon your declarative definitions successfully implementing the schema (assuming that the database was devoid), no messages will be shown to you if everything was successful, including connectivity in subsequent runs or for an existing database; you will need to manually check via your database's native (or third-party) client. Using the stock MySQL client as an example, with our example schema being "myNewDatabase":

[user@host ~/your/project/directory$] mysql -u"USER" -p -h"HOST" -P3306 -e 'SHOW DATABASES; USE `myNewDatabase`; SHOW TABLES;'
Enter password: <FILL IN YOUR PASSWORD>

| Database           |
| information_schema |
| myNewDatabase      |
| Tables_in_myNewDatabase |
| myNewTable1             |
| myNewTable2             |
| myNewTable3             |
| users                   |


Okay, congratulations on a job well done! You are ready to start making subsequent, routine connections. Now for you to dig into:


Enabling SQL-level debugging

import logging

Connecting via a Bastion host

It's horrible security practice to expose your database server (which is usually unencrypted) to the public; and most worthwhile database tools support SSH Tunnels for encrypting communications between the database client and the database server (e.g. MySQL Workbench). Beyond that, it's usually unlikely that most database servers have anything exposed to the public, including SSH; and are generally only accessible to a few (web server) hosts that are calling upon them. This is where using a Bastion host comes into play, which straddles the public Internet with SSH/VPN in a DMZ and can reach private servers, and also where GGCom's DFWU may be of use to you as well, if you're looking to set up a Bastion host.

You will setup two tunnels, one that connects to the Bastion host ("BASTION.HOST") with port 22 (SSH's default port) and redirects SSH from the database server ("DATABASE.HOST") to a local port ("SSHREDIRPORT"), and the other that redirects the database server's port (e.g. MySQL's 3306) over the database server's SSH tunnel to your local port:

  2. ssh -N -l root -L 3306: -pSSHREDIRPORT

External links

See also