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

2023-09-21 16:21:05 300

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

Schema是什么？

不管我们做什么应用，只要和用户输入打交道，就有一个原则--永远不要相信用户的输入数据。意味着我们要对用户输入进行严格的验证，web开发时一般输入数据都以JSON形式发送到后端API，API要对输入数据做验证。一般我都是加很多判断，各种if，导致代码很丑陋，能不能有一种方式比较优雅的验证用户数据呢？Schema就派上用场了。

㈠MySQLdb部分

表结构：

mysql>usesakila;
mysql>descactor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
|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|onupdateCURRENT_TIMESTAMP|
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4rowsinset(0.00sec)

数据库连接模块：

[root@DataHacker~]#catdbapi.py
#!/usr/bin/envipython
#coding=utf-8
#Author:linwaterbin@gmail.com
#Time:2014-1-29

importMySQLdbasdbapi

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~]#catQueryColumnMetaData.py
#!/usr/bin/envipython

fromdbapiimport*

cur=conn.cursor()
statement="""select*fromactorlimit1"""
cur.execute(statement)

print"outputcolumnmetadata....."
print
forrecordincur.description:
printrecord

cur.close()
conn.close()

1.）调用execute()之后，cursor应当设置其description属性
2.）是个tuple，共7列：列名、类型、显示大小、内部大小、精度、范围以及一个是否接受null值的标记

[root@DataHacker~]#chmod+xQueryColumnMetaData.py
[root@DataHacker~]#./QueryColumnMetaData.py
outputcolumnmetadata.....

('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]:fromdbapiimport*
In[2]:cur=conn.cursor()
In[3]:v_sql="selectactor_id,last_namefromactorlimit2"
In[4]:cur.execute(v_sql)
Out[4]:2L
In[5]:results=cur.fetchone()
In[6]:printresults[0]
58
In[7]:printresults[1]
AKROYD

我们能够借助cursorclass属性来作为字典返回

In[2]:importMySQLdb.cursors
In[3]:importMySQLdb
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="selectactor_id,last_namefromactorlimit2"
In[7]:cur.execute(v_sql)
Out[7]:2L
In[8]:results=cur.fetchone()
In[9]:printresults['actor_id']
58
In[10]:printresults['last_name']
AKROYD

㈡SQLAlchemy--SQL炼金术师

虽然SQL有国际标准，但遗憾的是，各个数据库厂商对这些标准的解读都不一样，并且都在标准的基础上实现了各自的私有语法。为了隐藏不同SQL“方言”之间到区别，人们开发了诸如SQLAlchemy之类的工具

SQLAlchemy连接模块：

[root@DataHackerDesktop]#catsa.py
importsqlalchemyassa
engine=sa.create_engine('mysql://root:oracle@127.0.0.1/testdb',pool_recycle=3600)
metadata=sa.MetaData()

example1：表定义

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)

example2：表删除

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

example3：5种约束

3.1primarykey
下面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.2ForeignKey
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.3unique
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.4check
虽然能成功，但MySQL目前尚未支持check约束。这里就不举例了。
3.5notnull
In[21]:t_null=Table('t_null',metadata,Column('id',Integer,nullable=False))
In[22]:t_null.create(bind=engine)

4默认值

分2类：悲观(值由DBServer提供)和乐观(值由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][...]
Options:
--versionshowprogram'sversionnumberandexit
-h,--helpshowthishelpmessageandexit
-qTERMassignwherepredicate
-cCOL,--column=COLassignquerycolumn
-tTABLEassignquerytable
-f,--format-fmustmatchup-o
-oOUTFILEassignoutputfile
我们要的效果：
[root@DataHacker~]#./sesc.py-tactor-clast_name-qs%-f-ooutput.txt
[root@DataHacker~]#catoutput.txt
************1row*******************
actor_id:180
first_name:JEFF
last_name:SILVERSTONE
last_update:2006-02-1504:34:33
************2row*******************
actor_id:195
first_name:JAYNE
last_name:SILVERSTONE
last_update:2006-02-1504:34:33
......<此处省略大部分输出>......

请看代码

#!/usr/bin/envpython
importoptparse
fromdbapiimport*

#构造OptionParser实例，配置期望的选项
parser=optparse.OptionParser(usage="%prog[options][...]",version='1.0',)
#定义命令行选项，用add_option一次增加一个
parser.add_option("-q",action="store",type="string",dest="term",help="assignwherepredicate")
parser.add_option("-c","--column",action="store",type="string",dest="col",help="assignquerycolumn")
parser.add_option("-t",action="store",type="string",dest="table",help="assignquerytable")
parser.add_option("-f","--format",action="store_true",dest="format",help="-fmustmatchup-o")
parser.add_option("-o",action="store",type="string",dest="outfile",help="assignoutputfile")
#解析命令行
options,args=parser.parse_args()
#把上述dest值赋给我们自定义的变量
table=options.table
column=options.col
term=options.term
format=options.format
#实现动态读查询
statement="select*from%swhere%slike'%s'"%(table,column,term)
cur=conn.cursor()
cur.execute(statement)
results=cur.fetchall()
#模拟\G输出形式
ifformatisTrue:
columns_query="describe%s"%(table)
cur.execute(columns_query)
heards=cur.fetchall()
column_list=[]
forrecordinheards:
column_list.append(record[0])
output=""
count=1
forrecordinresults:
output=output+"************%srow************\n\n"%(count)
forfield_noinxrange(0,len(column_list)):
output=output+column_list[field_no]+":"+str(record[field_no])+"\n"
output=output+"\n"
count=count+1
else:
output=[]
forrecordinxrange(0,len(results)):
output.append(results[record])
output=''.join(output)
#把输出结果定向到指定文件
ifoptions.outfile:
outfile=options.outfile
withopen(outfile,'w')asout:
out.write(output)
else:
printoutput
#关闭游标与连接
conn.close()
cur.close()

总结

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

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

热门推荐

随机推荐