schema是什么？
不管我们做什么应用，只要和用户输入打交道，就有一个原则--永远不要相信用户的输入数据。意味着我们要对用户输入进行严格的验证，web开发时一般输入数据都以json形式发送到后端api，api要对输入数据做验证。一般我都是加很多判断，各种if，导致代码很丑陋，能不能有一种方式比较优雅的验证用户数据呢？schema就派上用场了。本文主要介绍了mysql数据库设计之利用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连接模块：
[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()
相关推荐：
关于数据库中table与schema的区别详解
mysql中关于information_schema的详细介绍
mysql分布式集群之mycat（二）schema代码详解
以上就是mysql数据库利用python操作schema方法详解的详细内容。