create engine mysql_sqlalchemy中create_engine用法

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:

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

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:

# default

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

psycopg2

pg8000

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:

# default

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

mysql-python

MySQL-connector-python

OurSQL

More notes on connecting to MySQL at MySQL.

Oracle

The Oracle dialect uses cx_oracle as the default DBAPI:

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

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

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:

# pyodbc

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

pymssql

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:

# sqlite:///

where is relative:

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

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

#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')

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

engine = create_engine('sqlite://')

More notes on connecting to SQLite at SQLite.

Others

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