Pi4SPmcp

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    def create_table_prod():
32        cursor.execute('''
33        CREATE TABLE IF NOT EXISTS `xl1034_shop_products` (
34        `id` INT UNSIGNED NOT NULL,
35        `brand_id` INT NULL DEFAULT NULL,
36        `name` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
37        `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
38        `sku` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
39        `price` DOUBLE NOT NULL,
40        `purchase_price` DOUBLE NOT NULL,
41        `description` VARCHAR(191) NULL COLLATE 'utf8mb4_unicode_ci',
42        `video_code` VARCHAR(191) NULL COLLATE 'utf8mb4_unicode_ci',
43        `currency_id` INT NOT NULL,
44        `available` TINYINT NOT NULL DEFAULT '0',
45        `created_at` TIMESTAMP NULL DEFAULT NOW(),
46        `updated_at` TIMESTAMP NULL DEFAULT NOW()
47                                                           )
48        COLLATE='utf8mb4_unicode_ci'
49        ENGINE=InnoDB
50        ''')
51
52
53    create_table_prod()
54
55
56
57    def test():
58
59        l_o = 0
60        n = []
61
62        while l_o != len(offers_tag):
63            # offer_vendor_tag = offer_tag[l_o].find('vendor')
64            id_tag = offer_tag[l_o].get('id')
65            if offer_tag[l_o].get('available') == 'true':
66                available = 1
67            else:
68                available = 0
69
70            offer_name_tag = offer_tag[l_o].find('name').text
71            slug = slugify(offer_name_tag)
72
73            offer_sku_tag = offer_tag[l_o].find('vendorCode').text
74            offer_price_tag = offer_tag[l_o].find('price').text
75
76            offer_price_p_tag = offer_tag[l_o].find('purchase_price').text
77            offer_description_tag = offer_tag[l_o].find('description').text
78            try:
79                # Не во всех офферах есть видео
80                offer_video_tag = offer_tag[l_o].find('video')
81                video_code_tag = offer_video_tag.text
82            except:
83                video_code_tag = None
84
85            brand_id = 1231
86            currency_id = 0
87
88            cursor.execute('''
89                    INSERT INTO `xl1034_shop_products` (`id`, `brand_id`, `name`, `slug`, `sku`, `price`,
90                    `purchase_price`, `description`, `video_code`, `currency_id`, `available` )
91                    VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")
92                    ''' % (id_tag,
93                           brand_id,
94                           offer_name_tag,
95                           slug,
96                           offer_sku_tag,
97                           offer_price_tag,
98                           offer_price_p_tag,
99                           offer_description_tag,
100                           video_code_tag,
101                           currency_id,
102                           available
103                           )
104                           )
105            db.commit()
106
107            l_o += 1
108
109
110    test()