mysql schema设计_MySQL Schema设计（三）：利用Python操作Schema

表结构：

mysql> use sakila;

mysql> desc actor;

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| actor_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| first_name | varchar(45) | NO | | NULL | |

| last_name | varchar(45) | NO | MUL | NULL | |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

4 rows in set (0.00 sec)

数据库连接模块：

[root@DataHacker ~]# cat dbapi.py

#!/usr/bin/env ipython

#coding = utf-8

#Author: linwaterbin@gmail.com

#Time: 2014-1-29

import MySQLdb as dbapi

USER = 'root'

PASSWD = 'oracle'

HOST = '127.0.0.1'

DB = 'sakila'

conn = dbapi.connect(user=USER,passwd=PASSWD,host=HOST,db=DB)

1 打印列的元数据

[root@DataHacker ~]# cat QueryColumnMetaData.py

#!/usr/bin/env ipython

from dbapi import *

cur = conn.cursor()

statement = """select * from actor limit 1"""

cur.execute(statement)

print "output column metadata....."

print

for record in cur.description:

print record

cur.close()

conn.close()

1.)调用execute()之后，cursor应当设置其description属性

2.)是个tuple，共7列：列名、类型、显示大小、内部大小、精度、范围以及一个是否接受null值的标记

[root@DataHacker ~]# chmod +x QueryColumnMetaData.py

[root@DataHacker ~]# ./QueryColumnMetaData.py

output column metadata.....

('actor_id', 2, 1, 5, 5, 0, 0)

('first_name', 253, 8, 45, 45, 0, 0)

('last_name', 253, 7, 45, 45, 0, 0)

('last_update', 7, 19, 19, 19, 0, 0)

2 通过列名访问列值

默认情况下，获取方法从数据库作为"行"返回的值是元组

In [1]: from dbapi import *

In [2]: cur = conn.cursor()

In [3]: v_sql = "select actor_id,last_name from actor limit 2"

In [4]: cur.execute(v_sql)

Out[4]: 2L

In [5]: results = cur.fetchone()

In [6]: print results[0]

58

In [7]: print results[1]

AKROYD

我们能够借助cursorclass属性来作为字典返回

In [2]: import MySQLdb.cursors

In [3]: import MySQLdb

In [4]: conn = MySQLdb.connect(user='root',passwd='oracle',host='127.0.0.1',db='sakila',cursorclass=MySQLdb.cursors.DictCursor)

In [5]: cur = conn.cursor()

In [6]: v_sql = "select actor_id,last_name from actor limit 2"

In [7]: cur.execute(v_sql)

Out[7]: 2L

In [8]: results = cur.fetchone()

In [9]: print results['actor_id']

58

In [10]: print results['last_name']

AKROYD