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.
