Import Data into Postgres Table Using Pandas

In this lesson, we’ll explore how to use Pandas to import data from CSV files into PostgreSQL tables efficiently. We’ll walk through reviewing the database, reading data from files, applying transformations, and loading it into tables—all while handling potential issues. This is a fundamental skill for managing structured data in real-world applications.

Objectives of This Lesson

By the end of this lesson, you’ll learn how to:

• Review database tables and corresponding data files.
• Use Pandas to read data from CSV files into a DataFrame.
• Transform data as needed (e.g., converting date formats).
• Load data from DataFrame into PostgreSQL tables.
• Implement exception handling for troubleshooting data import issues.

Practice Dataset

The demonstration uses two datasets:

• sales_reps_data.csv (Sales Representatives Data)
• toyota_sales_data.csv (Toyota Sales Data)

These files correspond to the tables in the car_sales_db database. You can use these files or replace them with your own datasets.

Step-by-Step Guide

1. Set Up the Database

• Ensure the car_sales_db database has the following tables: sales_reps_datatoyota_sales_data
• Verify the database schema matches the structure of the CSV files.

2. Prepare Your Environment

• Create a new Jupyter Notebook (e.g., load_csv_into_database.ipynb).
• Import Pandas:

import pandas as pd        

3. Read Data from CSV Files

• Load the CSV file into a Pandas DataFrame:

sales_reps_df = pd.read_csv('data/car_sales/sales_reps_data.csv')
print(sales_reps_df.head())        

• Verify the data shape and preview the first few rows:

print(sales_reps_df.shape)        

4. Transform Data

• Ensure the column data types align with the database schema. For example, convert the hire_date column to a date type:

sales_reps_df['hire_date'] = pd.to_datetime(
    sales_reps_df['hire_date'], format='%m/%d/%y'
)        

5. Write Data to PostgreSQL

• Use the to_sql() method from Pandas:

sales_reps_df.to_sql(
    'sales_reps_data',
    con=connection_string,
    if_exists='append',
    index=False
)        

6. Handle Errors

If the table exists and causes a conflict, specify the behavior using the if_exists parameter:

• append: Adds data to the existing table.
• replace: Drops the table and recreates it.
• fail: Throws an error if the table already exists.

Avoid writing the DataFrame index by using index=False.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError

engine = create_engine("postgresql+pg8000://car_sales_user:itversity@localhost:5432/car_sales_db")

try:
    with engine.connect() as connection:
        with connection.begin():
            sales_reps_df.to_sql(
                'sales_reps_data',
                con=connection,
                if_exists='append',
                index=True
            )
except SQLAlchemyError as e:
    print(f"Database error: {e}")        

7. Review and Verify

• Verify the data loaded successfully by querying the PostgreSQL database. Ensure the table has the expected rows and transformed columns.

What’s Next?

In the next lesson, we’ll explore Understanding Pandas Series: A Complete Guide with Real-World Examples. Stay tuned for insights on using Pandas with other data sources and database targets!

Conclusion

In this lesson, we covered:

1. Exporting data from CSV files into Pandas DataFrames.
2. Applying transformations like date conversions.
3. Writing transformed data into PostgreSQL tables.

While we used CSV as the source and PostgreSQL as the target, this process is flexible. You can replace the source file format or the target database (e.g., MySQL, SQLite) and still follow the same principles. Pandas simplifies data integration tasks for small to moderate-sized datasets, but adjustments are necessary for handling larger data volumes.

Conclusion for the Short Course

Through this short course, you have learned how to read data from a CSV file, apply transformations such as date formatting, and load the data into a target Postgres table.

Key Takeaways:

1. Pandas simplifies the process of reading and transforming data.
2. The to_sql() method provides a convenient way to insert data into a database.
3. Error handling is crucial for understanding and fixing issues during data insertion.

Keep in mind:

• The techniques in this course work best for small to moderate data volumes.
• For larger datasets, additional optimizations may be needed.

If you’d like to explore more about handling larger data volumes or other advanced topics, let us know in the comments.

Thank you for following along! If you found this course helpful, please like, comment, and subscribe to our channel. Your feedback helps us create better content!

? Test your knowledge of Python Pandas with our quiz! Click ??[here] to get started

Call to Action

? This article is authored by Siva Kalyan Geddada and Abhinav Sai Penmetsa

?? Share this newsletter with your network to help them master PostgreSQL and Pandas!

?? Questions? Drop a comment or message us directly—we’re here to help!

?? Let’s build robust database solutions together!