k5fzYpmi

1import psycopg2
2from psycopg2 import OperationalError
3
4
5def create_connection(db_name, db_user, db_password, db_host, db_port):
6    connection = None
7    try:
8        connection = psycopg2.connect(
9            database=db_name,
10            user=db_user,
11            password=db_password,
12            host=db_host,
13            port=db_port,
14        )
15        print("Connection to PostgreSQL DB successful")
16    except OperationalError as e:
17        print(f"The error '{e}' occurred")
18    return connection
19
20connection = create_connection("autoru", "postgres", "0000", "localhost", "5432")
21
22
23def execute_query(connection, query):
24    """Функция для организации таблиц, вставки, изменения и удаления записей в базе данных PostgreSQL"""
25    connection.autocommit = True
26    cursor = connection.cursor()
27    try:
28        cursor.execute(query)
29        print("Query executed successfully")
30    except OperationalError as e:
31        print(f"The error '{e}' occurred")
32
33
34create_transport_table = """
35    CREATE TABLE IF NOT EXISTS transport (
36    id_car INTEGER PRIMARY KEY,
37    brand VARCHAR(60) NOT NULL, 
38    model VARCHAR(60) NOT NULL,
39    release_year INTEGER NOT NULL,
40    kilometrage INTEGER NOT NULL,
41    color VARCHAR(60) NOT NULL,
42    engine INTEGER NOT NULL,
43    transmission VARCHAR(60),
44    wheel VARCHAR(60),
45    condition VARCHAR(255) 
46    )"""
47
48execute_query(connection, create_transport_table)
49
50"""-------------------------------------------------------"""
51
52create_type_t_table = """
53    CREATE TABLE IF NOT EXISTS type_t (
54    id_type_t INTEGER PRIMARY KEY,
55    type VARCHAR(60) NOT NULL
56    )"""
57
58execute_query(connection, create_type_t_table)
59
60"""-------------------------------------------------------"""
61
62create_user_table = """
63    CREATE TABLE IF NOT EXISTS users (
64    id_user INTEGER PRIMARY KEY,
65    name VARCHAR(60) NOT NULL,
66    telephone VARCHAR(60) NOT NULL,
67    email VARCHAR(60) NOT NULL 
68    )"""
69
70execute_query(connection, create_user_table)
71
72"""-------------------------------------------------------"""
73
74create_location_table = """
75    CREATE TABLE IF NOT EXISTS location (
76    location_id INTEGER PRIMARY KEY,
77    region VARCHAR(60) NOT NULL,
78    city VARCHAR(60) NOT NULL 
79    )"""
80
81execute_query(connection, create_location_table)
82
83"""-------------------------------------------------------"""
84
85create_type_u_table = """
86    CREATE TABLE IF NOT EXISTS type_u (
87    id_type_u INTEGER PRIMARY KEY,
88    type VARCHAR(60) NOT NULL
89    )"""
90
91execute_query(connection, create_type_u_table)
92
93"""-------------------------------------------------------"""
94
95create_transport_type_table = """
96CREATE TABLE IF NOT EXISTS transport_type (
97  id_car INTEGER REFERENCES transport(id_car),
98  id_type_t INTEGER REFERENCES type_t(id_type_t) 
99)
100"""
101
102execute_query(connection, create_transport_type_table)
103
104"""-------------------------------------------------------"""
105
106create_sell_cars_table = """
107CREATE TABLE IF NOT EXISTS sell_cars (
108  id_ad INTEGER PRIMARY KEY,
109  id_car INTEGER REFERENCES transport(id_car),
110  id_user INTEGER REFERENCES users(id_user),
111  price MONEY NOT NULL,
112  discount INT NOT NULL,
113  location_id INTEGER REFERENCES location(location_id),
114  comment TEXT
115)
116"""
117
118execute_query(connection, create_sell_cars_table)
119
120"""-------------------------------------------------------"""
121
122create_deal_table = """
123CREATE TABLE IF NOT EXISTS deal (
124  id_ad INTEGER PRIMARY KEY,
125  buyer_id INTEGER REFERENCES users(id_user),
126  date DATE NOT NULL,
127  amount MONEY NOT NULL
128)
129"""
130
131execute_query(connection, create_deal_table)
132
133"""-------------------------------------------------------"""
134
135create_type_user_table = """
136CREATE TABLE IF NOT EXISTS type_user (
137  id_user INTEGER REFERENCES users(id_user),
138  id_type_u INTEGER REFERENCES type_u(id_type_u)
139)
140"""
141
142execute_query(connection, create_type_user_table)
143
144"""-------------------------------------------------------"""
145import pandas as pd
146from scipy import stats
147from faker import Faker
148
149data = pd.read_csv('D:/Datasets/cars/cars.csv')
150
151brand = data['manufacturer_name'].values
152model = data['model_name'].values
153transmission = data['transmission'].values
154color = data['color'].values
155year = data['year_produced'].values
156kilometrage = data['odometer_value'].values
157price = data['price_usd'].astype('int')
158region = data['location_region'].values
159type_car = data['body_type'].values
160engine = stats.uniform.rvs(loc=170, scale=200, size=38530, random_state=20).astype('int')
161
162
163fake = Faker()
164name = []
165telephone = []
166email = []
167date = []
168comment = []
169for _ in range(38531):
170    name.append(fake.name())
171    telephone.append(fake.phone_number())
172    email.append(fake.email())
173    date.append(fake.date())
174    comment.append(fake.word())
175
176id_type_t = data['body_type'].map({'sedan': 0, 'hatchback': 1, 'universal': 2, 'suv': 3, 'minivan': 4})
177type_user = ['частный владелец'] * 38530 + ['автосалон']
178id_type_u = [0] * 38530
179city = data['location_region'].map({'Минская обл.': 'Минск', 'Гомельская обл.': 'Гомель', 'Витебская обл.': 'Витебск', 'Брестская обл.': 'Брест', 'Могилевская обл.': 'Могилев'})
180location_id = data['location_region'].map({'Минская обл.': 0, 'Гомельская обл.': 1, 'Витебская обл.':2, 'Брестская обл.':3, 'Могилевская обл.':4})
181wheel = ['правый'] * 38500 + ['левый'] * 30