MySQL数据库设计之利用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生成数字图片代码分享等,有什么问题可以随时留言,小编会及时回复大家的,欢迎留言交流讨论。

时间: 2017-10-29

mysql数据库中的information_schema和mysql可以删除吗?

新装的mysql里面有两个数据库:information_schema 和 mysql  .他们是干么用的?可以删除么?当然是不可以删除的. 1.information schema 是mysql系统用的所有字典信息,包括数据库系统有什么库,有什么表,有什么字典,有什么存储过程等所有对象信息和进程访问.状态信息.一旦删除该数据库系统将无法使用. 2.mysql数据库是保存系统有关的权限,对象和状态信息.同样是不能删除的.并且这两个数据库都很小,不占用空间,你为什么要删除呢.? mysql数据库中

MySQL结合使用数据库分析工具SchemaSpy的方法

近来,我和一帮老客户一起工作,他们让花时间给他们说一下在他们新环境中使用的新工具.其中,我发现的一个非常有用的工具是SchemaSpy. SchemaSpy是Java开发的的工具(要求java 5或更高版本的支持),主要用来分析数据库中数据模型的元数据,并且能生成基于浏览器可视化的显示.通过点击就可了解数据表的层次结构,父子表关系等,主要通过HTML 链接或者实体关系图来表达.它也被设计成用来帮助解决由于约束而导致的数据库关联失败的迟钝错误. 我所喜欢ERD工具所能做的一件事是能快速的生成一个图

出现错误mysql Table 'performance_schema...解决办法

测试环境搭了一个mariadb 5.7,使用jdbc驱动 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.34</version> </dependency> 在hibernate工程中正常. 临时搭建了一个jdbc的简单工程,发现连接数据库报错: Table 'pe

解析MySQL的information_schema数据库

information_schema数据库是在mysql的版本5.0之后产生的,一个虚拟数据库,物理上并不存在.information_schema数据库类似与"数据字典",提供了访问数据库元数据的方式,即数据的数据.比如数据库名或表名,列类型,访问权限(更加细化的访问方式). 其中重要的表有:SCHEMATA表---SCHEMATA表提供了当前mysql实例中所有数据库的信息.是show databases的结果取之此表.TABLES表---TABLES表提供了关于数据库中的表的信息

MySQL5.7中 performance和sys schema中的监控参数解释(推荐)

1.performance schema:介绍 在MySQL5.7中,performance schema有很大改进,包括引入大量新加入的监控项.降低占用空间和负载,以及通过新的sys schema机制显著提升易用性.在监控方面,performance schema有如下功能: ①:元数据锁: 对于了解会话之间元数据锁的依赖关系至关重要.从MySQL5.7.3开始,就可以通过metadata_locks表来了解元数据锁的相关信息: --哪些会话拥有哪些元数据锁    --哪些会话正在等待元数据锁

浅谈XML Schema中的elementFormDefault属性

elementFormDefault属性与命名空间相关,其值可设置为qualified或unqualified 如果设置为qualified: 在XML文档中使用局部元素时,必须使用限定短名作为前缀 sean.xsd: <?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sean=&

mysql-5.5.28源码安装过程中错误总结

介绍一下关于mysql-5.5.28源码安装过程中几大错误总结,希望此文章对各位同学有所帮助.系统centOS 6.3 mini (没有任何编译环境)预编译环境首先装了众所周知的 cmake(yum install cmake -y) 复制代码 代码如下: ../bootstrap Error when bootstrapping CMake: Cannot find appropriate C compiler on this system. Please specify one using

python中os和sys模块的区别与常用方法总结

前言 本文主要介绍了关于python中os和sys模块区别与常用方法的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 官方解释: os: This module provides a portable way of using operating system dependent functionality. 翻译:提供一种方便的使用操作系统函数的方法. sys:This module provides access to some variables used or

js中document.write使用过程中的一点疑问解答

本人是新手,所以就记录下来了.这个答案是在百度中看到的,所以算是转载. 下面的内容解答了何为如果在页面加载完后如果调用document.write就会覆盖整个文档. 提示中的 [HTML 输出中] 指的是当页面加载的时候. 复制代码 代码如下: <html> <head></head> <body> <script type="text/javascript">document.write("<p>Hel

将sqlite3中数据导入到mysql中的实战教程

前言 sqlite3只小巧轻便,但是并不支持并发访问,当网站并发量较大时候,数据库请求队列边长,有可能导致队列末尾去数据库操作超时,从而操作失败.因此需要切换到支持并发访问的数据库.切换数据库需要将老的数据导出,再导入到新的数据库中,但是sqlite3和mysql的数据库并不完全兼容,需要做部分调整才能正常导入到mysql中.我最近工作中就遇到了这个问题. 最近一个项目中使用magenetico抓取磁力链接,由于它使用的是sqlite3, 文件会越来越大,而且不支持分布式:所以需要将其改造成My

Java中实现文件上传下载的三种解决方案(推荐)

java文件上传与文件下载是程序开发中比较常见的功能,下面通过本文给大家介绍Java中实现文件上传下载的三种解决方案,具体详情如下所示: 第一点:Java代码实现文件上传 FormFile file=manform.getFile(); String newfileName = null; String newpathname=null; String fileAddre="/numUp"; try { InputStream stream = file.getInputStream(

Java8中Optional类型和Kotlin中可空类型的使用对比

本文主要给大家介绍了关于Java8中Optional类型和Kotlin中可空类型使用的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍: 在 Java 8中,我们可以使用 Optional 类型来表达可空的类型. package com.easy.kotlin; import java.util.Optional; import static java.lang.System.out; /** * Optional.ofNullable - 允许传递为 null 参数 *

vue 中自定义指令改变data中的值

通过局部自定义指令实现了一个拖动的指令 html: <div style="position:absolute;width:100px;height:100px;border:1px solid red" v-drag="{set:set}"></div> script: methods:{ set(x,y){ this.data.x=x; this.data.y=y; } }, directives:{ // 拖动的自定义指令 drag(e

Maven在Windows中的配置以及IDE中的项目创建实例

Maven在Windows下的配置 1.Maven下载地址:http://maven.apache.org/download.cgi,下载红框里的版本即可. 2.解压到D盘: 3.修改配置文件settings.xml. a)修改为阿里云的镜像,国内的镜像下载速度会快很多. <mirrors> <mirror> <id>alimaven</id> <name>aliyun maven</name> <url>http://m