5xhnxwkj

1import pandas as pd
2 
3import pymssql as psql
4import psycopg2
5from sqlalchemy import create_engine
6 
7import time
8import datetime
9import traceback
10
11def get_connection_postgresql_fspb():
12    return psycopg2.connect(dbname='dep_spb', user='adminfko', password='C1W8RiG6Ix', host="adm-fko-spb-pg1.dns-shop.ru")
13
14def create_engine_postgres_fko_spb_pg1():
15    return create_engine(f'postgresql+psycopg2://adminfko:C1W8RiG6Ix@adm-fko-spb-pg1.dns-shop.ru/dep_spb', isolation_level="AUTOCOMMIT")
16
17def print_log(*args):
18    print(datetime.datetime.now().strftime("[%Y.%m.%d %H:%M:%S] "), *args)
19
20#dim.dim_branches
21def datacreator_dim_branches(table_name="dim_branches", schema="dim"):
22
23    engine = create_engine_postgres_fko_spb_pg1()
24
25    try:
26        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
27        print_log(f"DROP TABLE {schema}.{table_name}")
28    except:
29        print_log(f"""Cannot drop table: {schema}.{table_name}""")
30
31    query_test = '''
32            CREATE TABLE dim.dim_branches (
33            	"Ссылка" uuid NULL,
34            	"ПометкаУдаления" bool NULL,
35            	"Код" float8 NULL,
36            	"Наименование" text NULL,
37            	"БрендФилиала" float8 NULL,
38            	"ВидФилиала" text NULL,
39            	"Город" uuid NULL,
40            	"ГрафикПоставок" text NULL,
41            	"ДатаЗапускаЗарплатногоПроекта" timestamp NULL,
42            	"ДатаОткрытияМагазина" timestamp NULL,
43            	"ДатаЗакрытия" timestamp NULL,
44            	"ЗаРубежом" bool NULL,
45            	"КаналПродаж" text NULL,
46            	"Комментарий" text NULL,
47            	"КоординатыДолгота" float8 NULL,
48            	"КоординатыШирота" float8 NULL,
49            	"КраткоеНаименование" text NULL,
50            	"НаименованиеПолное" text NULL,
51            	"НаличиеПарковки" bool NULL,
52            	"НаличиеСкладскойБазы" bool NULL,
53            	"НаличностьЗаДень" float8 NULL,
54            	"ОсновноеЮрЛицо" text NULL,
55            	"ОсновноеЮрЛицоДляСклада" text NULL,
56            	"ОтключитьСнятиеРезервов" text NULL,
57            	"ПереносОстатковВБДПродаж" text NULL,
58            	"ПриписанК_Администрации" uuid NULL,
59            	"ПриписанК_Балансу" uuid NULL,
60            	"ПриписанК_ИБД" uuid NULL,
61            	"ПриписанК_Ремонту" uuid NULL,
62            	"ПриписанК_Сборке" uuid NULL,
63            	"ПриписанК_Складу" uuid NULL,
64            	"ПриписанК_МРЦ" uuid NULL,
65            	"ПриписанК_СЦ" uuid NULL,
66            	"ПриписанК_Территории" uuid NULL,
67            	"ПриписанК_ФилиалуЛогистики" uuid NULL,
68            	"Проект" uuid NULL,
69            	"Регион" text NULL,
70            	"СкладБрака" text NULL,
71            	"СкладуРазрешеноПродавать" text NULL,
72            	"Телефоны" text NULL,
73            	"ТорговаяПлощадь" float8 NULL,
74            	"ТочкаВыдачи" text NULL,
75            	"ФилиалОтраженияПродаж" text NULL,
76            	"ФорматФилиала" text NULL,
77            	"ФормированиеРезервовОЕМ" text NULL,
78            	"ЭлектроннаяОчередь" text NULL,
79            	"ЭлПочта" text NULL,
80            	"ВремяНаРазгрузкуМашины" float8 NULL,
81            	"ВремяНаПодготовкуКлиентскихЗака" float8 NULL,
82            	"АдресноеХранение" text NULL,
83            	"РегиональныйСкладЗИП" text NULL,
84            	"СегментМагазина" text NULL,
85            	"СтавкаАренды" float8 NULL,
86            	"ОбщаяПлощадь" float8 NULL,
87            	"Ссылка_source" text NULL,
88            	"Город_source" text NULL,
89            	"ОсновноеЮрЛицо_source" text NULL,
90            	"ОсновноеЮрЛицоДляСклада_source" text NULL,
91            	"ПриписанК_Администрации_source" text NULL,
92            	"ПриписанК_Балансу_source" text NULL,
93            	"ПриписанК_ИБД_source" text NULL,
94            	"ПриписанК_Ремонту_source" text NULL,
95            	"ПриписанК_Сборке_source" text NULL,
96            	"ПриписанК_Складу_source" text NULL,
97            	"ПриписанК_МРЦ_source" text NULL,
98            	"ПриписанК_СЦ_source" text NULL,
99            	"ПриписанК_Территории_source" text NULL,
100            	"ПриписанК_ФилиалуЛогистики_source" text NULL,
101            	"Проект_source" text NULL,
102            	"СкладБрака_source" text NULL,
103            	"ТочкаВыдачи_source" text NULL,
104            	"ФилиалОтраженияПродаж_source" text NULL,
105            	"СегментМагазина_source" text NULL
106            );
107            CREATE INDEX "dim_branches_приписанк_территории_idx" ON dim.dim_branches USING btree ("ПриписанК_Территории");
108            CREATE UNIQUE INDEX "dim_branches_ссылка_idx" ON dim.dim_branches USING btree ("Ссылка");
109
110        '''
111
112    print_log("Start create table for {schema}.{table_name}")
113    
114    try:
115        engine.execute(query_test)
116        print_log("{schema}.{table_name} table created successfully")
117    except:
118        print_log("An error occured while creating table {schema}.{table_name}")
119
120#dim.dim_categories
121def datacreator_dim_categories(table_name="dim_categories", schema="dim"):
122
123    engine = create_engine_postgres_fko_spb_pg1()
124
125    try:
126        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
127        print_log(f"DROP TABLE {schema}.{table_name}")
128    except:
129        print_log(f"""Cannot drop table: {schema}.{table_name}""")
130
131    query_test = '''
132            CREATE TABLE dim.dim_categories (
133	        "Ссылка" uuid NULL,
134	        "ПометкаУдаления" bool NULL,
135	        "Владелец" uuid NULL,
136	        "Родитель" uuid NULL,
137	        "ЭтоГруппа" bool NULL,
138	        "Код" text NULL,
139	        "Наименование" text NULL,
140	        "ЧисловоеПредставление" float8 NULL,
141	        "Коэфициент1" float8 NULL,
142	        "Коэфициент2" float8 NULL,
143	        "ДоступнаПокупателю" bool NULL,
144	        "НаименованиеДляПокупателя" text NULL,
145	        "Лидирующая" bool NULL,
146	        "АнглоязычноеНаименование" text NULL,
147	        "Ссылка_source" text NULL,
148	        "Владелец_source" text NULL,
149	        "Родитель_source" text NULL
150        );
151
152        '''
153
154    print_log("Start create table for {schema}.{table_name}")
155    
156    try:
157        engine.execute(query_test)
158        print_log("{schema}.{table_name} table created successfully")
159    except:
160        print_log("An error occured while creating table {schema}.{table_name}")
161
162#dim.dim_city
163def datacreator_dim_city(table_name="dim_city", schema="dim"):
164
165    engine = create_engine_postgres_fko_spb_pg1()
166
167    try:
168        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
169        print_log(f"DROP TABLE {schema}.{table_name}")
170    except:
171        print_log(f"""Cannot drop table: {schema}.{table_name}""")
172
173    query_test = '''
174            CREATE TABLE dim.dim_city (
175	        id int2 NOT NULL,
176	        source_id bytea NOT NULL,
177	        "name" varchar(50) NULL,
178	        code varchar(7) NULL,
179	        region_id int2 NULL,
180	        is_deleted bool NULL,
181	        gmt int2 NULL,
182	        geography_id int4 NULL,
183	        longitude float4 NULL,
184	        latitude float4 NULL,
185	        geometry_point bytea NULL,
186	        "_dateload" timestamp(6) NULL,
187	        salary_coefficient numeric(4, 2) NULL,
188	        parent_id int2 NULL,
189	        source_parent_id bytea NULL,
190	        status varchar(100) NULL,
191	        is_manual_correction bool NULL,
192	        guid uuid NOT NULL
193        );
194        CREATE UNIQUE INDEX dim_city_guid_idx ON dim.dim_city USING btree (guid, name);
195        CREATE INDEX dim_city_id_idx ON dim.dim_city USING btree (id, source_id, name, guid, code);
196
197        '''
198
199    print_log("Start create table for {schema}.{table_name}")
200    
201    try:
202        engine.execute(query_test)
203        print_log("{schema}.{table_name} table created successfully")
204    except:
205        print_log("An error occured while creating table {schema}.{table_name}")
206
207#dim.dim_competitor
208def datacreator_dim_competitor(table_name="dim_competitor", schema="dim"):
209
210    engine = create_engine_postgres_fko_spb_pg1()
211
212    try:
213        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
214        print_log(f"DROP TABLE {schema}.{table_name}")
215    except:
216        print_log(f"""Cannot drop table: {schema}.{table_name}""")
217
218    query_test = '''
219            CREATE TABLE dim.dim_competitor (
220	        guid uuid NOT NULL,
221	        "name" varchar NOT NULL,
222	        route int4 NOT NULL,
223	        state int4 NULL,
224	        CONSTRAINT dim_competitor_pkey PRIMARY KEY (guid)
225        );
226        CREATE UNIQUE INDEX dim_competitor_guid_idx ON dim.dim_competitor USING btree (guid);
227        '''
228
229    print_log("Start create table for {schema}.{table_name}")
230    
231    try:
232        engine.execute(query_test)
233        print_log("{schema}.{table_name} table created successfully")
234    except:
235        print_log("An error occured while creating table {schema}.{table_name}")
236        
237#dim.dim_competitor_product
238def datacreator_dim_competitor_product(table_name="dim_competitor_product", schema="dim"):
239
240    engine = create_engine_postgres_fko_spb_pg1()
241
242    try:
243        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
244        print_log(f"DROP TABLE {schema}.{table_name}")
245    except:
246        print_log(f"""Cannot drop table: {schema}.{table_name}""")
247
248    query_test = '''
249            CREATE TABLE dim.dim_competitor_product (
250	        product_name text NULL,
251	        product_group_id int2 NULL,
252	        product_group_code bpchar(7) NULL,
253	        product_category_id int2 NULL,
254	        product_category_code bpchar(7) NULL,
255	        product_brand_id int4 NULL,
256	        product_brand_code bpchar(7) NULL,
257	        last_update date NULL,
258	        is_actual int2 NULL,
259	        last_actual date NULL,
260	        is_matching int2 NULL,
261	        "owner" bpchar(36) NOT NULL,
262	        product_id bpchar(36) NOT NULL,
263	        CONSTRAINT dim_competitor_product_pkey PRIMARY KEY (product_id)
264        );
265
266        '''
267
268    print_log("Start create table for {schema}.{table_name}")
269    
270    try:
271        engine.execute(query_test)
272        print_log("{schema}.{table_name} table created successfully")
273    except:
274        print_log("An error occured while creating table {schema}.{table_name}")
275
276#dim.dim_competitor_rprt
277def datacreator_dim_competitor_rprt(table_name="dim_competitor_rprt", schema="dim"):
278
279    engine = create_engine_postgres_fko_spb_pg1()
280
281    try:
282        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
283        print_log(f"DROP TABLE {schema}.{table_name}")
284    except:
285        print_log(f"""Cannot drop table: {schema}.{table_name}""")
286
287    query_test = '''
288            CREATE TABLE dim.dim_competitor_rprt (
289	        id int2 NOT NULL,
290	        parent_id int2 NULL,
291	        "name" text NULL,
292	        code text NULL,
293	        link text NULL,
294	        image text NULL,
295	        is_deleted bool NULL,
296	        geo_info text NULL,
297	        guid bpchar(36) NULL,
298	        CONSTRAINT dim_competitor_rprt_pkey PRIMARY KEY (id)
299        );
300
301        '''
302
303    print_log("Start create table for {schema}.{table_name}")
304    
305    try:
306        engine.execute(query_test)
307        print_log("{schema}.{table_name} table created successfully")
308    except:
309        print_log("An error occured while creating table {schema}.{table_name}")
310
311#dim.dim_manufactures
312def datacreator_dim_manufactures(table_name="dim_manufactures", schema="dim"):
313
314    engine = create_engine_postgres_fko_spb_pg1()
315
316    try:
317        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
318        print_log(f"DROP TABLE {schema}.{table_name}")
319    except:
320        print_log(f"""Cannot drop table: {schema}.{table_name}""")
321
322    query_test = '''
323        CREATE TABLE dim.dim_manufactures (
324	    "Ссылка" text NULL,
325	    "ПометкаУдаления" bool NULL,
326	    "Владелец" text NULL,
327	    "Родитель" text NULL,
328	    "ЭтоГруппа" bool NULL,
329	    "Код" text NULL,
330	    "Наименование" text NULL,
331	    "ЧисловоеПредставление" float8 NULL,
332	    "Коэфициент1" float8 NULL,
333	    "Коэфициент2" float8 NULL,
334	    "ДоступнаПокупателю" bool NULL,
335	    "НаименованиеДляПокупателя" text NULL,
336	    "Лидирующая" bool NULL,
337	    "АнглоязычноеНаименование" text NULL,
338	    "Ссылка_source" text NULL,
339	    "Владелец_source" text NULL,
340	    "Родитель_source" text NULL
341    );
342        '''
343
344    print_log("Start create table for {schema}.{table_name}")
345    
346    try:
347        engine.execute(query_test)
348        print_log("{schema}.{table_name} table created successfully")
349    except:
350        print_log("An error occured while creating table {schema}.{table_name}")
351
352#dim.dim_products
353def datacreator_dim_products(table_name="dim_products", schema="dim"):
354
355    engine = create_engine_postgres_fko_spb_pg1()
356
357    try:
358        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
359        print_log(f"DROP TABLE {schema}.{table_name}")
360    except:
361        print_log(f"""Cannot drop table: {schema}.{table_name}""")
362
363    query_test = '''
364        CREATE TABLE dim.dim_products (
365	    "Ссылка" uuid NULL,
366	    "ПометкаУдаления" bool NULL,
367	    "Родитель" text NULL,
368	    "ЭтоГруппа" bool NULL,
369	    "Код" text NULL,
370	    "Наименование" text NULL,
371	    "ДатаСоздания" timestamp NULL,
372	    "НеИспользуется" text NULL,
373	    "ВидТовара" text NULL,
374	    "ПолнНаименование" text NULL,
375	    "СертификатРСТ" text NULL,
376	    "Гарантия" float8 NULL,
377	    "ГарантияНабор" float8 NULL,
378	    "ГарантияАСЦ" text NULL,
379	    "КодПроизводителя" text NULL,
380	    "Федеральный_Статус" text NULL,
381	    "РетэилФасовки" float8 NULL,
382	    "ВерхнийУровень" text NULL,
383	    "Комментарий" text NULL,
384	    "ДатаСнятияСПроизводства" timestamp NULL,
385	    "МинимальнаяПартия" float8 NULL,
386	    "Ассортиментный_Статус" text NULL,
387	    "СтатусЖизненногоЦикла" text NULL,
388	    "КратностьЗакупки" float8 NULL,
389	    "СрокГодности" float8 NULL,
390	    "СертификатНеТребуется" text NULL,
391	    "ИзделиеТовара" text NULL,
392	    "ПредназначенДляЮрЛица" text NULL,
393	    "Статус_Закупа" text NULL,
394	    "GTIN" text NULL,
395	    "Дубль" text NULL,
396	    "ДатаЗаписи" timestamp NULL,
397	    "ДатаНачалаЗапрета" timestamp NULL,
398	    "ДатаОкончанияЗапрета" timestamp NULL,
399	    "СтатусЦенообразования" text NULL,
400	    "НаименованиеДляСпецификации" text NULL,
401	    "ПринадлежностьDIY" float8 NULL,
402	    "Категория" uuid NULL,
403	    "Изделие" text NULL,
404	    "Ссылка_source" text NULL,
405	    "Родитель_source" text NULL,
406	    "СертификатРСТ_source" text NULL,
407	    "ИзделиеТовара_source" text NULL,
408	    "ПредназначенДляЮрЛица_source" text NULL,
409	    "Дубль_source" text NULL,
410	    "Категория_source" text NULL,
411	    "Изделие_source" text NULL
412    );
413        '''
414
415    print_log("Start create table for {schema}.{table_name}")
416    
417    try:
418        engine.execute(query_test)
419        print_log("{schema}.{table_name} table created successfully")
420    except:
421        print_log("An error occured while creating table {schema}.{table_name}")
422
423#dim.dim_products_matrix
424def datacreator_dim_products_matrix(table_name="dim_products_matrix", schema="dim"):
425
426    engine = create_engine_postgres_fko_spb_pg1()
427
428    try:
429        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
430        print_log(f"DROP TABLE {schema}.{table_name}")
431    except:
432        print_log(f"""Cannot drop table: {schema}.{table_name}""")
433
434    query_test = '''
435       CREATE TABLE dim.dim_products_matrix (
436    	"Ссылка" text NULL,
437    	"ПометкаУдаленния" bool NULL,
438    	"Код" text NULL,
439    	"Наименование" text NULL,
440    	"Категория" text NULL,
441    	"Товар" uuid NULL,
442    	"КоличествоТоваров" float8 NULL,
443    	"Порядок" float8 NULL,
444    	"ДатаСоздания" timestamp NULL,
445    	"ДатаВводаВМатрицу" timestamp NULL,
446    	"ДатаВыводаИзМатрицы" timestamp NULL,
447    	"НаименованиеДляПоставщиков" text NULL,
448    	"Статус" text NULL,
449    	"Комментарий" text NULL,
450    	"РаспределятьСверхЁмкости" bool NULL,
451    	"ВидСегмента" text NULL,
452    	"Ссылка_source" text NULL,
453    	"Категория_source" text NULL,
454    	"Товар_source" text NULL
455    );
456        '''
457
458    print_log("Start create table for {schema}.{table_name}")
459    
460    try:
461        engine.execute(query_test)
462        print_log("{schema}.{table_name} table created successfully")
463    except:
464        print_log("An error occured while creating table {schema}.{table_name}")
465
466#dim.dim_territories
467def datacreator_dim_territories(table_name="dim_territories", schema="dim"):
468
469    engine = create_engine_postgres_fko_spb_pg1()
470
471    try:
472        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
473        print_log(f"DROP TABLE {schema}.{table_name}")
474    except:
475        print_log(f"""Cannot drop table: {schema}.{table_name}""")
476
477    query_test = '''
478        CREATE TABLE dim.dim_territories (
479    	"Ссылка" uuid NULL,
480    	"ПометкаУдаления" bool NULL,
481    	"Родитель" uuid NULL,
482    	"Код" text NULL,
483    	"Наименование" text NULL,
484    	"ОтветственныйЗаТерриторию" uuid NULL,
485    	"КороткоеИмяДляРассылкиAD" text NULL,
486    	"Администрация" uuid NULL,
487    	"Ссылка_source" text NULL,
488    	"Родитель_source" text NULL,
489    	"ОтветственныйЗаТерриторию_source" text NULL,
490    	"Администрация_source" text NULL
491    );
492    CREATE INDEX "dim_territories_ссылка_idx" ON dim.dim_territories USING btree ("Ссылка");
493        '''
494
495    print_log("Start create table for {schema}.{table_name}")
496    
497    try:
498        engine.execute(query_test)
499        print_log("{schema}.{table_name} table created successfully")
500    except:
501        print_log("An error occured while creating table {schema}.{table_name}")
502
503#dim.dim_territory
504def datacreator_dim_territory(table_name="dim_territory", schema="dim"):
505
506    engine = create_engine_postgres_fko_spb_pg1()
507
508    try:
509        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
510        print_log(f"DROP TABLE {schema}.{table_name}")
511    except:
512        print_log(f"""Cannot drop table: {schema}.{table_name}""")
513
514    query_test = '''
515        CREATE TABLE dim.dim_territory (
516    	id int2 NOT NULL,
517    	source_id bytea NOT NULL,
518    	parent_id int2 NULL,
519    	source_parent_id bytea NULL,
520    	"name" varchar(50) NULL,
521    	code varchar(7) NULL,
522    	staff_id int4 NULL,
523    	is_deleted bool NULL,
524    	"_dateload" timestamp(6) NULL,
525    	poligon bytea NULL
526    );
527        '''
528
529    print_log("Start create table for {schema}.{table_name}")
530    
531    try:
532        engine.execute(query_test)
533        print_log("{schema}.{table_name} table created successfully")
534    except:
535        print_log("An error occured while creating table {schema}.{table_name}")
536
537#dim.dim_сities
538def datacreator_dim_сities(table_name="dim_сities", schema="dim"):
539
540    engine = create_engine_postgres_fko_spb_pg1()
541
542    try:
543        engine.execute(f"""DROP TABLE IF EXISTS {schema}.{table_name}""")
544        print_log(f"DROP TABLE {schema}.{table_name}")
545    except:
546        print_log(f"""Cannot drop table: {schema}.{table_name}""")
547
548    query_test = '''
549            CCREATE TABLE dim.dim_сities (
550	        "Ссылка" uuid NULL,
551	        "ПометкаУдаления" bool NULL,
552	        "Родитель" uuid NULL,
553	        "Код" float8 NULL,
554	        "Наименование" text NULL,
555	        "Регион" uuid NULL,
556	        "КоэффициентКОкладу" float8 NULL,
557	        "БезнальнаяНадбавка" float8 NULL,
558	        "РайонныйКоэффициент" float8 NULL,
559	        "СевернаяНадбавка" float8 NULL,
560	        "ЭкологическаяНадбавка" float8 NULL,
561	        "КоличествоДнейОтпускаВГод" float8 NULL,
562	        "GMT" float8 NULL,
563	        "МРОТ" float8 NULL,
564	        "ПрожиточныйМинимум" float8 NULL,
565	        "Страна" uuid NULL,
566	        "ДнейРасчетаПутей" float8 NULL,
567	        "Долгота" float8 NULL,
568	        "Широта" float8 NULL,
569	        "ВремяПереносаЗаказа" float8 NULL,
570	        "ДоставкаЗапущена" bool NULL,
571	        "АнглоязычноеНаименование" text NULL,
572	        "ТерриториальноеРасположение" uuid NULL,
573	        "Ссылка_source" text NULL,
574	        "Родитель_source" text NULL,
575	        "Регион_source" text NULL,
576	        "Страна_source" text NULL,
577	        "ТерриториальноеРасположение_source" text NULL
578    );
579    CREATE INDEX "dim_сities_ссылка_idx" ON dim."dim_сities" USING btree ("Ссылка");
580    CREATE INDEX "dim_сities_территориальноерасположен" ON dim."dim_сities" USING btree ("ТерриториальноеРасположение");
581        '''
582
583    print_log("Start create table for {schema}.{table_name}")
584    
585    try:
586        engine.execute(query_test)
587        print_log("{schema}.{table_name} table created successfully")
588    except:
589        print_log("An error occured while creating table {schema}.{table_name}")