Join us and get access to thousands of tutorials and a community of expert Pythonistas.
This lesson is for members only. Join us and get access to thousands of tutorials and a community of expert Pythonistas.
Importing CSV Files Into MySQL Tables
00:00 So far you have created the three linked MySQL tables. However, these tables are empty, and in this lesson you will learn how to efficiently populate them with data.
00:13 Specifically, you will learn how to import data from CSV files to MySQL tables.
00:21 Before you proceed, make sure that you have placed movies, ratings, and reviewers CSV tables to the data subfolder of your MySQL project folder. If you have not done so, I suggest that you create the data subfolder, and download the files from the supporting materials of the course.
00:45 Next, I recommend that you create a new file.
00:56
Let’s call it MySQLcode_import.
01:05 In this file, you would like to reuse a code snippet that allows you to connect to the online movie rating database. You can simply grab it from the previous file. In this file, firstly, you’ll create a list of dictionaries, a data structure that will store records from each CSV file in memory.
01:29
And secondly, you’ll execute an SQL statement to insert attribute values to columns of their respective MySQL tables. To prepare the data for the import to their respective MySQL tables, you will need csv and pathlib libraries. Type import csv import pathlib.
01:54
pathlib is a built-in module that allows you to handle file system paths. Let’s instantiate a Path class to extract the full path to movies.csv. Type csv_
02:13
Path.cwd(). CWD stands for current working directory. / "data/ movies.csv". Let’s print the resulting path.
02:37
It does look right. Next, you will have to create a variable movie_ data, which has a list data structure. movie_data = list().
02:54
This is where you are going to store a list of rows of the movie table. To open the CSV file, use open() method together with the with context manager. Type with csv_ path.open( and in parenthesis type mode="r") as csv_file: outcome = csv.reader( csv_file).
03:34
Here, reading from a CSV file is done using the reader object. Next, let’s loop over the rows of the movies file. Type for row in outcome: Next, specify the dictionary keys and their values corresponding to columns within the indices 0, 1, 2, and 3 given in the CSV file. movie = { "title": with the value of rows[0], followed by the "release_year" is the value of rows[1], followed by "genre": rows[2], and at last "collection_in_mil":
04:32
with the value of rows of [3]}, movie_data.append(movie). This code creates a unique dictionary called movie for each row of the movies file and appends it to the list called movie_data.
05:01
Since the first row contains column names, let’s skip it. movie_data = movie_data[1:]. And let’s print a couple of records.
05:27 As we expected, we see a couple of records, specifically for the movie called Forrest Gump and Three Idiots.
05:41
Next, you’ll be executing an SQL query to insert dictionary values to their corresponding columns of the movies table in an iterative way. Type for item in movie_data:.
06:00
Here, item refers to a dictionary in the list of dictionaries extracted from the CSV file. Next, create an SQL statement in the string format sql = "INSERT INTO movies (title, release_year, genre, collection _in_mil) VALUES and in parenthesis type (%s, %s, %s, and %s)".
06:48
The %s tells the string that the inserted value will be converted to a string and will replace the placeholder at that position.
07:01 Also, you have to specify the four values that you will be inserting into the table that correspond to title, release_year, genre, and collection_in_mil.
07:13
They are captured by the following tuple variable called val = (item ["title"], item, and in square brackets type ["release_year"], and in square brackets type genre.
07:40
And finally, item and in square brackets type ["collection _in_mil"]).
07:52
When executing this query, you will have to pass both variables: sql containing the SQL query and val containing dictionary values to insert.
08:04
Let’s instantiate a cursor class and use it with the context manager. Type with connection.cursor() as cursor:cursor.execute( sql, val).
08:27 Notice how the query execution is performed in an iterative manner looping over all dictionaries.
08:35
Here you have performed several database transactions, adding records to the movies table row by row. Now you will have to call the commit() method to make the changes to this table.
08:49
After the loop, type connection.commit().
08:56
Having executed the cell, let’s confirm if the movies table was indeed populated with data using MySQL Workbench. Indeed, if you run SELECT * FROM online_movie_rating. movies, we will see all of the records in that movies table.
09:23 I will leave the population of reviewers and ratings tables for you as an exercise, you will have to apply the same code structure demonstrated in this lesson.
09:36 Take your time and don’t forget to take a deep breath for a well-deserved break. I wish you happy coding. Once you have successfully imported the remaining CSV data to their respective tables, you’ll be ready to learn how to retrieve data from the online movie rating database.
Nataliya RP Team on May 6, 2025
Hello Sneha. I don’t think there is anything wrong with the code snippet you shared. A MySQL Interface Error usually signals a communication problem between your Python code and the MySQL server (via MySQL connector). There are several reasons as to why this error may occur. Let’s start with checking the connection to the MySQL server before you insert data into ratings table. The connection may have not been established or it may have timed out. Make sure the connection is properly initialized and let me know.
Sneha Nath on May 8, 2025
Hello Nataliya, thank you for your response. That’s a very helpful explanation of the MySQL Interface Error. I will check again and verify that the connection is properly established and hasn’t timed out. Meanwhile, I tried inserting records manually, and the code worked. Here’s the code snippet:
from getpass import getpass
from mysql.connector import connect, Error
try:
connection = connect(
host="localhost",
user=input("Enter your username: "),
password=getpass("Enter your password: "),
database="online_movie_rating"
)
print(connection)
except Error as e:
print(e)
insert_ratings_query = """
INSERT INTO ratings
(rating, movie_id, reviewer_id)
VALUES ( %s, %s, %s)
"""
ratings_records = [
(6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17),
(5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4),
(8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10),
(5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19),
(8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9),
(8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19),
(5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15),
(8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20),
(8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13),
(7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17),
(6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4),
(9.8, 13, 1)
]
with connection.cursor() as cursor:
cursor.executemany(insert_ratings_query, ratings_records)
connection.commit()
This works for now, but I was wondering if the records grows too large, will this manual procedure be feasible enough. Please guide, thanks.
Become a Member to join the conversation.
Sneha Nath on May 5, 2025
Hi everyone, I’m encountering a MySQLInterfaceError: Lock wait timeout exceeded when trying to insert data into my ratings table using Python and the mysql-connector library. This happens during the connection.commit() step after executing an INSERT statement.
My code:
for item in ratings_data:
Could there be a problem with my INSERT query itself? Please guide. Thankyou in advance.