SQLAlchemy有多个标识插入和日期列问题

Question

我遇到了一个奇怪的问题，当我试图插入包含日期类型的行时，我得到了"无效字符值的强制转换规范"错误。我缩小了导致问题出现的条件，似乎只有当所有这些因素结合在一起时才会发生这种情况:

1. 用identity_insert和
插入自动递增的PK id列
2. 插入日期列AND
3. 有多个对象添加到会话当我提交(fast_executemany=True)

下面我演示一下这个问题:

class TestDate(Model):
__table_args__ = {'schema': 'test'}
__tablename__ = 'TestDate'
id = Column(Integer, primary_key=True, autoincrement=True)
date = Column(Date, nullable=False)

class TestString(Model):
__table_args__ = {'schema': 'test'}
__tablename__ = 'TestString'
id = Column(Integer, primary_key=True, autoincrement=True)
string = Column(String, nullable=False)

db.session.add(TestDate(id=1, date='01/01/2021'))
db.session.commit()
# no issue
db.session.add(TestDate(date='01/01/2021'))
db.session.add(TestDate(date='01/01/2021'))
db.session.commit()
# no issue
db.session.add(TestString(id=1, string='01/01/2021'))
db.session.add(TestString(id=2, string='01/01/2021'))
db.session.commit()
# no issue
db.session.add(TestDate(id=4, date='01/01/2021'))
db.session.add(TestDate(id=5, date='01/01/2021'))
db.session.commit()
# error!

下面是使用SQLALCHEMY_ECHO=True:

的上述代码的输出

2021-02-17 00:51:14,102 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-02-17 00:51:14,108 INFO sqlalchemy.engine.base.Engine SET IDENTITY_INSERT test.[TestDate] ON
2021-02-17 00:51:14,109 INFO sqlalchemy.engine.base.Engine ()
2021-02-17 00:51:14,112 INFO sqlalchemy.engine.base.Engine INSERT INTO test.[TestDate] (id, date) VALUES (?, ?)
2021-02-17 00:51:14,112 INFO sqlalchemy.engine.base.Engine (1, '01/01/2021')
2021-02-17 00:51:14,120 INFO sqlalchemy.engine.base.Engine SET IDENTITY_INSERT test.[TestDate] OFF
2021-02-17 00:51:14,121 INFO sqlalchemy.engine.base.Engine ()
2021-02-17 00:51:14,126 INFO sqlalchemy.engine.base.Engine COMMIT
2021-02-17 00:51:15,471 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-02-17 00:51:15,476 INFO sqlalchemy.engine.base.Engine INSERT INTO test.[TestDate] (date) OUTPUT inserted.id VALUES (?)
2021-02-17 00:51:15,477 INFO sqlalchemy.engine.base.Engine ('01/01/2021',)
2021-02-17 00:51:15,486 INFO sqlalchemy.engine.base.Engine INSERT INTO test.[TestDate] (date) OUTPUT inserted.id VALUES (?)
2021-02-17 00:51:15,487 INFO sqlalchemy.engine.base.Engine ('01/01/2021',)
2021-02-17 00:51:15,493 INFO sqlalchemy.engine.base.Engine COMMIT
2021-02-17 00:51:16,880 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-02-17 00:51:16,888 INFO sqlalchemy.engine.base.Engine SET IDENTITY_INSERT test.[TestString] ON
2021-02-17 00:51:16,888 INFO sqlalchemy.engine.base.Engine ()
2021-02-17 00:51:16,891 INFO sqlalchemy.engine.base.Engine INSERT INTO test.[TestString] (id, string) VALUES (?, ?)
2021-02-17 00:51:16,891 INFO sqlalchemy.engine.base.Engine ((1, '01/01/2021'), (2, '01/01/2021'))
2021-02-17 00:51:16,898 INFO sqlalchemy.engine.base.Engine SET IDENTITY_INSERT test.[TestString] OFF
2021-02-17 00:51:16,899 INFO sqlalchemy.engine.base.Engine ()
2021-02-17 00:51:16,906 INFO sqlalchemy.engine.base.Engine COMMIT
2021-02-17 00:51:20,895 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-02-17 00:51:20,902 INFO sqlalchemy.engine.base.Engine SET IDENTITY_INSERT test.[TestDate] ON
2021-02-17 00:51:20,902 INFO sqlalchemy.engine.base.Engine ()
2021-02-17 00:51:20,905 INFO sqlalchemy.engine.base.Engine INSERT INTO test.[TestDate] (id, date) VALUES (?, ?)
2021-02-17 00:51:20,905 INFO sqlalchemy.engine.base.Engine ((4, '01/01/2021'), (5, '01/01/2021'))
2021-02-17 00:51:20,914 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
[snip]
pyodbc.DataError: ('22018', '[22018] [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification (0) (SQLExecute)')

我想要使用identity_insert的原因是，这样我就可以快速删除所有表并创建所有表，然后将表填充到从本地.tsv或.json文件加载的初始状态，这些文件具有数据库初始状态的所有行，以便快速测试和原型。如果我不提供id，那么我认为我无法获得fast_executemany的速度优势。

在上面的第二种情况中，似乎可以避免错误，因为如果不提供id, SQLAlchemy将被迫在自己的行中插入每个条目(这击败了fast_executemany)。更奇怪的是，如果我从失败案例中获取原始sqlalchemy输出，修复语法，并直接在SQL Server Management studio中运行它，它工作得很好。那么，到底发生了什么?

SET IDENTITY_INSERT test.[TestDate] ON
INSERT INTO test.[TestDate]
(id, date)
VALUES 
(4, '01/01/2021'),
(5, '01/01/2021')
SET IDENTITY_INSERT test.[TestDate] OFF
-- (2 rows affected)

方言是mssql: 'mssql+pyodbc:///?DRIVER={ODBC DRIVER 17 for SQL Server}'SQLAlchemy 1.3.22

Answer 1

好了，我明白了。当使用fast_executemany时，Date和DateTime列的字符串字面值的允许格式似乎非常严格，因为实现细节可能是性能改进所必需的。

所需的确切字符串格式在此链接中列出:

https://learn.microsoft.com/en - us/sql/relational databases/native -客户- odbc -日期- time/data类型-支持- - - - - - - odbc -日期-和-时间- improvements?view=sql -服务器- 2017