schema是什么？
不管我們做什么應(yīng)用，只要和用戶輸入打交道，就有一個原則–永遠不要相信用戶的輸入數(shù)據(jù)。意味著我們要對用戶輸入進行嚴格的驗證，web開發(fā)時一般輸入數(shù)據(jù)都以JSON形式發(fā)送到后端API，API要對輸入數(shù)據(jù)做驗證。一般我都是加很多判斷，各種if，導(dǎo)致代碼很丑陋，能不能有一種方式比較優(yōu)雅的驗證用戶數(shù)據(jù)呢？Schema就派上用場了。本文主要介紹了mysql數(shù)據(jù)庫設(shè)計之利用python操作Schema方法詳解，還是比較不錯的，這里分享給大家，供需要的朋友參考。
㈠ MySQLdb部分
表結(jié)構(gòu)：
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)
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
數(shù)據(jù)庫連接模塊：
[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)
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
1 打印列的元數(shù)據(jù)
[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()
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
1.）調(diào)用execute()之后，cursor應(yīng)當設(shè)置其description屬性
2.）是個tuple，共7列：列名、類型、顯示大小、內(nèi)部大小、精度、范圍以及一個是否接受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)
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
2 通過列名訪問列值
默認情況下，獲取方法從數(shù)據(jù)庫作為”行”返回的值是元組
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
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
我們能夠借助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
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
㈡ SQLAlchemy–SQL煉金術(shù)師
雖然SQL有國際標準，但遺憾的是，各個數(shù)據(jù)庫廠商對這些標準的解讀都不一樣，并且都在標準的基礎(chǔ)上實現(xiàn)了各自的私有語法。為了隱藏不同SQL“方言”之間到區(qū)別，人們開發(fā)了諸如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()
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
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)
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
example 2：表刪除
有2種方式，其一：? In?[5]:?t.drop(bind=engine,checkfirst=True)?? 另一種是：? In?[5]:?metadata.drop_all(bind=engine,checkfirst=True)，其中可以借助tables屬性指定要刪除的對象
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
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)
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
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)
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
㈢ 隱藏Schema
數(shù)據(jù)的安全是否暴露在完全可信任的對象面前，這是任何有安全意識的DBA都不會去冒的風險。比較好的方式是盡可能隱藏Schema結(jié)構(gòu)并驗證用戶輸入的數(shù)據(jù)完整性，這在一定程度上雖然增加了運維成本，但安全無小事。
這里借助開發(fā)一個命令行工具來闡述該問題
需求：隱藏表結(jié)構(gòu)，實現(xiàn)動態(tài)查詢，并將結(jié)果模擬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? ............</arg3></arg2></arg1>
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
請看代碼
#!/usr/bin/env?python import?optparse from?dbapi?import?* #構(gòu)造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 #實現(xiàn)動態(tài)讀查詢 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?************nn"%(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) #把輸出結(jié)果定向到指定文件 if?options.outfile: ?outfile?=?options.outfile ?with?open(outfile,'w')?as?out: ??out.write(output) else: ?print?output #關(guān)閉游標與連接 conn.close() cur.close()</arg3></arg2></arg1>
立即學(xué)習(xí)“Python免費學(xué)習(xí)筆記（深入）”；
相關(guān)推薦：
關(guān)于數(shù)據(jù)庫中table與schema的區(qū)別詳解
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
