1

I am trying to use Django (django 1.11.4) to read data from a SQL-Server view (sql server 2012 - I use sql_server.pyodbc [aka django-pyodbc] for this), and nothing seems to work.

Here's my model:

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'

and here's how the database view is created:

create view num_users_added_per_week
as

    select row_number() over(order by datepart(year, created_at), datepart(week, created_at)) as 'id',
datepart(year, created_at) as 'year', datepart(week, created_at) as 'week', count(*) as 'num_added'
    from [<database name>].[dbo].[<table name>] 
    where status = 'active' and created_at is not null
    group by datepart(year, created_at), datepart(week, created_at)

The view works just fine by itself (e.g., running 'select * from num_users_added_per_week' runs just fine (and very quickly)...

I used the following django command (i.e., 'action') to try 3 different ways of attempting to pull data via the model, and none of them worked (although, judging from other posts, these approaches seemed to work with previous versions of django) :(:

from django.core.management.base import BaseCommand, CommandError
from <project name>.models import NumUsersAddedPerWeek
from django.db import connection

class Command(BaseCommand):

    def handle(self, *args, **options):

        # attempt # 1 ...
        num_users_info = NumUsersAddedPerWeek.objects.all()
        info = num_users_info.first()
        for info in num_users_info:
            print(info)

        # attempt # 2 ...
        cursor = connection.cursor()
        cursor.execute('select * from num_users_added_per_week')
        result = cursor.fetchall()

        # attempt # 3 ...
        num_users_info = NumUsersAddedPerWeek.objects.raw('select * from num_users_added_per_week')
        for info in num_users_info:
            print(info)

Each of the 3 different approaches gives me the same error: "('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'num_users_added_per_week'. (208) (SQLExecDirectW)")"

Please note: my migrations are running just fine - adding class Meta: managed = False is crucial with latest versions of Django in situations where you do not want migrations to create / update / delete your sql table structure...

karlk
  • 21
  • 1
  • 6
  • Did you create the view through a migration in Django? If you created it directly in SSMS, are you sure you created if with the same user Django is connecting to the database with? – FlipperPA Apr 13 '18 at 18:18

1 Answers1

1

I figured it out - I have a custom Database Router (in settings.DATABASE_ROUTERS) that I had not properly added this to (I am doing this because the project has multiple databases - see Multi-DB to see why and how to do this). (So boneheaded bug on my part)

But here's what I found out: It turns out all three of the methods I used should work, if you have 1 database in your project. If you have multiple databases then you can query the database through your model object (e.g., <Model Name>.objects.all()) or through raw sql, but you have to specify the raw sql via your model (e.g., <Model Name>.objects.raw(<select * from <view name>)) - otherwise your Database Router will not know which database to use.

karlk
  • 21
  • 1
  • 6