· 6 years ago · Nov 29, 2019, 12:34 AM
1import xml.etree.ElementTree as etree
2from slugify import slugify
3import pymysql
4import requests
5import os
6
7tree = etree.parse('index_cron.xml')
8shop_tag = tree.find('shop')
9offers_tag = shop_tag.find('offers')
10# # print(f'В XML %s офферов' %len(offers_tag))
11offer_tag = offers_tag.findall('offer')
12categories_tag = shop_tag.find('categories')
13category_tag = categories_tag.findall('category')
14
15db = pymysql.connect(
16 host='localhost',
17 user='root',
18 password='731731731',
19 db='MYSQL',
20 charset='utf8mb4',
21 port=3307
22)
23
24db_name = input('Введите название БД') or 'test'
25
26with db.cursor() as cursor:
27 def create_db(name):
28 cursor.execute('CREATE DATABASE IF NOT EXISTS `%s`' % name)
29 cursor.execute('USE `%s`' % name)
30
31
32 create_db(db_name)
33
34
35 def create_table_sf():
36 cursor.execute('''
37 CREATE TABLE IF NOT EXISTS `system_files` (
38 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
39 `disk_name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
40 `file_name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
41 `file_size` INT NOT NULL,
42 `content_type` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
43 `title` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
44 `description` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
45 `field` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
46 `attachment_id` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
47 `attachment_type` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
48 `is_public` TINYINT NOT NULL DEFAULT '1',
49 `sort_order` INT NULL DEFAULT NULL,
50 `created_at` TIMESTAMP NULL DEFAULT NOW(),
51 `updated_at` TIMESTAMP NULL DEFAULT NOW(),
52 PRIMARY KEY (`id`),
53 INDEX `system_files_field_index` (`field`),
54 INDEX `system_files_attachment_id_index` (`attachment_id`),
55 INDEX `system_files_attachment_type_index` (`attachment_type`)
56 )
57 COLLATE='utf8mb4_unicode_ci'
58 ENGINE=InnoDB
59 AUTO_INCREMENT=3
60 ''')
61
62
63 def create_table_brands():
64 cursor.execute('''
65 CREATE TABLE IF NOT EXISTS `xl1034_shop_brands` (
66 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
67 `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
68 `description` TEXT NULL COLLATE 'utf8mb4_unicode_ci',
69 `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
70 `logotype` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci'
71 )
72 COLLATE='utf8mb4_unicode_ci'
73 ENGINE=InnoDB
74 AUTO_INCREMENT=24
75 ''')
76
77
78 def create_table_cat():
79 cursor.execute('''
80 CREATE TABLE IF NOT EXISTS `xl1034_shop_categories` (
81 `id` INT UNSIGNED NOT NULL,
82 `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
83 `description` TEXT NULL DEFAULT NULL,
84 `keywords` TEXT NULL DEFAULT NULL,
85 `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
86 `parent_id` INT NULL DEFAULT NULL,
87 `nest_left` INT NULL DEFAULT NULL,
88 `nest_right` INT NULL DEFAULT NULL,
89 `nest_depth` INT NULL DEFAULT NULL,
90 PRIMARY KEY (`id`)
91 )
92 COLLATE='utf8mb4_unicode_ci'
93 ENGINE=InnoDB
94 ''')
95
96
97 def create_table_cat_prod():
98 cursor.execute('''
99 CREATE TABLE IF NOT EXISTS `xl1034_shop_category_product` (
100 `category_id` INT UNSIGNED NOT NULL,
101 `product_id` INT UNSIGNED NOT NULL
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 ''')
123
124
125 def create_table_param():
126 cursor.execute('''
127 CREATE TABLE IF NOT EXISTS `xl1034_shop_parameters` (
128 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
129 `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
130 PRIMARY KEY (`id`)
131 )
132 COLLATE='utf8mb4_unicode_ci'
133 ENGINE=InnoDB
134 AUTO_INCREMENT=12
135 ''')
136
137
138 def create_table_param_prod():
139 cursor.execute('''
140 CREATE TABLE IF NOT EXISTS `xl1034_shop_parameter_product` (
141 `parameter_id` INT UNSIGNED NOT NULL,
142 `product_id` INT UNSIGNED NOT NULL,
143 `value` TEXT NULL COLLATE 'utf8mb4_unicode_ci'
144 )
145 COLLATE='utf8mb4_unicode_ci'
146 ENGINE=InnoDB
147 ''')
148
149
150 def create_table_prod():
151 cursor.execute('''
152 CREATE TABLE IF NOT EXISTS `xl1034_shop_products` (
153 `id` INT UNSIGNED NOT NULL,
154 `brand_id` INT NULL DEFAULT NULL,
155 `name` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
156 `slug` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
157 `sku` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
158 `price` DOUBLE NOT NULL,
159 `purchase_price` DOUBLE NOT NULL,
160 `description` LONGTEXT,
161 `video_code` LONGTEXT,
162 `currency_id` INT NOT NULL,
163 `available` TINYINT NOT NULL DEFAULT '0',
164 `created_at` TIMESTAMP NULL DEFAULT NOW(),
165 `updated_at` TIMESTAMP NULL DEFAULT NOW()
166 )
167 COLLATE='utf8mb4_unicode_ci'
168 ENGINE=InnoDB
169 ''')
170
171
172 create_table_sf()
173 create_table_brands()
174 create_table_cat()
175 create_table_cat_prod()
176 create_table_curr()
177 create_table_param()
178 create_table_param_prod()
179 create_table_prod()
180
181
182 # Удаляем из category_product все записи, где product_id == offer_id
183
184 def test():
185
186 currencies_tag = shop_tag.find('currencies')
187 currency_tag = currencies_tag.findall('currency')
188 l_o = 0
189
190 l_s = 0
191 while l_s != len(currency_tag):
192
193 id_attr = currency_tag[l_s].get('id')
194 rate_attr = currency_tag[l_s].get('rate')
195
196 unique_curr_check = cursor.execute(
197 'SELECT `code` FROM `xl1034_shop_currencies` WHERE `code` = "%s"' % slugify(id_attr))
198
199 if unique_curr_check == 0:
200 if id_attr == 'RUB':
201 curr_name = 'Рубли'
202 code = 'rub'
203 sign = 'руб.'
204 is_default = 1
205 elif id_attr == 'USD':
206 curr_name = 'Доллары'
207 code = 'usd'
208 sign = 'дол.'
209 is_default = 23
210
211 cursor.execute('''INSERT INTO `xl1034_shop_currencies` (`code`, `name`, `is_default`, `rate`, `sign`)
212 VALUES ("%s", "%s", "%s", "%s", "%s")''' % (
213 code, curr_name, is_default, rate_attr, sign))
214 db.commit()
215 else:
216 pass # Если нужно, тут тоже может быть update
217
218 l_s += 1
219
220 l_o = 0
221
222 while l_o != len(offers_tag):
223
224 id_tag = offer_tag[l_o].get('id')
225
226 unique_prod_check = cursor.execute('SELECT `id` FROM `xl1034_shop_products` WHERE `id` = "%s"' % id_tag)
227 if offer_tag[l_o].get('available') == 'true':
228 available = 1
229 else:
230 available = 0
231
232 offer_name_tag = offer_tag[l_o].find('name').text
233 slug = slugify(offer_name_tag)
234
235 offer_sku_tag = offer_tag[l_o].find('vendorCode').text
236 offer_price_tag = offer_tag[l_o].find('price').text
237
238 offer_price_p_tag = offer_tag[l_o].find('purchase_price').text
239 offer_description_tag = offer_tag[l_o].find('description').text
240
241 try:
242 # Не во всех офферах есть видео
243 offer_video_tag = offer_tag[l_o].find('video')
244 video_code_tag = pymysql.escape_string(offer_video_tag.text)
245 except:
246 video_code_tag = None
247
248 offer_vendor_tag = offer_tag[l_o].find('vendor').text
249
250 unique_brands_name_check = cursor.execute(
251 'SELECT `id`, `name` FROM `xl1034_shop_brands` WHERE `name` = "%s"' % offer_vendor_tag)
252 if unique_brands_name_check == 0:
253 cursor.execute('''
254 INSERT INTO `xl1034_shop_brands` (`name`, `slug`)
255 VALUES ("%s", "%s")
256 '''
257 % (offer_vendor_tag, slugify(offer_vendor_tag)))
258 db.commit()
259 cursor.execute(
260 'SELECT `id`, `name` FROM `xl1034_shop_brands` WHERE `name` = "%s"' % offer_vendor_tag)
261 brand_id = cursor.fetchone()[0]
262 else:
263 brand_id = cursor.fetchone()[0]
264
265 currencyid = offer_tag[l_o].find('currencyId').text
266
267 cursor.execute(
268 'SELECT `id`, `rate` FROM `xl1034_shop_currencies` WHERE `code` = "%s"' % currencyid)
269 currency_id = cursor.fetchone()[0]
270
271 if unique_prod_check:
272 cursor.execute('''
273 UPDATE `xl1034_shop_products`
274 SET `brand_id` = "%s",
275 `name` = "%s",
276 `slug` = "%s",
277 `sku` = "%s",
278 `price` = "%s",
279 `purchase_price` = "%s",
280 `description` = "%s",
281 `video_code` = "%s",
282 `currency_id` = "%s",
283 `available` = "%s",
284 `updated_at` = NOW()
285 WHERE `id`= "%s"
286 '''% (brand_id,
287 offer_name_tag,
288 slug,
289 offer_sku_tag,
290 offer_price_tag,
291 offer_price_p_tag,
292 pymysql.escape_string(offer_description_tag),
293 video_code_tag,
294 currency_id,
295 available,
296 id_tag)
297 )
298 db.commit()
299 else:
300 cursor.execute('''
301 INSERT INTO `xl1034_shop_products` (`id`, `brand_id`, `name`, `slug`, `sku`, `price`,
302 `purchase_price`, `description`, `video_code`, `currency_id`, `available` )
303 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")
304 ''' % (id_tag,
305 brand_id,
306 offer_name_tag,
307 slug,
308 offer_sku_tag,
309 offer_price_tag,
310 offer_price_p_tag,
311 pymysql.escape_string(offer_description_tag),
312 video_code_tag,
313 currency_id,
314 available
315 )
316 )
317 db.commit()
318
319 l_o += 1
320
321
322 test()
323
324
325 def insert_cat():
326
327 l_c = 0
328 while l_c != len(category_tag):
329
330 cat_id = category_tag[l_c].get('id')
331 unique_cat_check = cursor.execute('SELECT `id` FROM `xl1034_shop_categories` WHERE `id` = "%s"' % cat_id)
332
333 if unique_cat_check:
334 pass
335 else:
336 cursor.execute('''
337 INSERT INTO `xl1034_shop_categories` (`id`, `name`, `slug`, `parent_id`)
338 VALUE ("%s", "%s", "%s", "%s")
339 '''
340 % (category_tag[l_c].get('id'), category_tag[l_c].text, slugify(category_tag[l_c].text),
341 category_tag[l_c].get('parentId'))
342 )
343 db.commit()
344
345 l_c += 1
346
347
348 insert_cat()
349
350
351 def insert_cat_prod():
352 l_o = 0
353 while l_o != len(offer_tag):
354
355 param_tag = offer_tag[l_o].findall('param')
356 l_p = 0
357
358 offer_id = offer_tag[l_o].get('id')
359 while l_p != len(param_tag):
360 if param_tag[l_p].get('name') == 'Категория':
361 try:
362 int(param_tag[l_p].text)
363
364 unique_cat = cursor.execute(
365 'SELECT `category_id` FROM `xl1034_shop_category_product` WHERE (`category_id` = "%s" AND `product_id` = "%s")' % (
366 param_tag[l_p].text, offer_id))
367
368 if unique_cat:
369 pass
370 else:
371 cursor.execute('''
372 INSERT INTO `xl1034_shop_category_product` (`category_id`,`product_id`)
373 VALUE ("%s", "%s")
374 '''
375 % (param_tag[l_p].text, offer_id)
376 )
377 db.commit()
378
379 except:
380 break
381
382 l_p += 1
383 l_o += 1
384
385
386 insert_cat_prod()
387
388
389 def insert_param():
390
391 l_o = 0
392 while l_o != len(offer_tag):
393
394 param_tag = offer_tag[l_o].findall('param')
395 l_p = 0
396
397 while l_p != len(param_tag):
398
399 param_name = param_tag[l_p].get('name')
400
401 unique_param_check = cursor.execute('SELECT `name` FROM `xl1034_shop_PARAMETERS` WHERE `name` = "%s"'
402 % param_name)
403 if unique_param_check:
404 pass
405 elif param_name == 'Категория':
406 pass
407 else:
408 cursor.execute('''INSERT INTO `xl1034_shop_PARAMETERS` (`name`)
409 VALUE ("%s")'''
410 % param_name)
411 db.commit()
412
413 l_p += 1
414 l_o += 1
415
416
417 insert_param()
418
419
420 def insert_param_prod():
421
422 l_o = 0
423 while l_o != len(offer_tag):
424
425 param_tag = offer_tag[l_o].findall('param')
426 l_p = 0
427
428 offer_id = offer_tag[l_o].get('id')
429 while l_p != len(param_tag):
430 param_name = param_tag[l_p].get('name')
431 param_text = param_tag[l_p].text
432 if param_name == 'Категория':
433 pass
434 else:
435 param_id = cursor.execute(
436 'SELECT `name`, `id` FROM `xl1034_shop_PARAMETERS` WHERE `name` = "%s"'
437 % param_name)
438 id_param_prod = cursor.fetchone()[1]
439
440 unique_param_check = cursor.execute(
441 '''SELECT * FROM `xl1034_shop_parameter_product`
442 WHERE (`parameter_id` = "%s" AND `product_id` = "%s" AND `value` = "%s")'''
443 % (id_param_prod, offer_id, param_text))
444
445 if not unique_param_check:
446 cursor.execute('''INSERT INTO `xl1034_shop_parameter_product`
447 (`parameter_id`, `product_id`, `value`)
448 VALUE ("%s", "%s", "%s")'''
449 % (id_param_prod, offer_id, param_text))
450 db.commit()
451
452 l_p += 1
453 l_o += 1
454
455
456 insert_param_prod()
457
458
459 def insert_sys_f():
460 # После забираем картинки, и крутим их в цикле,
461 # Проверяем в system_files по имени и attachment_id (он должен быть равен offer id), если такой записи нет, то качаем файл, кладём в папочку и делаем запись в system_files.
462 # path0 = os.getcwd() # текущая рабочая директория
463 path0 = r'C:\Users\Eva\Desktop\0000\storage\app\uploads'
464 content_type = r'image/jpeg'
465 field = 'picture'
466 attachment_type = r'Xl1034\Shop\Models\Product'
467 is_public = 1
468 sort_order = 3
469
470 try:
471 path = os.mkdir(path0 + r'\test')
472 except:
473 path = (path0 + r'\test')
474
475 l_o = 0
476
477 while l_o != len(offer_tag):
478 attachment_id = offer_tag[l_o].get('id')
479 pic_tag = offer_tag[l_o].findall('picture')
480 l_pic = 0
481 while l_pic != len(pic_tag):
482 pic_url = pic_tag[l_pic].text
483 p = requests.get(pic_url)
484 path_pic_index = pic_url.rfind('/')
485 pic_disk_name = pic_url[path_pic_index + 1:]
486 disk_pic_path = str(path + '\\' + pic_disk_name)
487 out = open(disk_pic_path, "wb")
488 out.write(p.content)
489 out.close()
490 disk_name = pic_disk_name
491 file_size = os.path.getsize(disk_pic_path)
492
493 unique_pic_id_check = cursor.execute('''
494 SELECT `disk_name` FROM `SYSTEM_FILES` WHERE `disk_name` = "%s"
495 ''' % disk_name)
496
497 if not unique_pic_id_check:
498 cursor.execute('''
499 INSERT INTO `SYSTEM_FILES`
500 (`disk_name`, `file_name`, `file_size`, `content_type`, `field`, `attachment_id`, `attachment_type`, `is_public`, `sort_order`)
501 VALUE ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")'''
502 % (
503 disk_name, disk_name, file_size, content_type, field, attachment_id, attachment_type,
504 is_public, sort_order))
505 db.commit()
506 else:
507 # Замена работает по имени файла на диске, уверен, что это лучше всего. Хотя можно и по айди.
508 # Нинаю даже, если хочется пo id, то следует заменить WHERE `disk_name` на WHERE `id`
509 # Добавить после сета `disk_name` =%s, последний disk_name сделать первым(в массиве после ''')
510 # А также, в SELECT `disk_name` FROM `SYSTEM_FILES` WHERE `disk_name` = "%s"
511 # следует заменить все `disk_name` на `id`
512 cursor.execute('''
513 UPDATE `SYSTEM_FILES`
514 SET `file_name` = "%s",
515 `file_size` = "%s",
516 `content_type` = "%s",
517 `field` = "%s",
518 `attachment_id` = "%s",
519 `attachment_type` = "%s",
520 `is_public` = "%s",
521 `sort_order` = "%s",
522 `updated_at` = NOW()
523 WHERE `disk_name`= "%s"
524 ''' % (disk_name, file_size, content_type, field, attachment_id, attachment_type,
525 is_public, sort_order, disk_name)
526 )
527 db.commit()
528
529 l_pic += 1
530
531 l_o += 1
532
533
534 insert_sys_f()