B0JhUFhC

1import xml.etree.ElementTree as etree
2from slugify import slugify
3import pymysql
4import re
5
6tree = etree.parse('index_cron.xml')
7shop_tag = tree.find('shop')
8offers_tag = shop_tag.find('offers')
9# # print(f'В XML %s офферов' %len(offers_tag))
10offer_tag = offers_tag.findall('offer')
11
12db = pymysql.connect(
13    host='localhost',
14    user='root',
15    password='731731731',
16    db='MYSQL',
17    charset='utf8mb4',
18    port=3307
19)
20
21db_name = input('Введите название БД') or 'test'
22
23with db.cursor() as cursor:
24    def create_db(name):
25        cursor.execute('CREATE DATABASE IF NOT EXISTS `%s`' % name)
26        cursor.execute('USE `%s`' % name)
27
28
29    create_db(db_name)
30
31
32
33
34    def create_table_brands():
35        cursor.execute('''
36        CREATE TABLE IF NOT EXISTS `xl1034_shop_brands` (
37        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
38        `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
39        `description` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
40        `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
41        `logotype` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci'
42                                                        )
43        COLLATE='utf8mb4_unicode_ci'
44        ENGINE=InnoDB
45        AUTO_INCREMENT=24
46        ''')
47
48    def create_table_prod():
49        cursor.execute('''
50        CREATE TABLE IF NOT EXISTS `xl1034_shop_products` (
51        `id` INT UNSIGNED NOT NULL,
52        `brand_id` INT NULL DEFAULT NULL,
53        `name` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
54        `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
55        `sku` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
56        `price` DOUBLE NOT NULL,
57        `purchase_price` DOUBLE NOT NULL,
58        `description` VARCHAR(191) NULL COLLATE 'utf8mb4_unicode_ci',
59        `video_code` VARCHAR(191) NULL COLLATE 'utf8mb4_unicode_ci',
60        `currency_id` INT NOT NULL,
61        `available` TINYINT NOT NULL DEFAULT '0',
62        `created_at` TIMESTAMP NULL DEFAULT NOW(),
63        `updated_at` TIMESTAMP NULL DEFAULT NOW()
64                                                           )
65        COLLATE='utf8mb4_unicode_ci'
66        ENGINE=InnoDB
67        ''')
68
69
70    create_table_brands()
71
72    create_table_prod()
73
74
75    def test():
76
77        l_o = 0
78        n = []
79
80        while l_o != len(offers_tag):
81            id_tag = offer_tag[l_o].get('id')
82            if offer_tag[l_o].get('available') == 'true':
83                available = 1
84            else:
85                available = 0
86
87            offer_name_tag = offer_tag[l_o].find('name').text
88            slug = slugify(offer_name_tag)
89
90            offer_sku_tag = offer_tag[l_o].find('vendorCode').text
91            offer_price_tag = offer_tag[l_o].find('price').text
92
93            offer_price_p_tag = offer_tag[l_o].find('purchase_price').text
94            offer_description_tag = offer_tag[l_o].find('description').text
95            # print(offer_description_tag)
96
97            try:
98                # Не во всех офферах есть видео
99                offer_video_tag = offer_tag[l_o].find('video')
100                video_code_tag = offer_video_tag.text
101            except:
102                video_code_tag = None
103
104            brand_id = 1231
105            currency_id = 0
106
107            # cursor.execute('''
108            #         INSERT INTO `xl1034_shop_products` (`id`, `brand_id`, `name`, `slug`, `sku`, `price`,
109            #         `purchase_price`, `currency_id`, `available` )
110            #         VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")
111            #         ''' % (id_tag,
112            #                brand_id,
113            #                offer_name_tag,
114            #                slug,
115            #                offer_sku_tag,
116            #                offer_price_tag,
117            #                offer_price_p_tag,
118            #                # offer_description_tag,
119            #                # video_code_tag,
120            #                currency_id,
121            #                available
122            #                )
123            #                )
124            # db.commit()
125
126            # Далее, по vendor'у сделать запрос в таблицу brands, и если такой бренд есть, то забрать оттуда его id, если нет, то создать такой бренд и забрать его id.
127
128
129            offer_vendor_tag = offer_tag[l_o].find('vendor').text
130
131            testt = cursor.execute('''
132                    SELECT `name` FROM `xl1034_shop_brands` WHERE `name` = "%s"
133            '''
134                    % offer_vendor_tag
135                    )
136            if testt == 0:
137                cursor.execute('''
138                    INSERT INTO `xl1034_shop_brands` (`name`, `slug`) 
139                    VALUES ("%s", "%s")
140                '''
141                    %(offer_vendor_tag, slugify(offer_vendor_tag)))
142                db.commit()
143
144            l_o += 1
145
146
147    test()