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:
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:
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: