Python3实现将MySQL数据库中的记录生成JSON数据

绝地灬酷狼 2021-11-02 16:54 367阅读 0赞

**思路**:首先连接MYSQL数据库，将查询到的数据存放到字典中,然年将每个组装好的字典放到List中  
调用json.dumps(jsonData, ensure\_ascii=False)方法,生成JSON数据并返回。  
[MySQLData2Json.py][]  
在手机上的效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NDQ3J1bm5lcg_size_16_color_FFFFFF_t_70]

import json, MySQLdb
    import io
    import sys
    import urllib.request
    sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf8')
    
    '''
    Create on 2019-08-12
    @author:Philtellcy
    @function:Read Data from mysql database transform json format
    '''
    
    def Table2Json():
        try:
            # 连接数据库
            conn = MySQLdb.connect(host='localhost', user='root', 
            passwd='root', db='suibian',charset='utf8')
            cur = conn.cursor()
            sql = "select * from newstitle"
            cur.execute(sql)
            result = cur.fetchall()
            cur.close()
            jsonData = []
            # 循环读取元组数据
            print("读到了数据")
            for row in result:
                data = {}
                data['NewsID'] = str(row[0])
                data['Title'] = str(row[1])
                data['DescCn'] = str(row[2])
                data['DescJp'] = str(row[3])
                data['Title_ip'] = str(row[4])
                data['Title_cn'] = row[5]
                print(str(row[5]),row[5],data['Title_cn'])
                data['Category'] = str(row[6])
                data['TopicId'] = str(row[7])
                data['Sound'] = str(row[8])
                data['Url'] = str(row[9])
                data['Pic'] = str(row[10])
                data['PicDesc'] = str(row[11])
                data['CreatTime'] = str(row[12])
                data['PublishTime'] = str(row[13])
                data['ReadCount'] = str(row[14])
                data['HotFlag'] = str(row[15])
                data['Flag'] = str(row[16])
                data['WordCount'] = str(row[17])
                data['HardWeight'] = str(row[18])
                data['TFlag'] = str(row[19])
                data['uid'] = str(row[20])
                data['auid'] = str(row[21])
                data['groupid'] = str(row[22])
                data['Likes'] = str(row[23])
                data['DisLikes'] = str(row[24])
                data['newsfrom'] = str(row[25])
                data['VFlag'] = str(row[26])
                jsonData.append(data)
                jsondatar = json.dumps(jsonData, ensure_ascii=False)
            return jsondatar[1:len(jsondatar) - 1]
            print("连接成功")
        except Exception as e:
            print('MySQL connect fail ',e)
    
    if __name__ == '__main__':
        jsonData = Table2Json()
        print("转换成json的数据",jsonData)
        # 将数据存放到本地
        f = open('D:\getuidata.txt', 'w+',encoding='utf-8')
        f.write(jsonData)
        f.close()

[MySQLData2Json.py]: http://MySQLData2Json.py
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NDQ3J1bm5lcg_size_16_color_FFFFFF_t_70]: /images/20211101/c59c7aee4fe14998aae7c929a9fb45e3.png