MySQL 和 Mariadb
Support for the MySQL / MariaDB database.
The following table summarizes current support levels for database release versions.
支持的版本和功能
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语法都包含大量的特殊选项,包括 ENGINE
, CHARSET
, MAX_ROWS
, ROW_FORMAT
, INSERT_METHOD
以及更多。要适应这些参数的呈现,请指定表单 mysql_argument_name="value"
. 例如,指定带有 ENGINE
属于 InnoDB
, CHARSET
属于 utf8mb4
和 KEY_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_NAME
在 CREATE TABLE
语句。这些名称中的一小部分将用空格而不是下划线呈现;为了支持这一点,MySQL方言了解这些特定的名称,其中包括 DATA DIRECTORY
(例如) mysql_data_directory
) CHARACTER SET
(例如) mysql_character_set
) INDEX 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;
.
参见
自动增量行为
创建表时,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) )
可以通过传递禁用此行为 False
到 Column.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.SSCursor
或 pymysql.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_mode
在 first_connect
和 connect
活动::
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。
在重复键更新时插入..:请参见 在重复密钥更新时插入…(向上插入)
选择pragma,使用
Select.prefix_with()
和Query.prefix_with()
::select(...).prefix_with(['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])
更新限制::
update(..., mysql_limit=10, mariadb_limit=10)
优化器提示,使用
Select.prefix_with()
和Query.prefix_with()
::select(...).prefix_with("/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */")
索引提示,使用
Select.with_hint()
和Query.with_hint()
::select(...).with_hint(some_table, "USE INDEX xyz")
匹配操作员支持::
from sqlalchemy.dialects.mysql import match select(...).where(match(col1, col2, against="some expr").in_boolean_mode()) .. seealso:: :class:`_mysql.match`
在重复密钥更新时插入…(向上插入)
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
连接时,标记或与目标方言等效的内容。此设置当前已硬编码。
参见
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”。使用 deferrable
或 initially
关键字参数 ForeignKeyConstraint
或 ForeignKey
将具有在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") ) )
同样的说明适用于 DateTime
和 DATETIME
数据类型:
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 Name | Description |
---|---|
制作一个 |
- 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 Name Description MySQL BigInteger类型。
SQL二进制类型。
MySQL位类型。
SQL blob类型。
SQL布尔类型。
mysql char类型,用于固定长度的字符数据。
SQL日期类型。
MySQL日期时间类型。
mysql decimal类型。
mysql双类型。
MySQL枚举类型。
mysql float类型。
MySQL整数类型。
mysql json类型。
mysql longblob类型,用于2^32字节的二进制数据。
MySQL Longtext类型,用于最多2^32个字符的文本。
mysql mediumblob类型,用于二进制数据,最大2^24字节。
MySQL Mediuminteger类型。
mysql mediumtext类型,用于最多2^24个字符的文本。
mysql nchar类型。
MySQL数字类型。
mysql nvarchar类型。
mysql实型。
MySQL设置类型。
mysql smallinteger类型。
mysql文本类型,用于最多2^16个字符的文本。
MySQL时间类型。
MySQL时间戳类型。
mysql tinyblob类型,用于2^8字节的二进制数据。
mysql tinyint类型。
mysql tinytext类型,用于最多2^8个字符的文本。
sql varbinary类型。
mysql varchar类型,用于可变长度字符数据。
mysql-year类型,用于1901-2155年的单字节存储。
- class sqlalchemy.dialects.mysql.BIGINT(display_width=None, **kw)
Object Name Description insert(table[, values, inline, bind, ...], **dialect_kw)
构建一个
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