使用mssql-django执行Django迁移时出现OUTPUT语法错误

问题概述 #

使用mssql-django包连接Django应用与MSSQL数据库时，makemigrations命令可正常运行，但执行migrate命令向django_migrations表插入首行数据时触发语法错误。

错误信息 #

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'OUTPUT'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

生成的错误SQL语句 #

INSERT INTO [backdrop_django_migrations] ([app], [name], [applied]) VALUES (?, ?, ?) OUTPUT INSERTED.[id]

问题分析 #

该SQL语句存在语法错误：MSSQL的OUTPUT子句应放置在VALUES子句之前。已尝试修改mssql-django库代码，但希望先确认是否有其他用户遇到相同问题；查阅官方文档未找到相关内容，更换支持范围内的版本后问题仍存在。

数据库连接配置（settings.py） #

'default': {
    'ENGINE': 'mssql',
    'NAME': 'database',
    'HOST': 'hostname\sqlexpress',
    'PORT': '1433',
    'OPTIONS': {
        'driver': 'ODBC Driver 17 for SQL Server',
        'query_timeout' : 300,
        'connection_retries' : 5,
        'connection_timeout' : 5
    },
    'CONN_MAX_AGE': 5,
},

环境信息 #

• Django: 3.2
• mssql-django: 1.2
• Python: 3.7.4
• SQL Server: 2019 Express
• OS: Windows 10

完整栈追踪 #

Traceback (most recent call last):
File "manage.py", line 33, in
main()
File "manage.py", line 29, in main
execute_from_command_line(sys.argv)
File "venv\lib\site-packages\django\core\management\__init__.py", line 419, in execute_from_command_line
utility.execute()
File "venv\lib\site-packages\django\core\management\__init__.py", line 413, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "venv\lib\site-packages\django\core\management\base.py", line 354, in run_from_argv
self.execute(*args, **cmd_options)
File "venv\lib\site-packages\django\core\management\base.py", line 398, in execute
output = self.handle(*args, **options)
File "venv\lib\site-packages\django\core\management\base.py", line 89, in wrapped
res = handle_func(*args, **kwargs)
File "venv\lib\site-packages\django\core\management\commands\migrate.py", line 246, in handle
fake_initial=fake_initial,
File "venv\lib\site-packages\django\db\migrations\executor.py", line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
File "venv\lib\site-packages\django\db\migrations\executor.py", line 147, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "venv\lib\site-packages\django\db\migrations\executor.py", line 229, in apply_migration
self.record_migration(migration)
File "venv\lib\site-packages\django\db\migrations\executor.py", line 244, in record_migration
self.recorder.record_applied(migration.app_label, migration.name)
File "venv\lib\site-packages\django\db\migrations\recorder.py", line 87, in record_applied
self.migration_qs.create(app=app, name=name)
File "venv\lib\site-packages\django\db\models\query.py", line 453, in create
obj.save(force_insert=True, using=self.db)
File "venv\lib\site-packages\django\db\models\base.py", line 727, in save
force_update=force_update, update_fields=update_fields)
File "venv\lib\site-packages\django\db\models\base.py", line 765, in save_base
force_update, using, update_fields,
File "venv\lib\site-packages\django\db\models\base.py", line 868, in _save_table
results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
File "venv\lib\site-packages\django\db\models\base.py", line 908, in _do_insert
using=using, raw=raw,
File "venv\lib\site-packages\django\db\models\manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "venv\lib\site-packages\django\db\models\query.py", line 1270, in _insert
return query.get_compiler(using=using).execute_sql(returning_fields)
File "venv\lib\site-packages\django\db\models\sql\compiler.py", line 1410, in execute_sql
cursor.execute(sql, params)
File "venv\lib\site-packages\django\db\backends\utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "venv\lib\site-packages\django\db\backends\utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "venv\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "venv\lib\site-packages\django\db\utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "venv\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "venv\lib\site-packages\mssql\base.py", line 620, in execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'OUTPUT'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

内容的提问来源于stack exchange，提问作者Kenta