本文介绍如何用Python操作MySQL数据库，包括安装python-mysqldb，连接数据库。(单纯用SQL语言，处理变量相当麻烦)

1. Database connector

1.1 安装database connector

正如Java用JDBC连接数据库一样，Python用MySQLdb连接数据库。用如下命令安装python-mysqldb：

sudo apt-get install python-mysqldb

1.2 连接MySQL

#!/usr/bin/env python
import MySQLdb
import codecs  # for charset utf-8

# Step 1: connect to MySQL
db = MySQLdb.connect(host="localhost",
        user="root",
        passwd="Jelline", # password
        db="gtfs")  # name of the data base

# Step 2: create a Cursor object to execute sql queries
cur = db.cursor()

# Step 3: execute queries
cur.execute("SELECT * FROM stops ORDER BY stop_id")

# Step 4: process or print the results
with open(filename, 'w') as fp : # fp is a file object
# with codecs.open(filename, 'w', encoding='utf-8') as fp : # fp is a file object
    for row in cur.fetchall() : # cur.fetchall() retunrs a tuple of tuples 
        s = '\t'.join(str(item) for item in row)
        fp.write(s + '\n')

fp.close()

# Step 5: clean up
cur.close()
db.close()

2. 基本用法

快速浏览了Python MySQL Documentation，做个简单笔记，如下：

## execute queries 
songs = ('Purple Haze', 'All Along the Watch Tower', 'Foxy Lady')
for song in songs:
    cur.execute("INSERT INTO song (title) VALUES (%s)", song)   # Substitution Query

cur.execute("SELECT * FROM song WHERE id = %s or id = %s", (1,2)) # use a tuple to enclose all multiple parameters to substitue

## print results
rows = cur.fetchall()  #fetch all rows, return tuple
cur.fetchone() #fetch one row

## Cursor attributes
print cur.description
print cur.rowcount  # the number of rows

## Exceptions & Errors
try:
    cur.execute("SELECT * FROM song")
    rows = cur.fetchall()
except MySQLdb.Error, e:
    try:
        print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
    except IndexError:
        print "MySQL Error: %s" % str(e)

关于Curson的属性和方法,可参见PEP 0249 — Python Database API Specification v2.0: Cursor attributes, Cursor methods。

3. ORM

面向对象是从软件工程基本原则（如耦合、聚合、封装）的基础上发展起来的，而关系数据库则是从数学理论发展而来的，两套理论存在显著的区别。为了解决这个不匹配的现象，ORM技术应运而生。ORM(Object Relational Mapping，对象关系映射），是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换^[1]。

从StackOverflow了解到两款ORM：SQLAlchemy和peewee，前者功能强大，后者轻量级。

SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

peewee

Peewee is a simple and small ORM for the Python programming language. It provides a lightweight, expressive Python API for interacting with relational databases

参考资料：
[1]维基百科词条：对象关系映射
[2]StackOverflow: How do I connect to a MySQL Database in Python?
[3]Python MySQL Documentation
[4]Wikipiedia: SQLAlchemy, Peewee ORM