Engine 配置¶

Engine Configuration

中文

Engine 是任何SQLAlchemy应用程序的起点。它是实际数据库及其 DBAPI 的“基地”，通过连接池和描述如何与特定类型的数据库/DBAPI组合通信的 Dialect 提供给SQLAlchemy应用程序。

一般结构可以如下图所示：

在上图中，Engine 引用 Dialect 和 Pool，它们共同解释DBAPI的模块函数以及数据库的行为。

创建引擎只需发出一个简单的调用 create_engine():

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost:5432/mydatabase")

上述引擎创建了一个针对PostgreSQL定制的 Dialect 对象，以及一个在首次收到连接请求时将在 localhost:5432 建立DBAPI连接的 Pool 对象。注意 Engine 及其底层的 Pool 在调用 Engine.connect() 或 Engine.begin() 方法之前不会建立第一个实际的DBAPI连接。这些方法中的任意一个也可能被其他首次需要数据库连接的SQLAlchemy Engine 依赖对象（如ORM的 Session 对象）调用。通过这种方式，可以说 Engine 和 Pool 具有 延迟初始化 行为。

创建后，可以直接使用 Engine 与数据库交互，或将其传递给 Session 对象以使用ORM。本节介绍了配置 Engine 的详细信息。下一节使用Engines和Connection 将详细介绍 Engine 及类似对象的使用API，通常用于非ORM应用程序。

英文

The Engine is the starting point for any SQLAlchemy application. It’s “home base” for the actual database and its DBAPI, delivered to the SQLAlchemy application through a connection pool and a Dialect, which describes how to talk to a specific kind of database/DBAPI combination.

The general structure can be illustrated as follows:

Where above, an Engine references both a Dialect and a Pool, which together interpret the DBAPI’s module functions as well as the behavior of the database.

Creating an engine is just a matter of issuing a single call, create_engine():

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost:5432/mydatabase")

The above engine creates a Dialect object tailored towards PostgreSQL, as well as a Pool object which will establish a DBAPI connection at localhost:5432 when a connection request is first received. Note that the Engine and its underlying Pool do not establish the first actual DBAPI connection until the Engine.connect() or Engine.begin() methods are called. Either of these methods may also be invoked by other SQLAlchemy Engine dependent objects such as the ORM Session object when they first require database connectivity. In this way, Engine and Pool can be said to have a lazy initialization behavior.

The Engine, once created, can either be used directly to interact with the database, or can be passed to a Session object to work with the ORM. This section covers the details of configuring an Engine. The next section, 使用Engines和Connection, will detail the usage API of the Engine and similar, typically for non-ORM applications.

支持的数据库¶

Supported Databases

中文

SQLAlchemy 包含许多适用于各种后端的 Dialect 实现。SQLAlchemy 已包含最常见数据库的方言；其他一些数据库的方言则需要额外安装单独的方言。

有关各种可用后端的信息，请参阅 Dialects 部分。

英文

SQLAlchemy includes many Dialect implementations for various backends. Dialects for the most common databases are included with SQLAlchemy; a handful of others require an additional install of a separate dialect.

See the section Dialects for information on the various backends available.

引擎创建 API¶

Engine Creation API

Object Name	Description
create_engine(url, **kwargs)	Create a new `Engine` instance.
create_mock_engine(url, executor, **kw)	Create a “mock” engine used for echoing DDL.
create_pool_from_url(url, **kwargs)	Create a pool instance from the given url.
engine_from_config(configuration[, prefix], **kwargs)	Create a new Engine instance using a configuration dictionary.
make_url(name_or_url)	Given a string, produce a new URL instance.
URL	Represent the components of a URL used to connect to a database.

function sqlalchemy.create_engine(url: str | _url.URL, **kwargs: Any) → Engine¶

Create a new Engine instance.

The standard calling form is to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments:

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

备注

Please review 数据库 URL for general guidelines in composing URL strings. In particular, special characters, such as those often part of passwords, must be URL encoded to be properly parsed.

Additional keyword arguments may then follow it which establish various options on the resulting Engine and its underlying Dialect and Pool constructs:

engine = create_engine(
    "mysql+mysqldb://scott:tiger@hostname/dbname",
    pool_recycle=3600,
    echo=True,
)

The string form of the URL is dialect[+driver]://user:password@host/dbname[?key=value..], where dialect is a database name such as mysql, oracle, postgresql, etc., and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle, etc. Alternatively, the URL can be an instance of URL.

**kwargs takes a wide variety of options which are routed towards their appropriate components. Arguments may be specific to the Engine, the underlying Dialect, as well as the Pool. Specific dialects also accept keyword arguments that are unique to that dialect. Here, we describe the parameters that are common to most create_engine() usage.

Once established, the newly resulting Engine will request a connection from the underlying Pool once Engine.connect() is called, or a method which depends on it such as Engine.execute() is invoked. The Pool in turn will establish the first actual DBAPI connection when this request is received. The create_engine() call itself does not establish any actual DBAPI connections directly.

参见

Engine 配置

Dialects

使用Engines和Connection

参数:

connect_args¶ – a dictionary of options which will be passed directly to the DBAPI’s connect() method as additional keyword arguments. See the example at 自定义 DBAPI connect() 参数/连接例程.
creator¶ –
a callable which returns a DBAPI connection. This creation function will be passed to the underlying connection pool and will be used to create all new database connections. Usage of this function causes connection parameters specified in the URL argument to be bypassed.

This hook is not as flexible as the newer DialectEvents.do_connect() hook which allows complete control over how a connection is made to the database, given the full set of URL arguments and state beforehand.

参见

DialectEvents.do_connect() - event hook that allows full control over DBAPI connection mechanics.

自定义 DBAPI connect() 参数/连接例程
echo=False¶ –
if True, the Engine will log all statements as well as a repr() of their parameter lists to the default log handler, which defaults to sys.stdout for output. If set to the string "debug", result rows will be printed to the standard output as well. The echo attribute of Engine can be modified at any time to turn logging on and off; direct control of logging is also available using the standard Python logging module.

参见

配置日志记录 - further detail on how to configure logging.
echo_pool=False¶ –
if True, the connection pool will log informational output such as when connections are invalidated as well as when connections are recycled to the default log handler, which defaults to sys.stdout for output. If set to the string "debug", the logging will include pool checkouts and checkins. Direct control of logging is also available using the standard Python logging module.

参见

配置日志记录 - further detail on how to configure logging.
empty_in_strategy¶ –
No longer used; SQLAlchemy now uses “empty set” behavior for IN in all cases.

自 1.4 版本弃用: The create_engine.empty_in_strategy keyword is deprecated, and no longer has any effect. All IN expressions are now rendered using the “expanding parameter” strategy which renders a set of boundexpressions, or an “empty set” SELECT, at statement executiontime.
enable_from_linting¶ –
defaults to True. Will emit a warning if a given SELECT statement is found to have un-linked FROM elements which would cause a cartesian product.

在 1.4 版本加入.

参见

Built-in FROM linting will warn for any potential cartesian products in a SELECT statement
execution_options¶ – Dictionary execution options which will be applied to all connections. See Connection.execution_options()
future¶ –
Use the 2.0 style Engine and Connection API.

As of SQLAlchemy 2.0, this parameter is present for backwards compatibility only and must remain at its default value of True.

The create_engine.future parameter will be deprecated in a subsequent 2.x release and eventually removed.

在 1.4 版本加入.

在 2.0 版本发生变更: All Engine objects are “future” style engines and there is no longer a future=False mode of operation.

参见

SQLAlchemy 2.0 - Major Migration Guide
hide_parameters¶ –
Boolean, when set to True, SQL statement parameters will not be displayed in INFO logging nor will they be formatted into the string representation of StatementError objects.

参见

配置日志记录 - further detail on how to configure logging.
implicit_returning=True¶ – Legacy parameter that may only be set to True. In SQLAlchemy 2.0, this parameter does nothing. In order to disable “implicit returning” for statements invoked by the ORM, configure this on a per-table basis using the Table.implicit_returning parameter.
insertmanyvalues_page_size¶ –
number of rows to format into an INSERT statement when the statement uses “insertmanyvalues” mode, which is a paged form of bulk insert that is used for many backends when using executemany execution typically in conjunction with RETURNING. Defaults to 1000, but may also be subject to dialect-specific limiting factors which may override this value on a per-statement basis.

在 2.0 版本加入.

参见

INSERT 语句的“插入多个值”行为

控制批处理大小

Connection.execution_options.insertmanyvalues_page_size
isolation_level¶ –
optional string name of an isolation level which will be set on all new connections unconditionally. Isolation levels are typically some subset of the string names "SERIALIZABLE", "REPEATABLE READ", "READ COMMITTED", "READ UNCOMMITTED" and "AUTOCOMMIT" based on backend.

The create_engine.isolation_level parameter is in contrast to the Connection.execution_options.isolation_level execution option, which may be set on an individual Connection, as well as the same parameter passed to Engine.execution_options(), where it may be used to create multiple engines with different isolation levels that share a common connection pool and dialect.

在 2.0 版本发生变更: The create_engine.isolation_level parameter has been generalized to work on all dialects which support the concept of isolation level, and is provided as a more succinct, up front configuration switch in contrast to the execution option which is more of an ad-hoc programmatic option.

参见

设置事务隔离级别（包括 DBAPI 自动提交）
json_deserializer¶ – for dialects that support the JSON datatype, this is a Python callable that will convert a JSON string to a Python object. By default, the Python json.loads function is used.
json_serializer¶ – for dialects that support the JSON datatype, this is a Python callable that will render a given object as JSON. By default, the Python json.dumps function is used.
label_length=None¶ –
optional integer value which limits the size of dynamically generated column labels to that many characters. If less than 6, labels are generated as “_(counter)”. If None, the value of dialect.max_identifier_length, which may be affected via the create_engine.max_identifier_length parameter, is used instead. The value of create_engine.label_length may not be larger than that of create_engine.max_identfier_length.

参见

create_engine.max_identifier_length
logging_name¶ –
String identifier which will be used within the “name” field of logging records generated within the “sqlalchemy.engine” logger. Defaults to a hexstring of the object’s id.

参见

配置日志记录 - further detail on how to configure logging.

Connection.execution_options.logging_token
max_identifier_length¶ –
integer; override the max_identifier_length determined by the dialect. if None or zero, has no effect. This is the database’s configured maximum number of characters that may be used in a SQL identifier such as a table name, column name, or label name. All dialects determine this value automatically, however in the case of a new database version for which this value has changed but SQLAlchemy’s dialect has not been adjusted, the value may be passed here.

参见

create_engine.label_length
max_overflow=10¶ – the number of connections to allow in connection pool “overflow”, that is connections that can be opened above and beyond the pool_size setting, which defaults to five. this is only used with QueuePool.
module=None¶ – reference to a Python module object (the module itself, not its string name). Specifies an alternate DBAPI module to be used by the engine’s dialect. Each sub-dialect references a specific DBAPI which will be imported before first connect. This parameter causes the import to be bypassed, and the given module to be used instead. Can be used for testing of DBAPIs as well as to inject “mock” DBAPI implementations into the Engine.
paramstyle=None¶ – The paramstyle to use when rendering bound parameters. This style defaults to the one recommended by the DBAPI itself, which is retrieved from the .paramstyle attribute of the DBAPI. However, most DBAPIs accept more than one paramstyle, and in particular it may be desirable to change a “named” paramstyle into a “positional” one, or vice versa. When this attribute is passed, it should be one of the values "qmark", "numeric", "named", "format" or "pyformat", and should correspond to a parameter style known to be supported by the DBAPI in use.
pool=None¶ – an already-constructed instance of Pool, such as a QueuePool instance. If non-None, this pool will be used directly as the underlying connection pool for the engine, bypassing whatever connection parameters are present in the URL argument. For information on constructing connection pools manually, see 连接池.
poolclass=None¶ – a Pool subclass, which will be used to create a connection pool instance using the connection parameters given in the URL. Note this differs from pool in that you don’t actually instantiate the pool in this case, you just indicate what type of pool to be used.
pool_logging_name¶ –
String identifier which will be used within the “name” field of logging records generated within the “sqlalchemy.pool” logger. Defaults to a hexstring of the object’s id.

参见

配置日志记录 - further detail on how to configure logging.
pool_pre_ping¶ –
boolean, if True will enable the connection pool “pre-ping” feature that tests connections for liveness upon each checkout.

参见

断开连接处理 - 悲观
pool_size=5¶ – the number of connections to keep open inside the connection pool. This used with QueuePool as well as SingletonThreadPool. With QueuePool, a pool_size setting of 0 indicates no limit; to disable pooling, set poolclass to NullPool instead.
pool_recycle=-1¶ –
this setting causes the pool to recycle connections after the given number of seconds has passed. It defaults to -1, or no timeout. For example, setting to 3600 means connections will be recycled after one hour. Note that MySQL in particular will disconnect automatically if no activity is detected on a connection for eight hours (although this is configurable with the MySQLDB connection itself and the server configuration as well).

参见

设置池回收
pool_reset_on_return='rollback'¶ –
set the Pool.reset_on_return parameter of the underlying Pool object, which can be set to the values "rollback", "commit", or None.

参见

返回时重置
pool_timeout=30¶ –
number of seconds to wait before giving up on getting a connection from the pool. This is only used with QueuePool. This can be a float but is subject to the limitations of Python time functions which may not be reliable in the tens of milliseconds.
pool_use_lifo=False¶ –
use LIFO (last-in-first-out) when retrieving connections from QueuePool instead of FIFO (first-in-first-out). Using LIFO, a server-side timeout scheme can reduce the number of connections used during non- peak periods of use. When planning for server-side timeouts, ensure that a recycle or pre-ping strategy is in use to gracefully handle stale connections.

参见

使用 FIFO 与 LIFO

处理断开连接
plugins¶ – string list of plugin names to load. See CreateEnginePlugin for background.
query_cache_size¶ –
size of the cache used to cache the SQL string form of queries. Set to zero to disable caching.

The cache is pruned of its least recently used items when its size reaches N * 1.5. Defaults to 500, meaning the cache will always store at least 500 SQL statements when filled, and will grow up to 750 items at which point it is pruned back down to 500 by removing the 250 least recently used items.

Caching is accomplished on a per-statement basis by generating a cache key that represents the statement’s structure, then generating string SQL for the current dialect only if that key is not present in the cache. All statements support caching, however some features such as an INSERT with a large set of parameters will intentionally bypass the cache. SQL logging will indicate statistics for each statement whether or not it were pull from the cache.

备注

some ORM functions related to unit-of-work persistence as well as some attribute loading strategies will make use of individual per-mapper caches outside of the main cache.

参见

SQL 编译缓存

在 1.4 版本加入.
use_insertmanyvalues¶ –
True by default, use the “insertmanyvalues” execution style for INSERT..RETURNING statements by default.

在 2.0 版本加入.

参见

INSERT 语句的“插入多个值”行为

function sqlalchemy.engine_from_config(configuration: Dict[str, Any], prefix: str = 'sqlalchemy.', **kwargs: Any) → Engine¶

Create a new Engine instance using a configuration dictionary.

The dictionary is typically produced from a config file.

The keys of interest to engine_from_config() should be prefixed, e.g. sqlalchemy.url, sqlalchemy.echo, etc. The ‘prefix’ argument indicates the prefix to be searched for. Each matching key (after the prefix is stripped) is treated as though it were the corresponding keyword argument to a create_engine() call.

The only required key is (assuming the default prefix) sqlalchemy.url, which provides the database URL.

A select set of keyword arguments will be “coerced” to their expected type based on string values. The set of arguments is extensible per-dialect using the engine_config_types accessor.

参数:

configuration¶ – A dictionary (typically produced from a config file, but this is not a requirement). Items whose keys start with the value of ‘prefix’ will have that prefix stripped, and will then be passed to create_engine().
prefix¶ – Prefix to match and then strip from keys in ‘configuration’.
kwargs¶ – Each keyword argument to engine_from_config() itself overrides the corresponding item taken from the ‘configuration’ dictionary. Keyword arguments should not be prefixed.

function sqlalchemy.create_mock_engine(url: str | URL, executor: Any, **kw: Any) → MockConnection¶

Create a “mock” engine used for echoing DDL.

This is a utility function used for debugging or storing the output of DDL sequences as generated by MetaData.create_all() and related methods.

The function accepts a URL which is used only to determine the kind of dialect to be used, as well as an “executor” callable function which will receive a SQL expression object and parameters, which can then be echoed or otherwise printed. The executor’s return value is not handled, nor does the engine allow regular string statements to be invoked, and is therefore only useful for DDL that is sent to the database without receiving any results.

E.g.:

from sqlalchemy import create_mock_engine

def dump(sql, *multiparams, **params):
    print(sql.compile(dialect=engine.dialect))

engine = create_mock_engine("postgresql+psycopg2://", dump)
metadata.create_all(engine, checkfirst=False)

参数:

url¶ – A string URL which typically needs to contain only the database backend name.
executor¶ – a callable which receives the arguments sql, *multiparams and **params. The sql parameter is typically an instance of ExecutableDDLElement, which can then be compiled into a string using ExecutableDDLElement.compile().

在 1.4 版本加入: - the create_mock_engine() function replaces the previous “mock” engine strategy used with create_engine().

参见

如何以字符串形式获取 CREATE TABLE/DROP TABLE 输出？

function sqlalchemy.engine.make_url(name_or_url: str | URL) → URL¶

Given a string, produce a new URL instance.

The format of the URL generally follows RFC-1738, with some exceptions, including that underscores, and not dashes or periods, are accepted within the “scheme” portion.

If a URL object is passed, it is returned as is.

参见

数据库 URL

function sqlalchemy.create_pool_from_url(url: str | URL, **kwargs: Any) → Pool¶

Create a pool instance from the given url.

If poolclass is not provided the pool class used is selected using the dialect specified in the URL.

The arguments passed to create_pool_from_url() are identical to the pool argument passed to the create_engine() function.

在 2.0.10 版本加入.

class sqlalchemy.engine.URL¶

Represent the components of a URL used to connect to a database.

URLs are typically constructed from a fully formatted URL string, where the make_url() function is used internally by the create_engine() function in order to parse the URL string into its individual components, which are then used to construct a new URL object. When parsing from a formatted URL string, the parsing format generally follows RFC-1738, with some exceptions.

A URL object may also be produced directly, either by using the make_url() function with a fully formed URL string, or by using the URL.create() constructor in order to construct a URL programmatically given individual fields. The resulting URL object may be passed directly to create_engine() in place of a string argument, which will bypass the usage of make_url() within the engine’s creation process.

在 1.4 版本发生变更: The URL object is now an immutable object. To create a URL, use the make_url() or URL.create() function / method. To modify a URL, use methods like URL.set() and URL.update_query_dict() to return a new URL object with modifications. See notes for this change at The URL object is now immutable.

参见

数据库 URL

URL contains the following attributes:

URL.drivername: database backend and driver name, such as postgresql+psycopg2
URL.username: username string
URL.password: password string
URL.host: string hostname
URL.port: integer port number
URL.database: string database name
URL.query: an immutable mapping representing the query string. contains strings for keys and either strings or tuples of strings for values.

Members

create(), database, difference_update_query(), drivername, get_backend_name(), get_dialect(), get_driver_name(), host, normalized_query, password, port, query, render_as_string(), set(), translate_connect_args(), update_query_dict(), update_query_pairs(), update_query_string(), username

Class signature

class sqlalchemy.engine.URL (builtins.tuple)

classmethod sqlalchemy.engine.URL.create(drivername: str, username: str | None = None, password: str | None = None, host: str | None = None, port: int | None = None, database: str | None = None, query: Mapping[str, Sequence[str] | str] = {}) → URL¶

Create a new URL object.

参见

数据库 URL

参数:

drivername¶ – the name of the database backend. This name will correspond to a module in sqlalchemy/databases or a third party plug-in.
username¶ – The user name.
password¶ –
database password. Is typically a string, but may also be an object that can be stringified with str().

备注

The password string should not be URL encoded when passed as an argument to URL.create(); the string should contain the password characters exactly as they would be typed.

备注

A password-producing object will be stringified only once per Engine object. For dynamic password generation per connect, see 生成动态身份验证令牌.
host¶ – The name of the host.
port¶ – The port number.
database¶ – The database name.
query¶ – A dictionary of string keys to string values to be passed to the dialect and/or the DBAPI upon connect. To specify non-string parameters to a Python DBAPI directly, use the create_engine.connect_args parameter to create_engine(). See also URL.normalized_query for a dictionary that is consistently string->list of string.

返回:

new URL object.

在 1.4 版本加入: The URL object is now an immutable named tuple. In addition, the query dictionary is also immutable. To create a URL, use the make_url() or URL.create() function/ method. To modify a URL, use the URL.set() and URL.update_query() methods.

attribute sqlalchemy.engine.URL.database: str | None¶: database name

method sqlalchemy.engine.URL.difference_update_query(names: Iterable[str]) → URL¶

Remove the given names from the URL.query dictionary, returning the new URL.

E.g.:

url = url.difference_update_query(["foo", "bar"])

Equivalent to using URL.set() as follows:

url = url.set(
    query={
        key: url.query[key]
        for key in set(url.query).difference(["foo", "bar"])
    }
)

在 1.4 版本加入.

参见

URL.query

URL.update_query_dict()

URL.set()

attribute sqlalchemy.engine.URL.drivername: str¶: database backend and driver name, such as postgresql+psycopg2

method sqlalchemy.engine.URL.get_backend_name() → str¶

Return the backend name.

This is the name that corresponds to the database backend in use, and is the portion of the URL.drivername that is to the left of the plus sign.

method sqlalchemy.engine.URL.get_dialect(_is_async: bool = False) → Type[Dialect]¶: Return the SQLAlchemy Dialect class corresponding to this URL’s driver name.

method sqlalchemy.engine.URL.get_driver_name() → str¶

Return the backend name.

This is the name that corresponds to the DBAPI driver in use, and is the portion of the URL.drivername that is to the right of the plus sign.

If the URL.drivername does not include a plus sign, then the default Dialect for this URL is imported in order to get the driver name.

attribute sqlalchemy.engine.URL.host: str | None¶: hostname or IP number. May also be a data source name for some drivers.

attribute sqlalchemy.engine.URL.normalized_query¶

Return the URL.query dictionary with values normalized into sequences.

As the URL.query dictionary may contain either string values or sequences of string values to differentiate between parameters that are specified multiple times in the query string, code that needs to handle multiple parameters generically will wish to use this attribute so that all parameters present are presented as sequences. Inspiration is from Python’s urllib.parse.parse_qs function. E.g.:

>>> from sqlalchemy.engine import make_url
>>> url = make_url(
...     "postgresql+psycopg2://user:pass@host/dbname?alt_host=host1&alt_host=host2&ssl_cipher=%2Fpath%2Fto%2Fcrt"
... )
>>> url.query
immutabledict({'alt_host': ('host1', 'host2'), 'ssl_cipher': '/path/to/crt'})
>>> url.normalized_query
immutabledict({'alt_host': ('host1', 'host2'), 'ssl_cipher': ('/path/to/crt',)})

attribute sqlalchemy.engine.URL.password: str | None¶: password, which is normally a string but may also be any object that has a __str__() method.

attribute sqlalchemy.engine.URL.port: int | None¶: integer port number

attribute sqlalchemy.engine.URL.query: immutabledict¶

an immutable mapping representing the query string. contains strings for keys and either strings or tuples of strings for values, e.g.:

   >>> from sqlalchemy.engine import make_url
   >>> url = make_url(
   ...     "postgresql+psycopg2://user:pass@host/dbname?alt_host=host1&alt_host=host2&ssl_cipher=%2Fpath%2Fto%2Fcrt"
   ... )
   >>> url.query
   immutabledict({'alt_host': ('host1', 'host2'), 'ssl_cipher': '/path/to/crt'})

To create a mutable copy of this mapping, use the ``dict`` constructor::

   mutable_query_opts = dict(url.query)

参见

URL.normalized_query - normalizes all values into sequences for consistent processing

Methods for altering the contents of URL.query:

URL.update_query_dict()

URL.update_query_string()

URL.update_query_pairs()

URL.difference_update_query()

method sqlalchemy.engine.URL.render_as_string(hide_password: bool = True) → str¶

Render this URL object as a string.

This method is used when the __str__() or __repr__() methods are used. The method directly includes additional options.

参数:: hide_password¶ – Defaults to True. The password is not shown in the string unless this is set to False.

return a new URL object with modifications.

Values are used if they are non-None. To set a value to None explicitly, use the URL._replace() method adapted from namedtuple.

参数:

drivername¶ – new drivername
username¶ – new username
password¶ – new password
host¶ – new hostname
port¶ – new port
query¶ – new query parameters, passed a dict of string keys referring to string or sequence of string values. Fully replaces the previous list of arguments.

返回:

new URL object.

在 1.4 版本加入.

参见

URL.update_query_dict()

method sqlalchemy.engine.URL.translate_connect_args(names: List[str] | None = None, **kw: Any) → Dict[str, Any]¶

Translate url attributes into a dictionary of connection arguments.

Returns attributes of this url (host, database, username, password, port) as a plain dictionary. The attribute names are used as the keys by default. Unset or false attributes are omitted from the final dictionary.

参数:

**kw¶ – Optional, alternate key names for url attributes.
names¶ – Deprecated. Same purpose as the keyword-based alternate names, but correlates the name to the original positionally.

method sqlalchemy.engine.URL.update_query_dict(query_parameters: Mapping[str, str | List[str]], append: bool = False) → URL¶

Return a new URL object with the URL.query parameter dictionary updated by the given dictionary.

The dictionary typically contains string keys and string values. In order to represent a query parameter that is expressed multiple times, pass a sequence of string values.

E.g.:

>>> from sqlalchemy.engine import make_url
>>> url = make_url("postgresql+psycopg2://user:pass@host/dbname")
>>> url = url.update_query_dict(
...     {"alt_host": ["host1", "host2"], "ssl_cipher": "/path/to/crt"}
... )
>>> str(url)
'postgresql+psycopg2://user:pass@host/dbname?alt_host=host1&alt_host=host2&ssl_cipher=%2Fpath%2Fto%2Fcrt'

参数:

query_parameters¶ – A dictionary with string keys and values that are either strings, or sequences of strings.
append¶ – if True, parameters in the existing query string will not be removed; new parameters will be in addition to those present. If left at its default of False, keys present in the given query parameters will replace those of the existing query string.

在 1.4 版本加入.

参见

URL.query

URL.update_query_string()

URL.update_query_pairs()

URL.difference_update_query()

URL.set()

method sqlalchemy.engine.URL.update_query_pairs(key_value_pairs: Iterable[Tuple[str, str | List[str]]], append: bool = False) → URL¶

Return a new URL object with the URL.query parameter dictionary updated by the given sequence of key/value pairs

E.g.:

>>> from sqlalchemy.engine import make_url
>>> url = make_url("postgresql+psycopg2://user:pass@host/dbname")
>>> url = url.update_query_pairs(
...     [
...         ("alt_host", "host1"),
...         ("alt_host", "host2"),
...         ("ssl_cipher", "/path/to/crt"),
...     ]
... )
>>> str(url)
'postgresql+psycopg2://user:pass@host/dbname?alt_host=host1&alt_host=host2&ssl_cipher=%2Fpath%2Fto%2Fcrt'

参数:

key_value_pairs¶ – A sequence of tuples containing two strings each.
append¶ – if True, parameters in the existing query string will not be removed; new parameters will be in addition to those present. If left at its default of False, keys present in the given query parameters will replace those of the existing query string.

在 1.4 版本加入.

参见

URL.query

URL.difference_update_query()

URL.set()

method sqlalchemy.engine.URL.update_query_string(query_string: str, append: bool = False) → URL¶

Return a new URL object with the URL.query parameter dictionary updated by the given query string.

E.g.:

>>> from sqlalchemy.engine import make_url
>>> url = make_url("postgresql+psycopg2://user:pass@host/dbname")
>>> url = url.update_query_string(
...     "alt_host=host1&alt_host=host2&ssl_cipher=%2Fpath%2Fto%2Fcrt"
... )
>>> str(url)
'postgresql+psycopg2://user:pass@host/dbname?alt_host=host1&alt_host=host2&ssl_cipher=%2Fpath%2Fto%2Fcrt'

参数:

query_string¶ – a URL escaped query string, not including the question mark.
append¶ – if True, parameters in the existing query string will not be removed; new parameters will be in addition to those present. If left at its default of False, keys present in the given query parameters will replace those of the existing query string.

在 1.4 版本加入.

参见

URL.query

URL.update_query_dict()

attribute sqlalchemy.engine.URL.username: str | None¶: username string

连接池¶

Pooling

中文

当调用 connect() 或 execute() 方法时，Engine 会向连接池请求一个连接。默认的连接池 QueuePool 会根据需要建立与数据库的连接。随着并发语句的执行，QueuePool 会将其连接池扩展到默认的五个连接，并允许默认“溢出”数量为十个。由于 Engine 本质上是连接池的“主控中心”，因此应该在应用中为每个数据库仅创建一个 Engine 实例，而不是为每次连接都新建一个实例。

备注

SQLite 引擎默认不使用 QueuePool。有关 SQLite 连接池的使用详情，请参阅 SQLite。

有关连接池的更多信息，请参阅连接池。

英文

The Engine will ask the connection pool for a connection when the connect() or execute() methods are called. The default connection pool, QueuePool, will open connections to the database on an as-needed basis. As concurrent statements are executed, QueuePool will grow its pool of connections to a default size of five, and will allow a default “overflow” of ten. Since the Engine is essentially “home base” for the connection pool, it follows that you should keep a single Engine per database established within an application, rather than creating a new one for each connection.

备注

QueuePool is not used by default for SQLite engines. See SQLite for details on SQLite connection pool usage.

For more information on connection pooling, see 连接池.

Engine 配置¶

支持的数据库¶

数据库 URL¶

转义密码中的特殊字符，例如 @ 符号¶

以编程方式创建 URL¶

后端特定的 URL¶

PostgreSQL¶

MySQL¶

Oracle¶

Microsoft SQL Server¶

SQLite¶

其他¶

引擎创建 API¶

连接池¶

自定义 DBAPI connect() 参数/连接例程¶

传递给 dbapi.connect() 的特殊关键字参数¶

将参数添加到 URL 查询字符串¶

使用 connect_args 字典参数¶

控制如何将参数传递给 DBAPI connect() 函数¶

生成动态身份验证令牌¶

在连接后修改 DBAPI 连接，或在连接后运行命令¶

完全替换 DBAPI `connect()` 函数¶

配置日志记录¶

有关 Echo 标志的更多信息¶

设置日志记录名称¶

设置每个连接/子引擎令牌¶

隐藏参数¶

Engine 配置¶

支持的数据库¶

数据库 URL¶

转义密码中的特殊字符，例如 @ 符号¶

以编程方式创建 URL¶

后端特定的 URL¶

PostgreSQL¶

MySQL¶

Oracle¶

Microsoft SQL Server¶

SQLite¶

其他¶

引擎创建 API¶

连接池¶

自定义 DBAPI connect() 参数/连接例程¶

传递给 dbapi.connect() 的特殊关键字参数¶

将参数添加到 URL 查询字符串¶

使用 connect_args 字典参数¶

控制如何将参数传递给 DBAPI connect() 函数¶

生成动态身份验证令牌¶

在连接后修改 DBAPI 连接，或在连接后运行命令¶

完全替换 DBAPI connect() 函数¶

配置日志记录¶

有关 Echo 标志的更多信息¶

设置日志记录名称¶

设置每个连接/子引擎令牌¶

隐藏参数¶

完全替换 DBAPI `connect()` 函数¶