使用sqlalchemy连接各类数据库时engine格式

通用格式

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;详情见

# 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, including MySQL-connector-python and OurSQL。详情见

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

Oracle

The Oracle dialect uses cx_oracle as the default DBAPI.详情见

engine = create_engine('oracle://scott:[email protected]: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: 详情见

# sqlite://<nohostname>/<path>
# where <path> 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://')