django连接sqlserver

• 方式一
  1.安装包

pip install django-sqlserver django-pytds pyodbc django-pyodbc pypiwin32

2.setting配置

 'method1': {
        'ENGINE': 'sqlserver',  # fetch next 分页 sqlserver 2008R2 不支持
        'NAME': '',
        'HOST': '',
        'PORT': '1433',
        'USER': 'sa',
        'PASSWORD': '',
        'OPTIONS': {
            'DRIVER': 'SQL Server Native Client 10.0',
        },
    },

3.查询语句

>>> from apps.mainapp.mainmodels.nt_uf_spxxcs_model import nt_uf_spxxcs_model
>>> print nt_uf_spxxcs_model.objects.all()[1:2].query
SELECT [uf_spxxcs].[id], [uf_spxxcs].[spbm], [uf_spxxcs].[spmc], [uf_spxxcs].[gysmc], [uf_spxxcs].[zdsj] FROM [uf_spxxcs] ORDER BY 1 OF
FSET 1 ROWS FETCH NEXT 1 ROWS ONLY

这个引擎django在orm分页时，使用的是OFFSET…FETCH NEXT…语法，但公司用的SQL Server 2008 R2 数据库版本较低不支持这种语法。

• 方式二
  1.安装

 pip install django-pyodbc

2.setting

 'nt120': {
       'ENGINE': 'django_pyodbc',  # row_num 伪分页
        'NAME': '',
        'HOST': '3',
        'USER': 'sa',
        'PASSWORD': '',
        'OPTIONS': {
            'driver': 'ODBC Driver 11 for SQL Server',
        },
    },

3.连接数据库报错如下

pyodbc.InterfaceError: ('IM002', u'[IM002]  [Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序(SQLDriverConnect)')

需要自行下载安装ODBC Driver 11 for SQL Server，可参考下面的博客
https://blog.csdn.net/grape875499765/article/details/49304559

4.因为我有SQL Server Native Client 10.0 驱动直接改就好了

3.查询语句

>>> from apps.mainapp.mainmodels.nt_uf_spxxcs_model import nt_uf_spxxcs_model
>>> print nt_uf_spxxcs_model.objects.all()[1:2].query
SELECT
	_row_num,
	[id],
	[spbm],
	[spmc],
	[gysmc],
	[zdsj]
FROM
	(
		SELECT
			ROW_NUMBER () OVER (ORDER BY [AAAA].[id] ASC) AS _row_num,
			[AAAA].[id],
			[AAAA].[spbm],
			[AAAA].[spmc],
			[AAAA].[gysmc],
			[AAAA].[zdsj]
		FROM
			(
				SELECT
					[uf_spxxcs].[id],
					[uf_spxxcs].[spbm],
					[uf_spxxcs].[spmc],
					[uf_spxxcs].[gysmc],
					[uf_spxxcs].[zdsj]
				FROM
					[uf_spxxcs]
			) AS [AAAA]
	) AS QQQ
WHERE
	1 < _row_num
AND _row_num <= 2

分页成功！！！