Sqlalchemy 简明教程

SQLAlchemy - Dialects

SQLAlchemy 使用方言系统与各种类型的数据库进行通信。每个数据库都有一个相应的 DBAPI 包装器。所有方言都要求安装合适的 DBAPI 驱动程序。

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.

以下方言包含在 SQLAlchemy API 中 −

Following dialects are included in SQLAlchemy API −

  1. Firebird

  2. Microsoft SQL Server

  3. MySQL

  4. Oracle

  5. PostgreSQL

  6. SQL

  7. Sybase

一个基于 URL 的 Engine 对象是由 create_engine() 函数产生的。这些 URL 可以包括用户名、密码、主机名和数据库名称。可能存在用于附加配置的可选关键字参数。在某些情况下,文件路径是可以接受的,而在另一些情况下,“数据源名称”会替换“主机”和“数据库”部分。数据库 URL 的典型形式如下 −

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

PostgreSQL 方言使用 psycopg2 作为默认 DBAPI。pg8000 也可用作纯 Python 替代品,如下所示:

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

MySQL 方言使用 mysql-python 作为默认 DBAPI。有许多可用的 MySQL DBAPI,例如 MySQL-connector-python,如下所示 −

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

Oracle 方言使用 cx_oracle 作为默认 DBAPI,如下所示 −

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

SQL Server 方言使用 pyodbc 作为默认 DBAPI。pymssql 也可用。

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 连接到基于文件的数据库,默认使用 Python 内置模块 sqlite3 。由于 SQLite 连接到本地文件,因此 URL 格式略有不同。URL 的“file”部分是数据库的文件名。对于相对文件路径,这需要三个斜杠,如下所示 −

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

要使用 SQLite:memory:database,请指定一个空 URL,如下所示 −

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

engine = create_engine('sqlite://')

Conclusion

在本教程的第一部分中,我们学习了如何使用 Expression Language 执行 SQL 语句。Expression language 将 SQL 构造嵌入 Python 代码中。在第二部分中,我们讨论了 SQLAlchemy 的对象关系映射功能。ORM API 将 SQL 表与 Python 类进行映射。

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.