MySQL 和 Mariadb

优质
小牛编辑
145浏览
2023-12-01

Support for the MySQL / MariaDB database.

The following table summarizes current support levels for database release versions.

Supported MySQL / MariaDB versions

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

支持的版本和功能

SQLAlchemy支持MySQL从5.0.2版到现代版本,以及MariaDB的所有现代版本。有关任何给定服务器版本中支持的功能的详细信息,请参阅官方MySQL文档。

在 1.4 版更改: 现在支持的最低MySQL版本是5.0.2。

MariaDB支持

MySQL的MariaDB变体保留了与MySQL协议的基本兼容性,但是这两个产品的开发仍然存在分歧。在SQLAlchemy领域内,这两个数据库有少量的语法和行为差异,SQLAlchemy会自动适应这些差异。要连接到MariaDB数据库,不需要更改数据库URL::

engine = create_engine("mysql+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4")

在第一次连接时,SQLAlchemy方言使用一个服务器版本检测方案来确定后台数据库是否报告为MariaDB。根据这个标志,方言可以在其行为必须不同的地区做出不同的选择。

仅限MariaDB模式

方言也支持 可选择的 “MariaDB-only”连接模式,对于应用程序使用MariaDB特定功能并且与MySQL数据库不兼容的情况,这可能很有用。要使用此操作模式,请将上述URL中的“mysql”令牌替换为“mariadb”:

engine = create_engine("mariadb+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4")

如果服务器版本检测检测到备份数据库不是MariaDB,则上述引擎在第一次连接时将引发错误。

使用发动机时 "mariadb" 作为方言名, all mysql-specific options that include the name "mysql" in them are now named with "mariadb" . 这意味着可以选择 mysql_engine 应该命名 mariadb_engine ,等等。“mysql”和“mariadb”选项可以同时用于那些同时使用“mysql”和“mariadb”方言的url的应用程序:

my_table = Table(
    "mytable",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("textdata", String(50)),
    mariadb_engine="InnoDB",
    mysql_engine="InnoDB",
)

Index(
    "textdata_ix",
    my_table.c.textdata,
    mysql_prefix="FULLTEXT",
    mariadb_prefix="FULLTEXT",
)

当反映上述结构时,也会发生类似的行为,即当数据库URL基于“mariadb”名称时,“mariadb”前缀将出现在选项名称中。

1.4 新版功能: 为MySQL方言添加了支持“mariadb only mode”的“mariadb”方言名称。

连接超时和断开

MySQL/MariaDB具有自动关闭连接的特性,对于空闲了一段固定时间的连接,默认为8小时。要避免出现此问题,请使用 create_engine.pool_recycle 选项,确保连接在池中存在固定秒数时将被丢弃并替换为新连接:

engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)

为了更全面地检测池连接的断开连接,包括适应服务器重启和网络问题,可以采用预ping方法。见 处理断开连接 对于目前的方法。

参见

处理断开连接 -了解处理超时连接和数据库重新启动的几种技术。

创建包含存储引擎的表参数

MySQL和MariaDB的CREATE TABLE语法都包含大量的特殊选项,包括 ENGINECHARSETMAX_ROWSROW_FORMATINSERT_METHOD 以及更多。要适应这些参数的呈现,请指定表单 mysql_argument_name="value" . 例如,指定带有 ENGINE 属于 InnoDBCHARSET 属于 utf8mb4KEY_BLOCK_SIZE 属于 1024 ::

Table('mytable', metadata,
      Column('data', String(32)),
      mysql_engine='InnoDB',
      mysql_charset='utf8mb4',
      mysql_key_block_size="1024"
     )

当支持时 仅限MariaDB模式 模式时,还必须包括与“MariaDB”前缀对应的类似键。当然,这些值可以独立变化,因此可以维护MySQL和MariaDB上的不同设置:

# support both "mysql" and "mariadb-only" engine URLs

Table('mytable', metadata,
      Column('data', String(32)),

      mysql_engine='InnoDB',
      mariadb_engine='InnoDB',

      mysql_charset='utf8mb4',
      mariadb_charset='utf8',

      mysql_key_block_size="1024"
      mariadb_key_block_size="1024"

     )

MySQL/MariaDB方言通常会将指定为 mysql_keyword_name 将呈现为 KEYWORD_NAMECREATE TABLE 语句。这些名称中的一小部分将用空格而不是下划线呈现;为了支持这一点,MySQL方言了解这些特定的名称,其中包括 DATA DIRECTORY (例如) mysql_data_directoryCHARACTER SET (例如) mysql_character_setINDEX DIRECTORY (例如) mysql_index_directory

最常见的论点是 mysql_engine ,表示表的存储引擎。在历史上,MySQL服务器安装默认为 MyISAM 对于该值,尽管较新版本可能默认为 InnoDB . 这个 InnoDB 引擎通常优先用于支持事务和外键。

A Table 它是在MySQL/MariaDB数据库中创建的,存储引擎为 MyISAM 基本上是非事务性的,这意味着引用此表的任何插入/更新/删除语句都将作为自动提交调用。它也不支持外键约束;而 CREATE TABLE 语句在使用 MyISAM 存储引擎将丢弃这些参数。反射这样的表也不会产生外键约束信息。

对于完全原子事务以及对外键约束的支持,所有参与 CREATE TABLE 语句必须指定一个事务引擎,在大多数情况下, InnoDB .

区分大小写和表反射

MySQL和MariaDB对区分大小写的标识符名称的支持不一致,这是基于底层操作系统的特定细节的支持。但是,已经观察到,无论存在什么区分大小写的行为,外键声明中的表名都是 总是 从数据库接收的所有小写字母,使其无法准确反映相关表使用混合大小写标识符名称的模式。

因此,强烈建议在SQLAlchemy中以及在MySQL/MariaDB数据库本身中,表名都声明为小写,特别是在使用数据库反射特性的情况下。

事务隔离级别

所有MySQL/MariaDB方言都支持通过方言特定参数设置事务隔离级别 create_engine.isolation_level 被接受 create_engine() 以及 Connection.execution_options.isolation_level 传递给的参数 Connection.execution_options() . 此功能通过发出命令来工作 SET SESSION TRANSACTION ISOLATION LEVEL <level> 对于每个新连接。对于特殊的自动提交隔离级别,使用DBAPI特定的技术。

要设置隔离级别,请使用 create_engine() ::

engine = create_engine(
                "mysql://scott:tiger@localhost/test",
                isolation_level="READ UNCOMMITTED"
            )

使用每个连接执行选项进行设置:

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="READ COMMITTED"
)

的有效值 isolation_level 包括:

  • READ COMMITTED

  • READ UNCOMMITTED

  • REPEATABLE READ

  • SERIALIZABLE

  • AUTOCOMMIT

特殊 AUTOCOMMIT MySQL提供的MySQL、pisdb和MySQL当前支持的各种属性的“autoql、dba”都是MySQL特有的。使用它,数据库连接将为的值返回true SELECT @@autocommit; .

参见

设置事务隔离级别,包括DBAPI Autocommit

自动增量行为

创建表时,SQLAlchemy将自动设置 AUTO_INCREMENT 论第一 Integer 未标记为外键的主键列::

>>> t = Table('mytable', metadata,
...   Column('mytable_id', Integer, primary_key=True)
... )
>>> t.create()
CREATE TABLE mytable (
        id INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id)
)

可以通过传递禁用此行为 FalseColumn.autoincrement 的参数 Column . 对于某些存储引擎,此标志还可用于在多列键的辅助列上启用自动递增:

Table('mytable', metadata,
      Column('gid', Integer, primary_key=True, autoincrement=False),
      Column('id', Integer, primary_key=True)
     )

服务器端光标

服务器端游标支持可用于mysqlclient、PyMySQL、mariadbconnector方言,也可能在其他语言中可用。这将使用“Buffered=True/False”标志(如果可用),或者使用诸如 MySQLdb.cursors.SSCursorpymysql.cursors.SSCursor 在内部。

服务器端游标通过使用 Connection.execution_options.stream_results 连接执行选项:

with engine.connect() as conn:
    result = conn.execution_options(stream_results=True).execute(text("select * from table"))

请注意,服务器端游标可能不支持某些类型的SQL语句;通常,只有返回行的SQL语句才应与此选项一起使用。

1.4 版后已移除: 方言级别的服务器u side_cursors标志已弃用,将在将来的版本中删除。请使用 Connection.stream_results 无缓冲游标支持的执行选项。

参见

使用服务器端游标(即流结果)

统一码

字符集选择

大多数MySQL/MariaDB dbapi都提供了为连接设置客户机字符集的选项。通常使用 charset URL中的参数,例如:

e = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")

这个字符集是 客户端字符集 用于连接。一些mysql dbapis会将其默认为 latin1 有些人会利用 default-character-set 设置在 my.cnf 也要归档。对于特定的行为,应该参考正在使用的DBAPI的文档。

传统上用于Unicode的编码是 'utf8' . 但是,对于MySQL版本5.5.3和MariaDB 5.5 on forward,一种新的MySQL特定编码 'utf8mb4' 已经引入,从MySQL8.0开始,如果是普通的,服务器将发出警告 utf8 在任何服务器端指令中指定,替换为 utf8mb3 . 这种新编码的基本原理是因为MySQL的传统utf-8编码只支持最多3个字节的码位,而不是4个字节。因此,当与MySQL或MariaDB数据库通信时,如果数据库和客户机DBAPI都支持这个新字符集,那么这个新字符集是首选的,如:

e = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")

所有现代DBAPI都应该支持 utf8mb4 字符集

为了使用 utf8mb4 使用旧版创建的架构的编码 utf8 ,可能需要更改MySQL/MariaDB模式和/或服务器配置。

参见

The utf8mb4 Character Set -在MySQL文档中

处理二进制数据警告和Unicode

MySQL 5.6、5.7及更高版本(撰写本文时不是Mariadb)现在在尝试将二进制数据传递到数据库时发出警告,而当二进制数据本身对该编码无效时,字符集编码也已就位:

default.py:509: Warning: (1300, "Invalid utf8mb4 character string:
'F9876A'")
  cursor.execute(statement, parameters)

此警告是由于MySQL客户端库试图将二进制字符串解释为Unicode对象,即使数据类型如 LargeBinary 正在使用中。要解决此问题,SQL语句要求二进制“character set introducer”出现在呈现为以下形式的任何非空值之前:

INSERT INTO table (data) VALUES (_binary %s)

这些字符集介绍器是由DBAPI驱动程序提供的,假定使用mysqlclient或pymysql(建议使用这两种方法)。添加查询字符串参数 binary_prefix=true 到修复此警告的URL::

# mysqlclient
engine = create_engine(
    "mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")

# PyMySQL
engine = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")

这个 binary_prefix 其他MySQL驱动程序可能支持标志,也可能不支持标志。

SQLAlchemy本身无法呈现此 _binary 前缀可靠,因为它不与空值一起工作,空值有效,可以作为绑定参数发送。当mysql驱动程序将参数直接呈现到SQL字符串中时,它是传递这个额外关键字的最有效的地方。

参见

Character set introducers -在MySQL网站上

ANSI引用样式

MySQL/MariaDB有两种标识符“引用样式”,一种使用反号,另一种使用引号,例如 `some_identifier 与 `"some_identifier"`` 。所有MySQL方言都通过检查 sql_mode 当第一次与特定的 Engine 。此引用样式在呈现表名和列名以及反映现有数据库结构时起作用。检测是完全自动的,不需要特殊配置即可使用这两种报价方式。

更改sql_mode

MySQL支持在多个 Server SQL Modes 对于服务器和客户端都是如此。要更改 sql_mode 对于给定的应用程序,开发人员可以利用SQLAlChemy的事件系统。

在下面的示例中,事件系统用于设置 sql_modefirst_connectconnect 活动::

from sqlalchemy import create_engine, event

eng = create_engine("mysql://scott:tiger@localhost/test", echo='debug')

# `insert=True` will ensure this is the very first listener to run
@event.listens_for(eng, "connect", insert=True)
def connect(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'")

conn = eng.connect()

在上面所示的示例中,“connect”事件将在第一次为给定池创建特定DBAPI连接时,在该连接对该连接池可用之前调用该连接上的“set”语句。此外,因为该函数注册了 insert=True ,它将优先于已注册函数的内部列表。

MySQL/MariaDB SQL扩展

许多MySQL/MariaDB SQL扩展都是通过SQLAlchemy的通用函数和运算符支持来处理的:

table.select(table.c.password==func.md5('plaintext'))
table.select(table.c.username.op('regexp')('^[a-d]'))

当然,任何有效的SQL语句也可以作为字符串执行。

目前有一些有限的直接支持MySQL/MariaDB扩展到SQL。

在重复密钥更新时插入…(向上插入)

MySQL/MariaDB允许通过 ON DUPLICATE KEY UPDATE 条款 INSERT 语句。只有当候选行与表中现有的主键或唯一键不匹配时,才会插入该行;否则,将执行更新。该语句允许分别指定要插入的值和要更新的值。

SQLAlchemy提供 ON DUPLICATE KEY UPDATE 通过特定于MySQL的支持 insert() 函数,提供生成方法 Insert.on_duplicate_key_update()

>>> from sqlalchemy.dialects.mysql import insert

>>> insert_stmt = insert(my_table).values(
...     id='some_existing_id',
...     data='inserted value')

>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     data=insert_stmt.inserted.data,
...     status='U'
... )
>>> print(on_duplicate_key_stmt)
INSERT INTO my_table (id, data) VALUES (%s, %s)
ON DUPLICATE KEY UPDATE data = VALUES(data), status = %s

与postgresql的“on conflict”短语不同,“on duplicate key update”短语将始终与任何主键或唯一键匹配,并且在匹配的情况下始终执行更新;它没有引发错误或跳过执行更新的选项。

ON DUPLICATE KEY UPDATE 用于执行已存在行的更新,使用新值和建议插入的值的任意组合。这些值通常使用传递给 Insert.on_duplicate_key_update() 给定的列键值(通常是列的名称,除非它指定 Column.key )作为键和文本或SQL表达式作为值:

>>> insert_stmt = insert(my_table).values(
...          id='some_existing_id',
...          data='inserted value')

>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     data="some data",
...     updated_at=func.current_timestamp(),
... )

>>> print(on_duplicate_key_stmt)
INSERT INTO my_table (id, data) VALUES (%s, %s)
ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP

以类似于 UpdateBase.values() ,可接受其他参数形式,包括单个字典:

>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     {"data": "some data", "updated_at": func.current_timestamp()},
... )

以及一个2元组列表,它将以类似于中描述的方式自动提供参数顺序更新语句。 参数顺序更新 . 不像 Update 对象,则不需要特殊标志来指定意图,因为参数形式为this context是明确的:

>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     [
...         ("data", "some data"),
...         ("updated_at", func.current_timestamp()),
...     ]
... )

>>> print(on_duplicate_key_stmt)
INSERT INTO my_table (id, data) VALUES (%s, %s)
ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP

在 1.3 版更改: 在mysql中支持参数有序更新子句,进行重复的密钥更新

警告

这个 Insert.on_duplicate_key_update() 方法做 not 考虑到python端的默认更新值或生成函数,例如使用 Column.onupdate . 除非在参数中显式指定了这些值,否则不会对on-duplicate key样式的更新执行这些值。

为了引用建议的插入行,特殊别名 Insert.inserted 在上作为属性提供 Insert 对象;此对象是 ColumnCollection 包含目标表的所有列:

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh')

>>> do_update_stmt = stmt.on_duplicate_key_update(
...     data="updated value",
...     author=stmt.inserted.author
... )

>>> print(do_update_stmt)
INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE data = %s, author = VALUES(author)

呈现时,“插入的”命名空间将生成表达式 VALUES(<columnname>) .

1.2 新版功能: 在重复的key update子句上添加了对mysql的支持

行数支持

SQLAlchemy标准化DBAPI cursor.rowcount 属性是“更新或删除匹配的行数”语句的常规定义。这与大多数mysql dbapi驱动程序的默认设置相矛盾,即“实际修改/删除的行数”。因此,sqlAlchemy mysql方言总是添加 constants.CLIENT.FOUND_ROWS 连接时,标记或与目标方言等效的内容。此设置当前已硬编码。

参见

CursorResult.rowcount

MySQL/MariaDB-特定索引选项

MySQL和MariaDB特定于 Index 结构可用。

索引长度

MySQL和MariaDB都提供了创建具有一定长度的索引项的选项,其中“length”是指每个值中将成为索引一部分的字符或字节数。SQLAlchemy通过 mysql_length 和/或 mariadb_length 参数::

Index('my_index', my_table.c.data, mysql_length=10, mariadb_length=10)

Index('a_b_idx', my_table.c.a, my_table.c.b, mysql_length={'a': 4,
                                                           'b': 9})

Index('a_b_idx', my_table.c.a, my_table.c.b, mariadb_length={'a': 4,
                                                           'b': 9})

前缀长度以字符表示(非二进制字符串类型),以字节表示(二进制字符串类型)。传递给关键字参数的值 must 可以是整数(因此,为索引的所有列指定相同的前缀长度值)或dict,其中键是列名,值是相应列的前缀长度值。MySQL和MariaDB只允许一个索引列的长度,如果它是CHAR、VARCHAR、TEXT、BINARY、VARBINARY和BLOB。

索引前缀

MySQL存储引擎允许您在创建索引时指定索引前缀。SQLAlchemy通过 mysql_prefix 参数对 Index ::

Index('my_index', my_table.c.data, mysql_prefix='FULLTEXT')

传递给关键字参数的值将简单地传递给基础的创建索引,因此 must 成为MySQL存储引擎的有效索引前缀。

1.1.5 新版功能.

参见

CREATE INDEX -MySQL文档

索引类型

某些MySQL存储引擎允许您在创建索引或主键约束时指定索引类型。SQLAlchemy通过 mysql_using 参数对 Index ::

Index('my_index', my_table.c.data, mysql_using='hash', mariadb_using='hash')

以及 mysql_using 参数对 PrimaryKeyConstraint ::

PrimaryKeyConstraint("data", mysql_using='hash', mariadb_using='hash')

传递给关键字参数的值将简单地传递给基础的create index或primary key子句,因此 must 成为MySQL存储引擎的有效索引类型。

有关更多信息,请访问:

https://dev.mysql.com/doc/refman/5.0/en/create-index.html

https://dev.mysql.com/doc/refman/5.0/en/create-table.html

索引解析器

在MySQL中创建全文索引还支持“with parser”选项。这可以使用关键字参数 mysql_with_parser ::

Index(
    'my_index', my_table.c.data,
    mysql_prefix='FULLTEXT', mysql_with_parser="ngram",
    mariadb_prefix='FULLTEXT', mariadb_with_parser="ngram",
)

1.3 新版功能.

MySQL/MariaDB外键

MySQL和MariaDB关于外键的行为有一些重要的注意事项。

要避免的外键参数

MySQL和MariaDB都不支持外键参数“deferable”、“INITIALLY”或“MATCH”。使用 deferrableinitially 关键字参数 ForeignKeyConstraintForeignKey 将具有在DDL表达式中呈现这些关键字的效果,这将在MySQL或MariaDB上引发错误。为了在MySQL/MariaDB后端忽略这些关键字的同时对外键使用这些关键字,请使用自定义编译规则:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import ForeignKeyConstraint

@compiles(ForeignKeyConstraint, "mysql", "mariadb")
def process(element, compiler, **kw):
    element.deferrable = element.initially = None
    return compiler.visit_foreign_key_constraint(element, **kw)

实际上,“MATCH”关键字更隐蔽,并且SQLAlchemy和MySQL或MariaDB后端一起明确禁止使用。MySQL/MariaDB悄悄地忽略了这个参数,但是除此之外,ON UPDATE和ON DELETE选项也会被后端忽略。因此,MATCH永远不应该与MySQL/MariaDB后端一起使用;就像deferable和最初的情况一样,可以在DDL定义时使用自定义编译规则来更正ForeignKeyConstraint。

外键约束的反射

并非所有MySQL/MariaDB存储引擎都支持外键。当使用非常常见的 MyISAM MySQL存储引擎中,由表反射加载的信息将不包含外键。对于这些表,您可以提供 ForeignKeyConstraint 反射时间:

Table('mytable', metadata,
      ForeignKeyConstraint(['other_id'], ['othertable.other_id']),
      autoload_with=engine
     )

参见

创建包含存储引擎的表参数

MySQL/MariaDB独特的约束和反射

SQLAlchemy支持 Index 用标志构造 unique=True ,指示唯一索引,以及 UniqueConstraint 构造,表示唯一约束。当发出DDL来创建这些约束时,MySQL/MariaDB都支持这两个对象/语法。但是,MySQL/MariaDB没有独立于唯一索引的unique约束构造;也就是说,MySQL/MariaDB上的“unique”约束相当于创建一个“unique index”。

在反映这些构造时, Inspector.get_indexes() 以及 Inspector.get_unique_constraints() 方法将 both 返回MySQL/MariaDB中唯一索引的条目。但是,在使用执行全表反射时 Table(..., autoload_with=engine) ,即 UniqueConstraint 构造为 not 全反射的一部分 Table 构造;此构造始终由 Index 使用 unique=True 中显示的设置 Table.indexes 收藏。

时间戳/日期时间问题

MySQL/MariaDB在更新当前时间戳时呈现的显式“默认值”为“时间戳”

MySQL/MariaDB曾经为 TIMESTAMP 在短语“TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”中输入数据类型,其中包括在更新发生时自动用当前时间戳更新列的非标准SQL,在需要服务器端更新更改的情况下,无需使用触发器。

MySQL5.6引入了一个新标志 explicit_defaults_for_timestamp 这将禁用上述行为,并且在MySQL8中,此标志缺省为true,这意味着为了在不更改此标志的情况下获得MySQL“on update timeamp”,必须显式呈现上面的DDL。此外,相同的DDL对于使用 DATETIME 数据类型也是如此。

SQLAlchemy的MySQL方言还没有一个选项来生成MySQL的“ON UPDATE CURRENT_TIMESTAMP”子句,注意到这不是一个通用的“ON UPDATE”,因为在标准SQL中没有这样的语法。SQL炼金术 Column.server_onupdate 参数当前与此特殊的MySQL行为无关。

要生成此DDL,请使用 Column.server_default 参数,并传递还包括ON UPDATE子句的文本子句:

from sqlalchemy import Table, MetaData, Column, Integer, String, TIMESTAMP
from sqlalchemy import text

metadata = MetaData()

mytable = Table(
    "mytable",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('data', String(50)),
    Column(
        'last_updated',
        TIMESTAMP,
        server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
    )
)

同样的说明适用于 DateTimeDATETIME 数据类型:

from sqlalchemy import DateTime

mytable = Table(
    "mytable",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('data', String(50)),
    Column(
        'last_updated',
        DateTime,
        server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
    )
)

即使 Column.server_onupdate feature不会生成这个DDL,它仍然需要向ORM发出信号,告诉ORM应该获取这个更新的值。此语法如下所示:

from sqlalchemy.schema import FetchedValue

class MyClass(Base):
    __tablename__ = 'mytable'

    id = Column(Integer, primary_key=True)
    data = Column(String(50))
    last_updated = Column(
        TIMESTAMP,
        server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
        server_onupdate=FetchedValue()
    )

时间戳列和空

MySQL在历史上强制指定timestamp数据类型的列隐式包含当前时间戳的默认值,即使未声明此值,并且另外将该列设置为非空,与所有其他数据类型的行为相反:

mysql> CREATE TABLE ts_test (
    -> a INTEGER,
    -> b INTEGER NOT NULL,
    -> c TIMESTAMP,
    -> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -> e TIMESTAMP NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE ts_test;
+---------+-----------------------------------------------------
| Table   | Create Table
+---------+-----------------------------------------------------
| ts_test | CREATE TABLE `ts_test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) NOT NULL,
  `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `e` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

上面,我们看到一个整型列默认为空,除非它被指定为非空。但是当列的类型为timestamp时,会生成一个隐式的默认值current_timestamp,这也会强制该列为非空列,即使我们没有这样指定它。

属性可以在MySQL端更改MySQL的这一行为 explicit_defaults_for_timestamp MySQL 5.6中引入的配置标志。在启用此服务器设置的情况下,时间戳列的行为与MySQL端的任何其他数据类型在缺省值和可空性方面相同。

但是,为了适应绝大多数没有指定这个新标志的MySQL数据库,sqlAlchemy使用任何没有指定的时间戳列显式地发出“空”说明符。 nullable=False . 以便容纳指定 explicit_defaults_for_timestamp ,对于确实指定了 nullable=False . 以下示例说明:

from sqlalchemy import MetaData, Integer, Table, Column, text
from sqlalchemy.dialects.mysql import TIMESTAMP

m = MetaData()
t = Table('ts_test', m,
        Column('a', Integer),
        Column('b', Integer, nullable=False),
        Column('c', TIMESTAMP),
        Column('d', TIMESTAMP, nullable=False)
    )


from sqlalchemy import create_engine
e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
m.create_all(e)

输出:

CREATE TABLE ts_test (
    a INTEGER,
    b INTEGER NOT NULL,
    c TIMESTAMP NULL,
    d TIMESTAMP NOT NULL
)

在 1.0.0 版更改: -sqlAlchemy现在在所有情况下都为时间戳列呈现空值或非空值,以适应 explicit_defaults_for_timestamp . 在此版本之前,它不会为时间戳列呈现“not null”,即 nullable=False .

MySQL SQL构造

Object NameDescription

match

制作一个 MATCH (X, Y) AGAINST ('TEXT') 条款。

class sqlalchemy.dialects.mysql.match(*cols, **kw)

与所有SQLAlchemy方言一样,已知对MySQL有效的所有大写类型都可以从顶级方言导入:

from sqlalchemy.dialects.mysql import \
        BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \
        DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \
        LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \
        NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \
        TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR

特定于MySQL或具有特定于MySQL的构造参数的类型如下:

Object NameDescription

BIGINT

MySQL BigInteger类型。

BINARY

SQL二进制类型。

BIT

MySQL位类型。

BLOB

SQL blob类型。

BOOLEAN

SQL布尔类型。

CHAR

mysql char类型,用于固定长度的字符数据。

DATE

SQL日期类型。

DATETIME

MySQL日期时间类型。

DECIMAL

mysql decimal类型。

DOUBLE

mysql双类型。

ENUM

MySQL枚举类型。

FLOAT

mysql float类型。

INTEGER

MySQL整数类型。

JSON

mysql json类型。

LONGBLOB

mysql longblob类型,用于2^32字节的二进制数据。

LONGTEXT

MySQL Longtext类型,用于最多2^32个字符的文本。

MEDIUMBLOB

mysql mediumblob类型,用于二进制数据,最大2^24字节。

MEDIUMINT

MySQL Mediuminteger类型。

MEDIUMTEXT

mysql mediumtext类型,用于最多2^24个字符的文本。

NCHAR

mysql nchar类型。

NUMERIC

MySQL数字类型。

NVARCHAR

mysql nvarchar类型。

REAL

mysql实型。

SET

MySQL设置类型。

SMALLINT

mysql smallinteger类型。

TEXT

mysql文本类型,用于最多2^16个字符的文本。

TIME

MySQL时间类型。

TIMESTAMP

MySQL时间戳类型。

TINYBLOB

mysql tinyblob类型,用于2^8字节的二进制数据。

TINYINT

mysql tinyint类型。

TINYTEXT

mysql tinytext类型,用于最多2^8个字符的文本。

VARBINARY

sql varbinary类型。

VARCHAR

mysql varchar类型,用于可变长度字符数据。

YEAR

mysql-year类型,用于1901-2155年的单字节存储。

class sqlalchemy.dialects.mysql.BIGINT(display_width=None, **kw)
Object NameDescription

insert(table[, values, inline, bind, ...], **dialect_kw)

构建一个 Insert 对象。

Insert

特定于MySQL的insert实现。

function sqlalchemy.dialects.mysql.insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Support for the MySQL / MariaDB database via the mysqlclient (maintained fork of MySQL-Python) driver.

DBAPI

Documentation and download information (if applicable) for mysqlclient (maintained fork of MySQL-Python) is available at: https://pypi.org/project/mysqlclient/

Connecting

Connect String:

mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

驱动程序状态

mysqlclient DBAPI是 MySQL-Python 不再维护的DBAPI。 mysqlclient 支持Python2和Python3,非常稳定。

统一码

请看 统一码 有关Unicode处理的当前建议。

SSL连接

mysqlclient和PyMySQL DBAPI接受“ssl”键下的附加字典,该字典可以使用 create_engine.connect_args 字典::

engine = create_engine(
    "mysql+mysqldb://scott:tiger@192.168.0.134/test",
    connect_args={
        "ssl": {
            "ssl_ca": "/home/gord/client-ssl/ca.pem",
            "ssl_cert": "/home/gord/client-ssl/client-cert.pem",
            "ssl_key": "/home/gord/client-ssl/client-key.pem"
        }
    }
)

为方便起见,还可以在URL内内联指定以下密钥,这些密钥将被自动解释到“SSL”字典中:“ssl_ca”、“ssl_cert”、“ssl_key”、“ssl_capath”、“ssl_cipher”、“ssl_check_hostname”。示例如下:

connection_uri = (
    "mysql+mysqldb://scott:tiger@192.168.0.134/test"
    "?ssl_ca=/home/gord/client-ssl/ca.pem"
    "&ssl_cert=/home/gord/client-ssl/client-cert.pem"
    "&ssl_key=/home/gord/client-ssl/client-key.pem"
)

如果服务器使用的是自动生成的自签名证书或与主机名不匹配(从客户端来看),则可能还需要指明 ssl_check_hostname=false ::

connection_uri = (
    "mysql+pymysql://scott:tiger@192.168.0.134/test"
    "?ssl_ca=/home/gord/client-ssl/ca.pem"
    "&ssl_cert=/home/gord/client-ssl/client-cert.pem"
    "&ssl_key=/home/gord/client-ssl/client-key.pem"
    "&ssl_check_hostname=false"
)

参见

SSL连接 在PyMySQL方言中

使用mysqldb和google cloud sql

google cloud sql现在建议使用mysqldb方言。使用以下URL连接:

mysql+mysqldb://root@/<dbname>?unix_socket=/cloudsql/<projectid>:<instancename>

服务器端光标

mysqldb方言支持服务器端游标。见 服务器端光标 .

PyySQL

Support for the MySQL / MariaDB database via the PyMySQL driver.

DBAPI

Documentation and download information (if applicable) for PyMySQL is available at: https://pymysql.readthedocs.io/

Connecting

Connect String:

mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

统一码

请看 统一码 有关Unicode处理的当前建议。

SSL连接

PyMySQL DBAPI接受与MySQLdb相同的SSL参数,如 SSL连接 。有关示例,请参阅该部分。

mysql-python兼容性

pymysql dbapi是mysql python(mysqldb)驱动程序的纯python端口,目标是100%的兼容性。mysql python的大多数行为注释也适用于pymysql驱动程序。

MySQL连接器

Support for the MySQL / MariaDB database via the MySQL Connector/Python driver.

DBAPI

Documentation and download information (if applicable) for MySQL Connector/Python is available at: https://pypi.org/project/mysql-connector-python/

Connecting

Connect String:

mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

注解

mysql connector/python dbapi自发布以来一直存在许多问题,其中一些问题可能仍未解决,mysqlconnector方言是 未作为SQLAlchemy持续集成的一部分进行测试 .推荐的mysql方言是mysqlclient和pymysql。

异步

通过异步驱动程序支持MySQL/MariaDB数据库。

DBAPI

Documentation and download information (if applicable) for asyncmy is available at: https://github.com/long2ice/asyncmy

连接

Connect String:

mysql+asyncmy://user:password@host:port/dbname[?key=value&key=value...]

注解

添加2021年9月的异步方言是为了提供MySQL/MariaDB异步兼容,因为 Aimysql 数据库驱动程序已经变得没有维护,但是异步本身是非常新的。

使用特殊的异步中介层,异步方言可用作 SQLAlchemy asyncio 扩展包。

此方言通常应仅与 create_async_engine() 引擎创建功能::

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("mysql+asyncmy://user:pass@hostname/dbname?charset=utf8mb4")

Aimysql

通过aiomysql驱动程序支持MySQL/MariaDB数据库。

DBAPI

Documentation and download information (if applicable) for aiomysql is available at: https://github.com/aio-libs/aiomysql

连接

Connect String:

mysql+aiomysql://user:password@host:port/dbname[?key=value&key=value...]

警告

截至2021年9月的aiomysql方言似乎没有维护,不再适用于Python版本3.10。请参阅 异步 当前MySQL/MariaDD异步功能的方言。

aiomysql方言是SQLAlChemy的第二种Python异步方言。

使用特殊的异步中介层,aiomysql方言可用作 SQLAlchemy asyncio 扩展包。

此方言通常应仅与 create_async_engine() 引擎创建功能::

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("mysql+aiomysql://user:pass@hostname/dbname?charset=utf8mb4")

CyMySQL

Support for the MySQL / MariaDB database via the CyMySQL driver.

DBAPI

Documentation and download information (if applicable) for CyMySQL is available at: https://github.com/nakagami/CyMySQL

Connecting

Connect String:

mysql+cymysql://<username>:<password>@<host>/<dbname>[?<options>]

注解

cymysql方言是 未作为SQLAlchemy持续集成的一部分进行测试 可能还有未解决的问题。推荐的mysql方言是mysqlclient和pymysql。

URSQL

Support for the MySQL / MariaDB database via the OurSQL driver.

DBAPI

Documentation and download information (if applicable) for OurSQL is available at: https://packages.python.org/oursql/

Connecting

Connect String:

mysql+oursql://<user>:<password>@<host>[:<port>]/<dbname>

注解

oursql mysql方言是传统语言,不再支持上游语言,并且 未作为SQLAlchemy持续集成的一部分进行测试 .推荐的mysql方言是mysqlclient和pymysql。

1.4 版后已移除: OurSQL DBAPI已弃用,将在将来的版本中删除。请使用受支持的dbapi之一连接到mysql。

统一码

请看 统一码 有关Unicode处理的当前建议。

脓毒症

通过PyODBC驱动程序支持MySQL/MariaDB数据库。

DBAPI

Documentation and download information (if applicable) for PyODBC is available at: https://pypi.org/project/pyodbc/

连接

Connect String:

mysql+pyodbc://<username>:<password>@<dsnname>

注解

pyodbcformysql方言是 未作为SQLAlchemy持续集成的一部分进行测试 . 推荐使用MySQL方言mysqlclient和PyMySQL。但是,如果您想使用mysql+pyodbc方言,并且需要对 utf8mb4 字符(包括emoji等补充字符)请确保使用当前版本的MySQL Connector/ODBC并指定“ANSI”( not 或Unicode驱动程序的“n”版本的连接。

传递精确的pyodbc连接字符串::

import urllib
connection_string = (
    'DRIVER=MySQL ODBC 8.0 ANSI Driver;'
    'SERVER=localhost;'
    'PORT=3307;'
    'DATABASE=mydb;'
    'UID=root;'
    'PWD=(whatever);'
    'charset=utf8mb4;'
)
params = urllib.parse.quote_plus(connection_string)
connection_uri = "mysql+pyodbc:///?odbc_connect=%s" % params