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');

Redirecting Console Outputs in Visual Studio

Instead of writing the file stream objects into your code, a simpler solution is to redirect the output just like the pipeline in Linux.

Basically, you just have to go to project properties -> Debugging -> Command Arguments, and add:
"> Output.txt".

Here is a nice post for it:
http://asawicki.info/news_1496_redirecting_output_to_file_in_visual_c.html

Make LaTeX floats behave better

LaTeX floating objects can easily give me headaches. Today I saw this link:

http://mintaka.sdsu.edu/GF/bibliog/latex/floats.html

It provides a set of really neat commands to put in the beginning of your document, and all of a sudden the floats behave so nicely now :-)

% Alter some LaTeX defaults for better treatment of figures:
    % See p.105 of "TeX Unbound" for suggested values.
    % See pp. 199-200 of Lamport's "LaTeX" book for details.
    %   General parameters, for ALL pages:
    \renewcommand{\topfraction}{0.9} % max fraction of floats at top
    \renewcommand{\bottomfraction}{0.8} % max fraction of floats at bottom
    %   Parameters for TEXT pages (not float pages):
    \setcounter{topnumber}{2}
    \setcounter{bottomnumber}{2}
    \setcounter{totalnumber}{4}     % 2 may work better
    \setcounter{dbltopnumber}{2}    % for 2-column pages
    \renewcommand{\dbltopfraction}{0.9} % fit big float above 2-col. text
    \renewcommand{\textfraction}{0.07} % allow minimal text w. figs
    %   Parameters for FLOAT pages (not text pages):
    \renewcommand{\floatpagefraction}{0.7} % require fuller float pages
 % N.B.: floatpagefraction MUST be less than topfraction !!
    \renewcommand{\dblfloatpagefraction}{0.7} % require fuller float pages

 % remember to use [htp] or [htpb] for placement

LaTeX centering a table that is larger than text width

Had this problem today with my thesis proposal, and luckily the solution is not hard to find:
http://mathandprogramming.blogspot.com/2011/07/latex-centering-table-larger-than.html

You basically need to use a package called chngpage and add an environment surrounding the table:

% allows for temporary adjustment of side margins
\usepackage{chngpage}

\begin{table}
    \begin{adjustwidth}{-.5in}{-.5in}  
        \begin{center}
        \begin{tabular}{|c|}
           % really wide table here
        \end{tabular} 
        \caption{This way the caption will also be the same width as the table, wider than the other text. }
        \label{myTable}
        \end{center}
    \end{adjustwidth}
\end{table}

Find and replace a word in all the text document

So some day I need to replace a word to another one for all the XML file in a folder. I was like OK I'll write a C++ code. There goes another hour of my working time then.

Then I received this piece of code from a colleague:
find . -name "*.xml" -exec perl -pi.org -e 's/DICOMIO/CartoXP/' {} \;

Works like a charm.

Also, a linux command line to check how many cores do you have:
cat /proc/cpuinfo | grep processor | wc -l

I just need to share this one....

So today I was browsing 9GAG, saw a post like: telnet this and enjoy, starwars fans :-)

So I tried:

telnet towel.blinkenlights.nl

Most epic ASCII stuff ever made.

How to use nice colors in Matlab Plot

One major problem I always had in Matlab plotting is that other than the "default" 6-7 colors, it's hard to use different colors.

I just found a really nice solution:
http://scslin.blogspot.com/2011/09/matlab-plot-with-more-colors.html

Basically, you first download a small function "rgb" from:
http://www.mathworks.com/matlabcentral/fileexchange/24497-rgb-triple-of-color-name-version-2

Then, you can call:
plot(x,y,'color',rgb('olive'));


You will be able to draw a line using a "olive" color.

The available colors are as follows:

http://www.mathworks.com/matlabcentral/fx_files/24497/2/rgbss2.PNG

Some useful functions for subplot in Matlab

So there are two things that the subplot function in Matlab can not do.

1. Generate an "overall" title on the top of all sub-figures.
2. Make the sub-figures as compact as possible (usually very large margins will be made).

Normally, what we can do is save each sub-figure as eps or pdf, and use LaTeX to organize them. This works well and I highly recommend this way when you are preparing publication ready figures, but there are times that you just want to generate some report quickly and nicely.

So I found this two nice functions in Matlab file exchange:
1. mtit. This one will place a title automatically for you on top of everything.
2. subaxis. This one does roughly the same thing as "subplot", but with options that enables you to remove margins.

Upload images to our BBS abroad

Seems that our BBS can not allow we to upload image from other countries...
OK. This script can bypass the IP checking and do the uploading. It is developed by Zimu Liu.

#!/bin/sh

if [ -z "$1" ]; then
 echo "Usage:  ./`basename \"$0\"` "
 exit -1
fi

FULLPATH="$1"
if [ ! -r "$FULLPATH" ]; then
 echo "File does not exist, or you do not have read privilge!"
 exit -1
fi

FILESIZE=$(cat "$FULLPATH" | wc -c)
if [ $FILESIZE -ge 300000 ]; then
 echo "The size of file exceeds 300000 bytes!"
 exit -1
fi

FILENAME="`basename "$1"`"
EXT=$(echo $FILENAME | grep -i .gif$ || echo $FILENAME | grep -i .jpg$)
if [ -z "$EXT" ]; then
 FILENAME="$FILENAME.jpg"
fi

URL=$(curl -F "up=@${FULLPATH};filename=c:\\${FILENAME}" \
 -F MAX_FILE_SIZE=300000 -F board=C_Computer -F level=0 -F live=9999 -F "exp=" \
 bbs.njupt.edu.cn/cgi-bin/bbsdoupload \
 | tr -d "\015" | tr -d "\012" \
     | sed "s/^$//g")

echo "\nCopy the following URL to your brower:\n"
echo "http://bbs.njupt.edu.cn/cgi-bin/$URL\n"

Solving the LaTeX error: "Too many unprocessed floats"

Yesterday I generated a LaTeX document using Matlab script. This document have about 50 tables, with even table embedded with 30 images. So LaTeX thought that it I can't handle this much floating objects.

I searched around on the internet and found the best solution for me is to add
\clearpage
after every table. This command will force LaTeX to dump all the cached floating objects till here. So basically it will print one table per page, which is fine in my case.

Kindle菜单乱码问题简单解决方案

Kindle看中文电子书最大的头痛就是菜单有如完形填空，我最喜欢的作家钱锺书居然能变成三个框框，各种纠结。当时上网找方案基本都是教唆我越狱，实在嫌麻烦。今天在网上找到了一个超级方便的，特此留底。

源链接：

http://www.douban.com/group/topic/17140908/

解决办法很简单，就是改变默认的语言设置，输入以下命令即可：

;debugOn

~changeLocale zh-CN

;debugOff

输入时要注意大小写，具体输入步骤是：在home页面，先按下回车（也就是方向键旁边那个弯弯箭头键），会出现一个输入框，输入第一条命令后，回车，再输入第二条，再回车，再第3条回车。这样就设置成功了，然后重启kindle 3。

重启步骤：home -> Menu -> Settings -> Menu -> Restart

也就是进入设置那里再按MENU选择restart重启，或者直接滑动那个关机键按住15秒左右就重启了。

重启之后就感觉世界终于清净了，不仅Home里面所有书名都完整显示了，而且之前一直乱码的告别天堂也终于没问题了。

Paper writing notes learned from Dr Thompson

Today I received another thorough editing from Dr Paul Thompson. I found some pattern that I think I should keep in mind for later.

1. In .bib files, when you need some character to be in Cap in the generated PDF, e.g. "fMRI" somewhere in the title, do the following in the .bib file: "f{MRI}".

2. The use of tenses! Make them consistent!

3. Dr Thompson seems to have an issue with "utilize", he changed all of them to "use"... I thought "utilize" sounds cooler :-(

4. "More details can be found..." is always changed into "More details may be found..."

5. Things like "achieves the best performance..." is always changed into "perform the best...". I think he don't like to convert verbs into nouns...

6. Do not say "our method can benefit../will be able to...". Say it with proud: "our method benefits.../will..."

7. He generally likes the sentence to be concise. E.g., "if utilizing all these additional information will be beneficial for classification..." is changed to: "if this additional information can help classification..."

8. "The results are summarized in...." is always changed into "Results are summarized in...", not sure why though...

9. He does not like "As we can observe from the Table..."; these are changed to "As we see in Table...". But Sherlock always tells us to observe T__T

Multivariate T-test

For a long time I do not even know that this type of test even existed...

I always thought that in order to extend t-test to multivariate, we have to try to generalize 2 sample t-test, change the x_i - x_j to the 2-norm etc. It turns out that there is a more conventional way to do it...

It is called, Hotelling's T squared test.

http://itl.nist.gov/div898/handbook/pmc/section5/pmc543.htm

And someone have a nice Matlab package for it:

http://www.mathworks.com/matlabcentral/fileexchange/2844

Install Language Pack for WIndows 7 Pro

Recently I have to install a Windows 7 for my friend's laptop, and the display language should be Chinese. I download the Windows 7 Pro from ELMS for ASU, and installed it smoothly. Windows finally learns something from Linux, so now all the drivers are downloaded and installed automatically via the internet during installation.

Then, changing the language from Eng to something else requires a little hacking. I downloaded the language pack DVD from ELMS also, and copy the lp.cab under (DVD)\zh-cn\ to E:\lp.cab

Then, I did the following:
-run command prompt as administrator
-type dism /online /add-package /packagepath:E:lp.cab
-in regedit del key \HKLM\SYSTEM\CurrentControlSet\Control\MUI UILanguage\eu-us

Then, restart and everything works.

Common Data Sets for Machine Learning

Multi-Label:
http://mlkd.csd.auth.gr/multilabel.html

Comprehensive:
http://www.csie.ntu.edu.tw/~cjlin/libsvmtools/datasets/

Tips in Mex Fortran, linux version

Starting from the day before yesterday, I need to write a Matlab interface for a Fortran source code, which is almost a black box for me, so that I can use "mex" to compile it and call it in Matlab.

The first thing is the compiler. In Windows, you can only choose a specific compiler, say, C/C++ or Fortran. So if you work with both C and Fortran code, you will have to use "mex -setup" from time to time, which is kinda annoying for a lazy person like me. But in Linux, you can work on a configuration file so that Matlab will automatically select the compiler for you.

First, in "/opt/R2009a/unix/bin/mexopts.sh", you can see the following segment:

#
FC='g95'
FFLAGS='-fexceptions'
FFLAGS="$FFLAGS -fPIC -fno-omit-frame-pointer"
FLIBS="$RPATH $MLIBS -lm"
FOPTIMFLAGS='-O'
FDEBUGFLAGS='-g'
#

The first line indicates the compiler for Fortran. "g95" is not provided, so, we will change it to gfortran which I think comes with Ubuntu. Thus, change this segement into:

#
FC='gfortran'
FFLAGS='-fexceptions'
FFLAGS="$FFLAGS -fPIC -fno-omit-frame-pointer"
FLIBS="$RPATH $MLIBS -lm"
FOPTIMFLAGS='-O'
FDEBUGFLAGS='-g'
#

Then, in Matlab, if you type
mex -setup

You will see something like:
The options files available for mex are:

1: /opt/R2009a/unix/bin/gccopts.sh :
Template Options file for building gcc MEX-files

2: /opt/R2009a/unix/bin/mexopts.sh :
Template Options file for building MEX-files via the system ANSI compiler

Choose 2. Then it's ok to compile.

The next thing is the interface. The help in Matlab has everything you know to create a wrapper, even some examples for you to follow. So although I know nothing about Fortran, I managed to write a "almost" complete interface which passed the compilation.

However, there're several important tips that costs me a whole day to figure out.

1. The name of the source file must be ".F", otherwise it won't compile.
2. If the source code you want to wrap was not developed by you, be very careful about all the data types in that code. For example, if one input for that function is "REAL", then it is 4-byte floating number. And in Matlab, unless specified, everything will be "double", which is 8-byte floating number. If you pass the double value directly into it, the value will NOT be preserved. So when you call the function in Matlab, use something like "nlam = int32(nlam);
   
   beta = single(beta);" Also, inside the interface, use something like "mxCopyPtrToReal4(beta_pr,beta,size)".
3. The next thing is managing the output. When you create the output matrices, do not use mxCreateDoubleMatrix. Use "mxCreateNumericMatrix", which will let you to specify the output types like int32 or single.
   

How-To: What should you do if you deleted files in NTFS by mistake

OK. Here is how it happens. One day, I was using the filezilla, pulling down a tiny little file to my hard disk of the desktop machine in my office. It is the major machine I use for research, and all my LaTeX papers, matlab codes are there.

Then, I select the whole "Research" disk, and downloaded that file into it. Then, assuming I am still selecting the tiny file on the remote server, I hit "delete" button. The computer seems to be hanging there with no response, which happens quite a lot when I use filezilla, by the way. So I force quit (thank god).

Then, after maybe a few minutes, I found that all my codes are gone somehow. WTF?!

It was all my fault, can't blame anyone.

So what I did was I delete almost everything in a NTFS disk, when I was using Ubuntu Linux. Bad news. There is no trash can, and everything is somehow "gone forever".

OK, enough for my miserable story.

The first thing I try is under Windows, a commercial software called "Recover my files". It will recover a lot of files, only without the file names. But file names and original locations are SO important because there are thousands of them and most of them are codes, which basically called by its name.

Then, I tried free wares in Ubuntu, such as ntfsundelete and photorec. They all can recover the files, but not the name and location. Well, at least I can recover them, it just takes time. My final choice is photorec.

There is one oerticular software that can recover your file, name and location, though. It is called: "Autopsy". Also a linux free ware, but it works like a webpage, and you can only recover the file one by one. But I did use it to recover a lot of vital source code.

One tip is, after you hurt your disk, you can use "ddrescue" to mirror the whole disk to another externel hard disk (should be much bigger than the one you want to backup). Then, the file you create works just like a disk, and yu can recover based on that without further damage.

The only thing you can do to prevent this from happening is to back up your system regularly. The reason that I can survive this with only one week of frustraion is that I uploaded the most vital part of my work to a remote server, for computation purpose. Because of that, I only need to recover a very small ratio, using the combanation of those softwares.

But starting from now, I will start to use file sync softwares to back up my works to another disk on a regular basis. My recommendation is Grsync. You can setup some commonly profiles, and just click excecute it will sync by only dealing with the differences. Very fast.

You can also write a script to automaticly sync the folders, say, every night. I do not have the time right now. Here are some useful links:

https://help.ubuntu.com/community/BackupYourSystem#grsync
http://www.unixgeeks.org/security/newbie/unix/cron-1.html
http://linuxbasement.com/content/backups-using-rsync-bash-cron
http://linuxgazette.net/104/odonovan.html

我父母的母校竟然会作出这样的事情

我基本不开QQ的，今天突然想找人问一件事情，就点开了，不出意外各个群都蹦了出来。在握大学班级的群里，我看到了这样的一段话：

ICQ 04:12:07:
京理工大学也是很久没有消息了，我就是被南京理工大学隐藏起来的秘密，我的同学都很少有知道我的情况的。我本想把自己的经历写下来，但是，由于校方当初的 承诺，我一直在苦苦等待。熬了一年了，我也自费了一年，母亲累的连临时工都打不了了，我最近一直在发烧，生怕哪天没机会再写动了。我现在已经烧了2个月 了，其中高烧39度以上就熬了10多天，平常最少也是38度左右，这便使我完全与南京失去了联系。

看看南京理工大学是如何对待自己的学生的吧。在校的研究生，自己的学生生了病都不理不管。希望大家看看，帮我四处院校bbs上传传，谢谢大家。

ICQ 04:13:08:
南京理工大学是如何对待自己的学生的，在校研究生因患白血病而不与报销医疗费用！至使我曾因没钱治疗而复发两次！

给我留下唯一的就是我同学和大学母校关爱，以及南京理工大学把责任退给穷苦的大学生，而不率而自己的义务。

我叫赵磊，男，23岁，曾经是南京邮电大学的学生。2008年在南京理工大学读研期间检查不幸患上了急性淋巴白血病。因为顾虑到经费问题（北京治疗相对江 苏省会便宜一些，而且，要是在南京治疗还要租房，吃饭，等等。再三考虑下，回京治疗会便宜一些），在北京的母亲在我检查出来的当天就要求我的导师把我送回 北京治疗，校方当时也保证相关的医疗问题会在南京那边帮忙询问解决。
但是，南京理工大学除了鼓动学生捐款之外基本上没有任何举动，只是一味的安抚我和我的母亲。在北京因为我的档案问题而不能报销

他 是我大学的室友，就睡在我的对床。知道他的消息之后我一直以来的校内状态都是为他祈福。因为距离的缘故，我除了寄钱也做不了太多。可是看着这个可能只是部 分的留言，我实在不能平静。南京理工是我父母的母校，我一直将它当作我的第二故乡来崇敬。可是学生在学校生了重病，校方却没有任何实质上的帮助。鼓动学生 捐款又能捐多少呢？公费医疗难道是墙上画的饼么？学生身体好的时候就从学生身上榨取各种杂费，学生真正需要帮助的时候呢？怎么能不让人心寒？