Issue #67 new

Schema support for inspectdb and other methods?

Amanjeev Sethi
created an issue

Please correct me if I am wrong but using inspectdb command, I face that my tables with different schema than dbo fail. I have at least three schemas in my rather large database and was wondering if there will be a schema support in the table name or if that is even possible?

As far as I know, this line fails for me because it is does not use the schema name before the table name and fails for non-default schemas.

cursor.execute("SELECT * FROM [%s] where 1=0" % (table_name))

When I run this SELECT statement directly with the schema name, it does not give any errors. (Returns empty "t"). However, within the inspectdb command I get the error -

Traceback (most recent call last):
  File "manage.py", line 9, in <module>
    execute_from_command_line(sys.argv)
  File "env/lib/python3.3/site-packages/django/core/management/__init__.py", line 399, in execute_from_command_line
    utility.execute()
  File "env/lib/python3.3/site-packages/django/core/management/__init__.py", line 392, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "env/lib/python3.3/site-packages/django/core/management/base.py", line 242, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "env/lib/python3.3/site-packages/django/core/management/base.py", line 285, in execute
    output = self.handle(*args, **options)
  File "env/lib/python3.3/site-packages/django/core/management/base.py", line 415, in handle
    return self.handle_noargs(**options)
  File "env/lib/python3.3/site-packages/django/core/management/commands/inspectdb.py", line 27, in handle_noargs
    for line in self.handle_inspection(options):
  File "env/lib/python3.3/site-packages/django/core/management/commands/inspectdb.py", line 62, in handle_inspection
    relations = connection.introspection.get_relations(cursor, table_name)
  File "env/lib/python3.3/site-packages/sqlserver_ado/introspection.py", line 142, in get_relations
    source_field_dict = self._name_to_index(cursor, table_name)
  File "env/lib/python3.3/site-packages/sqlserver_ado/introspection.py", line 139, in _name_to_index
    return dict([(d[0], i) for i, d in enumerate(self.get_table_description(cursor, table_name, False))])
  File "env/lib/python3.3/site-packages/sqlserver_ado/introspection.py", line 109, in get_table_description
    cursor.execute("SELECT * FROM [%s] where 1=0" % (table_name))
  File "env/lib/python3.3/site-packages/django/db/backends/util.py", line 69, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "env/lib/python3.3/site-packages/django/db/backends/util.py", line 53, in execute
    return self.cursor.execute(sql, params)
  File "env/lib/python3.3/site-packages/django/db/utils.py", line 99, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "env/lib/python3.3/site-packages/django/utils/six.py", line 549, in reraise
    raise value.with_traceback(tb)
  File "env/lib/python3.3/site-packages/django/db/backends/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "env/lib/python3.3/site-packages/pytds/__init__.py", line 879, in execute
    self._execute(operation, params)
  File "env/lib/python3.3/site-packages/pytds/__init__.py", line 608, in _execute
    self._session.find_result_or_done()
  File "env/lib/python3.3/site-packages/pytds/tds.py", line 3455, in find_result_or_done
    self.process_end(marker)
  File "env/lib/python3.3/site-packages/pytds/tds.py", line 2653, in process_end
    self.raise_db_exception()
  File "env/lib/python3.3/site-packages/pytds/tds.py", line 2442, in raise_db_exception
    raise ex
django.db.utils.ProgrammingError: Invalid object name 'PROD_DONE'.

What are your views about this?

Thanks.

Comments (4)

  1. Jonathan Bruckman

    I'm having a similar problem with django 1.7 and python 3.4. The output from the command manage.py inspectdb with the following in my settings.py file (obfuscated):

    DATABASES = {
        'default': {
            'NAME': 'DB_NAME',
            'ENGINE': 'sqlserver_ado',
            'HOST': 'DOMAIN,PORT',
            'PORT': '',
            'USER': 'DB_USER',
            'PASSWORD': 'DB_PASSWORD',
        }
    }
    
    # This is an auto-generated Django model module.
    # You'll have to do the following manually to clean this up:
    #   * Rearrange models' order
    #   * Make sure each model has one field with primary_key=True
    #   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
    # Feel free to rename the models, but don't rename db_table values or field names.
    #
    # Also note: You'll have to insert the output of 'django-admin.py sqlcustom [app_label]'
    # into your database.
    from __future__ import unicode_literals
    
    from django.db import models
    
    
    class Accounts(models.Model):
    Traceback (most recent call last):
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 525, in _execute_command
        recordset = self.cmd.Execute()
      File "<COMObject ADODB.Command>", line 3, in Execute
      File "C:\Python34\lib\site-packages\win32com\client\dynamic.py", line 282, in _ApplyTypes_
        result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
    pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft SQL Server Native Client 10.0', "Invalid object name 'Accounts'.", None, 0, -2147217865), None)
    
    During handling of the above exception, another exception occurred:
    
    Traceback (most recent call last):
      File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 63, in execute
        return self.cursor.execute(sql)
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 618, in execute
        self._execute_command()
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 534, in _execute_command
        self._raiseCursorError(klass, _message)
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 466, in _raiseCursorError
        eh(self.connection, self, errorclass, errorvalue)
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 91, in standardErrorHandler
        raise errorclass(errorvalue)
    sqlserver_ado.dbapi.DatabaseError: (-2147352567, 'Exception occurred.', (0, 'Microsoft SQL Server Native Client 10.0', "Invalid object name 'Accounts'.", None, 0, -2147217865), None)
    Command:
    SELECT * FROM [Accounts] where 1=0
    Parameters:
    []
    
    The above exception was the direct cause of the following exception:
    
    Traceback (most recent call last):
      File "C:\Develop\Python\Sicarii\manage.py", line 10, in <module>
        execute_from_command_line(sys.argv)
      File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 385, in execute_from_command_line
        utility.execute()
      File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 377, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "C:\Python34\lib\site-packages\django\core\management\base.py", line 288, in run_from_argv
        self.execute(*args, **options.__dict__)
      File "C:\Python34\lib\site-packages\django\core\management\base.py", line 338, in execute
        output = self.handle(*args, **options)
      File "C:\Python34\lib\site-packages\django\core\management\base.py", line 533, in handle
        return self.handle_noargs(**options)
      File "C:\Python34\lib\site-packages\django\core\management\commands\inspectdb.py", line 27, in handle_noargs
        for line in self.handle_inspection(options):
      File "C:\Python34\lib\site-packages\django\core\management\commands\inspectdb.py", line 63, in handle_inspection
        relations = connection.introspection.get_relations(cursor, table_name)
      File "C:\Python34\lib\site-packages\sqlserver_ado\introspection.py", line 142, in get_relations
        source_field_dict = self._name_to_index(cursor, table_name)
      File "C:\Python34\lib\site-packages\sqlserver_ado\introspection.py", line 139, in _name_to_index
        return dict([(d[0], i) for i, d in enumerate(self.get_table_description(cursor, table_name, False))])
      File "C:\Python34\lib\site-packages\sqlserver_ado\introspection.py", line 109, in get_table_description
        cursor.execute("SELECT * FROM [%s] where 1=0" % (table_name))
      File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 81, in execute
        return super(CursorDebugWrapper, self).execute(sql, params)
      File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 65, in execute
        return self.cursor.execute(sql, params)
      File "C:\Python34\lib\site-packages\django\db\utils.py", line 94, in __exit__
        six.reraise(dj_exc_type, dj_exc_value, traceback)
      File "C:\Python34\lib\site-packages\django\utils\six.py", line 549, in reraise
        raise value.with_traceback(tb)
      File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 63, in execute
        return self.cursor.execute(sql)
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 618, in execute
        self._execute_command()
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 534, in _execute_command
        self._raiseCursorError(klass, _message)
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 466, in _raiseCursorError
        eh(self.connection, self, errorclass, errorvalue)
      File "C:\Python34\lib\site-packages\sqlserver_ado\dbapi.py", line 91, in standardErrorHandler
        raise errorclass(errorvalue)
    django.db.utils.DatabaseError: (-2147352567, 'Exception occurred.', (0, 'Microsoft SQL Server Native Client 10.0', "Invalid object name 'Accounts'.", None, 0, -2147217865), None)
    Command:
    SELECT * FROM [Accounts] where 1=0
    Parameters:
    []
    
  2. Patrice de Muizon

    We're also seeing this same issue when running inspectdb due to non-dbo schemas defined in the database we're inspecting.

    Traceback (most recent call last):
      File "manage.py", line 10, in <module>
        execute_from_command_line(sys.argv)
      File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
        utility.execute()
      File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 377, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 288, in run_from_argv
        self.execute(*args, **options.__dict__)
      File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 338, in execute
        output = self.handle(*args, **options)
      File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 533, in handle
        return self.handle_noargs(**options)
      File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/inspectdb.py", line 27, in handle_noargs
        for line in self.handle_inspection(options):
      File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/inspectdb.py", line 63, in handle_inspection
        relations = connection.introspection.get_relations(cursor, table_name)
      File "/usr/local/lib/python2.7/dist-packages/django_mssql-1.7b1-py2.7.egg/sqlserver_ado/introspection.py", line 142, in get_relations
        source_field_dict = self._name_to_index(cursor, table_name)
      File "/usr/local/lib/python2.7/dist-packages/django_mssql-1.7b1-py2.7.egg/sqlserver_ado/introspection.py", line 139, in _name_to_index
        return dict([(d[0], i) for i, d in enumerate(self.get_table_description(cursor, table_name, False))])
      File "/usr/local/lib/python2.7/dist-packages/django_mssql-1.7b1-py2.7.egg/sqlserver_ado/introspection.py", line 109, in get_table_description
        cursor.execute("SELECT * FROM [%s] where 1=0" % (table_name))
      File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 81, in execute
        return super(CursorDebugWrapper, self).execute(sql, params)
      File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 65, in execute
        return self.cursor.execute(sql, params)
      File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 94, in __exit__
        six.reraise(dj_exc_type, dj_exc_value, traceback)
      File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 63, in execute
        return self.cursor.execute(sql)
      File "build/bdist.linux-x86_64/egg/sqlserver_pymssql/base.py", line 55, in execute
      File "pymssql.pyx", line 462, in pymssql.Cursor.execute (pymssql.c:6514)
    django.db.utils.ProgrammingError: (208, "Invalid object name 'ChandonItems'.DB-Lib error message 208, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")
    
  3. Michael Manfre repo owner

    I'll look in to supporting multiple table schemas for Django 1.8 and/or Django 1.9. Any potential fixes will not be backported and there is no ETA on if/when this will get implemented. Inspectdb related issues are low priority for me because the generated models are meant as a starting point and it is expected that manual cleanup should be done.

    For this instance of it failing to generate any models due to the many schemas, the workaround is to create a temp database with the tables from a single schema (add them to [dbo] to be safe), inspectdb it to get the models.py output, drop everything in the temp database, and then repeat for each of the remaining table schemas.

  4. Log in to comment