Setup Python to work with MS SQL Server in CentOS 6

I spent quite sometime and today finally I was able to use Python scripts to connect to database and run queries using pandas. Here are the steps:

BTW, these instructions are for CentOS 6.4, Python-2.7.6 with pip already installed.

1. Install the required packages.

yum install gcc gcc-c++ python-devel freetds unixODBC unixODBC-devel

Then, you can go ahead and install pyodbc with:

pip install pyodbc

2. Configure FreeTDS

Edit the file /etc/freetds.conf or ~/.freetds.conf if you do not have root privilege.

For each database you will be working with, add the section:

[DB_SERVER]

host = URL

port = 1433

tds version = 7.0

Then, copy the configure file to your home folder:

cp /etc/freetds.conf ~/.freetds.conf

To see if everything is working as of now, try:
tsql -S DB_SERVER -U username -P password

If you can successfully log in and run some simple queries, you know FreeTDS is working properly.

3. Configure unixODBC to work with FreeTDS

Add the following section to /etc/odbcinst.ini:

[FreeTDS]

Description     = MS SQL database access with Free TDS

Driver          = /usr/local/lib/libtdsodbc.so

Setup           = /usr/lib64/libtdsS.so
CPTimeout = 

CPReuse = 

FileUsage = 1

The path might be different for different machines.

Then, for each database you want to work with, add the following section to /etc/odbc.ini or ~/.odbc.ini if you do not have root privilege:

[DB_SOURCE]

Driver = FreeTDS

Description = ODBC connection via FreeTDS

Trace = No

Servername = DB_SERVER

Database = DB_NAME

To see if the configuration is working, try:
isql -v DB_SOURCE  username password

If you can successfully log in and run some simple queries, you know unixODBC is working properly.

Note: to make sure ODBC look for your local config file, do this:
export ODBCINI=/HOMEDIR/.odbc.ini

4. Try to connect with Python

Finally, in Python, your script should look like this:

import pyodbc

dsn = 'DB_SOURCE'
user = 'username'
password = 'password'
database = 'DB_NAME'

con_string = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
cnxn = pyodbc.connect(con_string)

Now you can embed your SQL queries in Python XD

Setting up Python-2.7 on CentOS 6

I just got a Linux desktop with CentOS 6 installed, and I need to set up the environment as Python-2.7 + Pandas + Numpy + Scipy with Emacs 24.3

It turns out that it's very tricky to use yum to install ANYTHING... (at least compared to Ubuntu)

First of all, install Python-2.7 side by side with the original 2.6 since otherwise you will mess up your OS. Then, install pip and configure it to Python-2.7. The step by step instruction is here:
https://github.com/0xdata/h2o/wiki/Installing-python-2.7-on-centos-6.3.-Follow-this-sequence-exactly-for-centos-machine-only

http://toomuchdata.com/2012/06/25/how-to-install-python-2-7-3-on-centos-6-2/

It's important to run:

yum groupinstall "Development tools"
yum install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel

Before compiling and installing Python.

Then, by typing:

pip install PACKAGE_NAME

You will be able to install the package for Python-2.7.

For Scipy, the important package to install before it are:
yum install blas blas-devel lapack lapack-devel atlas atlas-devel

For Matpoltlib, the important package to install before it are:
yum install freetype-devel libpng-devel

For iPython to have [Tab] nationalities:
pip install readline

To install Emacs 24:

http://vitalvastness.wordpress.com/2013/07/03/installing-emacs-24-on-centos-6/comment-page-1/

Install liblockfile from here (that’s the x86_64 link) … if you click on the download link it will invoke the package manager and install directly from Firefox.

cd /etc/yum.repos.d

wget http://pj.freefaculty.org/EL/pjku.repo

rpm –import http://pj.freefaculty.org/EL/PaulJohnson-BinaryPackageSigningKey

yum install emacs-24.2-4.el6.x86_64

Several Tips for Publishing Images from Matlab: PDF to Word, Font Size in Ubuntu etc.

Matlab provides some great convenience in creating high quality figures. However, esp. when you try to integrate these figures into your Word version of the paper, quite a few tweaks are needed.

1. Create, crop and insert high quality PDF images into word.

Matlab can create PDF or EPS figure quite easily by the print command, and you can even use LaTeX to combine multiple figures into one when you have to (subplot in Matlab create a very large margin between figures). However, one problem is to crop off the margins around the PDF. "pdfcrop" is a command line program in Ubuntu, but apparently it can not remove the margin on the bottom of the PDF. I found a nice Java program:

http://www.pdfscissors.com/

This can do the job quite nicely.

Then, before inserting PDF into word, keep in mind that there is no way to include such vector image into M$ Word documents. We have to convert it into high-quality TIFF image. There are quite a few options for this, just make sure to create TIFF image with 600 dpi. Just to be safe.

2. Change font size of Matlab figure in Ubuntu and save it into EPS.

This is a weird problem I had for a long time. Every time I tried to set the font size of Matlab figure in Ubuntu, it does not work. The same code works fine in Windows 7. The problem was that a font package is missing for Ubuntu. Just type:


sudo apt-get install xfonts-75dpi

Log out and log in, then it all works fine. Also, before save the figure as EPS, make sure to include:

set(gcf, 'PaperPositionMode', 'auto');