欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库设计之利用Python操作Schema方法详解

程序员文章站 2022-07-05 12:10:34
弓在箭要射出之前,低声对箭说道,“你的*是我的”。schema如箭,弓似python,选择python,是schema最大的*。而*应是一个能使自己变得更好的机会。...

弓在箭要射出之前,低声对箭说道,“你的*是我的”。schema如箭,弓似python,选择python,是schema最大的*。而*应是一个能使自己变得更好的机会。

schema是什么?

不管我们做什么应用,只要和用户输入打交道,就有一个原则--永远不要相信用户的输入数据。意味着我们要对用户输入进行严格的验证,web开发时一般输入数据都以json形式发送到后端api,api要对输入数据做验证。一般我都是加很多判断,各种if,导致代码很丑陋,能不能有一种方式比较优雅的验证用户数据呢?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()

总结

以上就是本文关于mysql数据库设计之利用python操作schema方法详解的全部内容,希望对大家有所帮助。欢迎参阅:python定时器实例代码python生成数字图片代码分享等,有什么问题可以随时留言,小编会及时回复大家的,欢迎留言交流讨论。