Python Insert data into MySQL Database

In this tutorial, we’ll show how to perform insert data into MySQL Database Python step by step.

1. Setup Required to connect MySQL

To set up the environment, we need to connect MySQL with Python, go through this tutorial to see how to connect MySQL using mysql connector.

If you follow along with the tutorial link, we have connected everything and have code below in our IDE.

# Importing the MySQL-Python-connector
import mysql.connector as mysqlConnector
# Creating connection with the MySQL Server Running 
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root')
# Checking if connection is made or not
if conn:
    print("Connection Successful :)")
else:
    print("Connection Failed :(")
# Creating a cursor object to traverse the resultset
cur = conn.cursor()
cur.execute("SHOW DATABASES")
for row in cur:
    print(row)
# Closing the connection
conn.close()

Output:

Connection Successful 
(‘information_schema’,)
(‘mysql’,)
(‘performance_schema’,)
(‘sys’,)

2. Python Insert data into MySQL DB:

As part of this example, we are going to insert some of the records into the newly created empty table students.

Use insert command – insert into <table-name> values(),()..() to populate the table with few records. It is important to commit the changes and modification done on the tables to reflect them into the database, you can use the commit() method for this.

import mysql.connector as mysqlConnector
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root',database='cse')
if conn:print("Connection Successful :)")
else:print("Connection Failed :(")
cur = conn.cursor()
try:
    cur.execute("insert into students values(13,'Kunal',91001,'a')")
    conn.commit()
    print("Query Executed Successfully !!!")
except Exception as e:
    print("Invalid Query")
    print(e)
conn.close()

Output:

Connection Successful 🙂
Query Executed Successfully !!!

2.1 Inserting Multiple records:

Inserting multiple records is as simple as passing the records in comma-separated values, or we can also pass the python list.

import mysql.connector as mysqlConnector
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root',database='cse')
if conn:print("Connection Successful :)")
else:print("Connection Failed :(")
cur = conn.cursor()
try:
    cur.execute("insert into students values(13,'Kunal',91001,'a'),(1,'rahul',91567,'b'),\
        (10,'shivam',91223,'a'),(31,'shivam',91085,'c')")
    conn.commit()
    print("Query Executed Successfully !!!")
except Exception as e:
    print("Invalid Query")
    print(e)
conn.close()

Output:

Connection Successful 🙂 
Query Executed Successfully !!!

3. References:

Happy Learning 🙂