import csv import pymysql import re from datetime import datetime, timedelta # --- Настройки подключения к БД --- conn = pymysql.connect( host='localhost', user='b2b_leads', password='v01lEHeGIdJUXSjQ', database='b2b_leads', charset='utf8mb4' ) cursor = conn.cursor() # --- Вспомогательные функции --- def normalize_phone(phone): digits = re.sub(r'\D', '', phone) if digits.startswith('89'): digits = '7' + digits[1:] elif digits.startswith('9') and len(digits) == 10: digits = '7' + digits return digits if len(digits) >= 10 else None def normalize_email(email): email = email.strip().lower() return email if re.match(r"[^@\s]+@[^@\s]+\.[^@\s]+", email) else None def excel_date_to_iso(val): try: base = datetime(1899, 12, 30) return (base + timedelta(days=int(val))).date().isoformat() except: return None def insert_or_get_id(table, column, value): cursor.execute(f"SELECT id FROM {table} WHERE {column} = %s", (value,)) result = cursor.fetchone() if result: return result[0] cursor.execute(f"INSERT INTO {table} ({column}) VALUES (%s)", (value,)) conn.commit() return cursor.lastrowid def link_m2m(link_table, left_id_name, right_id_name, left_id, right_id): cursor.execute(f"REPLACE INTO {link_table} ({left_id_name}, {right_id_name}) VALUES (%s, %s)", (left_id, right_id)) # --- Загрузка CSV --- with open('VB4.csv', newline='', encoding='utf-8-sig') as csvfile: reader = csv.DictReader(csvfile, delimiter=';') for i, row in enumerate(reader): # --- Компания --- company_name = row['Наименование'].strip() inn = row['ИНН'].strip() ogrn = row['ОГРН'].strip() ogrn = '{:.0f}'.format(float(ogrn.replace(',', '.'))) if 'E+' in ogrn else ogrn company_reg_date = excel_date_to_iso(row['Дата регистрации компании']) address = row['Адрес (место нахождения)'].strip() region = row['Регион регистрации'].strip() activity = row['Вид деятельности/отрасль'].strip() activity_code = row['Код основного вида деятельности'].strip() legal_form = row['Организационно-правовая форма'].strip() owner_info = row['Совладельцы, Приоритетный'].strip() capital = row['Уставный капитал, RUB'].replace(',', '').strip() or None revenue = row['2023, Выручка, RUB'].replace(',', '').strip() or None profit = row['2023, Чистая прибыль (убыток), RUB'].replace(',', '').strip() or None cursor.execute(""" INSERT INTO companies (name, inn, ogrn, registration_date, address, region, legal_form, owner_info, authorized_capital, revenue_2023, profit_2023) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE name = IF(VALUES(name) != '' AND VALUES(name) IS NOT NULL, VALUES(name), name), ogrn = IF(VALUES(ogrn) != '' AND VALUES(ogrn) IS NOT NULL, VALUES(ogrn), ogrn), registration_date = IF(VALUES(registration_date) IS NOT NULL, VALUES(registration_date), registration_date), address = IF(VALUES(address) != '' AND VALUES(address) IS NOT NULL, VALUES(address), address), region = IF(VALUES(region) != '' AND VALUES(region) IS NOT NULL, VALUES(region), region), legal_form = IF(VALUES(legal_form) != '' AND VALUES(legal_form) IS NOT NULL, VALUES(legal_form), legal_form), owner_info = IF(VALUES(owner_info) != '' AND VALUES(owner_info) IS NOT NULL, VALUES(owner_info), owner_info), authorized_capital = IF(VALUES(authorized_capital) IS NOT NULL, VALUES(authorized_capital), authorized_capital), revenue_2023 = IF(VALUES(revenue_2023) IS NOT NULL, VALUES(revenue_2023), revenue_2023), profit_2023 = IF(VALUES(profit_2023) IS NOT NULL, VALUES(profit_2023), profit_2023), id=LAST_INSERT_ID(id) """, (company_name, inn, ogrn, company_reg_date, address, region, legal_form, owner_info, capital, revenue, profit)) company_id = cursor.lastrowid # --- ОКВЭД --- if activity_code: okved_id = insert_or_get_id('okved', 'code', activity_code) cursor.execute("UPDATE okved SET description = %s WHERE id = %s", (activity, okved_id)) link_m2m('company_okveds', 'company_id', 'okved_id', company_id, okved_id) # --- Продавец --- wb_url = row['Страница на сайте Wildberries'].strip() seller_reg_date = row['Дата регистрации на Wildberries'].strip() seller_reg_date = excel_date_to_iso(seller_reg_date) if seller_reg_date else None rating = row['Рейтинг продавца'].strip() or None reviews = row['Количество отзывов'].strip() or None sold = row['Проданных товаров'].strip() or None cursor.execute(""" SELECT id FROM marketplace_sellers WHERE platform = 'wb' AND wb_url = %s """, (wb_url,)) result = cursor.fetchone() if result: seller_id = result[0] cursor.execute(""" UPDATE marketplace_sellers SET company_id = %s, registration_on_wb = IF(%s != '', %s, registration_on_wb), rating = IF(%s IS NOT NULL, %s, rating), reviews_count = IF(%s IS NOT NULL, %s, reviews_count), products_sold = IF(%s IS NOT NULL, %s, products_sold) WHERE id = %s """, (company_id, seller_reg_date, seller_reg_date, rating, rating, reviews, reviews, sold, sold, seller_id)) else: cursor.execute(""" INSERT INTO marketplace_sellers (company_id, platform, wb_url, registration_on_wb, rating, reviews_count, products_sold) VALUES (%s, 'wb', %s, %s, %s, %s, %s) """, (company_id, wb_url, seller_reg_date, rating, reviews, sold)) seller_id = cursor.lastrowid # --- Категории товаров --- categories_raw = row['Разделы товаров'].strip() if categories_raw: for cat in categories_raw.split(';'): cat_name = cat.strip() if cat_name: cat_id = insert_or_get_id('product_categories', 'name', cat_name) link_m2m('seller_product_categories', 'seller_id', 'category_id', seller_id, cat_id) # --- Бренды --- brands_raw = row['Бренды продавца'].strip() if brands_raw: for brand in brands_raw.split(';'): brand_name = brand.strip() if brand_name: brand_id = insert_or_get_id('brands', 'name', brand_name) link_m2m('seller_brands', 'seller_id', 'brand_id', seller_id, brand_id) # --- Контакт --- contact_name = row['Руководитель - ФИО'].strip() contact_position = row['Руководитель - должность'].strip() cursor.execute(""" SELECT id FROM contacts WHERE entity_type = 'company' AND entity_id = %s AND name = %s """, (company_id, contact_name)) contact = cursor.fetchone() if contact: contact_id = contact[0] else: cursor.execute(""" INSERT INTO contacts (entity_type, entity_id, name, position) VALUES ('company', %s, %s, %s) """, (company_id, contact_name, contact_position)) contact_id = cursor.lastrowid # --- Телефоны --- phones_raw = ','.join([row.get(k, '') for k in ['Телефоны', 'Сотовые телефоны', 'Телефоны источник 2', 'Сотовые телефоны источник 2']]) for phone in phones_raw.split(','): norm_phone = normalize_phone(phone) if norm_phone: phone_id = insert_or_get_id('phones', 'number', norm_phone) link_m2m('contact_phones', 'contact_id', 'phone_id', contact_id, phone_id) # --- Email --- emails_raw = ','.join([row.get(k, '') for k in ['Email', 'Email источник 2']]) for email in emails_raw.split(','): norm_email = normalize_email(email) if norm_email: email_id = insert_or_get_id('emails', 'email', norm_email) link_m2m('contact_emails', 'contact_id', 'email_id', contact_id, email_id) if i % 50 == 0: print(f"Обработано строк: {i}") conn.commit() conn.commit() print("Импорт завершен!")