Line 3 in django_pyodbc.creation attempts to import from the deprecated django.utils.hashcompat module. Any attempt to query a pyodbc database raises the deprecation warning.

Query fails on Django 1.4 for the following reason:

  File "C:\Users\michiya\workspace\Django-1.4.5\django\db\models\sql\compiler.py", line 808, in execute_sql
    sql, params = self.as_sql()
  File "C:\Users\michiya\workspace\django-pyodbc\django_pyodbc\compiler.py", line 243, in as_sql
    sql, params = self._as_sql(USE_ROW_NUMBER)
  File "C:\Users\michiya\workspace\django-pyodbc\django_pyodbc\compiler.py", line 168, in _as_sql
    grouping, gb_params = self.get_grouping(ordering_group_by)
TypeError: get_grouping() takes exactly 1 argument (2 given)

See:
https://docs.djangoproject.com/en/1.5/releases/1.5/#python-3-support

Greetings,

First off, thanks for the amazing work on django-pyodbc. We've been looking at moving our organization to Django, but have many projects which require SQL Server (a common refrain in these parts).

We hope to use django-pyodbc but this statement is a bit concerning: "Passes most of the tests of the Django test suite." I'm going to run the whole suite tomorrow to find out specifics, but even so, I was hoping the contributors to this repo could elaborate on this statement a bit, and what should be concerning if we consider running this in production. What are the key parts of it that fail - and how can we contribute to make it pass all the tests?

Are the fails on elements that don't exist in SQL Server, or didn't, like pagination support with TOP / OFFSET values? Are these test failures done strictly through Django's ORM? In other words: if we were running Rails, Grails, Symfony, etc, would we merely be seeing the limitations of the underlying database, or are we seeing things that can be done? Comments from the experts / contributors would be most appreciated.

We're more than happy to contribute, as we have with Pydanny's Two Scoops default project, and a few others.

We're also working on releasing an improved Django vagrant repo, once we get the chrome polished, which includes built in options to connect to SQL Server or any of the core-supported Django DBs.

Thanks again in advance.

I'm working with an old database and I see that constant SQL_SERVER_8_LIMIT_QUERY still exists in compiler.py, but it is no longer being used. It was last touched in 95b7433 and that version of compiler.py seemed to be using it to make SQL Server 2000 style paginations.

Has SQL Server 2000 support been removed? Would restoring this pagination functionality be possible, or would it just be too much of a pain?

Getting this error on Mac OS 10.9, Python 2.7.5, Django 1.5.4, SQL 2008:

DatabaseError
Exception Value: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')
Exception Location: /Users/mwoodward/.virtualenvs/stockadmin/lib/python2.7/site-packages/django_pyodbc/base.py in execute, line 361

Happens on any view function that has any database interaction (i.e. isn't limited to a specific database operation). Happy to provide additional details as I can.

Any way to limit the db's connection timeout? It sometimes takes a long couple of seconds to connect to the db and return results.

Hi there,
when i order from some fields and paginate like in Admin interface i've got the "Missing left_sql_quote property" error for SqlCompiler.

This happen in _fix_slicing_order

The implied line is 308 of compiler.py:

col = '{left_sql_quote}{0}___o{1}{right_sql_quote}'.format(
                        col.strip(self.left_sql_quote+self.right_sql_quote),
                        alias_id,
                        left_sql_quote=self.left_sql_quote,
                        right_sql_quote=self.right_sql_quote,
                    ) 

Changing the self.left_sql_quote to self.connection.ops.left_sql_quote seems to solve the problem.

col = '{left_sql_quote}{0}___o{1}{right_sql_quote}'.format(
                        col.strip(self.connection.ops.left_sql_quote+self.connection.ops.right_sql_quote),
                        alias_id,
                        left_sql_quote=self.connection.ops.left_sql_quote,
                        right_sql_quote=self.connection.ops.right_sql_quote,
                    ) 

Is it the right way to solve that?

Greetings,
Diego

Fails to recevie connection_created signal with the following error:

File "C:\Users\michiya\workspace\Django-1.5.1\django\dispatch\dispatcher.py", line 170, in send
response = receiver(signal=self, sender=sender, **named)
TypeError: receiver() takes exactly 2 arguments (1 given)

Loading of the backend fails with an ImproperlyConfigured.
The error occurs only when you use Django 1.3 or 1.2.
See the traceback below for more details.

Traceback (most recent call last):
  File "manage.py", line 14, in <module>
    execute_manager(settings)
  File "C:\Users\michiya\workspace\Django-1.3.7\django\core\management\__init__.py", line 438, in execute_manager
    utility.execute()
  File "C:\Users\michiya\workspace\Django-1.3.7\django\core\management\__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\michiya\workspace\Django-1.3.7\django\core\management\__init__.py", line 261, in fetch_command
    klass = load_command_class(app_name, subcommand)
  File "C:\Users\michiya\workspace\Django-1.3.7\django\core\management\__init__.py", line 67, in load_command_class
    module = import_module('%s.management.commands.%s' % (app_name, name))
  File "C:\Users\michiya\workspace\Django-1.3.7\django\utils\importlib.py", line 35, in import_module
    __import__(name)
  File "C:\Users\michiya\workspace\Django-1.3.7\django\core\management\commands\sql.py", line 4, in <module>
    from django.core.management.sql import sql_create
  File "C:\Users\michiya\workspace\Django-1.3.7\django\core\management\sql.py", line 6, in <module>
    from django.db import models
  File "C:\Users\michiya\workspace\Django-1.3.7\django\db\__init__.py", line 78, in <module>
    connection = connections[DEFAULT_DB_ALIAS]
  File "C:\Users\michiya\workspace\Django-1.3.7\django\db\utils.py", line 93, in __getitem__
    backend = load_backend(db['ENGINE'])
  File "C:\Users\michiya\workspace\Django-1.3.7\django\db\utils.py", line 51, in load_backend
    raise ImproperlyConfigured(error_msg)
django.core.exceptions.ImproperlyConfigured: 'django_pyodbc' isn't an available database backend. 
Try using django.db.backends.XXX, where XXX is one of
    'dummy', 'mysql', 'oracle', 'postgresql', 'postgresql_psycopg2', 'sqlite3'
Error was: cannot import name connection```

Hi,

I'm trying to use this beatiful driver with the MultiDabase support from django (1.5); I can inspect sucessfully the database using the inspectdb command, but i cant do operations with the ORM.

Looking at http://code.google.com/p/django-pyodbc/issues/detail?id=72, i can recognize the same problem: the database connector always try to use the 'default' database entry and that's is not my case.

I saw a patch in the issue link, but i'm not certainly that the codes are the same between the branches or if i can just apply the patch avaiable there (http://code.google.com/p/django-pyodbc/issues/attachmentText?id=72&aid=4831504032447418805&name=issue-72.patch&token=I-wyzRvjrj7fvEZv4qB-iOHL0PQ%3A1378494455736).

If I change my project to use the MSSQL database as the 'default' in the database options under de settings.py module, every operation occurs normally; if it isnt the default, the "Database Error: No such Column" is raised every time.

Thank you in advance.

Gabriel Abdalla Cavalcante

I'm having trouble connecting to a SQL Server database through python manage.py dbshell / loaddata.

I'm set up on Ubuntu with FreeTDS, unixODBC, pyodbc (3.0.7) and django-pyodbc from here.

I can successfully run syncdb and South migrations. However, when I try to run the dbshell or loaddata, I get this error:

[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

I can connect from the command line using isql and tsql. Any ideas on what I'm missing? Thanks in advance.

I am having a problem getting this project to run in various setups. These are all Linux and/or osX using FreeTDS.

I have an old version of one of these projects that a few years ago I customized to get working in these environments. I have lots of schema's in a pre-existing MsSQL box that I mapped to django ORM objects. The default database is postgres, but I have a particular app and all the models in it mapped with the multi-database support.

I'm running Django 1.5.5, pyodbc 3.0.6, and now django-pyodbc 0.10. I can run select style queries just fine like:

print Example.objects.all().count()

... anything that generates a select-style query seems ok. But anything like:

s = Example()
s.save()

results in:

Traceback (most recent call last):
File "/Users/ryan/Google Drive/code/cortex/temp.py", line 107, in
rss.save()
File "/Users/ryan/Google Drive/code/cortex/axons/models.py", line 62, in save
super(DateModel, self).save(_args, *_kwargs)
File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py", line 546, in save
force_update=force_update, update_fields=update_fields)
File "/usr/local/lib/python2.7/site-packages/django/db/models/base.py", line 650, in save_base
result = manager._insert([self], fields=fields, return_id=update_pk, using=using, raw=raw)
File "/usr/local/lib/python2.7/site-packages/django/db/models/manager.py", line 215, in _insert
return insert_query(self.model, objs, fields, **kwargs)
File "/usr/local/lib/python2.7/site-packages/django/db/models/query.py", line 1675, in insert_query
return query.get_compiler(using=using).execute_sql(return_id)
File "/usr/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 942, in execute_sql
for sql, params in self.as_sql():
File "/usr/local/lib/python2.7/site-packages/django_pyodbc/compiler.py", line 283, in as_sql
result.append('(%s)' % ', '.join([qn(c) for c in self.query.columns]))
AttributeError: 'InsertQuery' object has no attribute 'columns'

Django 1.6 now assumes that autocommit defaults to True, meaning that it must explicitly be set to work with 1.6 (eg, syncdb will appear to succeed but not create any tables). It might be worth adding a note to this effect in the README.

(I can submit a pull request if you'd like, but thought I'd run it by here first in case I'm missing something else)

Inserting of an entity fails with a ValueError.
The traceback below is the result of syncdb management command, see it for more details.

C:\Users\michiya\workspace\Django-1.5.1\django\utils\hashcompat.py:9: DeprecationWarning: django.utils.hashcompat is deprecated; use hashlib instead
  DeprecationWarning)

Creating tables ...
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table auth_user
Creating table django_content_type
Creating table django_session
Creating table django_site
Traceback (most recent call last):
  File "C:\Users\michiya\workspace\Django-1.5.1\django\core\management\base.py", line 222, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\core\management\base.py", line 255, in execute
    output = self.handle(*args, **options)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\core\management\base.py", line 385, in handle
    return self.handle_noargs(**options)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\core\management\commands\syncdb.py", line 109, in handle_noargs
    emit_post_sync_signal(created_models, verbosity, interactive, db)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\core\management\sql.py", line 195, in emit_post_sync_signal
    interactive=interactive, db=db)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\dispatch\dispatcher.py", line 170, in send
    response = receiver(signal=self, sender=sender, **named)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\contrib\auth\management\__init__.py", line 96, in create_permissions
    auth_app.Permission.objects.using(db).bulk_create(perms)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\db\models\query.py", line 444, in bulk_create
    self._batched_insert(objs_without_pk, fields, batch_size)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\db\models\query.py", line 902, in _batched_insert
    using=self.db)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\db\models\manager.py", line 215, in _insert
    return insert_query(self.model, objs, fields, **kwargs)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\db\models\query.py", line 1661, in insert_query
    return query.get_compiler(using=using).execute_sql(return_id)
  File "C:\Users\michiya\workspace\Django-1.5.1\django\db\models\sql\compiler.py", line 936, in execute_sql
    for sql, params in self.as_sql():
  File "C:\Users\michiya\workspace\django-pyodbc\django_pyodbc\compiler.py", line 317, in as_sql
    for p, vals in zip(placeholders, params)
ValueError: too many values to unpack

I'm trying to connect a service that I develop using the sqlite3 and should run on MS SQL 2008. Using the django_pyodbc I get an error loading time from database. This time fields are actualy varchar(8) in format HH:MM:SS. For that to work I build a custom time field and work fine on sqlit3, mysql and postgres. On django_pyodbc I get this:

"Traceback:
File "/usr/local/lib/python2.7/dist-packages/django/core/handlers/base.py" in get_response
  115.                         response = callback(request, *callback_args, **callback_kwargs)
File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/options.py" in wrapper
  372.                 return self.admin_site.admin_view(view)(*args, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in _wrapped_view
  91.                     response = view_func(request, *args, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/django/views/decorators/cache.py" in _wrapped_view_func
  89.         response = view_func(request, *args, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/sites.py" in inner
  202.             return view(request, *args, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in _wrapper
  25.             return bound_func(*args, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in _wrapped_view
  91.                     response = view_func(request, *args, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/django/utils/decorators.py" in bound_func
  21.                 return func(self, *args2, **kwargs2)
File "/usr/local/lib/python2.7/dist-packages/django/contrib/admin/options.py" in changelist_view
  1285.             'selection_note': _('0 of %(cnt)s selected') % {'cnt': len(cl.result_list)},
File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py" in __len__
  90.                 self._result_cache = list(self.iterator())
File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py" in iterator
  301.         for row in compiler.results_iter():
File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py" in results_iter
  803.                     row = self.resolve_columns(row, fields)
File "/usr/local/lib/python2.7/dist-packages/django_pyodbc-0.10-py2.7.egg/django_pyodbc/compiler.py" in resolve_columns
  41.             values.append(self.query.convert_values(value, field, connection=self.connection))
File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/query.py" in convert_values
  321.         return connection.ops.convert_values(value, field)
File "/usr/local/lib/python2.7/dist-packages/django_pyodbc-0.10-py2.7.egg/django_pyodbc/operations.py" in convert_values
  347.             value = value.time() # extract time

Exception Type: AttributeError at /admin/entities/address/
Exception Value: 'unicode' object has no attribute 'time'"

Apparently the compiler don't use the django proper methods to load the data. I believe this is a major bug and should be fixed with some urgency. If not and someone notice I have something wrong, please just return it to me.

Thanks

Is there anyone working to use pytds (https://github.com/denisenkom/pytds) instead of pyodbc?

I made a simple modification into the code to use the pytds and everything seems to be ok (the tests are running).

I found another project by the same author (https://bitbucket.org/denisenkom/django-pytds) but is outdated.

There is interest in supporting this project pytds? As the pytds is an implementation in pure Python, you may want to bet on it.

#4

I have my own fork of django-pyodbc on github at https://github.com/avidal/django-pyodbc which I created to add Django 1.4 compatibility.

I no longer work for the company that needed to use the library so I'm no longer working on that fork, but I wanted to point out that I did make a few adjustments that you may or may not have caught; and I'd love to have a place to point people if they want to use my fork of it since you seem to be actively working on the project.

What I do know is that we used my fork successfully (and still do) on a wide variety of Django projects without any issues. So, if there's something you can take from it, please do.

Does anyone have any experience with Redshift with this? Does it work without issues?
Any insight would be helpful.
Thanks a lot.

I'm working on Django 1.6 and django-pyodbc but don't work on Mac.

FreeTDS and unixODBC is working very well but when I install django-pyodbc and configure 'ENGINE': "django_pyodbc", and do ./manage.py runserver it's displays:

django.core.exceptions.ImproperlyConfigured: 'django_pyodbc' isn't an available database backend.
Try using django.db.backends.XXX, where XXX is one of
'dummy', 'mysql', 'oracle', 'postgresql', 'postgresql_psycopg2', 'sqlite3'
Error was: cannot import name connection```

any way to fix this please?

Unable to run Django test suite because of the following error:

Creating test database for alias 'default' ('test_defaultdb')...
Got an error creating the test database: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction. (226) (SQLExecDirectW)')
Destroying old test database 'default'...
Got an error recreating the test database: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]DROP DATABASE statement cannot be used inside a user transaction. (574) (SQLExecDirectW)')

I use django1.7 and pyobdc on windows 7

when I try to connected to an MS Access DB, I have this error

raise NotImplementedError('subclasses of BaseDatabaseWrapper may require a schema_editor() method')
NotImplementedError: subclasses of BaseDatabaseWrapper may require a schema_editor() method

Thanks

Hi. Just tried this out on Django 1.6 and started getting ValueError Too many values to unpack all over the place. Downgrading to Django 1.5 fixed the issue.

Any sense of how core the source of the error is? I can try to provide more context if necessary, but needed to just "fix it" tonight, which I did by downgrading Django.

Newbie to github. Don't know how to contact lionheart.

You create a great package! Thank you very much.

Do you have plan of using pypyodbc? It solved my problem when pyodbc failed to work on my mac.

I've made a very simple dummy project that has no models...and I'm trying to get it to syncdb to a remote MSSQL server. (I.e. it should go ahead and make all the default Django config tables like sessions, etc and also the Auth models like user and groups and permissions.)

My DATABASES stanza is:

DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'HOST': '__MY_HOSTNAME__, 1433',
        'NAME': '__MY_DBNAME__',                      # Or path to database file if using sqlite3.
        'USER': '__MY_USERNAME',
        'PASSWORD': '__MY_PASSWORD__',
        'OPTIONS': {
            'host_is_server': True,
            'extra_params': 'tds_version=8.0'
        },
    }
}

I am getting some frustrating behavior. When I do this with Django==1.6.2 and django-pyodbc==0.2.3, all of the console output from python manage.py syncdb looks fine. But nothing gets created in the database. No tables, no entries.

If I roll back both of them to Django=1.5.4 and django-pyodbc==0.2 then it works perfectly. The same syncdb command with the same config files produces the desired output and all the expected tables and entries get created in the remote database.

I can't run Django==1.6.2 with django-pyodbc==0.2 because of #29 . So I can't really tell which upgrade cause the issues.

Any thoughts?

When trying to connect to an MS SQL Server the error

django.core.exceptions.ImproperlyConfigured: 'sql_server.pyodbc' isn't an available database backend.

Try using 'django.db.backends.XXX', where XXX is one of:   'mysql', 'oracle', 'postgresql_psycopg2', 'sqlite3'
Error was: No module named 'sql_server'

keeps popping up when running

python3 manage.py shell

and

django.core.exceptions.ImproperlyConfigured: 'sql_server.pyodbc' isn't an available database backend.
Try using 'django.db.backends.XXX', where XXX is one of:
    u'mysql', u'oracle', u'postgresql_psycopg2', u'sqlite3'
Error was: No module named sql_server.pyodbc.base

when running

python2 manage.py shell

My settings.py contains:

DATABASES = {
   'default': {
       'ENGINE': 'sql_server.pyodbc',
       'USER': 'xx',
       'PASSWORD': 'xx',
       'NAME': 'xx',
       'OPTIONS': {
               'dsn': 'sqlserverdatasource',
               'unicode_results': True,
               'driver_supports_utf8': True,
               'autocommit': True,
                  }
   }
}

This problem is both on CentOS 6.5 and Slackware 14.1

The versions I am using are:
Python 3.4.1
django 1.6.5 (via pip)
pyodbc 3.0.7 (via pip)
django-pyodbc 0.2.5 (via pip)

and

Python 2.7.5
django 1.6.5 (via pip)
pyodbc 3.0.7 (via pip)
django-pyodbc 0.2.5 (via pip)

and

FreeTDS 0.91
unixODBC 2.3.1

Somehow the sql_server module is not installed in /usr/lib/python2.7/site-packages/django/db/backends/ or /usr/lib/python3.4/site-packages/django/db/backends/

However, when using

'ENGINE': 'django_pyodbc'

a database connection is made and records can be retrieved but inserting a new record (r.save()) is not possible as the error message:

IntegrityError: ('23000', "[23000] [FreeTDS][SQL Server]Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF. (544) (SQLExecDirectW)")

will pop up. There is code in

/usr/lib/pythonX.X/site-packages/django_pyodbc/compiler.py

that should prevent this error (by using SET IDENTITY_INSERT table ON) but it seems this code is not used by django.

Any help with this issue would be greatly appreciated.

Regards,

Matthieu Peeters

Hi @michiya and @dlo

Right now I use django-mssql which works excellent, but only on windows. I would really like to run on a linux server so I am now investigating the use of ODBC. I really appreciate all the work that has been done and that you guys are still doing.

It took me quite some time to find if and where the ODBC backend was maintained.
I found:
http://code.google.com/p/django-pyodbc/ (last commit about 3 years ago)
then:
https://github.com/avidal/django-pyodbc @avidal fork.
then:
https://github.com/aurorasoftware/django-pyodbc/ @aurorasoftware version which has @avidal improvements merged when issue #3 was closed.
and:
https://github.com/michiya/django-pyodbc-azure actively maintained by @michiya and with azure support.

Maybe you guys allready knew about each others fork and have your own reasons to have different forks. If not it could be an idea to consolidate the code and all future improvements to one repo. If there is anything I can do to help this process: testing or any other task please let me know.

Again thanks for sharing your work,

Paul

In DatabaseWrapper class in init method self.settings only defined if options is not None

so if we don't have OPTIONS key in settings then we've got an error in _cursor method return statement:

return CursorWrapper(cursor, self.driver_supports_utf8, self.encoding)

because self.encofing is never defined.

Hello,

I have been trying to get django-pyodbc working to connect to a SQL Server 2000 MSDE instance. I am using Django 1.6.1, Python 3.3 on Ubuntu 12.04.

Trying to do a simple operation such as

from django.db import connections
cursor = connections['default'].cursor()
result = cursor.execute('select * from customer')

causes the following to happen

File "/.../python3.3/dist-packages/django_pyodbc/base.py", line 410, in execute
    return self.cursor.execute(sql, params)
TypeError: The first argument to execute must be a string or unicode query.

Someone else experienced the same problem except they were running on Windows. They asked a question on StackOverflow here:

http://stackoverflow.com/questions/21272895/cant-query-sql-server-from-django-using-django-pyodbc

I tracked the problem down to line 367 in base.py:

            sql = sql.encode('utf-8')

    def format_sql(self, sql, n_params=None):
        if not self.driver_supports_utf8 and isinstance(sql, text_type):
            # Older FreeTDS (and other ODBC drivers?) don't support Unicode yet, so
            # we need to encode the SQL clause itself in utf-8
            sql = sql.encode('utf-8')

It seems that in Python 3, str.encode returns a bytes which is not a string type causing the TypeError to occur.

See this comment on StackOverflow for information on how the string/bytes changed from Python 2 to 3.

http://stackoverflow.com/a/11596746/1040695

This only seems to occur for the first query getting the product version. I need to do more analysis to see if happens later on.

  File "/.../python3.3/dist-packages/django/db/backends/__init__.py", line 159, in cursor
    cursor = util.CursorWrapper(self._cursor(), self)
  File "/.../python3.3/dist-packages/django_pyodbc/base.py", line 290, in _cursor
    if self.ops.sql_server_ver < 2005:
  File "/.../python3.3/dist-packages/django_pyodbc/operations.py", line 31, in _get_sql_server_ver
    cur.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)")
  File "/.../python3.3/dist-packages/django/db/backends/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/.../python3.3/dist-packages/django_pyodbc/base.py", line 410, in execute
    return self.cursor.execute(sql, params)
TypeError: The first argument to execute must be a string or unicode query.

Anyway, my quick fix was to comment out lines 364-367 in base.py.

Alternatively, the bytes could be converted back to a string by changing line 367 to

sql = sql.encode('utf-8').decode('utf-8')

I hope this helps someone workaround this bug. I don't know enough about django-pyodbc to be able to fix this properly.

Michael.

can't launch Django's regressiontests with the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The identifier that starts with 'backends_verylongmodelnamezzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz_m2m_also_quite_long_zzzzzzzzzzzzzzzzzzzzzzzzzzzz' is too long. Maximum length is 128. (103) (SQLExecDirectW)")

Hi,

when I'm using the date_hierarchy option to filter the change_list admin page for a model, the db-backend raises a NotImplementedError.

Request Method: GET
Request URL: http://:/admin///
Django Version: 1.6
Exception Type: NotImplementedError
Exception Location: //lib/python2.7/site-packages/django/db/backends/init.py in datetime_extract_sql, line 755
Python Executable: /usr/bin/python
Python Version: 2.7.5

admin.py:
class MyModelAdmin(admin.ModelAdmin):
list_display = [ "", "", "date" ]
list_filter = [ "date" ]
date_hierarchy = "date"
ordering = [ "-date" ]

admin.site.register(MyModel, MyModelAdmin)

Environment:
Django==1.6
django-pyodbc==0.2.5
pyodbc==3.0.7

settings.py:
DATABASES = {
'default': {
'ENGINE': 'django_pyodbc',
'USER': '<django_user>',
'PASSWORD': '',
'NAME': '<django_test>',
'OPTIONS': {
'dsn': '',
'encoding': 'utf-8',
'autocommit': True,
}
}
}

When removing the line "date_hierarchy = "date"" everything works as expected.

#51 was my naive attempt to use django-pyodbc with 1.7b1, it seems to be more complicated...

This is what I've found so far:

1. A ProgrammingError: Attempt to use a closed cursor is raised on select queries.commit comment
   If I replace cursor.close() with pass on django/db/models/sql/compiler.sql:820, selecting stuff in the shell works again. But it obviously needs to be fixed at django-pyodbcs side.

2. The use of TEST_* prefixes in the DATABASES setting is deprecated, but django-pyodbc assumes the keys exist. This makes test database creation fail.

3. Schema editors. Django 1.7 ships with migrations, so the BaseDatabaseWrapper defines a method schema_editor which raises NotImplementedError.

   ...
   File "/home/jieter/.virtualenvs/pyodbc-test/local/lib/python2.7/site-packages/django/db/backends/__init__.py", line 546, in schema_editor
   raise NotImplementedError('subclasses of BaseDatabaseWrapper may require a schema_editor() method')
   NotImplementedError: subclasses of BaseDatabaseWrapper may require a schema_editor() method
   

   If I add a method returning a simple SchemaEditor like this:

   def schema_editor(self, *args, **kwargs):
       "Returns a new instance of this backend's SchemaEditor"
       return DatabaseSchemaEditor(self, *args, **kwargs)

   (together with a DatabaseSchemaEditor extending BaseDatabaseSchemaEditor), I get a TransactionManagementError when ./manage.py runserver:

   ...
   File "/home/jieter/.virtualenvs/pyodbc-test/local/lib/python2.7/site-packages/django/db/migrations/loader.py", line 148, in build_graph
     self.applied_migrations = recorder.applied_migrations()
   File "/home/jieter/.virtualenvs/pyodbc-test/local/lib/python2.7/site-packages/django/db/migrations/recorder.py", line 48, in applied_migrations
     self.ensure_schema()
   File "/home/jieter/.virtualenvs/pyodbc-test/local/lib/python2.7/site-packages/django/db/migrations/recorder.py", line 41, in ensure_schema
     with self.connection.schema_editor() as editor:
   File "/home/jieter/.virtualenvs/pyodbc-test/local/lib/python2.7/site-packages/django/db/backends/schema.py", line 74, in __enter__
     atomic(self.connection.alias, self.connection.features.can_rollback_ddl).__enter__()
   File "/home/jieter/.virtualenvs/pyodbc-test/local/lib/python2.7/site-packages/django/db/transaction.py", line 274, in __enter__
     "The outermost 'atomic' block cannot use "
   django.db.transaction.TransactionManagementError: The outermost 'atomic' block cannot use savepoint = False when autocommit is off.
   

idea's?

When I try to insert a record into table with triggers on it (SQL Server 2008), I get the following error:
The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

A similar error has already been discussed and fixed in https://github.com/avidal/django-pyodbc/issues/1. Please merge the fix to your fork.
Thanks!

iODBC (installed by default on OS X) cannot load drivers specified by name in curly braces, i.e. {FreeTDS} ( see: http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/FAQ ) . You can, however, specify the path to the driver file and so Driver={FreeTDS} becomes Driver=/usr/local/lib/libtdsodbc.so .

Note that there are no curly braces in this format and thus the bug/missing-feature I've found is that django-pyodbc always adds curly braces. The second part of this problem is that it does a string match on the driver name to see if it's FreeTDS.

The two code changes I propose (to base.py) for remedy: First, look for path separator chars in the driver string and, if they appear, use the value without curly braces. And secondly to make a flag (isFreeTDS?) that is set if the driver value equals "FreeTDS" or ends with "/libtdsodbc.so". This flag would then be used instead of driver == "FreeTDS".

Thoughts?

I can list and read all the database models in the admin but when I click save it says I was successful but doesn't actually update.

Routers.py

class RemoteRouter(object):

    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'dbo':
            return 'remote'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'dbo':
            return 'remote'
        return None

Databases

DATABASES = {
    'default': {
        'NAME': 'worldviewnet1',
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'USER': 'hidden',
        'PASSWORD': 'hidden'
    },
   'remote': {
       'ENGINE': "django_pyodbc",
       'HOST': "DIGIDEV01",
       'USER': "hidden",
       'PASSWORD': "hidden",
       'NAME': "hidden",
       'OPTIONS': {
           'host_is_server': True
       },
   }
}

Models

class TextLibrary(models.Model):
    TextxtLibraryID = models.IntegerField(
        db_column='TextLibraryID',
        primary_key=True,
    )
    Name = models.CharField(
        db_column='Name',
        max_length=50,
        blank=True
    )
    Text = models.TextField(
        db_column='Text',
        blank=True
    )
    DisplayImage = models.BinaryField(
        db_column='DisplayImage',
        blank=True,
        null=True
    )
    CreatedDateTime = models.DateTimeField(
        db_column='CreatedDateTime',
        blank=True,
        null=True
    )
    UpdatedDateTime = models.DateTimeField(
        db_column='UpdatedDateTime',
        blank=True,
        null=True
    )
    ID_Category = models.IntegerField(
        db_column='ID_Category',
        blank=True,
        null=True,
    )

    class Meta:
        managed = False
        db_table = 'TextLibrary'


    def __unicode__(self):
        return self.Name

Admin.py

admin.site.register(TextLibrary)

I use Sybase ASE 11.9 and 12.5 via FreeTDS under linux. When I use django-pyodbc to execute any model query, it raise Exception at base.py line 289.

I think this is cause of lacking suppport for Sybase ASE. Please fix it.

Hi,
This is more of a question that may not be an issue. In my model I have a DecimalField with max_digits = 20, and decimal_places = 2. However, when I enter 444444444444444444 I notice the number is being saved as 444444444444444416. When I looked at the following code (operations.py):

def value_to_db_decimal(self, value, max_digits, decimal_places):
        """
        Transform a decimal.Decimal value to an object compatible with what is
        expected by the backend driver for decimal (numeric) columns.
        """
        if value is None:
            return None
        if isinstance(value, decimal.Decimal):
            context = decimal.getcontext().copy()
            context.prec = max_digits
            return "%.*f" % (decimal_places, value.quantize(decimal.Decimal(".1") ** decimal_places, context=context))
        else:
            return "%.*f" % (decimal_places, value)

I notice that the decimal data is being converted to a floating point. Would you be able to add some clarification as to why this is needed? I am in no way saying it is wrong. I am just trying to understand it.

Thanks,
Derrick

Backend-specific aggregate fails with AttributeError like this:

  File "C:\Users\michiya\workspace\Django-1.5.1\django\db\models\query.py", line 1298, in aggregate
    kwargs[arg.default_alias] = arg
AttributeError: 'Avg' object has no attribute 'default_alias'

When I run:

pip install django-pyodbc

it installs version 0.10 and not the latest version 0.2.2. I ran in to this issue from different systems on both OSX/Linux/Win last week when the version was 0.2.1.

Fix offcourse is:

pip install django-pyodbc==0.2.2

local/lib/python2.7/site-packages/django_pyodbc/compiler.py", line 283, in as_sql
result.append('(%s)' % ', '.join([qn(c) for c in self.query.columns]))
AttributeError: 'InsertQuery' object has no attribute 'columns'

no columns or values in self.query

In the format_results function in the CursorWrapper class in the file base.py the rows are run through an encoding process where they are encoded to utf-8.

I am using the FreeTDS driver with this library so self.driver_needs_utf8 is always True.

I have a problem where the MSSQL database I am interfacing with contains non-utf-8 characters. I have have only read access to the database and no control over the it to make changes that would resolve this.

In order to continue using the database I have two fixes.

One:
change ling 349 from: fr.append(row.decode('utf-8'))
to: fr.append(row.encode('ISO-8859-1').decode('utf-8'))

Two:
Override the init method in the CursorWrapper to automatically set driver_needs_utf8 to be False.

Would it be possible to incorporate a way in the options for the database to either encode from the database into what the database is encoded in and then decode it to utf-8 or setting the driver_needs_utf8 to be false in some way.

When I install following and run ./manage.py syncdb (or test), I get an error as pasted below. I really think this is the most basic setup, if this does not work ...

Inspectdb on existng databases works. And many other stuff.

OS is Unubtu 14.04, btw. MS SQL is 2008.

pip freeze:

Django==1.6.5
argparse==1.2.1
django-pyodbc==0.2.4
pyodbc==3.0.7
wsgiref==0.1.2

(both with easy_install and from trunk for django-pyodbc I get same result)

from settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'abc',
        'USER': 'sa',
        'PASSWORD': '',
        'OPTIONS': {
                'dsn': 'MSSQL-PYTHON',
        },
    }
}

Traceback:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 399, in execute_from_command_line
    utility.execute()
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 392, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/commands/test.py", line 50, in run_from_argv
    super(Command, self).run_from_argv(argv)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/base.py", line 242, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/commands/test.py", line 71, in execute
    super(Command, self).execute(*args, **options)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/base.py", line 285, in execute
    output = self.handle(*args, **options)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/commands/test.py", line 88, in handle
    failures = test_runner.run_tests(test_labels)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/test/runner.py", line 145, in run_tests
    old_config = self.setup_databases()
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/test/runner.py", line 107, in setup_databases
    return setup_databases(self.verbosity, self.interactive, **kwargs)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/test/runner.py", line 279, in setup_databases
    verbosity, autoclobber=not interactive)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/backends/creation.py", line 339, in create_test_db
    load_initial_data=False)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 159, in call_command
    return klass.execute(*args, **defaults)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/base.py", line 285, in execute
    output = self.handle(*args, **options)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/base.py", line 415, in handle
    return self.handle_noargs(**options)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/commands/syncdb.py", line 112, in handle_noargs
    emit_post_sync_signal(created_models, verbosity, interactive, db)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/core/management/sql.py", line 216, in emit_post_sync_signal
    interactive=interactive, db=db)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/dispatch/dispatcher.py", line 185, in send
    response = receiver(signal=self, sender=sender, **named)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/contrib/auth/management/__init__.py", line 93, in create_permissions
    "content_type", "codename"
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/models/query.py", line 96, in __iter__
    self._fetch_all()
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/models/query.py", line 857, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/models/query.py", line 1071, in iterator
    for row in self.query.get_compiler(self.db).results_iter():
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 713, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 806, in execute_sql
    return list(result)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 800, in <lambda>
    result = iter((lambda: cursor.fetchmany(GET_ITERATOR_CHUNK_SIZE)),
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/utils.py", line 106, in inner
    return func(*args, **kwargs)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/utils.py", line 99, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django/db/utils.py", line 106, in inner
    return func(*args, **kwargs)
  File "/home/matija/7o/mssql2/local/lib/python2.7/site-packages/django_pyodbc-0.2.4-py2.7.egg/django_pyodbc/base.py", line 467, in fetchmany
    return [self.format_results(row) for row in self.cursor.fetchmany(chunk)]
django.db.utils.ProgrammingError: No results.  Previous SQL was not a query.

I'm using Ubuntu 12.04 server, Django 1.5.5, pyodbc-3.0.7 and I am trying to connect to a MSSQL 2005 server. I've installed django-pyodbc through pip and modified my settings.py like so:

 DATABASES = {
    'default': {
        'ENGINE': 'django_pyodbc', 
        'NAME': 'db_name',                     
        'USER': 'user_name',                    
        'PASSWORD': 'password',              
        'HOST': 'AB131\A_INS01',                  
        'PORT': '',                    
        'OPTIONS': {
           'host_is_server': True
        },
    }
}

But when I try to run syncdb I get:

 Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

The ODBC driver installed on the Windows machine is:

  SQL Server 6.01.7601.17514 SQLSRV32.DLL

What else do I need to do? Are there any other drivers that I need to install on either the Linux or Windows machine?

Hi,

When I try to use the Microsoft's Linux Client Driver(http://msdn.microsoft.com/en-us/library/hh568451.aspx) instead FreeTDS on linux, the following error occurs:

Exception Value: ('08001', '[08001] [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Neither DSN nor SERVER keyword supplied (0) (SQLDriverConnect)')

A full traceback can be viewed at:
https://gist.github.com/gcavalcante8808/8305693

The relevant part of my settings.py:

https://dpaste.de/Zn6s

The purpose of the use of this driver at this moment is for measure performance and support by that said 'Official driver' against the 'FreeTDS'.

Thank you.

The backend currently has no support for time zones. See:
https://docs.djangoproject.com/en/1.5/topics/i18n/timezones/