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:

importmysql.connectordb=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 : 1Inserted 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:

importmysql.connectordb=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:

Similar Reads