· 4 years ago · Dec 16, 2020, 05:18 PM
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 DEFAULT 0,
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
146import numpy as np
147from scipy import stats
148from faker import Faker
149from psycopg2.extensions import register_adapter, AsIs
150psycopg2.extensions.register_adapter(np.int64, psycopg2._psycopg.AsIs)
151psycopg2.extensions.register_adapter(np.int32, psycopg2._psycopg.AsIs)
152
153data = pd.read_csv('D:/Datasets/cars/cars.csv')
154
155brand = data['manufacturer_name'].values
156model = data['model_name'].values
157transmission = data['transmission'].values
158color = data['color'].values
159year = data['year_produced'].values.astype(int)
160kilometrage = data['odometer_value'].values.astype(int)
161price = data['price_usd'].values.astype(int)
162region = data['location_region'].values
163type_car = data['body_type'].values
164engine = stats.uniform.rvs(loc=170, scale=200, size=38531, random_state=20).astype(int)
165
166fake = Faker()
167name = []
168telephone = []
169email = []
170date = []
171comment = []
172condition = []
173for _ in range(38531):
174 name.append(fake.name())
175 telephone.append(fake.phone_number())
176 email.append(fake.email())
177 date.append(fake.date())
178 comment.append(fake.word())
179 condition.append(fake.word())
180
181id_type_t_list = list(data['body_type'].map({'universal': 0, 'suv': 1, 'sedan': 2, 'hatchback': 3, 'liftback': 4, 'minivan': 5, 'minibus': 6, 'van': 7, 'pickup': 8, 'coupe': 9, 'cabriolet': 10, 'limousine': 11}))
182id_type_t_set = [i for i in range(12)]
183types_cars = ['universal', 'suv', 'sedan', 'hatchback', 'liftback', 'minivan', 'minibus', 'van', 'pickup', 'coupe', 'cabriolet', 'limousine']
184type_user = ['частный владелец', 'автосалон']
185id_type_u_set = [0, 1]
186id_type_u_list = [0] * 38531
187city_list = data['location_region'].map({'Минская обл.': 'Минск', 'Гомельская обл.': 'Гомель', 'Витебская обл.': 'Витебск', 'Брестская обл.': 'Брест', 'Могилевская обл.': 'Могилев', 'Гродненская обл.': 'Гродно'})
188location_id_list = data['location_region'].map({'Минская обл.': 0, 'Гомельская обл.': 1, 'Витебская обл.': 2, 'Брестская обл.': 3, 'Могилевская обл.': 4, 'Гродненская обл.': 5})
189location_id_set = [i for i in range(6)]
190city_set = ['Минск', 'Гомель', 'Витебск', 'Брест', 'Могилев', 'Гродно']
191region_set = ['Минская обл.', 'Гомельская обл.', 'Витебская обл.', 'Брестская обл.', 'Могилевская обл.', 'Гродненская обл.']
192wheel = ['правый'] * 38500 + ['левый'] * 31
193buyer_id = [i for i in range(38530, -1, -1)]
194
195"""-------------------------------------------------------"""
196"""Заполняем таблицу TRANSPORT"""
197transport = []
198for i in range(38531):
199 values = (i, brand[i], model[i], year[i], kilometrage[i], color[i], engine[i], transmission[i], wheel[i], condition[i])
200 transport.append(values)
201
202transport_records = ", ".join(["%s"] * len(transport))
203
204insert_query = (
205 f"INSERT INTO transport (id_car, brand, model, release_year, kilometrage, color, engine, transmission, wheel, condition) VALUES {transport_records}"
206)
207
208connection.autocommit = True
209cursor = connection.cursor()
210cursor.execute(insert_query, transport)
211
212"""-------------------------------------------------------"""
213"""Заполняем таблицу TYPE_T"""
214type_t = []
215for i in range(len(id_type_t_set)):
216 values = (id_type_t_set[i], types_cars[i])
217 type_t.append(values)
218
219type_t_records = ", ".join(["%s"] * len(type_t))
220
221insert_query = (
222 f"INSERT INTO type_t (id_type_t, type) VALUES {type_t_records}"
223)
224
225connection.autocommit = True
226cursor = connection.cursor()
227cursor.execute(insert_query, type_t)
228
229"""-------------------------------------------------------"""
230"""Заполняем таблицу USERS"""
231users = []
232for i in range(38531):
233 values = (i, name[i], telephone[i], email[i])
234 users.append(values)
235
236
237users_records = ", ".join(["%s"] * len(users))
238
239insert_query = (
240 f"INSERT INTO users (id_user, name, telephone, email) VALUES {users_records}"
241)
242
243connection.autocommit = True
244cursor = connection.cursor()
245cursor.execute(insert_query, users)
246
247"""-------------------------------------------------------"""
248"""Заполняем таблицу LOCATION"""
249location = []
250for i in range(len(location_id_set)):
251 values = (location_id_set[i], region_set[i], city_set[i])
252 location.append(values)
253
254
255location_records = ", ".join(["%s"] * len(location))
256
257insert_query = (
258 f"INSERT INTO location (location_id, region, city) VALUES {location_records}"
259)
260
261connection.autocommit = True
262cursor = connection.cursor()
263cursor.execute(insert_query, location)
264
265"""-------------------------------------------------------"""
266"""Заполняем таблицу TYPE_U"""
267type_u = []
268for i in range(len(id_type_u_set)):
269 values = (id_type_u_set[i], type_user[i])
270 type_u.append(values)
271
272
273type_u_records = ", ".join(["%s"] * len(type_u))
274
275insert_query = (
276 f"INSERT INTO type_u (id_type_u, type) VALUES {type_u_records}"
277)
278
279connection.autocommit = True
280cursor = connection.cursor()
281cursor.execute(insert_query, type_u)
282
283"""-------------------------------------------------------"""
284"""Заполняем таблицу TRANSPORT_TYPE"""
285transport_type = []
286for i in range(38531):
287 values = (i, id_type_t_list[i])
288 transport_type.append(values)
289
290
291transport_type_records = ", ".join(["%s"] * len(transport_type))
292
293insert_query = (
294 f"INSERT INTO transport_type (id_car, id_type_t) VALUES {transport_type_records}"
295)
296
297connection.autocommit = True
298cursor = connection.cursor()
299cursor.execute(insert_query, transport_type)
300
301
302"""-------------------------------------------------------"""
303"""Заполняем таблицу SELL_CARS"""
304sell_cars = []
305for i in range(38531):
306 values = (i, i, i, price[i], location_id_list[i], comment[i])
307 sell_cars.append(values)
308
309
310sell_cars_records = ", ".join(["%s"] * len(sell_cars))
311
312insert_query = (
313 f"INSERT INTO sell_cars (id_ad, id_car, id_user, price, location_id, comment) VALUES {sell_cars_records}"
314)
315
316connection.autocommit = True
317cursor = connection.cursor()
318cursor.execute(insert_query, sell_cars)
319
320"""-------------------------------------------------------"""
321"""Заполняем таблицу DEAL"""
322deal = []
323for i in range(38531):
324 values = (i, buyer_id[i], date[i], price[i])
325 deal.append(values)
326
327
328deal_records = ", ".join(["%s"] * len(deal))
329
330insert_query = (
331 f"INSERT INTO deal (id_ad, buyer_id, date, amount) VALUES {deal_records}"
332)
333
334connection.autocommit = True
335cursor = connection.cursor()
336cursor.execute(insert_query, deal)
337
338"""-------------------------------------------------------"""
339"""Заполняем таблицу TYPE_USER"""
340type_user = []
341for i in range(38531):
342 values = (i, id_type_u_list[i])
343 type_user.append(values)
344
345
346type_user_records = ", ".join(["%s"] * len(type_user))
347
348insert_query = (
349 f"INSERT INTO type_user (id_user, id_type_u) VALUES {type_user_records}"
350)
351
352connection.autocommit = True
353cursor = connection.cursor()
354cursor.execute(insert_query, type_user)
355
356"""-------------------------------------------------------"""