How to insert values into MySQL server table using Python?
Prerequisite: Python: MySQL Create Table
In this article, we are going to see how to get the size of a table in MySQL using Python. Python allows the integration of a wide range of database servers with applications. A database interface is required to access a database from Python. MySQL Connector-Python module is an API in python for communicating with a MySQL database
Approach:
- Set up a Database serving either locally or globally.
- Install Python Connector inorder to communicate with Databases.
- Establish Database Connection using a Connector.
- Need to have a Table to insert data, Create a Table if you don’t have any.
- Modify Data in Table [ CRUD operation ] using a cursor object returned by Connector.
- Close the Database connection If you are done with it.
We are going to use this table:
Example 1: Adding one row into a Table with static values :
Syntax : “INSERT INTO table_name (column_name) VALUES ( valuesOfRow );”
Below is the implementation:
Python3
import mysql.connector db = mysql.connector.connect( host="localhost", user="root", passwd="root", database="testdb")# getting the cursor by cursor() methodmycursor = db.cursor() insertQuery = "INSERT INTO Fruits (Fruit_name) VALUES ('Apple');" mycursor.execute(insertQuery) print("No of Record Inserted :", mycursor.rowcount) # we can use the id to refer to that row later.print("Inserted Id :", mycursor.lastrowid) # To ensure the Data Insertion, commit database.db.commit() # close the Connectiondb.close() |
Output:
No of Record Inserted : 1 Inserted Id : 1
How our table looks in SQL after insertion:
Example 2: Adding multiple rows into a table with static values :
Syntax : ”INSERT INTO table_name (column_name)
VALUES ( valuesOfRow1),(valuesOfRow2),….(valuesOfRowN);”
Below is the implementation:
Python3
import mysql.connector db = mysql.connector.connect( host="localhost", user="root", passwd="root", database="testdb") #getting the cursor by cursor() methodmycursor = db.cursor() insertQuery = '''INSERT INTO Fruits (Fruit_name, Taste, Production_in ) VALUES ('Banana','Sweet',210);''' mycursor.execute(insertQuery) print("No of Record Inserted :", mycursor.rowcount) # To ensure the data insertion, Always commit to the database.db.commit() # close the Connectiondb.close() |
Output:
No of Record Inserted : 2
How our table looks in SQL after insertion:
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course
