In this tutorial, we will learn how to insert a single row and insert multiple rows of data in a MySQL table using Python.
To Insert data into a MySQL Table or to add data to the MySQL Table which we have created in our previous tutorial, We will use the INSERT SQL statement.
If you are new to SQL, you should first learn about the SQL INSERT statement.
Python MySQL - INSERT Data
Let us see the basic syntax to use INSERT INTO statement to insert data into our table:
INSERT INTO table_name (column_names) VALUES(data)We can insert data in a table in two ways:
Inserting a single row at a time
Inserting multiple rows at a time
Inserting Single Row in MySQL Table
In this section, we will see the code example for inserting a single row of data in a MySQL table:
We will be adding data to the students table we created in Python MySQL - Create Table tutorial.
import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "root", passwd = "himaniroot@99", database = "studytonight" ) cursor = db.cursor() ## defining the Query query ="INSERT INTO students(name, branch, address) VALUES (%s, %s,%s)" ## There is no need to insert the value of rollno ## because in our table rollno is autoincremented #started from 1 ## storing values in a variable values = ("Sherlock", "CSE", "220 Baker Street, London") ## executing the query with values cursor.execute(query, values) ## to make final output we have to run ## the 'commit()' method of the database object db.commit() print(cursor.rowcount, "record inserted")The output of the above code will be:
1 record inserted
Inserting Multiple Rows in MySQL Table
In this section, we will see the code example for inserting multiple rows of data in a MySQL table.
To insert multiple rows into the table, the executemany() method is used. It takes a list of tuples containing the data as a second parameter and the query as the first argument.
import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "root", passwd = "himaniroot@99", database = "studytonight" ) cursor = db.cursor() ## defining the Query query ="INSERT INTO students(Name, Branch,Address) VALUES (%s, %s, %s)" ## storing values in a variable values = [ ("Peter", "ME","Noida"), ("Amy", "CE","New Delhi"), ("Michael", "CSE","London") ] ## executing the query with values cursor.executemany(query, values) ## to make final output we have to run ## the 'commit()' method of the database object db.commit() print(cursor.rowcount, "records inserted")If all three rows were entered successfully, the output of the above code will be:
3 records inserted
So in this tutorial we learned how we can insert data into MySQL table in Python.
In the previous tutorial, we have seen how to create a database and connect it to the python program. In this tutorial, we will see how to insert a single row as well as multiple rows using the python program. Apart from this, we will see how we can get the id of the last inserted row. So, let’s get started! Also read:
Python MySQL Overview and Introduction We already know that the INSERT statement is used to insert the data into the table. However, let’s revise it one more time- To insert a single row of data in all columns of the table- INSERT INTO table VALUES(val1, val2, val3...); Note that, when you want to insert the data into all columns of the row, there is no need to
mention the column names in the insert statement. To insert a single row of data in particular columns of the table- INSERT INTO table(col1,col2...) VALUES(val1,val2...); To insert multiple rows- INSERT INTO table(col1,col2) VALUES(val1,val2), (val1,val2), (val1,val2); Also read: Python MySQL – Connect a MySQL Database with PythonInsert Data into Table
In the examples below, we will use a table that we have already created using the MySQL CLI. Following is the table description of the ’emp’ table-
Insert a Single Row
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="journaldev" ) mycursor = mydb.cursor() sql = "INSERT INTO emp (name, city) VALUES (%s, %s)" val = ("John", "Boston") mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, "record inserted successfully")
Code language: Python (python)Here, we have first established the connection with the database. Then we created a cursor and wrote an insert query. Using the cursor, we execute that query.
Note that, even if you execute the query, the changes won’t be made in the table unless you write a statement mydb.commit().
the mycursor.rowcount statement returns how many rows are inserted into the table.
Let’s run the python program and check the emp table to see if the data is inserted into it.
SELECT * FROM EMP;
Code language: SQL (Structured Query Language) (sql)Insert Multiple Rows
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="journaldev" ) mycursor = mydb.cursor() sql = "INSERT INTO emp (name, city) VALUES (%s, %s)" val = [ ("John", "Boston"), ("Anna", "Mumbai"), ("Peter", "Boston"), ("Sara", "Gurgaon"), ("Ester", "Mumbai") ] mycursor.executemany(sql, val) mydb.commit() print(mycursor.rowcount, " records inserted successfully")
Code language: Python (python)Here, instead of writing multiple values in the insert statement, we have only specified the type of data that we will insert.
Then, we have created the list of values that we want to insert.
Note that, we must use the executemany() method when there are multiple rows has to be inserted into the table.
Finally, we make changes in the table using the commit() method.
Let’s run the program and check the table to see if all values are inserted correctly.
SELECT * FROM emp;
Code language: SQL (Structured Query Language) (sql)Get the id of Inserted Record
By using the cursor object, we can get the id of the row that we have just inserted.
If we insert multiple rows, the object will return the id of the last inserted row.
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="journaldev" ) mycursor = mydb.cursor() sql = "INSERT INTO emp (name, city) VALUES (%s, %s)" val = ("Sean", "San Jose") mycursor.execute(sql, val) mydb.commit() print("id of the last record is ", mycursor.lastrowid)
Code language: Python (python)Here, have a look at the last line of the code. The lastrowid property returns the id of the last inserted row.
Till now, we have six records present in the table. When we execute the above program, we must get the if 7 in the output.
As you can see, we have got the correct output.
Conclusion
In this tutorial, we have learned how to insert a single row as well as multiple rows into the MySQL table using the python program. We have learned to get the id of the last inserted row which is quite useful when you want to assign a unique id based on the auto_increment id such as student PRN number, employee number or order id, etc. In the next tutorial, we will learn to fetch the data from the MySQL table.