python的sqlite3中create table和transaction的问题 [英] Questions about create table and transaction in sqlite3 of python

问题描述

我正在通过 sqlite3 学习在 python 中使用 sqlite.

I'm learning to use sqlite in python by sqlite3.

插入、更新支持事务和commit()等SQL操作应该在关闭连接之前调用，否则数据库中的任何内容都不会改变.但是，创建表"不支持事务，或者创建表是自动提交的.例如，

The SQL operations like insert, update support support transaction and the commit() should be called before close the connection, or nothing would change in the database. However, the 'create table' do not support transaction, or the create table is auto committed. For example,

con.execute('create table xxx (xxxx)')

表格将立即创建.更重要的是，如果我们在创建表之前执行插入语句，而没有 commit().然后执行create table，会提交insert，以及'create table'.

the table would be created instantly. What's more, if we execute an insert statement before creating table, without commit(). Then execute create table, the insert would be committed, as well as the 'create table'.

我在文档 https://docs 中没有发现任何有关此类行为的内容.python.org/2/library/sqlite3.html#sqlite3-types ,https://www.python.org/dev/peps/pep-0249/#id3 或 https://sqlite.org/atomiccommit.html.

I found nothing mentioned about such behavior in document https://docs.python.org/2/library/sqlite3.html#sqlite3-types ,https://www.python.org/dev/peps/pep-0249/#id3 or https://sqlite.org/atomiccommit.html.

我的问题是:1. 有没有其他操作是这样的?2. 其他 DBMS 的行为是什么?3. 有没有这方面的规范?

My question is: 1. Is there any other operations behave like this? 2. What does other DBMS behave? 3. Is there any specification about this?

推荐答案

这种行为既不是来自 SQLite(像 CREATE TABLE 这样的命令在事务中工作得很好)也不是来自 Python；它是 Python sqlite3 模块，它试图变得聪明并在某些地方插入自动事务.

This behaviour comes neither from SQLite (commands like CREATE TABLE work perfectly well inside transactions) nor from Python; it's the Python sqlite3 module that tries to be clever and inserts automatic transactions at some places.

这是记录，但给出的原因相当脆弱的:

This is documented, but the reasons given are rather flimsy:

这样做有两个原因.首先是其中一些命令在事务中不起作用.

There are two reasons for doing that. The first is that some of these commands don’t work within transactions.

如果模块不会自动启动事务，则不需要在执行这些命令之前关闭它们.

If the module wouldn't automatically start transactions, it would not be required to close them before those commands.

另一个原因是 sqlite3 需要跟踪事务状态(如果事务处于活动状态).

The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

如果模块不会自动启动事务，则不需要跟踪事务状态

If the module wouldn't automatically start transaction, it would not need to keep track of the transaction state

请注意，DML 语句的检测并非在所有情况下都能正常工作.

Please note that the detection of DML statements does not work correctly in all cases.

我建议通过清除 isolation_level 来禁用所有这些疯狂.

I'd recommend to disable all this craziness by clearing isolation_level.

这篇关于python的sqlite3中create table和transaction的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！