WebFaction
Community site: login faq

I'm developing a Django application, and I have a command that needs to pull in data from a Microsoft SQL Server 2008 database. So in other words, my Django data does not need to be stored in a SQL Server database, but I do need to access one.

Do you have any recommendations on how to accomplish this? From what I've read, SQL Server doesn't play very nice with Linux. I'm guessing a solution might involve pyodbc or pymssql, but since my knowledge in this area is somewhat limited, I'm hoping someone can provide some steps of what I need to install/configure.

Thanks in advance for the help!

asked 14 Mar '11, 12:25

vkdev
5246
accept rate: 0%


Before you can use pydobc, you first need to install FreeTDS and unixODBC in your home directory and configure a driver.

First, install FreeTDS, unixODBC, and pyodbc with the following commands:

PYTHON=python2.7    # set this to whichever python version you want to install for.

mkdir -p $HOME/bin $HOME/lib $HOME/src $HOME/tmp

export TMPDIR=$HOME/tmp
export PATH="$HOME/bin:$PATH"
export C_INCLUDE_PATH="$HOME/include:$C_INCLUDE_PATH"
export LIBRARY_PATH="$HOME/lib:$LIBRARY_PATH"
export LD_LIBRARY_PATH="$HOME/lib:$LD_LIBRARY_PATH"

###########################################################
# FreeTDS 0.9.1
# original: http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.91.tar.gz
###########################################################
cd $HOME/src
wget 'http://mirror.ryansanden.com/pyodbc-7555f7da/freetds-0.91.tar.gz'
tar -xzf freetds-0.91.tar.gz
cd freetds-0.91
./configure --prefix=$HOME
make          # 25 sec
make install

###########################################################
# unixODBC 2.3.1
# original: ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.1.tar.gz
###########################################################
cd $HOME/src
wget 'http://mirror.ryansanden.com/pyodbc-7555f7da/unixODBC-2.3.1.tar.gz'
tar -xzf unixODBC-2.3.1.tar.gz
cd unixODBC-2.3.1
./configure --prefix=$HOME
make          # 30 sec
make install

###########################################################
# pyodbc @ 7555f7da7d11b1dbe98895a8ce88df20d20a1006
# original: https://code.google.com/p/pyodbc/
###########################################################
cd $HOME/src
wget 'http://mirror.ryansanden.com/pyodbc-7555f7da/pyodbc-7555f7da.tar.gz'
tar -xzf pyodbc-7555f7da.tar.gz
cd pyodbc-7555f7da
CFLAGS="-I$HOME/include $CLFAGS" LDFLAGS="-L$HOME/lib $LDFLAGS" $PYTHON setup.py install

Next, edit ~/etc/odbcinst.ini so that it looks like this (but change xxxxx to your username):

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /home/xxxxx/lib/libtdsodbc.so
Setup = /home/xxxxx/lib/libtdsS.so
CPTimeout =
CPReuse =

Once that's done, you should be able to connect to your DB using 'FreeTDS' as the driver name, eg:

pyodbc.connect('DRIVER={FreeTDS};SERVER=xxx.xxx.xxx.xxx;DATABASE=xxx;UID=xxx;PWD=xxx')

Hope that helps!

permanent link
This answer is marked "community wiki".

answered 15 Mar '11, 19:29

seanf
12.2k41836
accept rate: 37%

edited 16 Mar '12, 03:47

ryans ♦♦
5.0k93360

Wow, this is why I recommend webfaction to everyone I know. Thank you for the thoughtful and thorough response! After a little tweaking with the connection string -- turned out I needed to include the port number for the particular server I'm connecting too -- it's working great!

(15 Mar '11, 21:16) vkdev

I followed the steps and I receive the following error. error: Setup script exited with error: command 'gcc' failed with exit status 1

(22 Aug '11, 08:02) bjinwright

It seems that he util folder for pyodbc is missing from the zip archive for the latest pyodbc version on Google code. As a workaround, you can get it from github: wget https://nodeload.github.com/mkleehammer/pyodbc/zipball/2.1.9 and then install it using "python setup.py build install" from the "mkleehammer-pyodbc-e3c95dc" that would be created after unzipping it.

(22 Aug '11, 10:13) todork

Yes, thanks so much! Very detailed explanation!

(10 Jan '12, 18:19) Mike Williamson

The previous install instructions were using trunk software versions, so later updates broke the install. I now re-did the install procedure with the latest versions locked and mirrored.

(16 Mar '12, 03:48) ryans ♦♦

I guess you should install some MS Sql odbc driver like FreeTDS: http://code.google.com/p/pyodbc/wiki/ConnectionStrings#Unix,_Linux

permanent link

answered 14 Mar '11, 15:53

dariosky
112
accept rate: 0%

Yeah, that would make sense. Have you done this before? Any chance someone could point me in the right direction for how to install and configure that driver? The documentation I've found online hasn't been that thorough.

(14 Mar '11, 20:16) vkdev

You should be able to connect using pyodbc. You can find out more about it here:

http://code.google.com/p/pyodbc/

You can install it on our server by entering:

mkdir ~/lib/python2.6
easy_install-2.6 pyodbc
permanent link

answered 14 Mar '11, 12:40

aaronh ♦♦
1.3k3
accept rate: 34%

edited 14 Mar '11, 12:43

Okay, so that installed fine. However, once I try to use pyodbc and connect to my database, I receive the following error:

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

My connection line looks like:

pyodbc.connect('DRIVER={SQL Server};SERVER=xxx.xxx.xxx.xxx;DATABASE=xxx;UID=xxx;PWD=xxx')

Do you know where to go from here? I appreciate the help.

(14 Mar '11, 13:57) vkdev

Everything goes well until

CFLAGS="-I$HOME/include $CLFAGS" LDFLAGS="-L$HOME/lib $LDFLAGS" $PYTHON setup.py install

then I get

fatal: Not a git repository (or any of the parent directories): .git WARNING: git describe failed with: 32768 WARNING: Unable to determine version. Using 3.0.0.0 running install Checking .pth file support in /home/squareup/lib/python2.7/ error: can't create or remove files in install directory

The following error occurred while trying to add or remove files in the installation directory:

[Errno 2] No such file or directory: '/home/squareup/lib/python2.7/test-easy-install-32728.pth'

The installation directory you specified (via --install-dir, --prefix, or the distutils default setting) was:

/home/squareup/lib/python2.7/

This directory does not currently exist. Please create it and try again, or choose a different installation directory (using the -d or --install-dir option).

Any advice will be welcome.

permanent link

answered 06 Apr '12, 19:55

squareup
1626
accept rate: 50%

Hello everyone!

I'm having the same problem too, and I tried to install all those programs as suggested. The programs are now installed, but the thing is that there are some of them already on the server's /bin directory. How do I make my environment to call my $HOME/bin/ programs instead of the server's /bin directory programs?

Thanks!

permanent link

answered 17 Jun '13, 20:04

M Valverde
1
accept rate: 0%

Set your PATH environment variable to put your own bin directory first, eg:

export PATH=$HOME/bin:$PATH
(17 Jun '13, 20:07) seanf

I did that before, but I think because I wrote: export PATH=$PATH:$HOME/bin My home´s bin programs didn't get precedence over system's bin programs. Thanks! It's working fine now

(17 Jun '13, 21:08) M Valverde
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×909
×337
×14
×3
×2

question asked: 14 Mar '11, 12:25

question was seen: 14,665 times

last updated: 17 Jun '13, 21:08

WEBFACTION
REACH US
SUPPORT
AFFILIATE PROGRAM
LEGAL
© COPYRIGHT 2003-2019 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM