How Load data to PSQL Database using Python
Importing Libraries: Begin by importing the required libraries. In this case, pandas and SQLAlchemy are used.
If you haven’t installed these libraries yet, you can refer on How To Install pandas and SQLAlchemy In Python On Mac
First, you need to retrieve your hostname and username from PgAdmin. You can obtain the connection information quite easily by following these steps:
1. Right-click on the server name in PgAdmin, which is PostgreSQL 16.
2. Select “Properties.”
3. Click on “Connection.”
4. Here, you can find your hostname and username.
After obtaining all the necessary information from PgAdmin, proceed to Visual Studio Code and utilize Python for the tasks.
import pandas as pd
from sqlalchemy import create_engine
# Define the connection string
conn_string = 'postgresql://postgres:password@localhost/Fuel_Consumption'
# Create a database engine and connect to it
db = create_engine(conn_string)
conn = db.connect()
# Define a list of file names to load
files = ['BatteryElectric', 'FuelCon20', 'FuelCon21', 'FuelCon22', 'FuelCon23', 'FuelCon24', 'PlugHybridElectric']
# Iterate over each file name
for file in files:
# Read the CSV file into a pandas DataFrame
df = pd.read_csv(f'/Users/m7/Downloads/PracticePython/FuelConsumptionRatings/{file}.csv')
# Write the DataFrame to the PostgreSQL database
df.to_sql(file, con=conn, if_exists='replace', index=False)
If you encounter an error during data loading such as: “UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0xe9 in position 2479: invalid continuation byte,”
you can resolve it by modifying the query from:
df = pd.read_csv(f'/Users/m7/Downloads/PracticePython/FuelConsumptionRatings/{file}.csv')to:
df = pd.read_csv(f'/Users/m7/Downloads/PracticePython/FuelConsumptionRatings/{file}.csv', encoding="ISO-8859-1")This code snippet reads CSV files from specified paths and loads them into tables in the PostgreSQL database named after the CSV filenames. Each CSV file corresponds to a separate table in the database. If a table with the same name already exists in the database, it will be replaced with the new data.
