MySQL Schema設計（三）利用Python操作Schema

弓在箭要射出之前，低聲對箭說道，“你的自由是我的”。Schema如箭，弓似Python，選擇Python，是Schema最大的自由。而自由應是一個能使自己變得更好的機會。
㈠ MySQLdb部分
表結構：

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-29import MySQLdb as dbapiUSER = '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 ipythonfrom dbapi import *cur = conn.cursor()statement = """select * from actor limit 1"""cur.execute(statement)print "output column metadata....."printfor record in cur.description:    print recordcur.close()conn.close()

1.）調用execute()之後，cursor應當設定其description屬性

2.）是個tuple，共7列：列名、類型、顯示大小、內部大小、精度、範圍以及一個是否接受null值的標記

[root@DataHacker ~]# chmod +x QueryColumnMetaData.py[root@DataHacker ~]# ./QueryColumnMetaData.pyoutput 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]: 2LIn [5]: results = cur.fetchone()In [6]: print results[0]58In [7]: print results[1]AKROYD

我們能夠藉助cursorclass屬性來作為字典返回

In [2]: import MySQLdb.cursorsIn [3]: import MySQLdbIn [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]: 2LIn [8]: results = cur.fetchone()In [9]: print results['actor_id']58In [10]: print results['last_name']AKROYD

㈡ SQLAlchemy--SQL鍊金術師
雖然SQL有國際標準，但遺憾的是，各個資料庫廠商對這些標準的解讀都不一樣，並且都在標準的基礎上實現了各自的私人文法。為了隱藏不同SQL“方言”之間到區別，人們開發了諸如SQLAlchemy之類的工具
SQLAlchemy串連模組：

SQLAlchemy串連模組：[root@DataHacker Desktop]# cat sa.pyimport sqlalchemy as saengine = sa.create_engine('mysql://root:oracle@127.0.0.1/testdb',pool_recycle=3600)metadata = sa.MetaData()

example 1：表定義

In [3]: t = Table('t',metadata,     ...:                Column('id',Integer),     ...:                Column('name',VARCHAR(20)),     ...:                mysql_engine='InnoDB',     ...:                mysql_charset='utf8'     ...:              )In [4]: t.create(bind=engine)

example 2：表刪除

有2種方式，其一：In [5]: t.drop(bind=engine,checkfirst=True) 另一種是：In [5]: metadata.drop_all(bind=engine,checkfirst=True)，其中可以藉助tables屬性指定要刪除的對象

example 3： 5種約束

3 .1 primary key下面2種方式都可以，一個是列級，一個是表級In [7]: t_pk_col = Table('t_pk_col',metadata,Column('id',Integer,primary_key=True),Column('name',VARCHAR(20)))In [8]: t_pk_col.create(bind=engine)In [9]: t_pk_tb = Table('t_pk_01',metadata,Column('id',Integer),Column('name',VARCHAR(20)),PrimaryKeyConstraint('id','name',name='prikey'))In [10]: t_pk_tb.create(bind=engine)3.2 Foreign KeyIn [13]: t_fk = Table('t_fk',metadata,Column('id',Integer,ForeignKey('t_pk.id')))In [14]: t_fk.create(bind=engine)In [15]: t_fk_tb = Table('t_fk_tb',metadata,Column('col1',Integer),Column('col2',VARCHAR(10)),ForeignKeyConstraint(['col1','col2'],['t_pk.id','t_pk.name']))In [16]: t_fk_tb.create(bind=engine)3.3 uniqueIn [17]: t_uni = Table('t_uni',metadata,Column('id',Integer,unique=True))In [18]: t_uni.create(bind=engine)In [19]: t_uni_tb = Table('t_uni_tb',metadata,Column('col1',Integer),Column('col2',VARCHAR(10)),UniqueConstraint('col1','col2'))In [20]: t_uni_tb.create(bind=engine)3.4 check     雖然能成功，但MySQL目前尚未支援check約束。這裡就不舉例了。3.5 not nullIn [21]: t_null = Table('t_null',metadata,Column('id',Integer,nullable=False))In [22]: t_null.create(bind=engine)

4 預設值

分2類：悲觀(值由DB Server提供)和樂觀(值由SQLAlshemy提供)，其中樂觀又可分：insert和update

4.1 例子：insertIn [23]: t_def_inser = Table('t_def_inser',metadata,Column('id',Integer),Column('name',VARCHAR(10),server_default='cc'))In [24]: t_def_inser.create(bind=engine)3.2 例子：updateIn [25]: t_def_upda = Table('t_def_upda',metadata,Column('id',Integer),Column('name',VARCHAR(10),server_onupdate='DataHacker'))In [26]: t_def_upda.create(bind=engine)3.3 例子：Passive In [27]: t_def_pass = Table('t_def_pass',metadata,Column('id',Integer),Column('name',VARCHAR(10),DefaultClause('cc')))In [28]: t_def_pass.create(bind=engine)

㈢ 隱藏Schema
資料的安全是否暴露在完全可信任的對象面前，這是任何有安全意識的DBA都不會去冒的風險。比較好的方式是儘可能隱藏Schema結構並驗證使用者輸入的資料完整性，這在一定程度上雖然增加了營運成本，但安全無小事。
這裡藉助開發一個命令列工具來闡述該問題需求：隱藏表結構，實現動態查詢，並將結果類比mysql \G輸出

版本：[root@DataHacker ~]# ./sesc.py --version1.0查看協助：[root@DataHacker ~]# ./sesc.py -hUsage: sesc.py [options] <arg1> <arg2> [<arg3>...]Options:  --version             show program's version number and exit  -h, --help            show this help message and exit  -q TERM               assign where predicate  -c COL, --column=COL  assign query column  -t TABLE              assign query table  -f, --format          -f must match up -o  -o OUTFILE            assign output file我們要的效果：[root@DataHacker ~]# ./sesc.py -t actor -c last_name -q s% -f -o output.txt[root@DataHacker ~]# cat output.txt************ 1 row *******************actor_id: 180first_name: JEFFlast_name: SILVERSTONElast_update: 2006-02-15 04:34:33************ 2 row *******************actor_id: 195first_name: JAYNElast_name: SILVERSTONElast_update: 2006-02-15 04:34:33......<此處省略大部分輸出>......

請看代碼

#!/usr/bin/env pythonimport optparsefrom dbapi import *#構造OptionParser執行個體，配置期望的選項parser = optparse.OptionParser(usage="%prog [options] <arg1> <arg2> [<arg3>...]",version='1.0',)#定義命令列選項，用add_option一次增加一個parser.add_option("-q",action="store",type="string",dest="term",help="assign where predicate")parser.add_option("-c","--column",action="store",type="string",dest="col",help="assign query column")parser.add_option("-t",action="store",type="string",dest="table",help="assign query table")parser.add_option("-f","--format",action="store_true",dest="format",help="-f must match up -o")parser.add_option("-o",action="store",type="string",dest="outfile",help="assign output file")#解析命令列options,args = parser.parse_args()#把上述dest值賦給我們自訂的變數table = options.tablecolumn = options.colterm = options.termformat = options.format#實現動態讀查詢statement = "select * from %s where %s like '%s'"%(table,column,term)cur = conn.cursor()cur.execute(statement)results = cur.fetchall()#類比 \G 輸出形式if format is True:  columns_query = "describe %s"%(table)  cur.execute(columns_query)  heards = cur.fetchall()  column_list = []  for record in heards:    column_list.append(record[0])  output = ""  count = 1  for record in results:    output = output + "************ %s row ************\n\n"%(count)    for field_no in xrange(0, len(column_list)):      output = output + column_list[field_no]+ ": " + str(record[field_no]) + "\n"    output = output + "\n"    count = count + 1else:  output = []  for record in xrange(0,len(results)):    output.append(results[record])  output = ''.join(output)#把輸出結果定向到指定檔案if options.outfile:  outfile = options.outfile  with open(outfile,'w') as out:    out.write(output)else:  print output#關閉遊標與串連conn.close()cur.close()

By DataHacker2014-2-5Good Luck！