Monday, February 28, 2011

Installing MySQL ODBC Drivers on Fedora Core 14 (32 bit) Python

Background:
We are working with a virtual machine (VMWare Workstation) running Fedora Core 14 as a Guest Operating System (Windows XP SP Whatever is host).
VMWare tools has already been installed to make my life less tedious, but you should keep in mind that there are possible requirements that are covered by that installation process that wont show up here.

MySQL:

First lets install an instance of mysql and get it running:
( Always assume that I'm allowing yum to install all dependencies).
> sudo yum install mysql mysql-server

Now that we have
> sudo /etc/init.d/mysqld start

Now if you were paying attention you should have caught the following snippet coming out to terminal:

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password

So we need to do that next:
> sudo mysqladmin -u root password 'testing'

Now log into mysql:
> mysql -u root -p

.

Lets create a test datbase with a test table and some testing data:
mysql> CREATE DATABASE testing;
mysql> USE testing;
mysql> CREATE TABLE IF NOT EXISTS testing(c1 int, c2 text);
mysql> INSERT INTO testing VALUES(1, 'monkey soup');
mysql> \q

PyODBC:
Next we need to install the pyodbc libraries for python. check out the pyodbc site: http://code.google.com/p/pyodbc/
Looking through the documentation we can see that we will need to pre-install the gcc compiler and the unixODBC-devel  package, lets get that done.
> sudo yum install gcc unixODBC-devel

RightO! We have that out of the way, so now lets get the source (pyodbc-2.1.8.zip in my case) and try to build/install pyodbc:


> unzip pyodbc-[version].zip
> cd pyodbc-[version]
> sudo python setup.py build


Crap:
gcc: error trying to exec 'cc1plus': execvp: No such file or directory
error: command 'gcc' failed with exit status 1

No sweat, googling the error, we need gcc for c++, simple enough:
> sudo yum install gcc-c++

Try this ish again!
> sudo python setup.py build

DAMNIT!:

fatal error: Python.h: No such file or directory

Easy enough, install python-devel (just from experience, just do it):
> sudo yum install python-devel

One more time baby!:
> sudo python setup.py build

Nice, one weird thing down in a surely painful journey... Now install
> sudo python setup.py install
Nice!

Python, ODBC, PyODBC:
Alright, now we need to create a test script so that we can make sure everything is working as expected.
> touch test.py
> chmod +x test.py
> vim test.py

test.py is going to look like this:

#!/usr/bin/env python
import pyodbc
from pprint import pprint
cnxn = pyodbc.connect("DRIVER={MySQL};SERVER=127.0.0.1;DATABASE=testing;UID=root;PWD=testing")
cursor = pyodbc.cursor()
cursor.execute("SELECT * FROM testing")
rows = cursor.fetchall()
for r in rows:
        pprint(r)
cursor.execute("INSERT INTO testing VALUES(2,'cheddar cheese')")



Alright, lets go!
> ./test.py

Of course it would be too much to ask for that to work, lets see what we have:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc5.so' : file not found (0) (SQLDriverConnectW)")

This is gonna take a second ... ... ... (30 minutes-ish later) ...
K, so we need to install the mysql-connector-odbc package
> sudo yum install mysql-connector-odbc

So lets run test.py again ...
> ./test.py

There we go everything works for MySQL, this is good we have verified that in some sense ODBC is up and running on our system and we can connect to a MySQL database.

No comments:

Post a Comment