xCVyMY3p

1import xml.etree.ElementTree as etree
2from slugify import slugify
3import pymysql
4
5tree = etree.parse('index_cron.xml')
6shop_tag = tree.find('shop')
7offers_tag = shop_tag.find('offers')
8# # print(f'В XML %s офферов' %len(offers_tag))
9offer_tag = offers_tag.findall('offer')
10
11len_offer = len(offers_tag) - len(offers_tag)
12
13db = pymysql.connect(
14    host='localhost',
15    user='root',
16    password='731731731',
17    db='MYSQL',
18    charset='utf8mb4',
19    port=3307
20)
21
22db_name = input('Введите название БД') or 'test'
23
24with db.cursor() as cursor:
25    def create_db(name):
26        cursor.execute('CREATE DATABASE IF NOT EXISTS `%s`' % name)
27        cursor.execute('USE `%s`' % name)
28
29
30    create_db(db_name)
31
32
33    def create_table_sf():
34        cursor.execute('''
35        CREATE TABLE IF NOT EXISTS `system_files` (
36        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
37        `disk_name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
38        `file_name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
39        `file_size` INT NOT NULL,
40        `content_type` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
41        `title` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
42        `description` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
43        `field` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
44        `attachment_id` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
45        `attachment_type` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
46        `is_public` TINYINT NOT NULL DEFAULT '1',
47        `sort_order` INT NULL DEFAULT NULL,
48        `created_at` TIMESTAMP NULL DEFAULT NULL,
49        `updated_at` TIMESTAMP NULL DEFAULT NULL,
50        PRIMARY KEY (`id`),
51        INDEX `system_files_field_index` (`field`),
52        INDEX `system_files_attachment_id_index` (`attachment_id`),
53        INDEX `system_files_attachment_type_index` (`attachment_type`)
54                                                  )
55        COLLATE='utf8mb4_unicode_ci'
56        ENGINE=InnoDB
57        AUTO_INCREMENT=309
58        ''')
59
60
61    def create_table_brands():
62        cursor.execute('''
63        CREATE TABLE IF NOT EXISTS `xl1034_shop_brands` (
64        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
65        `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
66        `description` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
67        `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
68        `logotype` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci'
69                                                        )
70        COLLATE='utf8mb4_unicode_ci'
71        ENGINE=InnoDB
72        AUTO_INCREMENT=24
73        ''')
74
75
76    def create_table_cat():
77        cursor.execute('''
78        CREATE TABLE IF NOT EXISTS `xl1034_shop_categories` (
79        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
80        `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
81        `description` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
82        `keywords` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
83        `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
84        `parent_id` INT NULL DEFAULT NULL,
85        `nest_left` INT NULL DEFAULT NULL,
86        `nest_right` INT NULL DEFAULT NULL,
87        `nest_depth` INT NULL DEFAULT NULL,
88        PRIMARY KEY (`id`)
89                                                )
90        COLLATE='utf8mb4_unicode_ci'
91        ENGINE=InnoDB
92        AUTO_INCREMENT=5536
93        ''')
94
95
96    def create_table_cat_prod():
97        cursor.execute('''
98        CREATE TABLE IF NOT EXISTS `xl1034_shop_category_product` (
99        `category_id` INT UNSIGNED NOT NULL,
100        `product_id` INT UNSIGNED NOT NULL,
101        PRIMARY KEY (`category_id`, `product_id`)
102                                                    )
103        COLLATE='utf8mb4_unicode_ci'
104        ENGINE=InnoDB
105        ''')
106
107
108    def create_table_curr():
109        cursor.execute('''
110        CREATE TABLE IF NOT EXISTS `xl1034_shop_currencies` (
111        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
112        `code` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
113        `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
114        `is_default` TINYINT NOT NULL DEFAULT '0',
115        `rate` DOUBLE NOT NULL DEFAULT '1',
116        `sign` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
117        `is_prefix` TINYINT NOT NULL DEFAULT '0',
118        PRIMARY KEY (`id`)
119                                                )
120        COLLATE='utf8mb4_unicode_ci'
121        ENGINE=InnoDB
122        AUTO_INCREMENT=2
123        ''')
124
125
126    def create_table_param():
127        cursor.execute('''
128        CREATE TABLE IF NOT EXISTS `xl1034_shop_parameters` (
129        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
130        `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
131        PRIMARY KEY (`id`)
132                                              )
133        COLLATE='utf8mb4_unicode_ci'
134        ENGINE=InnoDB
135        AUTO_INCREMENT=123
136        ''')
137
138
139    def create_table_param_prod():
140        cursor.execute('''
141        CREATE TABLE IF NOT EXISTS `xl1034_shop_parameter_product` (
142        `parameter_id` INT UNSIGNED NOT NULL,
143        `product_id` INT UNSIGNED NOT NULL,
144        `value` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
145        PRIMARY KEY (`parameter_id`, `product_id`)
146                                                     )
147        COLLATE='utf8mb4_unicode_ci'
148        ENGINE=InnoDB
149        ''')
150
151
152    def create_table_prod():
153        cursor.execute('''
154        CREATE TABLE IF NOT EXISTS `xl1034_shop_products` (
155        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
156        `brand_id` INT NULL DEFAULT NULL,
157        `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
158        `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
159        `sku` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
160        `price` DOUBLE NOT NULL,
161        `purchase_price` DOUBLE NOT NULL,
162        `description` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
163        `video_code` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
164        `currency_id` INT NOT NULL,
165        `available` TINYINT NOT NULL DEFAULT '0',
166        `created_at` TIMESTAMP NULL DEFAULT NULL,
167        `updated_at` TIMESTAMP NULL DEFAULT NULL,
168        PRIMARY KEY (`id`)
169                                                           )
170        COLLATE='utf8mb4_unicode_ci'
171        ENGINE=InnoDB
172        AUTO_INCREMENT=2304
173        ''')
174
175
176    # create_table_sf()
177    create_table_brands()
178
179
180    # create_table_cat()
181    # create_table_cat_prod()
182    # create_table_curr()
183    # create_table_param()
184    # create_table_param_prod()
185    # create_table_prod()
186
187
188    def insert_brands(l_o = len_offer):
189
190        """Заполнение таблицы xl1034_shop_BRANDS"""
191        n = []
192
193        while l_o != len(offers_tag):
194            offer_vendor_tag = offer_tag[l_o].find('vendor')
195
196            slug_offer_vendor_tag = offer_vendor_tag.text
197
198            n.append(slug_offer_vendor_tag)
199
200            # cursor.execute('''
201            # INSERT INTO `xl1034_shop_BRANDS` (name, slug) VALUES ("%s", "%s")
202            # ''' % (slug_offer_vendor_tag, sovt))
203            # db.commit()
204
205            l_o += 1
206
207        b_name = list(set(n))
208        len_b_name = len(b_name) - len(b_name)
209
210        while len_b_name != len(b_name):
211            sovt = slugify(b_name[len_b_name])
212            cursor.execute('''
213            INSERT INTO `xl1034_shop_BRANDS` (name, slug) VALUES ("%s", "%s")
214            ''' % (b_name[len_b_name], sovt))
215
216            db.commit()
217            len_b_name += 1
218
219
220    insert_brands()