Nai Biao Zhou
Nai Biao Zhou has twenty years of experience in software development, with strong analytical skills and a broad range of computer expertise. He has fifteen years of experience in development and maintenance of database applications on SQL Server in OLTP/OLAP/BI environments. He has a master's degree in Control Engineering and is pursuing a Master of Science degree in Predictive Analytics. Knowledge/Skills Advanced computer programming skills and well versed with C/C#, Java, ASP.NET, JavaScript, and SQL. In depth knowledge of Solution Architecture, and Software Development Life Cycle (SDLC). Microsoft Certified Application Developer (MCAD) since 2004.
- MSSQLTips Awards: Author of the Year - 2021 | Rising Star (50+ tips) - 2024 | Author Contender - 2020, 2023
10 comments
Hello Ibrahim,
Thank you for the feedback. I checked the django-mssql-backend library (https://pypi.org/project/django-mssql-backend/). The latest release was on Apr 22, 2020. According to the project description, the library has these features:
Supports Django 2.2, 3.0
Supports Microsoft SQL Server 2008/2008R2, 2012, 2014, 2016, 2017, 2019
Therefore, the library may not support Django and MS SQL databases in your project.
However, you can try mssql-django(https://pypi.org/project/mssql-django/):
Supports Django 3.2, 4.0, 4.1 and 4.2
Tested on Microsoft SQL Server 2016, 2017, 2019, 2022
We can use the following database settings in the “settings.py” file. You may have a different version of the ODBC driver.
DATABASES = {
‘default’: {
‘ENGINE’: ‘mssql’,
‘NAME’: ‘django_db’,
‘USER’: ‘django’,
‘PASSWORD’: ‘password’,
‘HOST’: ‘192.168.2.21’,
‘PORT’: ”,
‘OPTIONS’: {
‘driver’: ‘ODBC Driver 17 for SQL Server’,
},
},
}
I got this problem:
Traceback (most recent call last):
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\db\utils.py”, line 113, in load_backend
return import_module(“%s.base” % backend_name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Python\Python311\Lib\importlib\__init__.py”, line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “<frozen importlib._bootstrap>”, line 1204, in _gcd_import
File “<frozen importlib._bootstrap>”, line 1176, in _find_and_load
File “<frozen importlib._bootstrap>”, line 1147, in _find_and_load_unlocked
File “<frozen importlib._bootstrap>”, line 690, in _load_unlocked
File “<frozen importlib._bootstrap_external>”, line 940, in exec_module
File “<frozen importlib._bootstrap>”, line 241, in _call_with_frames_removed
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\sql_server\pyodbc\base.py”, line 35, in <module>
from .operations import DatabaseOperations # noqa
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\sql_server\pyodbc\operations.py”, line 10, in <module>
import pytz
ModuleNotFoundError: No module named ‘pytz’
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File “C:\Python\Python311\Lib\runpy.py”, line 198, in _run_module_as_main
return _run_code(code, main_globals, None,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Python\Python311\Lib\runpy.py”, line 88, in _run_code
exec(code, run_globals)
File “c:\program files\microsoft visual studio\2022\community\common7\ide\extensions\microsoft\python\core\debugpy\adapter/../..\debugpy\launcher/../..\debugpy\__main__.py”, line 39, in <module>
cli.main()
File “c:\program files\microsoft visual studio\2022\community\common7\ide\extensions\microsoft\python\core\debugpy\adapter/../..\debugpy\launcher/../..\debugpy/..\debugpy\server\cli.py”, line 430, in main
run()
File “c:\program files\microsoft visual studio\2022\community\common7\ide\extensions\microsoft\python\core\debugpy\adapter/../..\debugpy\launcher/../..\debugpy/..\debugpy\server\cli.py”, line 284, in run_file
runpy.run_path(target, run_name=”__main__”)
File “c:\program files\microsoft visual studio\2022\community\common7\ide\extensions\microsoft\python\core\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py”, line 321, in run_path
return _run_module_code(code, init_globals, run_name,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “c:\program files\microsoft visual studio\2022\community\common7\ide\extensions\microsoft\python\core\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py”, line 135, in _run_module_code
_run_code(code, mod_globals, init_globals,
File “c:\program files\microsoft visual studio\2022\community\common7\ide\extensions\microsoft\python\core\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py”, line 124, in _run_code
exec(code, run_globals)
File “E:\Programming\DjangoWebProject\DjangoWebSite\manage.py”, line 24, in <module>
execute_from_command_line(sys.argv)
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\core\management\__init__.py”, line 442, in execute_from_command_line
utility.execute()
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\core\management\__init__.py”, line 416, in execute
django.setup()
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\__init__.py”, line 24, in setup
apps.populate(settings.INSTALLED_APPS)
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\apps\registry.py”, line 116, in populate
app_config.import_models()
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\apps\config.py”, line 269, in import_models
self.models_module = import_module(models_module_name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “C:\Python\Python311\Lib\importlib\__init__.py”, line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “<frozen importlib._bootstrap>”, line 1204, in _gcd_import
File “<frozen importlib._bootstrap>”, line 1176, in _find_and_load
File “<frozen importlib._bootstrap>”, line 1147, in _find_and_load_unlocked
File “<frozen importlib._bootstrap>”, line 690, in _load_unlocked
File “<frozen importlib._bootstrap_external>”, line 940, in exec_module
File “<frozen importlib._bootstrap>”, line 241, in _call_with_frames_removed
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\contrib\auth\models.py”, line 3, in <module>
from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\contrib\auth\base_user.py”, line 57, in <module>
class AbstractBaseUser(models.Model):
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\db\models\base.py”, line 143, in __new__
new_class.add_to_class(“_meta”, Options(meta, app_label))
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\db\models\base.py”, line 371, in add_to_class
value.contribute_to_class(cls, name)
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\db\models\options.py”, line 243, in contribute_to_class
self.db_table, connection.ops.max_name_length()
^^^^^^^^^^^^^^
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\utils\connection.py”, line 15, in __getattr__
return getattr(self._connections[self._alias], item)
~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\utils\connection.py”, line 62, in __getitem__
conn = self.create_connection(alias)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\db\utils.py”, line 193, in create_connection
backend = load_backend(db[“ENGINE”])
^^^^^^^^^^^^^^^^^^^^^^^^^^
File “E:\Programming\DjangoWebProject\DjangoWebSite\env\Lib\site-packages\django\db\utils.py”, line 126, in load_backend
raise ImproperlyConfigured(
django.core.exceptions.ImproperlyConfigured: The thread ‘MainThread’ (0x1) has exited with code 0 (0x0).
‘sql_server.pyodbc’ isn’t an available database backend or couldn’t be imported. Check the above exception. To use one of the built-in backends, use ‘django.db.backends.XXX’, where XXX is one of:
‘mysql’, ‘oracle’, ‘postgresql’, ‘sqlite3’
The program ‘python.exe’ has exited with code 4294967295 (0xffffffff).
Hello Jim,
Thank you very much for sharing your observation.
Regarding your question, a typical solution is to store the DB password in an environment variable. The following web pages can give you detailed information:
https://medium.com/django-musings/what-to-do-with-passwords-and-secrets-16c2409bfdd8The
https://stackoverflow.com/questions/42077532/django-security-and-settings
However, all these methods can not secure the password but just make it hard to hack.
You may need to talk to your security officers to find an acceptable solution.
Thanks!
Nai Biao Zhou
One observation, and one question.
Observation: I used the template for the DATABASES parameter, including ENGINE, NAME, USER, and so on. But because I wasn’t sure of the driver version in my environment, I simply omitted the OPTIONS. This configuration worked, my Django app successfully connected to the database. (I don’t know but perhaps this is because the SQL Server instance was local to the machine where my Django app was hosted?)
Question: Is there any way to *not* specify the PASSWORD in clear text — to have it be a hash code or something? Clear text passwords in a human-readable file are considered a major security flaw.
Thank you for reading my article.
Firstly, please check this link: https://stackoverflow.com/questions/46045834/pyodbc-data-source-name-not-found-and-no-default-driver-specified. I doubted the machine did not have the driver installed.
Secondly, please make sure the setting is in this format:
DATABASES = {
‘default’: {
‘ENGINE’: ‘sql_server.pyodbc’,
‘NAME’: ‘django_db’,
‘USER’: ‘django’,
‘PASSWORD’: ‘password’,
‘HOST’: ‘192.168.2.17’,
‘PORT’: ”,
‘OPTIONS’: {
‘driver’: ‘ODBC Driver 13 for SQL Server’,
},
},
}
Hi Dear Zhou,
I am having the same trouble with the other guy below, but unfortunately I couldn’t fix it by his way and still getting the error. When I paste your code snippet then go through the 2.4 step, I get an error like following:
>>>
Executing manage.py migrate
Traceback (most recent call last):
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\backends\base\base.py”, line 217, in ensure_connection
self.connect()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\backends\base\base.py”, line 195, in connect
self.connection = self.get_new_connection(conn_params)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\sql_server\pyodbc\base.py”, line 312, in get_new_connection
conn = Database.connect(connstr,
pyodbc.InterfaceError: (‘IM002’, ‘[IM002] [Microsoft][ODBC Driver Manager] Veri kaynağı adı bulunamadı ve varsayılan sÜrÜcÜ belirtilmemiş (0) (SQLDriverConnect)’)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\manage.py”, line 24, in <module>
execute_from_command_line(sys.argv)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\core\management\__init__.py”, line 381, in execute_from_command_line
utility.execute()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\core\management\__init__.py”, line 375, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\core\management\base.py”, line 323, in run_from_argv
self.execute(*args, **cmd_options)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\core\management\base.py”, line 364, in execute
output = self.handle(*args, **options)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\core\management\base.py”, line 83, in wrapped
res = handle_func(*args, **kwargs)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\core\management\commands\migrate.py”, line 87, in handle
executor = MigrationExecutor(connection, self.migration_progress_callback)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\migrations\executor.py”, line 18, in __init__
self.loader = MigrationLoader(self.connection)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\migrations\loader.py”, line 49, in __init__
self.build_graph()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\migrations\loader.py”, line 212, in build_graph
self.applied_migrations = recorder.applied_migrations()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\migrations\recorder.py”, line 73, in applied_migrations
if self.has_table():
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\migrations\recorder.py”, line 56, in has_table
return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor())
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\backends\base\base.py”, line 256, in cursor
return self._cursor()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\sql_server\pyodbc\base.py”, line 218, in _cursor
conn = super()._cursor()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\backends\base\base.py”, line 233, in _cursor
self.ensure_connection()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\backends\base\base.py”, line 217, in ensure_connection
self.connect()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\utils.py”, line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\backends\base\base.py”, line 217, in ensure_connection
self.connect()
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\django\db\backends\base\base.py”, line 195, in connect
self.connection = self.get_new_connection(conn_params)
File “C:\Users\hasanfatih8\source\repos\TravelAgency-WebApp\env\lib\site-packages\sql_server\pyodbc\base.py”, line 312, in get_new_connection
conn = Database.connect(connstr,
django.db.utils.InterfaceError: (‘IM002’, ‘[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) )
>>>
I hope we can solve this situation
Best,
Hasan Fatih
Hello Lakshmi,
Thank you for posting your solution here.
Regarding the ODBC Driver for SQL Server, my other tip: https://www.mssqltips.com/sqlservertip/6694/crud-operations-in-sql-server-using-python/ introduces how to verify whether a computer has the driver installed. Please see the subtitle, “1.2 ODBC Driver for SQL Server” in the tip.
Best Regards,
Nai Biao Zhou
Open the file “settings.py,” and substitute the highlighted code block in Figure 14 with the following code snippet. The package website (ES Solutions AB, 2020) explains these settings.
not working
‘driver’: ‘ODBC Driver 13 for SQL Server’,
WORKING
‘driver’: ‘SQL Server Native Client 10.0’,
I am connecting to Remote server.. looking for such solutions for years.. thanks for the guide…
great work…
Hello Lakshmi,
For some reason, I can not see the errors in your comment.
By the way, you may check the database permission configuration. During my testing, the user is a member of the db_owner role (shown in Figure 13).
Best Regards,
Nai Biao Zhou
HI,
2.3 Configuring Database Access in Django Settings
This step not working..
I need you help… The following error throws when Django Migrate action..
Pls help me…