Skip to content
Related Articles

Related Articles

Improve Article

How to insert values into MySQL server table using Python?

  • Difficulty Level : Basic
  • Last Updated : 03 Jan, 2021
Geek Week

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:

 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




My Personal Notes arrow_drop_up
Recommended Articles
Page :