How to insert data into MySQL Table in Python Programming
By Saruque Ahamed MollickIn this Python tutorial, we will be learning how to insert data into MySQL table in Python programming. Data insertion in a MySQL table using MySQL Connector/Python will be shown in this Python tutorial with step by step guidance.
In my Previous tutorials on MySQL in Python, I have shown several things like
- How to create a database in MySQL using Python
- How to create MySQL table in Python – Step by step tutorial
Working with MySQL Database in Python, you can consider the use of MySQL Connector/Python as the best. If you don’t know how to use it learn, How to install MySQL Connector in Python. Then connect your MySQL database with your Python Program.
Insert data into MySQL table in Python
Now, we are going to learn how to insert rows into MySQL table using Python program.
As this post is for all the learners so I will start from the beginning assuming that you don’t know much on this topic.
So our steps will be like this,
- Start your MySQL Server. ( For XAMPP users, start apache and MySQL from XAMPP Control panel )
- Install MySQL Connector in Python, if you don’t have installed.
- Connect MySQL with Python
- Create a database in MySQL using Python, If you don’t have one or if you wish to create a new one
- Create MySQL table in Python
- Now Insert data in that table using a Python program.
Don’t panic seeing all these steps.
I just wanted to show you the complete task by using Python.
If you already have MySQL Connector/Python no need to install it. If you already created a table then no need to create again. This tutorial is just to show you how to insert data in MySQL table using Python program.
Let us assume that we have a table named codespeedy with three columns –
category duration level
If you want to create this table you can get help from How to create MySQL table in Python – Step by step tutorial
Or you can directly create the table using MySQL Statement:
Now, we have to insert rows into MySQL table using Python Program.
Python program to insert rows into MySQL table
The below program in Python will insert a row with some values in the MySQL table.
import mysql.connector
db_connection = mysql.connector.connect(
host="localhost",
user="your_username_here",
passwd="your_password_here",
database="my_test_db"
)
my_database = db_connection.cursor()
sql_statement = "INSERT INTO codespeedy (category,duration,level) values(%s,%s,%s)"
values = ("Python","100 Hours","Advanced")
my_database.execute(sql_statement,values)
db_connection.commit()If you run this program and get output with no error that means you have successfully entered a row in your MySQL table.
Here my_test_db is my database name. You have to use your database name.
to know more about the db_connection please go through this tutorial, How to Connect MySQL with Python
After executing the SQL statement using execute() function you must have to commit it.
db_connection.commit()
To save the changes you must have to use the commit()
Finally, After running this Python program my table will look like this,
- Insert multiple rows in MySQL table in Python – Follow this tutorial to learn how to insert many rows or data into MySQL table in a single Python program.
execute() function in Python
execute function can take the first parameter as MySQL statement that is going to be executed.
The second parameter is for the data set. You can pass data to be inserted through this second parameter.
Now you can Also learn, How to Fetch data from MySQL table in Python Program
This tutorial is a part of our MySQL tutorial
Good stuff. …to the point codespeedy. Thank you!