如何将Django 1.8连接到MSSQL Server Express 2014数据库

Hey there! You’re already halfway there with the relevant packages installed (django-pyodbc, django-pyodbc-azure, etc.). Let’s walk through getting your Django project connected to MSSQL Server Express 2014 properly.

Step 1: Install the MSSQL ODBC Driver on Ubuntu #

First, you need the official Microsoft ODBC driver to bridge Ubuntu and MSSQL. Here’s how to install it:

1. Add Microsoft’s package repository:

   curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
   curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
   

2. Update packages and install the driver + dependencies:

   sudo apt-get update
   sudo ACCEPT_EULA=Y apt-get install msodbcsql13 unixodbc-dev
   

   Note: ODBC Driver 13 works well with MSSQL Server 2014. If you need a different version, adjust the package name (e.g., msodbcsql17 for newer drivers).

Step 2: Configure Django’s Database Settings #

Open your project’s settings.py file and update the DATABASES section. We’ll use django-pyodbc-azure (which you already have installed) since it’s well-maintained for MSSQL connections.

For SQL Server Authentication (Recommended for Ubuntu) #

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'your_database_name',  # Replace with your actual DB name
        'USER': 'sql_server_username',  # e.g., sa or a custom SQL auth user
        'PASSWORD': 'sql_server_password',
        'HOST': 'mssql_server_ip_address,1433',  # Replace with your server IP; 1433 is the default Express port
        'PORT': '',
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',  # Match the driver you installed
            'MARS_Connection': 'True',  # Helpful for handling multiple active result sets
            'unicode_results': True,  # Ensures proper Unicode handling
        },
    }
}

If You Prefer Using django-pyodbc #

If you want to use the django-pyodbc package instead, adjust the ENGINE and options slightly:

DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'your_database_name',
        'USER': 'sql_server_username',
        'PASSWORD': 'sql_server_password',
        'HOST': 'mssql_server_ip_address',
        'PORT': '1433',
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
            'unicode_results': True,
        },
    }
}

Step 3: Verify the Connection #

Test if Django can reach your MSSQL server with these quick checks:

1. Run migrations (if you have existing models):

   python manage.py migrate
   

2. Or test directly via the Django shell:

   python manage.py shell
   

   Then run these lines:

   from django.db import connection
   cursor = connection.cursor()
   print("Connection successful!")
   

   If no errors pop up, you’re good to go.

Critical Pre-Checks on MSSQL Server #

Don’t forget these steps on your MSSQL Server Express 2014 instance:

• Enable SQL Server Authentication (if you’re using it) instead of just Windows Auth.
• Allow remote connections to the server (in SQL Server Configuration Manager, enable TCP/IP for the Express instance).
• Open port 1433 on the MSSQL server’s firewall to allow incoming connections from your Ubuntu server.
• Verify your Ubuntu server can reach the MSSQL IP: use telnet mssql_server_ip 1433 or nc -zv mssql_server_ip 1433 to check port accessibility.

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