I can't syncdb via django_pyodbc of Django.

[Sugita Shinsuke]

Hi there.

Could you tell me a good way to use Django with Micro Soft DB SQL server. I tried django_pyodbc. Any suggestion is appreciated.

my PC of Environment is below.

Database:
SQL Server 2014 Express

OS:
Windows 7 Home Premium SP1

I used the django module, django_pyodbc.
And, I tried to run syncdb but I couldn't.

My settings.py is below.

DATABASES = {
   'default': {
       'ENGINE': "django_pyodbc",
       'HOST': "localhost",
       'USER': "djangouser",
       'PASSWORD': "xxxxx",
       'NAME': "db_test",
       'OPTIONS': {
            'host_is_server': True,
            'autocommit': True,
            'unicode_results': True,
            'extra_params': 'tds_version=8.0'
        },
   }
}

Things I tried and did not work:
1,change "localhost" to "127.0.0.1,1433" in the HOST property of the code.
2,removed 'OPTIONS'.


However, I tried the module pyodbc of pure python like below. It was fine.

---
conn = pyodbc.connect('driver={SQL Server};server=localhost;UID=djangouser;PWD=xxxxx;DATABASE=shannon_test;Trusted_Connection=yes')
---

But, DB connection is not established without "Trusted_Connection=yes".
At least I do not know any other way. Anyone who know this matter, please help.


The error message is here.
----
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][
SQL Server]\x82\xb1\x82\xcc\x83\x8d\x83O\x83C\x83\x93\x82\xc5\x97v\x8b\x81\x82\x
b3\x82\xea\x82\xbd\x83f\x81[\x83^\x83x\x81[\x83X "shannon_test" \x82\xf0\x8aJ\x8
2\xaf\x82\xdc\x82\xb9\x82\xf1\x81B\x83\x8d\x83O\x83C\x83\x93\x82\xc9\x8e\xb8\x94
s\x82\xb5\x82\xdc\x82\xb5\x82\xbd\x81B (4060) (SQLDriverConnect); [01S00] [Micro
soft][ODBC SQL Server Driver]\x90\xda\x91\xb1\x95\xb6\x8e\x9a\x97\xf1\x82\xcc\x9
1\xae\x90\xab\x82\xaa\x90\xb3\x82\xb5\x82\xad\x82\xa0\x82\xe8\x82\xdc\x82\xb9\x8
2\xf1\x81B (0); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]\x82\xb1\
x82\xcc\x83\x8d\x83O\x83C\x83\x93\x82\xc5\x97v\x8b\x81\x82\xb3\x82\xea\x82\xbd\x
83f\x81[\x83^\x83x\x81[\x83X "shannon_test" \x82\xf0\x8aJ\x82\xaf\x82\xdc\x82\xb
9\x82\xf1\x81B\x83\x8d\x83O\x83C\x83\x93\x82\xc9\x8e\xb8\x94s\x82\xb5\x82\xdc\x8
2\xb5\x82\xbd\x81B (4060); [01S00] [Microsoft][ODBC SQL Server Driver]\x90\xda\x
91\xb1\x95\xb6\x8e\x9a\x97\xf1\x82\xcc\x91\xae\x90\xab\x82\xaa\x90\xb3\x82\xb5\x
82\xad\x82\xa0\x82\xe8\x82\xdc\x82\xb9\x82\xf1\x81B (0)')
----

[pythonista]

I can actually help you here.

I had the same problem and tried 4 different modules.

The only module that works with sql server is 

https://github.com/michiya/django-pyodbc-azure

I am running it both on windows and linux, simultaneously.

The settings components are displayed on the web site.

Works like a charm

One dependency is pyodbc to be installed.

[Sugita Shinsuke]

Hello pythonista,

Thank you for replying.
I tried django-pyodbc-azure But syncdb didn't run yet.

As the website says, since I use Django 1.6.4,
I installed django-pyodbc-azure as "pip install "django-pyodbc-azure<1.2""

I checked the odbc driver by Control Panel > Administrative Tools > Data Sources (ODBC) > "Driver" tab
My settings.py is as below.

DATABASES = {
   'default': {
       'ENGINE': 'sql_server.pyodbc',
       'HOST': 'localhost', # I also tried 127.0.0.1
       'PORT': '1433', # I also tried empty
       'USER': 'djangouser',
       'PASSWORD': 'xxxxx',
       'NAME': 'db_test',
       'OPTIONS': { # I also removed the OPTION
           'driver': 'SQL Server Native Client 11.0', # I also change 'SQL Server Native Client 10.0'
       },
   }
}

I tried to run syncdb command again, and then I got some errors again.
here are error messages.

django.db.utils.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server
 Driver][SQL Server]

 [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]

[01S00] [Microsoft][ODBC SQL Server Driver]

2014年12月11日木曜日 1時58分58秒 UTC+9 pythonista:

[Sugita Shinsuke]

I use SQL Server Japanese edition.

So, I also wrote Japanese error message here.

django.db.utils.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]このログインで要求されたデータベース "db_test" 
を開けません。ログインに失敗しました。 (4060) (SQLDriverConnect);
[01S00] [Microsoft][ODBC SQL Server Driver]接続文字列の属性が正しくありません。 (0);
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
このログインで要求されたデータベース "db_test" を開けません。ログインに失敗しました。 (4060); [01S00] [Microsoft][ODBC SQL Server Driver]接続文字列の属性が正しくありません。 (0)')

2014年12月11日木曜日 1時58分58秒 UTC+9 pythonista:

[Fred Stluka]

Sugita,

Django does work with MS SQL Server, but not as well as with
other DBs.  I have to use it because I'm interacting with on old
legacy DB, but I'm gradually migrating everything to MySQL.

To connect to MS SQL Server, from Mac, Linux and Windows,
I use the following settings:

#######################################################
DATABASES = {
    'cf': {
        'ENGINE'    : 'django.db.backends.sql_server.pyodbc',
        'NAME'      : 'my_db_name',
        'USER'      : 'my_username',
        'PASSWORD'  : 'my_password',
        # ODBC DSN defined in /etc/freetds.conf or in /usr/local/etc/freetds.conf
        'HOST'      : 'my_dns_name',
        # Ignored for Windows; Required for Linux
        'OPTIONS'   : {
            # ODBC driver name in /etc/odbcinst.ini or in /usr/local/etc
            'driver': 'SQL Server',
            # NOTE: dsn option is added dynamically later, for Windows,
        },
    },
}
# The ODBC DSN name specified above as DATABASES.cf.HOST is ignored on
# Windows, where it must be specified as DATABASES.cf.OPTIONS.dsn instead.
# However, we haven't found a way to make DATABASES.cf.OPTIONS.dsn work in
# Linux (and probably the same for Mac).  It causes the error:
#    Data source name not found, and no default driver specified
# Therefore we add it here, but only for Windows.
#
# On 64-bit Windows, with our current 32-bit version of pyodbc, the DSN
# must be created via:
#    C:\Windows\SysWOW64\odbcad32.exe
# instead of the regular "ODBC Data Sources" app in Control Panel, which
# invokes:
#    C:\Windows\system32\odbcad32.exe
#
if os.name == 'nt':      # Windows
    DATABASES['cf']['OPTIONS']['dsn'] = 'my_dns_name'
#######################################################


To make this work, I had to install and configure the unixodbc
and freetds libraries, as well as pyodbc, and django-pyodbc.
Here are excerpt from my notes, about the long and torturous
path we took:


#######################################################

## Install non-Python ODBC libraries and tools

### Install unixODBC

Mac:

    $ brew install unixodbc

The `odbcinst.ini` file ends up in `/usr/local/etc/`.

Linux:

    $ sudo apt-get install unixodbc-dev  # Ubuntu and Debian
    $ sudo yum -y install unixODBC-devel # CentOS, RedHat, and Fedora

The `odbcinst.ini` file ends up in `/etc/`.

### Install FreeTDS

Mac:

    $ brew install freetds --with-unixodbc

Note that the `freetds.conf` file ends up in `/usr/local/etc/freetds.conf`.
Installing this Homebrew package also provides the `tsql` command, which
can be useful for testing.

Linux:

This package is typically available via the Linux package manager.

For Ubuntu and Debian:

    $ sudo apt-get install freetds-dev tdsodbc

The `freetds.conf` file ends up in `/etc/freetds/`. If you need the
`tsql` program (useful for testing), you also need the `freetds-bin` package:

    $ sudo apt-get install freetds-bin

On CentOS (and, presumably, Fedora and RedHat):

    $ sudo yum -y install freetds-devel

The `freetds.conf` file ends up in `/etc/`.

### Configure unixODBC and FreeTDS

This blog post provides a good overview of the process:
<http://lbolla.info/blog/2013/08/28/python-and-odbc>

(NOTE: Those instructions talk about setting up a `$HOME/.odbcinst.ini`.
That file does not appear to be necessary.)

Find your `odbcinst.ini` file (see the section, above, on installing
`unixODBC`), and make its contents look like this.

    [SQL Server]
    Description             = FreeTDS MSSQL
    # If you're on Linux, uncomment the following settings.
    #Driver                  = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    #Driver64                = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    #Setup                   = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    #Setup64                 = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    # Mac settings
    Driver                  = /usr/local/lib/libtdsodbc.so
    Driver64                = /usr/local/lib/libtdsodbc.so
    UsageCount              = 2
    CPTimeout               =
    CPTimeToLive            =
    DisableGetFunctions     =
    DontDLCLose             =
    ExFetchMapping          =
    Threading               =
    FakeUnicode             =
    IconvEncoding           =
    Trace                   =
    TraceFile               =
    TraceLibrary            =

NOTE: The blog post uses a section name of "FreeTDS". However, for the HHL
Django app, the section name _must_ be "SQL Server", _not_ "FreeTDS". The
section name defines the name of the driver, and the Django app uses the
string "SQL Server" for the driver name.

Next, add this section to your `freetds.conf` file (see the section,
above, on installing FreeTDS, for the location on your operation system):

    :::ini
    [my_dns_name]
        host = my_db_server_name
        port = my_db_server_port_number
        tds version = 7.0

### Confirm the FreeTDS configuration

    tsql -S my_dns_name -D my_db_name -U my_username
    Password:
    locale is "en_US.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    Default database being set to my_db_name
    1>

## Install pyodbc for Mac

2014-07-16 - Fred and Jim found new information for Max OS X

from:  http://stackoverflow.com/questions/20074620/installing-pyodbc-fails-on-osx-10-9-mavericks

OSX Mavericks dropped sql headers that are required for pyodbc compilation.

Following these steps allowed me to install pyodbc:

1. Install the iODBC library  (http://www.iodbc.org/)
    1. Download tar file from website, libiodbc-3.52.9.tar
    2. Extract iODBC sources from the tar file.  We extracted into directory
       /Users/jim/Downloads/libiodbc-3.52.9
2. Run pip install --allow-external pyodbc --allow-unverified pyodbc --no-install pyodbc
3. cd [VIRTUAL_ENV]/build/pyodbc
4. Run  python setup.py build_ext --include-dirs=/Users/jim/Downloads/libiodbc-3.52.9/include/
5. Run pip install --no-download pyodbc

#### Mac OS X note

The `pyodbc` package that _pip_ installs uses iODBC by default. We want it
to use `unixODBC`, instead, for a couple reasons.

* First, there are numerous sites on the Internet that make claims such as
  "iODBC is a slightly less desirable ODBC manager than unixODBC"
  (http://www.cerebralmastication.com/2013/01/installing-debugging-odbc-on-mac-os-x/).
  More people _seem_ to have better luck with `unixODBC`.
* Second, we're using `unixODBC` on Linux, and the configuration is already
  complicated enough. So, why not just use the same approach on both platforms?

To get `pyodbc` to use `unixODBC` on the mac, you have to build it from source,
and you have to hack the `setup.py`. Here's how to do it.

    $ . ~/pythons/hhl/bin/activate
    $ cd /tmp
    $ git clone https://code.google.com/p/pyodbc
    $ cd pyodbc
    $ patch </path/to/your/checked/out/hhl/patches/pyodbc1.patch
    $ pip uninstall pyodbc
    $ python setup.py install

## Install the SQL Server ODBC backend

`pip install` does not completely install the SQL Server ODBC backend, so
there's one more manual step. You must manually copy some software within
your Python virtual environment.

    $ cd ~/pythons/hhl/lib/python2.7/site-packages
    $ cp -r ../../../src/sql-server.pyodbc/sql_server django/db/backends

## Verify that Python can get to the SQL Server database

At this point, you should be able to open a connection to the HHL test
SQL Server database as follows:

    $ python
    Python 2.7.5+ (default, Sep 19 2013, 13:48:49)
    [GCC 4.8.1] on linux2
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import pyodbc
    >>> conn = pyodbc.connect('DRIVER=SQL Server;'
                              'SERVER=my_db_server_name'
                              'PORT=my_db_server_port_number;'
                              'DATABASE=my_db_name;'
                              'UID=my_username;'
                              'PWD=my_password')
    >>> conn
    <pyodbc.Connection object at 0x7f6df62d5f80>


#######################################################

Hope this helps!

--Fred

---

Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

---

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/75829654-59e0-4ba5-a824-8a61123387c7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sugita Shinsuke]

Hello Fred Stluka

Thank you for detailed document.

I resolved syncdb in Windows OS.

My SQL Server setting was something wrong.

I will try Linux edition also.

2014年12月12日金曜日 3時03分35秒 UTC+9 Fred Stluka:

--Fred

---

Fred Stluka -- mailt...@bristle.com -- http://bristle.com/~fred/