Re: Connecting to a MS SQL server from django
Larry,
I did it a long complicated way, but we recently had 2 new people
join our team, and refined the process for them, and it seemed to
go smoothly, so here's what they did.
Some sections differ for Mac vs Linux, and some even for different
Linux distros, but it should all work. I'm including all of it, even
the part about how to install Python, Django, virtualenv, etc., which
you probably already have, and MySQL, which you are not using so
you can skip.
The following document is in Markdown format.
HHL is the name of our project. Change it to your own project name.
Let me know how it works!
# Setting up your development environment.
## Install Python 2.7, if it's not already on your system.
### Mac
The Mac comes preinstalled with a 2.7 version of Python. Double-check the
version as shown:
$ /usr/bin/python --version
Python 2.7.5
If you don't get a 2.7 version of Python, or if you get a
"No such file or directory" error, install your own version of Python via
the [Homebrew][] package manager:
$ brew install python
### Linux
If your Linux system doesn't already have a version of Python, install
one with the package manager.
For Ubuntu and Debian:
$ sudo apt-get install python
On CentOS (and, presumably, Fedora and RedHat), see
<http://toomuchdata.com/2012/06/25/how-to-install-python-2-7-3-on-centos-6-2/>.
## Install `virtualenv`
Mac:
The version of Python that comes pre-installed on the Mac comes with
`easy_install`, so you can use that to install `virtualenv`:
$ sudo /usr/bin/easy_install virtualenv
Linux:
You can either use your system's package manager (e.g.,
`sudo apt-get install python-virtualenv` on Ubuntu and Debian), or you can
install `virtualenv` manually, as described at
<https://pypi.python.org/pypi/virtualenv>.
## Create and activate a virtual environment
Where you put the environment is up to you; the following is just an
example:
$ virtualenv ~/pythons/hhl
Confirm that you did, indeed, create a Python 2.7 virtual environment:
$ ~/pythons/hhl/bin/python
Python 2.7.5 (default, Nov 19 2013, 10:30:44)
[GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.2.79)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>>
Activate that environment:
$ source ~/pythons/hhl/bin/activate
## Install MySQL
Mac:
Again, it's easiest to use [Homebrew][]:
$ brew install mysql
After installing MySQL, homebrew will display suggestions on how to get MySQL
set up. Follow those instructions.
Linux:
You'll need both MySQL and the MySQL development files. Without the latter,
you won't be able to build the MySQL Python driver.
For Ubuntu and Debian:
$ sudo apt-get install mysql-server libmysqlclient-dev
On CentOS (and, presumably, Fedora and RedHat):
$ sudo yum -y install mysql-devel
## Install the Python MySQL driver
$ pip install MySQL-python
## 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.
[FreeTDS]
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 =
Next, add this section to your `freetds.conf` file (see the section,
above, on installing FreeTDS, for the location on your operation system):
:::ini
[_your_odbc_dsn_name_]
host = _your_db_host_name_or_ip_
port = _your_db_port_number_
tds version = 7.0
## Create the requirements.txt file, containing:
Django==1.4.2
MySQL-python==1.2.4
pyodbc==3.0.7
-e git+https://github.com/avidal/django-pyodbc.git@a329ba8e7ea9dfcffce18139863c977b1218c09b#egg=sql_server.pyodbc-dev
## Install all the packages
$ pip install -r requirements.txt
### 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.
Create the pyodbc1.patch file containing:
*** setup.py.orig 2013-12-19 21:49:10.000000000 -0500
--- setup.py 2013-12-19 21:35:21.000000000 -0500
***************
*** 145,151 ****
elif sys.platform == 'darwin':
! # OS/X now ships with iODBC.
! settings['libraries'].append('iodbc')
# Apple has decided they won't maintain the iODBC system in OS/X and has added deprecation warnings in 10.8.
# For now target 10.7 to eliminate the warnings.
--- 145,151 ----
elif sys.platform == 'darwin':
! # OS/X now ships with iODBC. We need unixodbc, however.
! settings['libraries'].append('odbc')
# Apple has decided they won't maintain the iODBC system in OS/X and has added deprecation warnings in 10.8.
# For now target 10.7 to eliminate the warnings.
Do these steps:
$ . ~/pythons/hhl/bin/activate
$ cd /tmp
$ git clone https://code.google.com/p/pyodbc
$ cd pyodbc
$ patch < 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
## Set up your Django environment, by adding this to settings.py:
DATABASES = {
'cf': {
'ENGINE' : 'django.db.backends.sql_server.pyodbc',
'NAME' : '_your_db_name_',
'USER' : '_your_db_username_',
'PASSWORD' : '_your_db_password_',
# ODBC DSN defined in /etc/freetds.conf
'HOST' : '_your_odbc_dsn_name_',
# Ignored for Windows; Required for Linux
'OPTIONS' : {
# ODBC driver name in /etc/odbcinst.ini
'driver': 'FreeTDS',
},
},
}
## 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=FreeTDS;'
'SERVER=_your_db_host_name_or_ip_;'
'PORT=_your_db_port_number_;'
'DATABASE=_your_db_name_;'
'UID=_your_db_username_;'
'PWD=_your_db_password_')
>>> conn
<pyodbc.Connection object at 0x7f6df62d5f80>
## Initialize your database
$ python manage.py syncdb
## Fire it up!
$ python manage.py runserver
[Homebrew]: http://brew.sh/
Good luck!
--Fred
Fred Stluka -- mailto:fred@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.
Fred Stluka -- mailto:fred@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 1/19/14 6:31 PM, Larry Martell wrote:
Fred- This project has been put on the back burner for a few weeks, but I will be getting back to it. If you could dig up your notes on this, that would be really great. Thanks very much! -larry On Sun, Jan 19, 2014 at 7:01 AM, Fred Stluka <fred@bristle.com> wrote:Larry, We tried django-mssql and gave up. Much better luck with django-pyodbc. We use the avidal version of it, along with pyodbc, FreeTDS, and unixODBC. Works perfectly. If you like, I can dig up my notes on exactly how we made the connection from Django to MS SQL Server. --Fred ________________________________ Fred Stluka -- mailto:fred@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 1/14/14 6:15 PM, Larry Martell wrote: I am trying to connect to a MS SQL server from django on Linux using Vernon Cole's django-mssql package (https://bitbucket.org/vernondcole/django-mssql-ado-merge/src). When I first tried to connect it failed with: Exception Type: DatabaseError at /report/CDSEM/EventsTable/ Exception Value: Cannot create connection to=PYRO:ado.connection@xx.xx.xx.xx:9099 I traced it through /usr/lib/python2.6/site-packages/adodbapi/remote.py and I see it tries to access os.environ['PROXY_PORT'], which is not defined and then it defaults to 9099 - Is that the correct port or should I set os.environ['PROXY_PORT']? How can I tell what port MS SQL db is listening on? I googled this and it said the default MS SQL port is 1433. I set os.environ['PROXY_PORT'] to that and now I don't get that error but it times out: /usr/lib/python2.6/site-packages/Pyro4-4.22-py2.6.egg/Pyro4/core.py(160)__call__() -> return self.__send(self.__name, args, kwargs) (Pdb) n TimeoutError: TimeoutE...imeout',) I tried opening port 1433 with iptables, but still no joy. I can connect to the MS SQL db locally from the host it's running so I know it's up and my credentials are correct, and I can ping the Windows box from the Linux host I am running on, so I know there is connectivity to it from there. Anyone here ever get this working? Anyone have any ideas as to what my problem is or how I can debug this further? Thanks! -larry
posted by twitter @ 9:26 PM
0 Comments
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home