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:f...@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.
------------------------------------------------------------------------
On 12/10/14 11:58 AM, pythonista wrote:
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.
On Wednesday, December 10, 2014 4:22:44 AM UTC-5, Sugita Shinsuke wrote:
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)')
----
--
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+unsubscr...@googlegroups.com
<mailto:django-users+unsubscr...@googlegroups.com>.
To post to this group, send email to django-users@googlegroups.com
<mailto:django-users@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
<https://groups.google.com/d/msgid/django-users/75829654-59e0-4ba5-a824-8a61123387c7%40googlegroups.com?utm_medium=email&utm_source=footer>.
For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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/5489DC49.1060305%40bristle.com.
For more options, visit https://groups.google.com/d/optout.
