48 lines
1.5 KiB
Python
48 lines
1.5 KiB
Python
from sqlalchemy import create_engine
|
|
import pandas as pd
|
|
|
|
# Database connection
|
|
engine = create_engine("mysql+pymysql://root:Kfsh&rc9788@localhost/car2db01022025")
|
|
|
|
try:
|
|
# Load car_generation table
|
|
car_generation_query = "SELECT * FROM car_generation;"
|
|
car_generation_df = pd.read_sql(car_generation_query, engine)
|
|
|
|
# Load car_serie table
|
|
car_serie_query = "SELECT * FROM car_serie;"
|
|
car_serie_df = pd.read_sql(car_serie_query, engine)
|
|
|
|
# Perform a LEFT JOIN to keep all car series and merge with car generations
|
|
merged_df = pd.merge(
|
|
car_serie_df, car_generation_df, on="id_car_generation", how="left"
|
|
)
|
|
|
|
# Select and rename the relevant columns
|
|
final_df = merged_df.rename(
|
|
columns={
|
|
"id_car_serie": "id_car_serie",
|
|
"id_car_model_x": "id_car_model", # Ensure correct column selection
|
|
"name_y": "generation_name", # Car generation name
|
|
"name_x": "serie_name", # Car series name
|
|
"year_begin": "year_begin",
|
|
"year_end": "year_end",
|
|
}
|
|
)[
|
|
[
|
|
"id_car_serie",
|
|
"id_car_model",
|
|
"generation_name",
|
|
"serie_name",
|
|
"year_begin",
|
|
"year_end",
|
|
]
|
|
]
|
|
|
|
# Save the filtered data to a JSON file
|
|
final_df.to_json("merged_car_data.json", orient="records", indent=4)
|
|
print("Filtered merged data saved to 'merged_car_data.json'.")
|
|
|
|
except Exception as e:
|
|
print("Error:", e)
|