python读取数据中的数据存储为JSON格式

桃扇骨 2023-07-17 07:24 25阅读 0赞

方法一：  
这种方式的得到的文件，虽然打开文件后报错，不要要修改，  
存储数据类型：  
\{“Name”: “ESET”, “link”: “https://www.welivesecurity.com/”\}  
\{“Name”: “FireEye”, “link”: “https://www.fireeye.com/blog.html\#dismiss-lightbox”\}  
\{“Name”: “TrendMicro”, “link”: “https://blog.trendmicro.com/trendlabs-security-intelligence/”\}  
\{“Name”: “Cybereason”, “link”: “https://www.cybereason.com/blog”\}

import pymysql
    
    import json
    
    
    
    
    
    def source_json():
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='find_report', charset='utf8')
    
        cursor = conn.cursor()  # 建立游标cursor
        #执行查询
        cursor.execute("select Name,link from source")   #数据库语句
    
        # 查询数据库多条数据
        result = cursor.fetchall()
        print(result)
        #
        fields = cursor.description  #查询数据中的字段
        cursor.close()   #关闭游标
    
        conn.close()    #关闭数据库连接
    
        # 定义字段名的列表
        column_list = []
        for i in fields:
            # 提取字段名，追加到列表中
            column_list.append(i[0])
        # print(column_list)
        # ['Id', 'name', 'password', 'birthplace']
    
        # 打开输出结果文件
        with open('./source.json', 'w+') as f:
            # 一次循环，row代表一行，row以元组的形式显示
            for row in result:
                # 定义Python 字典
                data = {}
                # 将row中的每个元素，追加到字典中。
                for i in range(len(column_list)):
                    data[column_list[i]] = row[i]
                # data[column_list[0]] = row[0]
                # # Python字段格式 和json字段格式转换
                # data[column_list[1]] = str(row[1])
                # data[column_list[2]] = str(row[2])
                # data[column_list[3]] = str(row[3])
                # Python的dict --转换成----> json的object
                jsondata = json.dumps(data, ensure_ascii=False)
                # 写入文件
                f.write(jsondata + '\n')
    
        f.close()
    
    
    
    source_json()

方法二：  
如果想要数据不报错可以存储为格式的：  
\[  
\{  
“Name”: “ESET”, “link”: “https://www.welivesecurity.com/”

\}，  
\{  
\{“Name”: “TrendMicro”, “link”: “https://blog.trendmicro.com/trendlabs-security-intelligence/”\}  
\}  
\]

import pymysql
    from ThreatIntellgence import settings
    import json
    
    def convert_to_json_string_1(data):
        return json.dumps([{'Name': i[0], 'link': i[1]} for i in data], indent=4)         #json 格式
    
    def convert_to_json_string_2(data):
        return json.dumps({'data': [{'name': i[0], 'value': i[1]} for i in data]}, indent=4)
    
    
    
    
    
    def source_json():
        connect = pymysql.connect(
            host=settings.MYSQL_HOST,
            db=settings.MYSQL_DBNAME,
            user=settings.MYSQL_USER,
            passwd=settings.MYSQL_PASSWD,
            charset='utf8',
            use_unicode=True)
        cursor = connect.cursor()  # 建立游标cursor
    
    
        #执行查询
        cursor.execute("select Name,link from source")
        # 查询数据库多条数据
        result = cursor.fetchall()
    
    
    
        # 打开输出结果文件
        with open('./source.json', 'w+') as f:
           f.write(convert_to_json_string_1(result))
    
        f.close()
    
    
    source_json()

#### dumps()存储 ####

import json
    
    
    def convert_to_json_string_1(data):
        return json.dumps([{'Name': i[0], 'link': i[1]} for i in data], indent=4)
    
    
    def convert_to_json_string_2(data):
        return json.dumps({'data': [{'name': i[0], 'value': i[1]} for i in data]}, indent=4)
    
    
    info =(('ESET', 'https://www.welivesecurity.com/'), ('FireEye', 'https://www.fireeye.com/blog.html#dismiss-lightbox'), ('TrendMicro', 'https://blog.trendmicro.com/trendlabs-security-intelligence/'), ('Cybereason', 'https://www.cybereason.com/blog'), ('Volexity', 'https://www.volexity.com/blog/'), ('Kaspersky', 'https://securelist.com/all/?category=908'))
    
    
    with open('some-file.json', 'w') as handle:
        handle.write(convert_to_json_string_1(info))
    handle.close()

#### loads()读取 ####

import json
    
    
    with open('./source.json', 'r') as f:
        temp = json.loads(f.read())
        print(temp)
        print(temp['Name'])