""" Скрипт для добавления объектов недвижимости из pars_samolet.sql в базу данных Render """ import psycopg2 import re import sqlite3 import uuid import sys from datetime import datetime # Данные подключения к БД на Render DB_CONFIG = { 'host': 'dpg-d5ht8vi4d50c739akh2g-a.virginia-postgres.render.com', 'port': 5432, 'database': 'lead_exchange_bk', 'user': 'lead_exchange_bk_user', 'password': '8m2gtTRBW0iAr7nY2Aadzz0VcZBEVKYM' } # ID администратора (который мы создали ранее) ADMIN_USER_ID = None # Будет получен из БД def get_admin_user_id(conn): """Получить ID администратора из БД""" cursor = conn.cursor() # Получаем всех администраторов cursor.execute(""" SELECT user_id, email, first_name, last_name FROM users WHERE role = 'ADMIN' ORDER BY created_at DESC """) admins = cursor.fetchall() if not admins: raise Exception("Admin user not found in database. Please run add_admin_user.py first.") if len(admins) == 1: admin_id, email, first_name, last_name = admins[0] print(f" Found admin: {email} ({first_name} {last_name})") return str(admin_id) # Если несколько админов, показываем их и выбираем последнего созданного (самый новый) print(f" Found {len(admins)} admin users:") for admin_id, email, first_name, last_name in admins: print(f" - {email} ({first_name} {last_name}) - ID: {admin_id}") # Используем самого нового администратора (первый в списке, т.к. ORDER BY created_at DESC) selected_admin = admins[0] print(f" ✅ Using: {selected_admin[1]}") return str(selected_admin[0]) def parse_sql_file(): """Парсинг SQL файла с объектами недвижимости""" print("Reading pars_samolet.sql...") with open('pars_samolet.sql', 'r', encoding='utf-8') as f: sql_content = f.read() # Извлекаем CREATE TABLE create_match = re.search(r'CREATE TABLE[^;]+;', sql_content, re.DOTALL) if not create_match: raise ValueError("CREATE TABLE not found") create_stmt = create_match.group(0) # Извлекаем все INSERT insert_pattern = r'INSERT INTO mytable\([^)]+\) VALUES\s*\([^;]+\);' inserts = re.findall(insert_pattern, sql_content, re.DOTALL) print(f"Found {len(inserts)} INSERT statements") # Создаем временную базу для парсинга conn = sqlite3.connect(':memory:') cursor = conn.cursor() cursor.execute(create_stmt) # Выполняем INSERT successful = 0 for i, insert in enumerate(inserts): try: cursor.execute(insert) successful += 1 except sqlite3.Error as e: print(f"Warning: Could not parse INSERT #{i+1}: {e}") continue print(f"Successfully parsed {successful}/{len(inserts)} records") # Получаем данные cursor.execute('SELECT * FROM mytable') rows = cursor.fetchall() # Получаем имена колонок cursor.execute("PRAGMA table_info(mytable)") columns = [col[1] for col in cursor.fetchall()] # Создаем список словарей objects = [dict(zip(columns, row)) for row in rows] conn.close() return objects def map_property_type(old_type): """Маппинг типов недвижимости""" mapping = { 'Квартира': 'APARTMENT', 'Дом': 'HOUSE', 'Коммерческая': 'COMMERCIAL', 'Участок': 'LAND' } return mapping.get(old_type, 'APARTMENT') def map_status(old_status): """Маппинг статусов""" mapping = { 'Доступно': 'PUBLISHED', 'Продано': 'SOLD', 'Архив': 'ARCHIVED' } return mapping.get(old_status, 'PUBLISHED') def insert_properties(objects, owner_user_id, auto_confirm=False): """Вставка объектов недвижимости в БД""" print(f"\nConnecting to database at {DB_CONFIG['host']}...") try: conn = psycopg2.connect(**DB_CONFIG) cursor = conn.cursor() print("Connected successfully!") # Проверяем, есть ли уже данные cursor.execute("SELECT COUNT(*) FROM properties") existing_count = cursor.fetchone()[0] print(f"Current properties in database: {existing_count}") if existing_count > 0: if auto_confirm: print(f"\nAuto-confirm enabled: Deleting {existing_count} existing properties...") cursor.execute("DELETE FROM properties") conn.commit() print("Deleted existing properties") else: try: response = input(f"\nDatabase already has {existing_count} properties. Delete them? (yes/y/no/n): ") if response.lower() in ['yes', 'y']: cursor.execute("DELETE FROM properties") conn.commit() print("Deleted existing properties") except EOFError: print("\n⚠️ Input interrupted. Keeping existing properties.") print("Run with --yes flag to auto-confirm or run interactively.") # Вставляем объекты inserted = 0 failed = 0 insert_query = """ INSERT INTO properties ( property_id, title, description, address, property_type, area, price, rooms, status, owner_user_id, created_user_id, created_at, updated_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) """ print(f"\nInserting {len(objects)} properties...") for i, obj in enumerate(objects): try: # Подготавливаем данные property_id = obj.get('property_id', str(uuid.uuid4())) title = obj.get('title', '')[:255] # Ограничиваем длину description = obj.get('description', '') address = obj.get('address', '') property_type = map_property_type(obj.get('property_type', 'Квартира')) area = float(obj['area']) if obj.get('area') else None price = int(obj['price']) if obj.get('price') else None rooms = int(obj['rooms']) if obj.get('rooms') else None status = map_status(obj.get('status', 'Доступно')) # Даты created_at = obj.get('created_at', datetime.now().isoformat()) updated_at = obj.get('updated_at', datetime.now().isoformat()) cursor.execute(insert_query, ( property_id, title, description, address, property_type, area, price, rooms, status, owner_user_id, owner_user_id, created_at, updated_at )) inserted += 1 if (i + 1) % 50 == 0: print(f" Inserted {i + 1}/{len(objects)}...") except Exception as e: failed += 1 print(f" Failed to insert property {obj.get('property_id', 'unknown')}: {e}") continue # Коммитим изменения conn.commit() print(f"\n✅ Successfully inserted {inserted} properties") if failed > 0: print(f"⚠️ Failed to insert {failed} properties") # Проверяем финальное количество cursor.execute("SELECT COUNT(*) FROM properties") final_count = cursor.fetchone()[0] print(f"\nTotal properties in database: {final_count}") cursor.close() conn.close() except psycopg2.Error as e: print(f"❌ Database error: {e}") raise except Exception as e: print(f"❌ Error: {e}") raise def main(): print("=" * 60) print("Adding Properties to Database") print("=" * 60) # Проверяем параметры командной строки auto_confirm = '--yes' in sys.argv or '-y' in sys.argv if auto_confirm: print("🤖 Auto-confirm mode enabled") # Парсим SQL файл objects = parse_sql_file() if not objects: print("No objects found in pars_samolet.sql") return print(f"\nParsed {len(objects)} properties from file") print(f"Sample property: {objects[0].get('title', 'N/A')}") # Подключаемся к БД и получаем ID администратора print("\nGetting admin user ID...") try: conn = psycopg2.connect(**DB_CONFIG) admin_id = get_admin_user_id(conn) conn.close() print(f"Admin user ID: {admin_id}") except Exception as e: print(f"❌ Error: {e}") return # Подтверждение if not auto_confirm: print(f"\nReady to insert {len(objects)} properties into database") print(f"Database: {DB_CONFIG['host']}/{DB_CONFIG['database']}") try: response = input("\nProceed? (yes/y/no/n): ") if response.lower() not in ['yes', 'y']: print("Cancelled by user") return except EOFError: print("\n❌ Error: EOF when reading input") print("Run with --yes flag to auto-confirm: python add_properties.py --yes") return else: print(f"\n✅ Auto-confirming insertion of {len(objects)} properties") # Вставляем объекты insert_properties(objects, admin_id, auto_confirm) print("\n" + "=" * 60) print("✅ Done!") print("=" * 60) if __name__ == '__main__': main()