How to Insert Data into a MySQL Table In Python

In this article, we show how to insert data into a MySQL table in Python.

In fact, this data is a new row in a table. So, for example, let's say we have a table that represents students, that has 3 columns, the student's first name, last name, and grade level. We will show how to add a new entry to this table, so that we can add another student, say, Lisa Sanders, grade 11.

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

So, the general code to insert data into a MySQL table in Python is shown below.

cursor.execute("INSERT INTO Table_name (column1_name, column2_name, column3_name) VALUES ('Value1', 'Value2', 'Value3')")

So, the code above is the general code to insert data into a MySQL table in Python.

As an example, let's say we have a table named Students, that is a list of students at a high school. This table has a student's first name, last name, and grade level.

This is shown below.

In our code below, we're going to add a new entry to this table, let's say, Samantha Stewarts, of grade 12.

So, keep in mind, the table name is Students and the columns are firstname, lastname, and grade.

The full code to the new students, Samantha Stewards, of grade 12 is shown below.

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("INSERT INTO Students (firstname, lastname, grade) VALUES ('Samantha', 'Stewart', '12')") db.close()

So, this is all the code that is needed to add this new student into the Students table.

So we first must import MySQLdb. Once that is imported, we gain connection to the MySQL database using the MySQLdb.connect() function. To see a full-length article on connecting to a MySQL database, see How to Connect to a MySQL Database in Python.

We then have to create a cursor for the table.

Next, we execute our function to insert data into the table using the cursor.execute() function. Inside of this function, we place in the line, "INSERT INTO Students (firstname, lastname, grade) VALUES ('Samantha', 'Stewart', '12')". So the whole line of code, is, cursor.execute("INSERT INTO Students (firstname, lastname, grade) VALUES ('Samantha', 'Stewart', '12')")

We then close the database once we've done what we've needed to.

If the data has been successfully inserted into the MySQL table, a '1' should be returned in the Python shell.

After I ran the above code, the table now contains the new entry. This is shown below.

And this is how data can be inserted into a MySQL table in Python.

Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python