Why this particular combination?
- I need to create a website that talks to a MS SQL database in order to display the company's inventory (and the db is created by proprietary software used by the company, so switching to another kind of database is not an option)
- I've been learning Django & Python for the past few months (via official documentation, online courses, personal projects), and since I'm starting to understand things, I don't think I should all of a sudden switch to .NET or Node.JS, just because the set-up is still a bit of a mystery
- I'm working on a macbook, so Docker is the only way to install MS SQL Server (as far as I know). I do have a decent PC a friend gave me though (just need to set it up), and if anyone thinks this will solve all my problems, I will go ahead and switch.
What I've done/figured out so far (perhaps this would be helpful to someone going through the same process):
- there are plenty of guides and tutorials that explain how to use Django with Postgre, SQLite, etc. -- it all looks very straight-forward, so all I really need to get is the set-up
- I've got SQL Server up and running in a Docker container (with the help of https://database.guide/how-to-install-sql-server-on-a-mac/, https://hub.docker.com/_/microsoft-mssql-server, and https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017&pivots=cs1-bash)
- I've successfully restored the company's database from a .bak file using Azure Data Studio, and I'm able to run queries on it from the command line, through Kitematic, and, of course, ADS itself.
- I've attempted to connect to the database from a fresh Django project (using PyCharm, though this is probably irrelevant), using the settings outlined here: https://github.com/michiya/django-pyodbc-azure I keep getting the error "image not found", and here's the complete output in the console (in case someone spots something useful):
Unhandled exception in thread started by <function check_errors.<locals>.wrapper at 0x105ff5158>
Traceback (most recent call last):
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/sql_server/pyodbc/base.py", line 15, in <module>
import pyodbc as Database
ImportError: dlopen(/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so, 2): Library not loaded: /usr/local/opt/unixodbc/lib/libodbc.2.dylib
Referenced from: /Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so
Reason: image not found
During handling of the above exception, another exception occurred:
*Traceback (most recent call last):
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/utils/autoreload.py", line 225, in wrapper
fn(*args, **kwargs)
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/core/management/commands/runserver.py", line 109, in inner_run
autoreload.raise_last_exception()
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/utils/autoreload.py", line 248, in raise_last_exception
raise _exception[1]
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/core/management/__init__.py", line 337, in execute
autoreload.check_errors(django.setup)()
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/utils/autoreload.py", line 225, in wrapper
fn(*args, **kwargs)
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/__init__.py", line 24, in setup
apps.populate(settings.INSTALLED_APPS)
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/apps/registry.py", line 112, in populate
app_config.import_models()
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/apps/config.py", line 198, in import_models
self.models_module = import_module(models_module_name)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/importlib/__init__.py", line 127, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "<frozen importlib._bootstrap>", line 1006, in _gcd_import
File "<frozen importlib._bootstrap>", line 983, in _find_and_load
File "<frozen importlib._bootstrap>", line 967, in _find_and_load_unlocked
File "<frozen importlib._bootstrap>", line 677, in _load_unlocked
File "<frozen importlib._bootstrap_external>", line 728, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/contrib/auth/models.py", line 2, in <module>
from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/contrib/auth/base_user.py", line 47, in <module>
class AbstractBaseUser(models.Model):
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/db/models/base.py", line 101, in __new__
new_class.add_to_class('_meta', Options(meta, app_label))
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/db/models/base.py", line 305, in add_to_class
value.contribute_to_class(cls, name)
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/db/models/options.py", line 203, in contribute_to_class
self.db_table = truncate_name(self.db_table, connection.ops.max_name_length())
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/db/__init__.py", line 33, in __getattr__
return getattr(connections[DEFAULT_DB_ALIAS], item)
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/db/utils.py", line 202, in __getitem__
backend = load_backend(db['ENGINE'])
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/django/db/utils.py", line 110, in load_backend
return import_module('%s.base' % backend_name)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/importlib/__init__.py", line 127, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/sql_server/pyodbc/base.py", line 17, in <module>
raise ImproperlyConfigured("Error loading pyodbc module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading pyodbc module: dlopen(/Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so, 2): Library not loaded: /usr/local/opt/unixodbc/lib/libodbc.2.dylib
Referenced from: /Users/student8/Desktop/GEMROVER/django_site/lib/python3.7/site-packages/pyodbc.cpython-37m-darwin.so
Reason: image not found*
I've been googling these errors for a few days now, and reading/watching various guides, but to no avail... It seems like it's complaining that pyodbc hasn't been installed, but I've double-checked, and I have it. Same for django-pyodbc-azure.
Just in case, here's what I have in my settings.py file:
DATABASES = {
'default': {
'ENGINE': 'sql_server.pyodbc',
'NAME': '*********',
'USER': 'sa',
'PASSWORD': '********',
'HOST': '192.168.1.97',
'PORT': '1433',
'OPTIONS': {
'driver': 'ODBC Driver 13 for SQL Server',
},
},
}
That particular combination of username, password (***'d out), host IP, and port works perfectly in Azure Data Studio.
- I've installed Django into another Docker container by following this guide: https://docs.docker.com/compose/django/ Unfortunately this shows how to install with Postgres, and it's not clear how to alter the requirements.txt file to make this work with SQL server (what do I put there instead of psycopg2>=2.7,<3.0 in order to make it work with MS SQL?). I suppose something like this would go into docker-compose.yml:
db:
image: "mcr.microsoft.com/mssql/server"
environment:
SA_PASSWORD: "Your_password123"
ACCEPT_EULA: "Y"
(borrowed from https://docs.docker.com/compose/aspnet-mssql-compose/)
And the Dockerfile would probably stay the same as shown here: https://docs.docker.com/compose/django/?
It's also not completely clear to me whether Django and SQL Server should be in the same Docker image, or two separate ones? I suppose one and the same, since that's how their tutorial handles Django + Postgres? Also, down the line, when it's time to deploy the website, would all this work smoothly with, say Apache, or if, perhaps I go with Azure?
Any help would be greatly appreciated! If all this strikes you as an extremely n00b situation: yes, I'm pretty new to web development... I've tried my best to read through everything I could possibly find here on Stack and elsewhere, but haven't been able to find the solution yet.
UPDATE 1:
Oh wow, I made a bit of progress! Usually asking the question helps. First I did
brew install unixodbc
Recommended here: https://github.com/mkleehammer/pyodbc/issues/87 This fixed the error I was getting earlier. Then I was getting another error:
Can't open lib 'ODBC Driver 13 for SQL Server'
But the following fixed it:
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql mssql-tools
(from Rene's answer to this question Can't open lib 'ODBC Driver 13 for SQL Server'? Sym linking issue?, minus the bit about --no-sandbox -- this gave more errors, so I removed it)
The default Django site loaded up (via python manage.py runserver), so I think I'm finally ready to start working with the SQL database! I'll report back once I've made sure everything is running smoothly. I would appreciate any comments about my previous questions (like whether or not I should have Django in a docker container, and if so, if it should share the container with SQL Server, or be in a separate one?).
