# coding=utf-8
'''
Created on 2016-10-26
@author: Jennifer
Project:讀取mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)，轉(zhuǎn)為json格式
'''
import json,MySQLdb

def TableToJson(): 
  try:
    #1-7：如何使用python DB API訪問(wèn)數(shù)據(jù)庫(kù)流程的
    #1.創(chuàng)建mysql數(shù)據(jù)庫(kù)連接對(duì)象connection
    #connection對(duì)象支持的方法有cursor(),commit(),rollback(),close()
    conn = MySQLdb.Connect(host='mysql服務(wù)器地址',user='用戶名',passwd='密碼',db='數(shù)據(jù)庫(kù)名稱',port=3306,charset = 'utf8') 
    #2.創(chuàng)建mysql數(shù)據(jù)庫(kù)游標(biāo)對(duì)象 cursor
    #cursor對(duì)象支持的方法有execute(sql語(yǔ)句),fetchone(),fetchmany(size),fetchall(),rowcount,close()
    cur = conn.cursor()
    #3.編寫(xiě)sql
    sql = "SELECT pm.name AS nm,pm.desc AS dc,pm.image_url AS iu,pm.image_type AS it, pm.on_going AS og, pm.type AS mt,pm.pkgName AS pn,pm.apk_url AS du,pm.apkMd5 AS am,pm.minversionCode AS mc,pm.versionCode AS vc,pm.versionName AS vn, pm.signatureMd5 AS sm,pm.source AS se,pm.action AS ao FROM message pm WHERE pm.id = '217'"
    #4.執(zhí)行sql命令
    #execute可執(zhí)行數(shù)據(jù)庫(kù)查詢select和命令insert，delete，update三種命令(這三種命令需要commit()或rollback())
    cur.execute(sql)
    #5.獲取數(shù)據(jù) 
    #fetchall遍歷execute執(zhí)行的結(jié)果集。取execute執(zhí)行后放在緩沖區(qū)的數(shù)據(jù)，遍歷結(jié)果，返回?cái)?shù)據(jù)。
    #返回的數(shù)據(jù)類型是元組類型，每個(gè)條數(shù)據(jù)元素為元組類型:(('第一條數(shù)據(jù)的字段1的值','第一條數(shù)據(jù)的字段2的值',...,'第一條數(shù)據(jù)的字段N的值'),(第二條數(shù)據(jù)),...,(第N條數(shù)據(jù)))
    data = cur.fetchall()
    print u'fetchall()返回的數(shù)據(jù)：',data
    #6.關(guān)閉cursor
    cur.close()
    #7.關(guān)閉connection
    conn.close()
    jsonData = []
    #循環(huán)讀取元組數(shù)據(jù)
    #將元組數(shù)據(jù)轉(zhuǎn)換為列表類型，每個(gè)條數(shù)據(jù)元素為字典類型:[{'字段1':'字段1的值','字段2':'字段2的值',...,'字段N:字段N的值'},{第二條數(shù)據(jù)},...,{第N條數(shù)據(jù)}]
    for row in data: 
      result = {} 
      result['nm'] = row[0] 
      result['dc'] = row[1] 
      result['iu'] = row[2] 
      result['it'] = str(row[3])  
      result['og'] = str(row[4])  
      result['mt'] = str(row[5])  
      result['pn'] = row[6] 
      result['du'] = row[7] 
      result['am'] = row[8] 
      result['mc'] = str(row[9]) 
      result['vc'] = str(row[10]) 
      result['vn'] = row[11] 
      result['sm'] = row[12]
      result['se'] = str(row[13]) 
      result['ao'] = str(row[14])  
      jsonData.append(result)
      print u'轉(zhuǎn)換為列表字典的原始數(shù)據(jù)：',jsonData
      
  except: 
    print 'MySQL connect fail...' 
  else:
    #使用json.dumps將數(shù)據(jù)轉(zhuǎn)換為json格式，json.dumps方法默認(rèn)會(huì)輸出成這種格式"\u5377\u76ae\u6298\u6263"，加ensure_ascii=False，則能夠防止中文亂碼。
    #JSON采用完全獨(dú)立于語(yǔ)言的文本格式，事實(shí)上大部分現(xiàn)代計(jì)算機(jī)語(yǔ)言都以某種形式支持它們。這使得一種數(shù)據(jù)格式在同樣基于這些結(jié)構(gòu)的編程語(yǔ)言之間交換成為可能。
    #json.dumps()是將原始數(shù)據(jù)轉(zhuǎn)為json（其中單引號(hào)會(huì)變?yōu)殡p引號(hào)），而json.loads()是將json轉(zhuǎn)為原始數(shù)據(jù)。
    jsondatar=json.dumps(jsonData,ensure_ascii=False)
    #去除首尾的中括號(hào)
    return jsondatar[1:len(jsondatar)-1]
 
if __name__ == '__main__': 
  #調(diào)用函數(shù)
  jsonData = TableToJson() 
  print u'轉(zhuǎn)換為json格式的數(shù)據(jù)：',jsonData 
  #以讀寫(xiě)方式w+打開(kāi)文件，路徑前加r，防止字符轉(zhuǎn)義
  f = open(r'D:\getui\getuidata.txt','w+')
  #寫(xiě)數(shù)據(jù)
  f.write(jsonData)
  #關(guān)閉文件 
  f.close()