Django 1.11.4连接SQL Server 2012视图读取数据失败求助

Hey there, let's work through this issue step by step—connecting older Django versions to SQL Server views can have a few gotchas, but we can nail it down.

First, let's clean up your model code since the db_table value was truncated. Make sure it exactly matches the name of your SQL Server view (case sensitivity might matter depending on your SQL Server collation):

class NumUsersAddedPerWeek(models.Model):
    id = models.BigIntegerField(primary_key=True)
    year = models.IntegerField('Year')
    week = models.IntegerField('Week')
    num_added = models.IntegerField('Number of Users Added')

    if not settings.RUNNING_UNITTESTS:
        class Meta:
            managed = False
            db_table = 'num_users_added_per_week'  # Replace with your actual view name

Now let's go through the most common issues:

1. Verify Your Database Configuration #

Django 1.11 requires specific settings for sql_server.pyodbc—double-check your settings.py has these critical details:

• Use the correct engine: ENGINE = 'sql_server.pyodbc'
• Add OPTIONS tailored for SQL Server 2012 compatibility:

  DATABASES = {
      'default': {
          'ENGINE': 'sql_server.pyodbc',
          'NAME': 'your_db_name',
          'USER': 'db_user',
          'PASSWORD': 'db_password',
          'HOST': 'your_server_name\\instance',  # Or IP address if no instance
          'PORT': '',
          'OPTIONS': {
              'driver': 'SQL Server Native Client 11.0',  # Match your installed driver
              'MARS_Connection': 'True',
              'unicode_results': True,
              'extra_params': 'DataTypeCompatibility=80',  # Ensures compatibility with 2012
          },
      }
  }
  

  Note: Make sure the driver you specify is actually installed on your machine/server—SQL Server Native Client 11.0 is the most reliable choice for 2012.

2. Confirm SQL Server Permissions #

Your database user needs explicit SELECT permission on the view. Run this in SQL Server Management Studio:

GRANT SELECT ON num_users_added_per_week TO your_db_user;

Also, ensure the user can access the schema the view lives in (e.g., dbo if that's your default schema).

3. Match Model Fields to View Columns Exactly #

• Double-check that every field in your model has the same data type as the corresponding column in the view. For example, if the view's id is an INT instead of BIGINT, adjust your model to use models.IntegerField(primary_key=True).
• Ensure column names match exactly (collation settings might make this case-sensitive). If your view uses column aliases, your model field names must match those aliases.

4. Test the Connection in Django Shell #

Open a Django shell to run a simple query and see the exact error:

python manage.py shell

Then run this code:

from your_app.models import NumUsersAddedPerWeek
try:
    results = NumUsersAddedPerWeek.objects.all()
    print(results.query)  # Shows the generated SQL—check if it targets the right view
    print(results.first())
except Exception as e:
    print(str(e))

Common errors here include:

• "Invalid object name" → db_table is incorrect, or permissions are missing
• "Data type mismatch" → Model field types don't align with the view
• "No primary key found" → Ensure your view returns a unique, non-null column for the primary key

5. Check Package Version Compatibility #

Django 1.11.4 works best with django-pyodbc version 1.11.x. Avoid newer versions that might be incompatible:

pip install django-pyodbc==1.11.1

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