SQLAlchemy uses system of dialects to communicate with various types of databases. Each database has a corresponding DBAPI wrapper. All dialects require that an appropriate DBAPI driver is installed.

Following dialects are included in SQLAlchemy API −

• Firebird
• Microsoft SQL Server
• MySQL
• Oracle
• PostgreSQL
• SQL
• Sybase

An Engine object based on a URL is produced by create_engine() function. These URLs can include username, password, hostname, and database name. There may be optional keyword arguments for additional configuration. In some cases, a file path is accepted, and in others, a “data source name” replaces the “host” and “database” portions. The typical form of a database URL is as follows −

dialect+driver://username:password@host:port/database

PostgreSQL

The PostgreSQL dialect uses psycopg2 as the default DBAPI. pg8000 is also available as a pure-Python substitute as shown below:

# default
engine = create_engine(''postgresql://scott:tiger@localhost/mydatabase'')

# psycopg2
engine = create_engine(''postgresql+psycopg2://scott:tiger@localhost/mydatabase'')

# pg8000
engine = create_engine(''postgresql+pg8000://scott:tiger@localhost/mydatabase'')

MySQL

The MySQL dialect uses mysql-python as the default DBAPI. There are many MySQL DBAPIs available, such as MySQL-connector-python as follows −

# default
engine = create_engine(''mysql://scott:tiger@localhost/foo'')

# mysql-python
engine = create_engine(''mysql+mysqldb://scott:tiger@localhost/foo'')

# MySQL-connector-python
engine = create_engine(''mysql+mysqlconnector://scott:tiger@localhost/foo'')

Oracle

The Oracle dialect uses cx_oracle as the default DBAPI as follows −

engine = create_engine(''oracle://scott:tiger@127.0.0.1:1521/sidname'')
engine = create_engine(''oracle+cx_oracle://scott:tiger@tnsname'')

Microsoft SQL Server

The SQL Server dialect uses pyodbc as the default DBAPI. pymssql is also available.

# pyodbc
engine = create_engine(''mssql+pyodbc://scott:tiger@mydsn'')

# pymssql
engine = create_engine(''mssql+pymssql://scott:tiger@hostname:port/dbname'')

SQLite

SQLite connects to file-based databases, using the Python built-in module sqlite3 by default. As SQLite connects to local files, the URL format is slightly different. The “file” portion of the URL is the filename of the database. For a relative file path, this requires three slashes as shown below −

engine = create_engine(''sqlite:///foo.db'')

And for an absolute file path, the three slashes are followed by the absolute path as given below −

engine = create_engine(''sqlite:///C:\path\to\foo.db'')

To use a SQLite:memory:database, specify an empty URL as given below −

engine = create_engine(''sqlite://'')

Conclusion

In the first part of this tutorial, we have learnt how to use the Expression Language to execute SQL statements. Expression language embeds SQL constructs in Python code. In the second part, we have discussed object relation mapping capability of SQLAlchemy. The ORM API maps the SQL tables with Python classes.