描述:用django访问sql server,网上有介绍几种方法,这是主要介绍django-pyodbc
测试环境:
系统: ubuntu 12.04
django 1.4
python 2.7
pyodbc 3.0.3 (python连接sql server的包)
django-pyodbc 1.2 (一个django连接数据库的第三方包,主要用pyodbc连接sql server)
安装调试
主要参考文档:
http://logrit.com/weblog/2012/07/16/accessing-sql-server-mssql-database-django/
http://tzangms.com/programming/2391/
关于利用pyodbc访问sql server,可参考以前写的一篇文档:
http://xiaolin0199.iteye.com/blog/2020913
安装odbc和pyodbc
- sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc python-dev
- sudo easy_install pyodbc
配置odbc
修改/etc/odbc.ini,添加
- [ODBC Data Sources]
- ODBCNAME = Microsoft SQL Server
- [MSSQL-PYTHON]
- Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
- Description = Hi! This is a description of the MSSQL-PYTHON driver
- Trace = No
- #Database = cxonline
- Server = 192.168.0.47
- Port = 1433
测试
可通过isql命令测试odbc是否可连接服务器192.168.0.47上的sql server
- isql -v MSSQL-PYTHON <Username> <Password>
- SELECT TOP 1 * FROM <SomeTable>
安装django-pyodbc
django-pyodbc (google-code):http://code.google.com/p/django-pyodbc/
可通过以下命令安装:
- svn checkout http://django-pyodbc.googlecode.com/svn/trunk/ django-pyodbc-read-only
- cd django-pyodbc-read-only
- python setup.py build install
当然也可以直接将安装包中的sql_server copy到要使用的项目根目录下,
由于下面要说的该包中有一个小bug,所以我直接将包copy到项目根目录下,修改bug后加入svn
关于django-pyodbc的一个bug
修改sql_server/pyodbc/operations.py 10行
- def __init__(self, connection):
- #super(DatabaseOperations, self).__init__()
- super(DatabaseOperations, self).__init__(connection)
- self.connection = connection
- self._ss_ver = None
django-pyodbc的使用
只要配置下数据库就可以连接sql server,而且可使用django原来的orm的写法
包括通过模型的定义来创建数据库等
修改 settings.py
- DATABASES = {
- 'default': {
- 'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
- 'NAME': 'icgoo_manage', # Or path to database file if using sqlite3.
- 'USER': 'lidongdev', # Not used with sqlite3.
- 'PASSWORD': 'xxxxx', # Not used with sqlite3.
- 'HOST': '192.168.0.8', # Set to empty string for localhost. Not used with sqlite3.
- 'PORT': '', # Set to empty string for default. Not used with sqlite3.
- },
- 'mssql': {
- 'NAME': 'cxonline_new',
- 'ENGINE': 'sql_server.pyodbc',
- 'HOST': '192.168.0.47',
- 'USER': 'sa',
- 'PASSWORD': 'xxxxxx',
- 'PORT': '1433', # Set to empty string for default. Not used with sqlite3.
- 'OPTIONS': {
- 'host_is_server': False,
- 'dsn': 'MSSQL-PYTHON',
- },
- }
- }
- DATABASE_OPTIONS = {
- 'host_is_server': False,
- 'dsn': 'MSSQL-PYTHON',
- }
settings中关键是需要定义dsn到上面配置好的odbc数据源
'dsn': 'MSSQL-PYTHON',
简单调试
- #进入django项目的shell调试
- $python manage.py shell
- >>> from django.db import connections
- >>> conn = connections['mssql']
- >>> cur = conn.cursor()
- >>> cur.execute('select Top 1 * from _CRM_Company')
- <pyodbc.Cursor object at 0xac1b528>
- >>> cur.fetchall()
- [(13528, u'\u91cd\u5e86\u65e0\u7ebf\u7eff\u6d32\u901a\u4fe1\u6280\u672f\u6709\u9650\u516c\u53f8', None, 1, u' ', None, u' ', u' ', None, None, None, No
- ne, False, 0, None, True, 83, 1, datetime.datetime(2008, 8, 28, 8, 55, 2, 240000), None, 2, None, -1, 0.0, 0.0)]