You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
111 lines
3.3 KiB
111 lines
3.3 KiB
import pandas as pd
|
|
import psycopg2 as psy
|
|
from getpass import getpass
|
|
|
|
data = pd.read_csv(r'./csv/carDetails.csv')
|
|
carBrand = pd.read_csv(r'./csv/carBrand.csv')
|
|
peopleBrand = pd.read_csv(r'./csv/brandWorkers.csv')
|
|
df = pd.DataFrame(data)
|
|
dfBrand = pd.DataFrame(carBrand)
|
|
dfPeople = pd.DataFrame(peopleBrand)
|
|
df = df.drop_duplicates()
|
|
dfBrand = dfBrand.drop_duplicates()
|
|
|
|
fichier = open("login.txt","r")
|
|
|
|
login = fichier.read()
|
|
print(login)
|
|
|
|
co = None
|
|
|
|
try:
|
|
co = psy. connect(
|
|
database ='db'+login,
|
|
user=login)
|
|
|
|
curs = co.cursor()
|
|
|
|
curs.execute ('''
|
|
DROP TABLE IF EXISTS Car, Brand, Model, Person CASCADE;
|
|
|
|
CREATE TABLE Brand (
|
|
Name varchar(80) PRIMARY KEY,
|
|
CreationDate numeric,
|
|
Headquarter varchar(80)
|
|
);
|
|
|
|
CREATE TABLE Model (
|
|
idModel numeric PRIMARY KEY,
|
|
Name varchar(120),
|
|
FuelType varchar(50),
|
|
Transmission varchar(50),
|
|
Engine varchar(32),
|
|
MaxPower varchar(32),
|
|
MaxTorque varchar(32),
|
|
Drivetrain varchar(5),
|
|
Length numeric,
|
|
Width numeric,
|
|
Height numeric,
|
|
SeatingCapacity numeric,
|
|
FuelTankCapacity numeric,
|
|
Brand varchar(80),
|
|
FOREIGN KEY (Brand) REFERENCES Brand(Name)
|
|
);
|
|
|
|
CREATE TABLE Car (
|
|
idCar numeric PRIMARY KEY,
|
|
Price numeric,
|
|
Color varchar(32),
|
|
Kilometer numeric,
|
|
Year numeric,
|
|
Owner varchar(30),
|
|
SellerType varchar(30),
|
|
Model numeric,
|
|
FOREIGN KEY (Model) REFERENCES Model(idModel)
|
|
);
|
|
|
|
CREATE TABLE Person (
|
|
id numeric PRIMARY KEY,
|
|
Name varchar(80),
|
|
Job varchar(80) CHECK (Job IN('CEO', 'Founder')),
|
|
Brand varchar(80),
|
|
FOREIGN KEY (Brand) REFERENCES Brand(Name)
|
|
);
|
|
''')
|
|
|
|
for row in dfBrand.itertuples():
|
|
curs.execute('''INSERT INTO Brand VALUES (%s, %s, %s);''',
|
|
(row.Name, row.CreationDate, row.Headquarter))
|
|
|
|
curs.execute('SELECT Name FROM Brand;')
|
|
|
|
for row in dfPeople.itertuples():
|
|
curs.execute('''INSERT INTO Person VALUES (%s, %s, %s, %s);''',
|
|
(row.Id, row.Name, row.Job, row.Brand))
|
|
|
|
id=1
|
|
idModel=1
|
|
for row in df.itertuples():
|
|
#tranformer les prix (inr) en euros et les longueurs (mm) en cm
|
|
price = row.Price / 90
|
|
lenght = row.Length / 10
|
|
|
|
curs.execute('''INSERT INTO Model VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);''',
|
|
(idModel, row.Model, row.FuelType, row.Transmission, row.Engine, row.MaxPower,
|
|
row.MaxTorque, row.Drivetrain, lenght, row.Width, row.Height, row.SeatingCapacity,
|
|
row.FuelTankCapacity, row.Brand))
|
|
curs.execute('''INSERT INTO Car VALUES (%s, %s, %s, %s, %s, %s, %s, %s);''',
|
|
(id, price, row.Color, row.Kilometer, row.Year, row.Owner, row.SellerType, idModel))
|
|
id = id + 1
|
|
idModel = idModel + 1
|
|
|
|
|
|
|
|
|
|
co.commit ()
|
|
curs.close ()
|
|
except (Exception , psy.DatabaseError ) as error :
|
|
print ( error )
|
|
finally :
|
|
if co is not None:
|
|
co.close () |