MySQL

Apr 20, 2012:

I repeated the same setup steps on a Lion box but they did not work for me. I was using Python v2.7.2 at the time. I have then upgraded to v2.7.3 so I am not sure if the Python upgrade is part of the fix.

I installed setuptools in v2.7.2 but I decided to remove it completely and use 'distribute' instead. The instructions are here. In particular, I ran the following commands:

$ curl -O http://python-distribute.org/distribute_setup.py

$ python distribute_setup.py

Then I installed pip. The instruction is here. In particular, I ran this line command:

sudo $ curl https://raw.github.com/pypa/pip/master/contrib/get-pip.py | python

Lastly I typed:

pip install mysql-python

And yes, I did get the "Library not loaded" problem so I set up the DYLD_LIBRARY_PATH and resolved it as in step 12 below.

Oct 28, 2011:

I am using 10.6.8 on an Intel Mac and I can use Python 32/64-bit x86 Python, along with x86, 64-bit version of MySQL database install in step 1 below without issue.

pre-Oct 28, 2011:

I did the following steps on an Intel Mac OS X Tiger box. The steps worked for me:

For Mac OS X 10.6, I need to use Python 2.7 32-bit version (no 64-bit universal binary) to make this work properly.

• 1. Download MySQL Community Server here.

  2. Download MySQL Connector here and follow the installer to install the driver.

     1. Note A1: I just tried the steps on Mac OS X 10.6.6 with MySQL Community Server for 10.6 (x86, 32-bit) version 5.5.10 and I don't seem to need this step 2.

  3. Download MySQL for Python here (version 1.2.3 at the time of writing).

  4. Double-click MySQL-python-1.2.3.tar.gz in Finder -> MySQL-python-1.2.3

  5. Install setuptools here.

  6. Make sure mysql/bin is in your search path. You can enter this command do that: "export PATH=$PATH:/usr/local/mysql/bin"

  7. cd into the MySQL-python-1.2.3 folder

1. sudo python setup.py clean (you may need it if the 'build' folder is there)

2. sudo python setup.py build

3. sudo python setup.py install

4. Launch Python shell

5. Enter the import command to verify the result: "import MySQLdb"

   1. Note A2: I ran into "Library not loaded: libmysqlclient.18.dylib". I ended up fixing it with the following export in my .bash_profile file: "export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:${DYLD_LIBRARY_PATH}"

To run the script, you need a local copy of the MySQL server running.

import MySQLdb as mysql

db = mysql.connect( host="ip_or_local_host_no_port",

user="the_user",

passwd="the_password",

db="test")

# db.autocommit = True

# cursor = db.cursor()

cursor = db.cursor(mysql.cursors.DictCursor)

cursor.execute("select * from my_table_name")

rows = cursor.fetchall()

for oneRow in rows:

print oneRow

theId = oneRow['id'] if oneRow['id'] != None else 0

theValue = oneRow['value'] if oneRow['value'] != None else ''

# to write to db, may need to take care of the quote if the stmt use single quote for stmt

preInsertStmt = """INSERT INTO value_table (value, create_ts) VALUES ('%s', NOW());"""

theInsertStmt = preInsertStmt % (theValue.replace(r"'", r"\'")

# replace(r"'", r"\'")

cursor.close()

db.commit()

db.close()

InnoDB is newer; MyISAM