Database Urls

The create_engine() function produces an Engine object based on a URL. These URLs follow RFC-1738, and usually can include username, password, hostname, database name as well as 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:

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;">dialect+driver://username:password@host:port/database</pre>

Dialect names include the identifying name of the SQLAlchemy dialect, a name such as sqlite, mysql, postgresql, oracle, or mssql. The drivername is the name of the DBAPI to be used to connect to the database using all lowercase letters. If not specified, a “default” DBAPI will be imported if available - this default is typically the most widely known driver available for that backend.

Examples for common connection styles follow below. For a full index of detailed information on all included dialects as well as links to third-party dialects, see Dialects.

PostgreSQL

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

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;"># 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')</pre>

More notes on connecting to PostgreSQL at PostgreSQL.

MySQL

The MySQL dialect uses mysql-python as the default DBAPI. There are many MySQL DBAPIs available, including MySQL-connector-python and OurSQL:

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;"># 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')

OurSQL

engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')</pre>

More notes on connecting to MySQL at MySQL.

Oracle

The Oracle dialect uses cx_oracle as the default DBAPI:

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;">engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')

engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')</pre>

More notes on connecting to Oracle at Oracle.

Microsoft SQL Server

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

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;"># pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

pymssql

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

More notes on connecting to SQL Server at Microsoft SQL Server.

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:

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;"># sqlite://<nohostname>/<path>

where <path> is relative:

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

And for an absolute file path, the three slashes are followed by the absolute path:

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;">#Unix/Mac - 4 initial slashes in total
engine = create_engine('sqlite:////absolute/path/to/foo.db')

Windows

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

Windows alternative using raw string

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

To use a SQLite :memory: database, specify an empty URL:

<pre style="margin: 5px 0px; padding: 10px; font-size: 1.2em; background-color: rgb(240, 240, 240); border: 1px solid rgb(204, 204, 204); box-shadow: rgb(223, 223, 223) 2px 2px 3px; overflow: auto; line-height: 1.3em;">engine = create_engine('sqlite://')</pre>

More notes on connecting to SQLite at SQLite.

Others

See Dialects, the top-level page for all additional dialect documentation.