Skip to content
Related Articles

Related Articles

How to insert values into MySQL server table using Python?

Improve Article
Save Article
  • Difficulty Level : Basic
  • Last Updated : 03 Jan, 2021
Improve Article
Save Article

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() method
mycursor = 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 Connection
db.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() method
mycursor = 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 Connection
db.close()

Output:

No of Record Inserted : 2

How our table looks in SQL after insertion:


My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!