The INSERT INTO command is utilized for inserting a record into a table. In Python, we can use the format specifier (%s) as a placeholder for the values. The actual data is then supplied as a tuple within the execute method of the cursor.
NOTE: Before inserting any value in the table, make sure that you create a table first. If you want to add a table in Python SQL, refer to the tutorial Python MySQL Create Table .
Python MySQL Insert Operation Syntax
It has the following syntax:
INSERT INTO TABLE_NAME (col1, col2,col3,...colN)
VALUES (val1, val2, val3,...valN);
MySQL Insert Operation Example
In this section, we will illustrate the MySQL Insert Operation using a practical example in Python.
Example
import mysql.connector
# Establishing connection to the MySQL database
db_connection = mysql.connector.connect(
host="localhost",
user="root", # Your MySQL username
password="Root", # Your MySQL password
database="mydatabase" # Your database name
)
# Creating a cursor object to execute SQL queries
db_cursor = db_connection.cursor()
# Writing the SQL INSERT query to add a new customer
sql_query = "INSERT INTO customers (name, address) VALUES (%s, %s)"
customer_data = ("John", "Highway 21")
# Executing the SQL query with provided values
db_cursor.execute(sql_query, customer_data)
# Committing the transaction to save changes in the database
db_connection.commit()
# Printing confirmation of how many records were inserted
print(db_cursor.rowcount, "record inserted successfully.")
Output:
Explanation:
In the preceding program, we initially established a connection between Python and the MySQL database utilizing the mysql.connector library. This connection is formed by providing specific credentials, including the host (localhost), username (root), password (Root), and the database name (mydatabase).
We have instantiated a cursor object referred to as dbcursor to carry out SQL commands. An SQL INSERT statement, designated as sqlquery, has been established for the purpose of adding a new entry into the customers table, which comprises two columns: name and address. The data intended for insertion, specifically "John" and "Highway 21", is provided as a tuple and assigned to the variable customer_data.
The dbcursor.execute function transmits the SQL statement to the database, which results in the creation of our customers table. Subsequently, the dbconnection.commit function is employed to ensure that the alterations are saved permanently within the database. In conclusion, the program outputs the count of rows that were inserted by utilizing db_cursor.rowcount, thereby validating the successful insertion.
NOTE: The statement: mydb.commit is required to save the changes in your MYSQL table. If you don't use it, no changes are saved in your table and once the connection is closed all the changes are lost.
Insert Multiple Rows
Python's MySQL interface enables the insertion of multiple rows into a table simultaneously. This can be achieved through the use of the executemany function.
Syntax for Inserting Multiple Rows
It has the following syntax:
cursor.executemany(sql_query, values_list)
In this syntax,
- SQL_query: This parameter refers to the SQL query that you intend to run.
- Values_list: This parameter defines a collection of tuples that contain the records the user wishes to insert.
Inserting Multiple Rows in Table Example
To illustrate the process of adding several rows to a table, let us consider an example.
Example
import mysql.connector
# Establishing the database connection
connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
# Creating a cursor object to interact with the database
cursor = connection.cursor()
# Writing the SQL query to insert multiple records into the customers table
insert_query = "INSERT INTO customers (name, address) VALUES (%s, %s)"
# Listing the name and address in the table
customer_data = [
('Ananya', 'Mumbai'),
('Raj', 'Delhi'),
('Yshakan', 'Bangalore'),
('Aman', 'Hyderabad'),
('Sneha', 'Chennai'),
('Karan', 'Pune'),
('Divya', 'Kolkata'),
('Ishaan', 'Jaipur'),
('Megha', 'Ahmedabad'),
('Neha', 'Surat'),
('Rohit', 'Lucknow'),
('Tina', 'Chandigarh'),
('Aditya', 'Bhopal')
]
# Inserting all the records in the table customers at once
cursor.executemany(insert_query, customer_data)
# Committing the transaction to the database
connection.commit()
# Printing the total number of rows that we have inserted in the table
print(cursor.rowcount, "records were successfully inserted.")
# Closing the cursor and connection using close() method
cursor.close()
connection.close()
The entry will be added successfully, and Python will produce the following output.
Output:
Explanation:
In the program outlined above, we have established a connection between Python and the MySQL database through the use of the mysql.connector library. This connection is formed by utilizing the provided credentials. Additionally, we have instantiated a cursor object referred to as db_cursor, which is employed to execute SQL statements.
We have constructed an SQL query referred to as insert_query. In this instance, we have formulated an SQL INSERT statement designed to add each record, with %s serving as the placeholders for the records that will be inserted.
Subsequently, we established a variable named customer_data that includes a collection of tuples. Each tuple symbolizes a row intended for insertion into the customers table. Within each tuple, there are two elements: name and address.
We utilized the executemany function to insert several records into the table simultaneously. The modifications made to our database were preserved by employing the commit function.
By utilizing the rowcount property, we determined the total number of rows that were successfully inserted. Finally, we employed the close method to terminate both the cursor and the connection.
Conclusion
This guide details the procedure for incorporating new information into MySQL tables. The INSERT INTO command is utilized to add a fresh entry to your table. By employing the executemany function, we can insert several records simultaneously. This approach not only conserves time but also minimizes the number of separate queries, thus enhancing the overall speed and efficiency of the process. The INSERT function is especially beneficial for developing reliable real-world applications that necessitate the management of substantial amounts of data.
Frequently Asked Questions / FAQs
1. What is the purpose of using the INSERT statement in MYSQL?
The MYSQL INSERT INTO command is utilized to add a new entry to your table.
2. What does %s represent in the SQL query?
The %s serves as a placeholder in parameterized queries, representing the specific records that are intended to be inserted.
3. In Python, which of the following method inserts multiple records at once into a table?
- execute
- insert
- run
- executemany
d) executemany
4. What will happen if we don't call the connection.commit after inserting data into the table?
- The Python Program will crash
- All the records will be saved automatically in the database
- The inserted records will not save to the database
- It creates a backup
c) The records that have been added will not be stored in the database.
5. Can we insert records in the table without mentioning column names in the SQL query?
Indeed, it is highly recommended to specify column names in order to avert mistakes and eliminate any potential misunderstandings.