Python and MySQL

How to work with MySQL in Python? Read below from the discussion here.  

Connecting to MYSQL with Python in 3 steps

1 – Setting

You must install a MySQL driver before doing anything. Unlike PHP, only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it’s hard to install it using easy_install.

For Windows user, you can get a exe of MySQLdb.

For Linux, this is a casual package (python-mysqldb).

For Mac, you can install MySQLdb using Macport.

2 – Usage

After installing, reboot. This is not mandatory, but will prevent me from answering 3 or 4 others questions in this post if something goes wrong. So please reboot.

Then it is just like using another package :

import MySQLdb

db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="john", # your username
                      passwd="megajonhy", # your password
                      db="jonhydb") # name of the data base

# you must create a Cursor object. It will let
#  you execute all the query you need
cur = db.cursor() 

# Use all the SQL you like

# print all the first cell of all the rows
for row in cur.fetchall() :
    print row[0]

Of course, there are thousand of possibilities and options, this is a very basic example. You will have to look at the documentation. A good starting point.

3 – More advanced usage

Once you know how it works, you may want to use an ORM to avoid writting SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community isSQLAlchemy.

I strongly advice you to use it: your life is going to be much easier.

I recently discovered another jewel in the Python world: peewee. It’s a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, where using big tools like SQLAlchemy or Django is overkill :

import peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john',passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

book = Book(author="me", title='Peewee is cool')
for book in Book.filter(author="me"):
    print book.title

Peewee is cool

This example works out of the box. Nothing other than having peewee (pip install peewee :-)) is required. No complicated setup. It’s really cool.


Perl package installation and manement


Package notes

CPAN is a great place to avoid reinvent the wheel, but some packages are not that reliable or not easy to use or install.

  • DBD::mysql. Follow this instruction. If you get something like “Library not loaded: libmysqlclient.16.dylib“, see here.
  • Interface to GNU Scientific Library Math::GSL. If you get lost at what the parameters are, read more in GSL manual, e.g. hypergeometric distribution.
  • Getopt::Long and Pod::Usage make a friendly command with options and help/man doc.
  • LSF (unauthorized!) – A perl API built on top of the LSF command line tools. I write my own version.
  • local::lib – create and use a local lib/ for perl modules with PERL5LIB (PS: best way to install package when you are not administrator.)
  • Dist::Zilla
  • FIle::Basename – Parse file paths into directory, filename and suffix.
  • Template Toolkit (TT) – website, book, and how to migarte from CGI
  • HTML::Table – set additional attribute by $table->setAttr("id=\"cluster_row\"");
  • Tree manipulation: Bio::TreeIO, Bio::Tree:Tree, Bio::Tree:TreeI, Bio::Tree::TreeFunctionsI, Bio::Tree::Node, Bio::Tree::NodeI
  • Perldoc server/mini CPAN: Pod::Webserver, Pod::POM::Web, Pod::Browser(Catalyst based), or CPAN::Mini::Webserver


This is my portal page of Node.js.


  • The only problem for me is how to install it as users without root privilege. I found two articles are useful (here and here).


  • Node FAQ (understanding node.js)
  • project70: my favorite node tutorial





  • MySQL driver (discussion. Best-choice node-mysqlexample. Alternative, Sequenlize (doc and Github))
  • Derby.js: MVC designed to be used by Express.js as middleware (example)
  • Express.js: a web app framework uses middleware concept to enhance functionality of applications.
  • Connect.js: an extensible HTTP server framework for node, providing high performance “plugins” known as middleware. Connect is bundled with over 20 commonly used middleware, including a logger, session support, cookie parser and more.
  • nodemon: automatic re-running (or manually restart by type in rs)
  • mongodb: doc, driver API, BSON API, SQL to MongoDB
  • Grunt and plugins

Blogs & Articles


Tips & Tricks: Simple and basic

More Tips & Tricks


  • MySQL workbench (manual)
  • MySQL monitor (command line)