python3.6 解析svg保存到mysql

 

import json
from collections import Counter
from json import JSONDecodeError

import mysql
import requests
from lxml import etree

# 定义远程 SVG 文件的 URL
file = r'D:\tmp_files\jmx\0919_3568.txt'
data_to_insert=[]
with open(file, 'r', encoding='utf-8') as file:
    # 逐行读取文件
    for line in file:
        try:
            # print(line.strip())  # 使用 strip() 去掉每行末尾的换行符
            url = line.strip()
            response = requests.get(url)
            start_index = url.rfind('/') + 1  # 从最后一个 '/' 开始
            end_index = url.find('_', start_index)  # 找到第一个 '_'
            # 提取子字符串
            pos_id = url[start_index:end_index]
            print("pos_id===", pos_id)
            # 发送 HTTP GET 请求获取 SVG 文件内容
            response = requests.get(url)
            # 打印响应内容（XML 格式）

            svg_content = response.content  # 使用 .content 获取字节类型的内容
            # print(svg_content)
            # print(svg_content.decode('utf-8'))  # 打印内容
            # 将字节类型的 SVG 内容解析为 XML 解析树
            root = etree.fromstring(svg_content)
            # print(svg_content)
            # print(root.xpath('//@class'))
            class_items = root.xpath('//@class')
            dataTitle_items = root.xpath('//@data-title')
            dictx = {}
            counter = Counter(class_items)
            store_btn_info = {}
            for obj, count in counter.items():
                # print(f"{obj}: {count}; ")
                store_btn_info[obj] = count
            btn_table = store_btn_info.get("btn_table")
            btn_poster = store_btn_info.get("btn_poster")
            btn_mainEntry = store_btn_info.get("btn_mainEntry")
            # print(btn_table,btn_poster,btn_mainEntry)
            btn_poster_elements = root.xpath("//*[@class='btn_poster']")
            btn_table_elements = root.xpath("//*[@class='btn_table']")
            btn_mainEntry_elements = root.xpath("//*[@class='btn_mainEntry']")

            dictx1 = {}
            btn_poster_title_list = []
            for element in btn_poster_elements:
                class_attr = str(element.get('class'))
                data_title = str(element.get('data-title'))
                # btn_poster_title_list.append(data_title)
                data_pictures = json.loads(str(element.get('data-pictures')).replace("'", '"'))
                # 打印提取到的属性
                # print(f"class_attr: {class_attr}",f"data-title: {data_title}",f"data-pictures: {data_pictures}")
                picture = data_pictures.get("picture")
                backPicture = data_pictures.get("backPicture")
                if picture != '' and backPicture != '':
                    dictx1[data_title] = [picture, backPicture]
                    btn_poster_title_list.append(str(data_title + '-E'))
                    btn_poster_title_list.append(str(data_title + '-I'))

                elif picture != '':
                    dictx1[data_title] = [picture]
                    btn_poster_title_list.append(str(data_title + '-E'))
                elif backPicture != '':
                    dictx1[data_title] = [backPicture]
                    btn_poster_title_list.append(str(data_title + '-I'))
            # print("-"*100)
            # print(dictx1)
            # print("-"*100)
            btn_poster = sum([len(v) for v in dictx1.values()])
            # print(sum([ len(v) for v in dictx1.values()]))
            btn_poster_title_list.sort()

            dictx2 = {}
            btn_table_dataId_list = []
            for element in btn_table_elements:
                class_attr = element.get('class')
                data_table_id = str(element.get('data-table-id')).replace(' ', '')
                # print(data_table_id)
                btn_table_dataId_list.append(data_table_id)
                data_title = str(element.get('data-title'))
                data_sku = str(element.get('data-sku'))
                # 打印提取到的属性
                # print(f"class_attr: {class_attr}",f"data-title: {data_table_id}",f"data-pictures: {data_title}",f"data-sku:{data_sku}")
                dictx2[data_table_id] = (data_sku, data_title)
            # print(dictx2)
            btn_table_dataId_list.sort()
            # print(btn_table_dataId_list)

            dictx3 = {}
            btn_poster_title_list_v1 = []
            for element in btn_mainEntry_elements:
                class_attr = element.get('class')
                data_items = json.loads(str(element.get('data-items')).replace("'", '"'))
                data_title = element.get('data-title')
                data_posters = json.loads(str(element.get('data-posters')).replace("'", '"'))
                # 打印提取到的属性
                dictx3[
                    "btn_mainEntryx"] = f"class_attr: {class_attr}\n", f"data_items: {data_items}\n", f"data_title: {data_title}\n", f"data_posters:{data_posters}"
                # print(f"class_attr: {class_attr}\n",f"data_items: {data_items}\n",f"data_title: {data_title}\n",f"data_posters:{data_posters}")
                data_items_titles = [item['title'] for item in data_items]
                data_posters_titles = [item['title'] for item in data_posters]
                data_posters_tit = [item['title'] for item in data_posters][0] if len(
                    [item['title'] for item in data_posters]) > 0 else ''
                picture = [item['picture'] for item in data_posters][0] if len(
                    [item['picture'] for item in data_posters]) else ''
                backPicture = [item['backPicture'] for item in data_posters][0] if len(
                    [item['backPicture'] for item in data_posters]) else ''
                if picture != '' and backPicture != '':
                    data_posters_titles[0] = str(data_posters_tit) + "-E"
                    data_posters_titles.append(str(data_posters_tit) + "-I")
                elif picture != '':
                    data_posters_titles[0] = str(data_posters_tit) + "-E"

                elif backPicture != '':
                    data_posters_titles[0] = str(data_posters_tit) + "-I"

                import itertools

                btn_poster_title_list_v1.extend(data_items_titles)
                btn_poster_title_list_v1.extend(data_posters_titles)

            btn_poster_title_list_v1 = btn_poster_title_list + btn_poster_title_list_v1
            btn_poster_title_list_v1.sort()

            # print(dictx3)

            dictx["btn_table"] = dictx2
            dictx["btn_poster"] = dictx1
            dictx["btn_mainEntry"] = dictx3
            # print(dictx)

            from mysql.connector import Error

            try:
                # 连接到 MySQL 数据库
                connection = mysql.connector.connect(
                    host='',  # 数据库主机地址
                    user='',  # 数据库用户名
                    password=',  # 数据库密码
                    database=''  # 数据库名称
                )
                if connection.is_connected():
                    # 创建游标对象
                    cursor = connection.cursor()
                    # cursor.execute(create_table_query)
                    # 插入数据
                    insert_query = '''
                          INSERT INTO tmp_store_svg_info_1 (
                            url,
                            store_id,
                            btn_table,
                            btn_poster,
                            btn_mainEntry,
                            btn_table_dataId_list,
                            btn_poster_title_list,
                            svg_btn_info,
                            svg_xml,
                            create_time
                          ) VALUES (
                            %s, %s, %s, %s, %s,%s,%s, %s, %s, NOW()
                          )
                          '''

                    # data_to_insert = [(
                    #     url
                    #     , pos_id
                    #     , str(btn_table)
                    #     , str(len(btn_poster_title_list_v1))
                    #     , str(btn_mainEntry)
                    #     , str(btn_table_dataId_list).replace("'", '"')
                    #     , str(btn_poster_title_list_v1).replace("'", '"')
                    #     , json.dumps(dictx)
                    #     , str(svg_content)
                    # )]


                    print("data_to_insert=",len(data_to_insert))
                    if len(data_to_insert)==100:
                        cursor.executemany(insert_query, data_to_insert)
                        connection.commit()
                        data_to_insert.clear()
                        cursor.close()
                        connection.close()
                        print("数据插入成功")
                    else:
                        data_to_insert.append(
                            (
                                url
                                , pos_id
                                , str(btn_table)
                                , str(len(btn_poster_title_list_v1))
                                , str(btn_mainEntry)
                                , str(btn_table_dataId_list).replace("'", '"')
                                , str(btn_poster_title_list_v1).replace("'", '"')
                                , json.dumps(dictx)
                                , str(svg_content)
                            )

                        )
            except Error as e:
                print(f"Error: {e}")
                if connection.is_connected():
                    connection.rollback()  # 回滚事务
            finally:
                # 关闭游标和连接
                if connection.is_connected():
                    cursor.close()
                    connection.close()
                    print("数据库连接已关闭")
        except JSONDecodeError as e:
            print(url)
            continue


def save(connection,li):
    try:
        insert_query = '''
              INSERT INTO tmp_store_svg_info_1 (
                url,
                store_id,
                btn_table,
                btn_poster,
                btn_mainEntry,
                btn_table_dataId_list,
                btn_poster_title_list,
                svg_btn_info,
                svg_xml,
                create_time
              ) VALUES (
                %s, %s, %s, %s, %s,%s,%s, %s, %s, NOW()
              )
              '''
        if connection.is_connected():
            # 创建游标对象
            cursor = connection.cursor()
            if len(data_to_insert) == 100:
                cursor.executemany(insert_query, data_to_insert)
                connection.commit()
                data_to_insert.clear()
                cursor.close()
                connection.close()
                print("数据插入成功")

    except Error as e:
        print(f"Error: {e}")
        if connection.is_connected():
            connection.rollback()  # 回滚事务
    finally:
        # 关闭游标和连接
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("数据库连接已关闭")