MySQL Schema设计（三）利用Python操作Schema
本文中 SQLAlchemy部分可参考http://www.liaoxuefeng.com/wiki/0014316089557264a6b348958f449949df42a6d3a2e542c000/0014320114981139589ac5f02944601ae22834e9c521415000
弓在箭要射出之前，低声对箭说道，“你的自由是我的”。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-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
㈡ SQLAlchemy--SQL炼金术师
虽然SQL有国际标准，但遗憾的是，各个数据库厂商对这些标准的解读都不一样，并且都在标准的基础上实现了各自的私有语法。为了隐藏不同SQL“方言”之间到区别，人们开发了诸如SQLAlchemy之类的工具
SQLAlchemy连接模块：
SQLAlchemy连接模块： [root@DataHacker Desktop]# cat sa.py import sqlalchemy as sa engine = 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 Key
In [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 unique
In [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 null
In [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 例子：insert In [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 例子：update In [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 --version 1.0 查看帮助： [root@DataHacker ~]# ./sesc.py -h Usage: 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: 180 first_name: JEFF last_name: SILVERSTONE last_update: 2006-02-15 04:34:33 ************ 2 row ******************* actor_id: 195 first_name: JAYNE last_name: SILVERSTONE last_update: 2006-02-15 04:34:33 ......<此处省略大部分输出>......
请看代码
#!/usr/bin/env python
import optparse
from 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.table
column = options.col
term = options.term
format = 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 + 1
else:
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 DataHacker
2014-2-5
Good Luck！
