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.

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:

______________________________________________________________________
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!
______________________________________________________________________

[prateek]

Hey Nate, your videos are just too good. I love how your channel is so dedicated towards real word data science. By the way I noticed that you started a video series, "For your Data Science Project" and I really want you to continue making videos for this particular series because there's literally no one on YouTube with such guidance on DS projects and I have been looking for one since a very long time because I have my placements just after 12 months and I really want to make a full stack data science project. Thank you.

[brentsimpson]

Mate! I'm a newbie python 'programmer' and this video has to be one of the most useful practical examples I've found. It'll take me a while to absorb it all but THANK YOU!😊
Brent

[austincelestine]

Holy cow, I wish I came across this earlier. This helped alot to importing these csv files into mysql. The 2nd video was great to except my formatting was different for my job. Great work!!! Absolutely love your content it really helps!

[sahej]

Love this series of real world on the job tasks

[smsyrandomacts]

Simply amazing.. knowledge transfer series. Great job Nat 👍

[hameedmulani]

One of the best tutorial I have ever seen

[zynkers]

These series are the best! Thanks Nate

[luzestrada]

You are the best! 💯 Thank you so much for sharing this valuable information 👏

[Davidkiania]

This is really good thanks. You’ve saved me lots of manual hours.

[mickyelmb]

Excellent tutorial. I was able to follow along in Jupyter. with no issues. Thank you for posting it.

[stratascratch]

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)

[exili]

Extremely helpful video, and a very useful tool, glad I found this on Reddit.

[TheSwil]

I really appreciate the quality of your videos! 💯

[radhakrishnanaik]

Hey Nate, your way of explanation is really very good .I would like to request you to prepare a video to fetch database table in csv file in python code in the form of pipeline

[arpitakar]

I loved it man thAt"s a serious effort vro

[yassw]

Hi Nate, this video is amazing and concise, thank you so much. However, I'm trying to learn this using as few advanced python libraries as possible for a class, so I can't use pandas or the like. I'm limiting myself to mySQL.connector and csv libraries. For this reason, MySQLCursor does not accept the "cursor.copy_expert" syntax. What would be a good alternative method to insert the contents of the csv file we saved during that step into the sql database?

[cristiansoto]

Hi Nate, thank you for your this valiable information. Could you make more videos about this?

I'm suscribed in stratascratch an it's amazing.

[lettalkaboutit]

Your video is fantastic, it help me, but this is where I struggle and I don't know if you can help with the following: my entire csv file needs to go in the table results, the dataframe created from the csv file have columns that contain foreign keys, I mean the csv files is a mix of different tables values, each column of my dataframe represents a table, My use case is that: 1/ first step: I have to loop through each column and compare the value in the column with the value from the corresponding table in the database, if the value does not exist then I create a SQL insert query to add this new value. After getting all new values in the databases then I do step 2

2/ Step : I get the value from each corresponding table from the database and for each columns I have to replace the data in each column by the table ID (foreign key), and next send all the dataframe in the database
Can you help me achieve this please?

[akshayjadiya]

Great Video Nate! Can you make a video playlist on how to get started with cloud services or provide relevant reading material for that here in comments?

[junaidmalik]

Fantastic Video, thanks Nate, u r awesome .
i just struggled understanding the col_str part at 17:50 of the Video, may be coz i am new to this!