MYSQL Tutorial: Efficiently Importing Large CSV Files into MySQL with Python and Pandas

hey everyone welcome back to our channel in today's video I'm going to show you how to import a large CSV file as the one on the left into a mySQL database table as that on the right using the powerful python pandas Library if you have ever struggled with importing large data sets into a database then this video is for you I'll take you through each step of the process from reading the CSV file to inserting the data into the database table so if you're ready to learn how to handle big data like a pro then let's get started suppose we have this CSV file with those few records and we want to import them into this empty MySQL table whose name is sales summary we can use the MySQL insert statement to do this as follows we write insert into sales summary which is the table name then type the word values after that we start under our records values as they are in our CSV file we make sure that every row values are separated by comma and enclosed by brackets this method is perfect when we have small CSV files but when it comes to large files with hundreds and even thousand rows it will be impractical to use that method due to the large consume time during the typing process of each row values but what if we have a tool to type all records we have in almost few seconds of course it will be an excellent solution and this is exactly the main role of python in this method so let's Dive Right In and walk through the step-by-step process together here we created a new Jupiter notebook to start working with pandas We Begin by importing our library now it's imported successfully then we need to load our targeted CSV file we will use the read CSV method and store it into a data frame let's name it DF we can use the head method to show the first five rows of our file let's start cleaning our data first of all we can drop all unnecessary columns that we won't need we will drop the day month year and age group columns great after that we should check for any missing values we can see that there are not any missing values in all columns excellent we should also check on all columns data types and fix any inconsistency we notice that we need to fix the data type for the date column by converting it to date instead of object we also need to convert the data types of the last five columns into float instead of integer now our data is cleaned and well prepared for the next step next we will save our clean data as a text file we need first to convert all rows as tuples inside a big list let's create an empty list and name it y then we will iterate over each row inside our date frame using for Loop for each row we will use the append method to add it inside the empty list in form of a tuple here is our large list created successfully the next step is to create a new text file using open method with the name sales let's store it in a variable called file for each Tuple inside our list I need to write it inside the file followed by a comma plus a new line and finally after finishing writing all tuples we need to close the file great the file is created in our current directory now it's time to import your CSV file into your mySQL database table to import our file we go to the text file we press Ctrl a and copy all the lines then we go to mySQL and right insert into sales and then type values and paste all the copied lines go to the last line and replace the last comma with a semicolon and finally run the query and here's our file imported successfully with more than 100 000 rows in almost few seconds in conclusion we've learned how to import large CSV files into a mySQL database table using Python and pandas we encourage you to try it out yourself and experiment with different techniques for handling large data sets if you have any questions or feedback please let us know in the comments section below thanks for watching and see you in another video

Channel: Data Analytics

Views: 11,590

Keywords:

Id: 5IU7PZZ0LM8

Length: 6min 37sec (397 seconds)

Published: Mon May 08 2023