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.