Windows上的Django-pydobc SQL Server连接问题 [英] Django-pydobc SQL server connection problems on windows

问题描述

另一位开发人员和我正在使用另一台服务器上的旧版SQL Server数据库(SQLEXPRESS)建立django(v1.4.2)项目.到目前为止，我们已经能够使用django-pyodbc从linux和mac以及使用django-mssql从运行Windows 7的笔记本电脑连接到数据库.我想在笔记本电脑上使用django-pyodbc来保持环境同步.

Another developer and I are setting up a django (v1.4.2) project using a legacy SQL server database (SQLEXPRESS) on another server. So far, we have been able to connect to the database from linux and mac using django-pyodbc, and from a laptop running windows 7 using django-mssql. I would like to use django-pyodbc on the laptop to keep the environments in sync.

在笔记本电脑上:

• pyodbc(3.0.6)已安装，并且在非Django .py脚本中，我可以连接并运行sql语句
• 通过下载zip下载了django-pyodbc 1.4；我不确定我是否安装正确:
  • 我解压缩了文件，并在顶层目录中运行了setup.py文件；它将sql_server目录放在/lib/site-packages目录中
  • pyodbc (3.0.6) is installed and in a non-django .py script I can connect and run sql statements
  • Downloaded django-pyodbc 1.4 by downloading the zip; I'm not sure I installed it right:
    • I unzipped the file, and ran the setup.py file in the top directory; it puts a sql_server directory in the /lib/site-packages directory
    • 不确定是否应该指向/site-packages/sql_server吗?
    • 测试连接选项有效

    因此，它不起作用，并且我收到以下错误消息，并且不知道下一步该怎么做:

    So, it doesn't work, and I get the following error message, and have no idea what to do next:

    ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53); [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')
    

    我像这样设置django settings.py文件:

    I setup the django settings.py file as like so:

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sql_server.pyodbc',
            'NAME': 'test',
            'USER': 'test',
            'PASSWORD': 'something_else',
            'HOST': 'mssqlx',
            'PORT': '12345',
            'OPTIONS': {
                'driver': 'SQL Server',
            },
        },
    }
    

    在linux上，我的设置文件具有一个DATABASES条目，如下所示:

    On linux, my settings file has a DATABASES entry like so:

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sql_server.pyodbc',
            'NAME': 'test',
            'USER': 'test',
            'PASSWORD': 'something_else',
            'HOST': 'mssqlx',       # ODBC DSN defined in /etc/freetds.conf
            'PORT': '12345',        # Probably unneeded.  Set in mssqlx
            'OPTIONS': {
                'driver': 'SQL Server',  # ODBC driver name in /etc/odbcinst.ini
                'extra_params': "TDS_VERSION=7.0"  # Probably unneeded.  Set in mssqlx
            }
        },
    }
    

    不知道它是否会帮助解决此问题，但是使用django-mssql(仅在Windows上运行)，(有效)条目为:

    don't know if it will help solve this, but using django-mssql (which only runs on windows), the (working) entry is:

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlserver_ado',
            'NAME': 'test',
            'USER': 'test',
            'PASSWORD': 'something_else',
            'HOST': '199.555.0.10',         # changed for this example
            'PORT': '12345',
            'OPTIONS': {'provider': 'SQLOLEDB'}
        },
    }
    

    不知道还有哪些其他信息可以帮助您.感谢您提供的任何帮助或见识.

    Don't know what other info might help. Thank you for any help or insight you can offer.

    ----发布后---- 这是最终有效的方法:

    ----POST MORTEM ---- Here's what finally worked:

    数据库设置的部分条目:

    partial entry in settings for DATABASES:

        'default': {
            'ENGINE'    : 'django.db.backends.sql_server.pyodbc',
            'NAME'      : 'test_db_name',
            'USER'      : 'test_db_user_name',
            'PASSWORD'  : 'password',
            # ODBC DSN defined in /etc/freetds.conf
            'HOST'      : 'mssql_test',
            # Ignored for Windows; Required for Linux
            'OPTIONS'   : {
                # ODBC driver name in /etc/odbcinst.ini
                'driver': 'SQL Server',
                # NOTE: dsn option is added dynamically later, for Windows
            }
        },
    
    # The ODBC DSN name specified above as DATABASES.default.HOST is ignored on
    # Windows, where it must be specified as DATABASES.default.OPTIONS.dsn instead.
    # However, we haven't found a way to make DATABASES.default.OPTIONS.dsn work in
    # Linux (and probably the same for Mac).  It causes the error:
    #    Data source name not found, and no default driver specified 
    # Therefore we add it here, but only for Windows.
    # Note: The username and pwd in the windows dsn file is apparently NOT used
    #       (b/c server hosts both test and prod database in same MSSQL
    #       instance, both test and prod dsn files happen to work - they have the
    #       same ip address and port number, but different username/password's)
    #
    # On 64-bit Windows, with our current 32-bit version of pyodbc, the DSN
    # must be created via:
    #    C:\Windows\SysWOW64\odbcad32.exe
    # instead of the regular "ODBC Data Sources" app in Control Panel, which 
    # invokes:
    #    C:\Windows\system32\odbcad32.exe
    #
    #   os.name is...
    #       nt      for Hans' laptop (Windows 7)
    #       posix   for the "Amazon Linux AMI" (CentOS) on AWS
    #       posix   for Fred's Mac
    if os.name == 'nt':      # Windows
        DATABASES['cf']['OPTIONS']['dsn'] = 'mssql_test'
    

    推荐答案

    尝试使用 https://github.com/michiya/django-pyodbc-azure .这应该在Linux和Windows上都可以使用.

    Try using https://github.com/michiya/django-pyodbc-azure. This should work on both Linux and Windows.

    然后按如下方式定义您的数据库设置:

    Then define your database settings as such:

    DATABASES = {
        'default': {
            'ENGINE': 'sql_server.pyodbc',
            'NAME': 'dbname',
            'HOST': 'dsn_entry',
            'PORT': 'port',
            'USER': '',
            'PASSWORD': 'pass',
            'OPTIONS': {
                'driver': 'FreeTDS',
                'dsn': 'dsn_entry',
                'host_is_server': True
            }
        }
    }
    

    在Windows下，OPTIONS中的'driver'条目应为:

    Under Windows the 'driver' entry in OPTIONS should be:

    'driver': 'SQL Native Client',
    

    糟糕，看不到您已经解决了问题.在这里留下我的答案作为参考.

    Oops, failed to see that the you had solved the problem. Leaving my answer here as reference.

    这篇关于Windows上的Django-pydobc SQL Server连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！