Solve Data Science Tasks In Python (Saving Hours) (automating import CSV files to database)

Solving real world data science tasks by automating this common data science task will save you hours of work. I'll cover how to write a python script to import a CSV or Excel file to a database. In this video I’ll specifically be showing you how to import a CSV file to a postgres database on AWS. Importing CSV files to a database is a common task needed for data science and analytics and it can be done completely with python using pandas dataframes, numpy, os library, and the posgres database wrapper psycopg2.

Here's the github repo: https://github.com/Strata-Scratch/csv_to_db_automation

This python script is completely manual. Part 2 where I completely refactor the python script so that our code can handle multiple files without changing a line of python code can be found here (https://youtu.be/TDwy1lSjEZo).

This is also my first python video so please leave a comment and let me know what you think. Did I cover the concepts in detail? Do you want more detail? Less? Was the code too small to read? Any feedback would be appreciated!

Topics Covered:
- How to read your CSV file into a pandas dataframe
- How to clean the table name and column headers using methods like replace() and lower()
- How to convert your pandas dataframe to a CSV file
- How to create a database table and import your CSV file to a postgres database on Amazon Web Services (AWS) in python

______________________________________________________________________
Resources:

- How to setup Anaconda: https://docs.anaconda.com/anaconda/install/
- How to install psygopg2: https://anaconda.org/anaconda/psycopg2

? Subscribe to my channel: https://bit.ly/2GsFxmA
? Practice real data science interview questions: https://platform.stratascratch.com/coding?utm_source=youtube&utm_medium=click&utm_campaign=YT+description+link

______________________________________________________________________
Timestamps:

Intro: (0:00)
Outlining steps & import python libraries: (2:23)
Reading your CSV file as a pandas dataframe: (4:31)
Cleaning your table name: (6:03)
Cleaning your column names: (10:25)
Generating SQL query to create database table: (12:40)
Connecting to your database: (19:04)
Generating SQL queries to import CSV file to database: (21:53)
Double checking file was imported correctly: (30:50)
Conclusion: (31:47)

______________________________________________________________________

Contact:

If you have any questions, comments, or feedback, please leave them here!
Feel free to also email me at [email protected]
______________________________________________________________________