denisenkom / django-sqlserver Goto Github PK
View Code? Open in Web Editor NEWDjango backend for MSSQL server using pytds or adodb backend (moved here from https://bitbucket.org/cramm/django-sqlserver)
License: MIT License
Django backend for MSSQL server using pytds or adodb backend (moved here from https://bitbucket.org/cramm/django-sqlserver)
License: MIT License
There is a similar ticket for django-mssql:
https://bitbucket.org/Manfre/django-mssql/issue/36/support-case-sensitive-comparisons
We get database collation by checking:
SELECT DATABASEPROPERTYEX('', 'Collation')
If the database collation is case sensitive (Latin1_General_BIN) and django tries to do case insensitive filtration such as icontains can we append the corresponding case insensitive collation (Latin1_General_CI_AS) just behind column name ?
The sql query will look like:
select * from where COLLATE Latin1_General_CI_AS like 'test%'
instead of
select * from where like 'test%'
Please correct me if I missed this in the wrapper or the original library. I understand that this is a wrapper to django-mssql but I was wondering if this is a better starting place to ask this. We have a rather large database with at least three different schemas. It seems to me that while running inspectdb
, schemas are not supported. I think it will be helpful to use Schemas as they are an integral part of the MSSQL databases.
Moreover, running inspectdb
on my database returns errors for the first table itself even if the table is given all permissions for anyone. I tried running myschem.PROD_DONE
as well but that also gave the same 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'.
Edit: More information -
Running SELECT * FROM myschem.PROD_DONE where 1=0
directly in the SQL serer studio does not give any error but while inspectdb runs, this line https://bitbucket.org/Manfre/django-mssql/src/8ac825580d01c653256d21931e87caf3aa3513fb/sqlserver_ado/introspection.py?at=stable/1.6#cl-109 fails.
I understand that this is a wrapper to django-mssql but I was wondering if this is a better starting place to ask this. The connections.introspection.table_names()
returns views as well. table_names()
calls get_tables()
which returns all objects and not just tables [1].
I think it will help if we can define not only the kind of objects but also if possible a list of tables that we need inspected. What do you think? Please feel free to correct me or point me to the right direction.
I'm using this backend on Django 1.10 without any problems. And as Django 1.11 is now out, is there any chance for support?
Tried on Django 1.11 and is seems that client_class is not implemented.
Traceback (most recent call last):
...
File "/project/virtual/local/lib/python2.7/site-packages/django/db/models/query.py", line 50, in __iter__
compiler = queryset.query.get_compiler(using=db)
File "/project/virtual/local/lib/python2.7/site-packages/django/db/models/sql/query.py", line 255, in get_compiler
connection = connections[using]
File "/project/virtual/local/lib/python2.7/site-packages/django/db/utils.py", line 212, in __getitem__
conn = backend.DatabaseWrapper(db, alias)
File "/project/virtual/local/lib/python2.7/site-packages/sqlserver/base.py", line 80, in __init__
super(DatabaseWrapper, self).__init__(*args, **kwargs)
File "/project/virtual/local/lib/python2.7/site-packages/sqlserver_ado/base.py", line 184, in __init__
super(DatabaseWrapper, self).__init__(*args, **kwargs)
File "/project/virtual/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 96, in __init__
self.client = self.client_class(self)
TypeError: 'NoneType' object is not callable
I use Python 3.4, Django1.8.18, django-mssql 1.8 and django-sqlserver 1.11. But when I run server, an error message comes up 'No module found Pythoncom'. Could you please help?
I changed settings.py like yours:
DATABASES = {
'default': {
'ENGINE': os.environ.get('BACKEND', 'sqlserver'),
'NAME': "DB",
# 'TEST_NAME': DATABASE,
'HOST': 'SADRA',
'USER': "hooman",
'PASSWORD': "behnejad",
'OPTIONS': {
'provider': os.environ.get('ADO_PROVIDER', 'SQLNCLI11'),
# 'extra_params': 'DataTypeCompatibility=80;MARS Connection=True;',
'use_legacy_date_fields': False,
},
},
'other': {
'ENGINE': os.environ.get('BACKEND', 'sqlserver'),
'NAME': DATABASE + '_other',
'TEST_NAME': DATABASE + '_other',
'HOST': HOST,
'USER': USER,
'PASSWORD': PASSWORD,
'OPTIONS': {
'provider': os.environ.get('ADO_PROVIDER', 'SQLNCLI11'),
# 'extra_params': 'DataTypeCompatibility=80;MARS Connection=True;',
'use_legacy_date_fields': False,
},
}
}
but I have this error:
raise ex django.db.utils.OperationalError: Login failed for user 'myusername'.
i have already checked my username and password in SQL authentication mode :)
help me with this man ?
We have to use Windows authentication in production, but I see you removed support for using the original ADO driver in 31b1ffb. Perhaps because it was broken and tricky to actually use or test due to the dependancy on python-tdb
?
It so happens I fixed it locally and got django-sqlserver
up and running on Django 1.11 (which django-mssql
doesn't support) with the ADO driver, only to pull and discover that you removed it.
So, my question is: Is it possible to use Windows authentication with the PyTDB driver, and if so, would you perhaps consider documenting it? If not, would you consider a patch to revive ADO support? After all, django-mssql
doesn't seem all that active, and it'd be nice to use one driver everywhere.
On the front page you mention:
You can also specify additional OPTIONS attribute as described in http://django-mssql.readthedocs.io/en/latest/settings.html#options
The link talks about extra_params
. But I don't see it being passed to pytds.connect()
. The part of the code responsible for creating the connection dict either hardcodes some values or checks _SUPPORTED_OPTIONS
, which contains only failover_partner
(that is already retrieved from the options few lines prior to checking _SUPPORTED_OPTIONS
).
In my case I'm missing the ability to set timeout
and login_timeout
(which should as easy as just passing them to connect
- e.g. through updating _SUPPORTED_OPTIONS
). I don't know about an easy way of adding extra_params
support, since I don't see that one being accepted by the connect
function.
Please refer to this post
https://stackoverflow.com/questions/48491168/error-connecting-django-2-0-with-sql-server-2014-using-python-3-6
Sorry I just saw there is no support for Django 2.0.1
I have been using your python-tds in all of my projects that have to connect to mssql server ever since I found it. Love the fact that it is pure python.
I am just starting with django and wanted to use sqlserver as the backend driver, but it doesn't seem to work with the current version (I have django 3.1).
The error is:
ImportError: cannot import name 'six' from 'django.utils'
If I modify that file to import six directly (it is installed as a dependency for my version of django) then I get:
AttributeError: module 'six' has no attribute 'memoryview'
Is the project still being maintained? If there are no plans to update it, would you be willing to offer any guidance on how it can be done?
Hi.
What am I supposed to put under "ENGINE" in Django's settings.py?
C:\Windows\system32>pip install pymssql
Collecting pymssql
Using cached pymssql-2.1.3.tar.gz
Installing collected packages: pymssql
Running setup.py install for pymssql ... error
Complete output from command c:\users\vitriv-desktop\appdata\local\programs\python\python36-32\python.exe -u -c "import setuptools, tokenize;__file__='C:\\Users\\VITRIV~1\\AppData\\Local\\Temp\\pip-build-3eryhq60\\pymssql\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record C:\Users\VITRIV~1\AppData\Local\Temp\pip-g1vedqw8-record\install-record.txt --single-version-externally-managed --compile:
setup.py: platform.system() => 'Windows'
setup.py: platform.architecture() => ('32bit', 'WindowsPE')
running install
running build
running build_ext
building '_mssql' extension
error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": http://landinghub.visualstudio.com/visual-cpp-build-tools
----------------------------------------
Command "c:\users\vitriv-desktop\appdata\local\programs\python\python36-32\python.exe -u -c "import setuptools, tokenize;__file__='C:\\Users\\VITRIV~1\\AppData\\Local\\Temp\\pip-build-3eryhq60\\pymssql\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record C:\Users\VITRIV~1\AppData\Local\Temp\pip-g1vedqw8-record\install-record.txt --single-version-externally-managed --compile" failed with error code 1 in C:\Users\VITRIV~1\AppData\Local\Temp\pip-build-3eryhq60\pymssql\
We are using:
Django 1.11 / django mssql 1.8 / django-sqlserver 1.11 / python-tds 1.9.0 / sql server 2016
running on windows servers.
Does this support a trusted connection?
We currently put the database username/password in settings.py and can't get a database connection with username and password set to ''. We get an ..invalid user '' error.
Using Django channels 1.x and have the worker process running as a windows service with a valid windows account.
Hi!
I'm using django-sqlserver in my project (Django 1.7.7, Python 3.4).
I tried to switch to Django 1.8c1 and it seems that they've made a lot of changes in django.db so django-sqlserver doesn't work anymore.
Are you going to implement Django 1.8 support soon?
Your project is pretty unique, I can't find any alternatives so far, so I count on you
As I'm new to python web framework.
With backend as MSSQL Server 2017. I needed to work
In settings.py
**DATABASES = {
'default': {
'ENGINE': 'django_pyodbc',
'HOST': '127.0.0.1',
'NAME': 'demo2016',
'USER': '',
'PASSWORD': '',
'PORT': '',
'OPTIONS': {
'driver': 'ODBC Driver 13 for SQL Server',
},
}
}**
In cmd prompt:
C:\Users\Vitriv-Desktop\Desktop\sqldjango>python manage.py migrate
Traceback (most recent call last):
File "manage.py", line 22, in <module>
execute_from_command_line(sys.argv)
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\core\management\__init__.py", line 364, in execute_from_command_line
utility.execute()
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\core\management\__init__.py", line 338, in execute
django.setup()
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\__init__.py", line 27, in setup
apps.populate(settings.INSTALLED_APPS)
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\apps\registry.py", line 108, in populate
app_config.import_models()
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\apps\config.py", line 202, in import_models
self.models_module = import_module(models_module_name)
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\importlib\__init__.py", line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "<frozen importlib._bootstrap>", line 994, in _gcd_import
File "<frozen importlib._bootstrap>", line 971, in _find_and_load
File "<frozen importlib._bootstrap>", line 955, in _find_and_load_unlocked
File "<frozen importlib._bootstrap>", line 665, in _load_unlocked
File "<frozen importlib._bootstrap_external>", line 678, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\contrib\auth\models.py", line 4, in <module>
from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\contrib\auth\base_user.py", line 52, in <module>
class AbstractBaseUser(models.Model):
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\base.py", line 124, in __new__
new_class.add_to_class('_meta', Options(meta, app_label))
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\base.py", line 325, in add_to_class
value.contribute_to_class(cls, name)
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\options.py", line 214, in contribute_to_class
self.db_table = truncate_name(self.db_table, connection.ops.max_name_length())
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\__init__.py", line 33, in __getattr__
return getattr(connections[DEFAULT_DB_ALIAS], item)
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\utils.py", line 211, in __getitem__
backend = load_backend(db['ENGINE'])
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\utils.py", line 115, in load_backend
return import_module('%s.base' % backend_name)
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\importlib\__init__.py", line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "C:\Users\Vitriv-Desktop\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django_pyodbc\base.py", line 98, in <module>
raise ImproperlyConfigured("Django %d.%d is not supported." % DjangoVersion[:2])
django.core.exceptions.ImproperlyConfigured: Django 1.11 is not supported.
I notice that DATETIME columns do not work with this back-end. I was using django-pytds with DATETIME columns, but without the ability to write. I need to access a legacy database that has DATETIME columns that I cannot change. Is there a change that can be made to make these work with django-sqlserver?
python manage.py dbshell
fails due to missing method
Please add nolock support in sql compiler.
I will be a great add on in sql compiler.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.