安装 Python for MySQL 的连接扩展

sudo apt-get install python-mysqldb

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 (http://www.python.org/dev/peps/pep-0249/), and is built on top of the MySQL C API. Can discuss using MySQL and Python in forum (http://forums.mysql.com/list.php?50).

一个Python访问mysql的 sample script
1. 建立一个 database 和 一个table:
mysql ->
create database my_db;
use my_db;
create table test(
id int,
name varchar(50)
);
insert into test values(1,”hello”);
insert into test values(2,”world”);

2.Python代码 sample.py

import MySQLdb
class Eb_db:
def __init__(self):
try:
connection = MySQLdb.connect(host=”localhost”, user=”root”, passwd=”123456″, db=”my_db” )
cursor = connection.cursor()
cursor.execute( “SELECT * FROM test ” )
except MySQLdb.OperationalError, message:
errorMessage = “Error %d:\n%s” % (message[ 0 ], message[ 1 ] […]

Another script:

#!/usr/bin/env python
# -*-coding:UTF-8-*-#这一句告诉python用UTF-8编码
"""
***** This is a MySQL test *****

select:
conn=Connection()
conn.select_db('test')
cur=conn.cursor()
cur.execute('select * from user')
cur.scroll(0)
row1=cur.fetchone()
row1[0]
row1[1]
row1[2]

insert:
cur.execute('insert into user (name,passwd) values(\'benyur\',\'12345\')')
cur.insert_id()

update:
cur.execute('update user set passwd=\'123456\' where name=\'benyur\'')

delete:
cur.execute('delete from user where id=2')

**********************************
"""

from MySQLdb import *

def conn():
conn=Connection()
conn.select_db('test')
cur=conn.cursor()
cur.execute('select * from user')
cur.scroll(0)
row1=cur.fetchone()
row1[0]
row1[1]
row1[2]

def usage():
print __doc__

if __name__=='__main__':
usage()

3. 使用
import MySQLdb
3.1. 连接
conn = MySQLdb.Connection(host, user, password, dbname)
3.2. 选择数据库
conn.select_db(’database name’)
3.3. 获得cursor
cur = conn.cursor()
3.4. cursor位置设定
cur.scroll(int, mode)
mode可为相对位置或者绝对位置，分别为relative和absolute。
3.5. select
cur.execute(‘select clause’)
例如
cur.execute(‘select * from mytable’)
row = cur.fetchall()
或者：
row1 = cur.fetchone()
3.6. insert
cur.execute(‘inset clause’)
例如
cur.execute(‘insert into table (row1, row2) values (\’111\’, \’222\’)’)
conn.commit()
3.7. update
cur.execute(‘update clause’)
例如
cur.execute(“update table set row1 = ‘’ where row2 = ‘row2 ‘ ”)
conn.commit()
3.8. delete
cur.execute(‘delete clause’)
例如
cur.execute(“delete from table where row1 = ‘row1’ ”)
conn.commit()