Python MySQL Insert Operation
We will use the INSERT SQL statement to add data to the MySQL Table or to insert data into a MySQL table.
Please make sure you have the following before continuing: -
- Username and password to connect to MySQL
- MySQL table name in which you want to insert data.
To add records, create the " Employee " table in the MySQL server.
Python MySQL – INSERT Operation
Let's look at the fundamental syntax for using the INSERT INTO statement to add data to our table:
INSERT INTO table_name (column_names) VALUES(data)
There are two ways to insert data into a table:
- Inserting a single row at a time
- Inserting multiple rows at a time
Insert a Single Row into the MySQL table using Python
The following steps are followed to insert data into MySQL table from Python.
1. Connect to MySQL from Python
When using the MySQL Connector module to connect to a MySQL database from Python, refer to the Python MySQL database connection.
2. Define a SQL Insert Query
Create a SQL INSERT query to add a record to a table. We write column names and their values to insert in a table in the insert query.
For Example, INSERT INTO MYSQL TABLE (COLUMN 1, COUNTRY 2,...) VALUES (VALUE 1, VALUE 2,...).
3. Get Cursor Object from Connection
The following step is to build a cursor object using the connection.cursor() method. Using this method, a new MySQLCursor object is created.
4. Execute the Insert query using execute() method
Execute the insert query using the cursor.execute() method. The operation stored in the Insert query is carried out using this method.
5. Commit the changes
Using the commit() method of a connection class, make changes persistent in a database following the successful execution of a query.
6. Get the number of rows affected
Use the cursor.rowcount() method to determine how many rows were affected when an insert operation was completed. The count is based on the number of rows you are inserting.
7. Verify the Result using the SQL SELECT query
You may view the new modifications by executing a MySQL select query from Python.
8. Close the Cursor Object and Database connection object
Once your work is finished, use the cursor.close() and connection.close() methods to close open connections.
Let us look at an Example Program
import mysql.connector
try:
connection = mysql.connector.connect(host='localhost',
database='JTP',
user='root',
password='root')
mySql_insert_query = """INSERT INTO Employee (id, name, salary) VALUES
(1, 'Swami', 2000000) """
cursor = connection.cursor()
cursor.execute(mySql_insert_query)
connection.commit()
print(cursor.rowcount, "Record inserted successfully")
cursor.close()
except mysql.connector.Error as error:
print("Failed to insert record into Employee table {}".format(error))
finally:
if connection.is_connected():
connection.close()
Output
Python Variables in a MySQL Insert Query
There are situations when you must insert the value of a Python variable into a table's column. For instance, the user enters their information in the user signup form. These values can be taken from Python variables and added to a table.
We can use the prepared statement and parameterized query to enter Python variables into the table.
Placing placeholders (%s) for parameters in a parameterized query allows us to provide Python variables as a query parameter.
Example code for inserting python variable value into table’s column
import mysql.connector
def insert_varibles_into_table(id, name, salary):
try:
connection = mysql.connector.connect(host='localhost',
database='JTP',
user='root',
password='root')
cursor = connection.cursor()
mySql_insert_query = """INSERT INTO Employee (id, name, salary)
VALUES (%s, %s, %s) """
record = (id, name, salary)
cursor.execute(mySql_insert_query, record)
connection.commit()
print("Record inserted successfully")
except mysql.connector.Error as error:
print("Insertion into MYSQL table is failed {}".format(error))
finally:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")
insert_varibles_into_table(2, 'Manaswini', 3000000)
insert_varibles_into_table(3, 'Manideep', 2500000 )
Output
Multiple Rows into MySQL table can be Inserted using the Cursor's executemany()
Previously, a single record was inserted using the cursor object's execute() method.
The cursor's executemany() function inserts multiple rows from the Python application into a table in a single insert query.
Syntax of the executemany() function is as follows:
cursor.executemany(operation, seq_of_params)
The sequence seq_of_params provided to this method's Insert operation is applied to all parameter sequences.
In the seq_of_params argument, you must also provide the insert query and lists of tuples.
One row you want to insert can be found in each list tuple. As a result, you can add as many rows to the list and pass it to a cursor. executemany() method along with the insert query.
A comma separates each tuple, which is enclosed in brackets.
For instance, to insert several rows into a table, the following SQL query can be used:\
INSERT INTO Employee (Id, Name, Salary) VALUES (%s, %s, %s)
And we are sending the below list to seq_of_params.
records_to_insert = [(4, 'Madhusudan Rao', 3200000),
(5, 'Sujatha', 2200000),
(6, 'Murali', 1500000)]
Example of inserting multiple rows at once into a MySQL table
import mysql.connector
try:
connection = mysql.connector.connect(host='localhost',
database='JTP',
user='root',
password='root')
mySql_insert_query = """INSERT INTO Employee (id, name, salary)
VALUES (%s, %s, %s) """
records_to_insert = [(4, 'Madhusudan Rao', 3200000),
(5, 'Sujatha', 2200000),
(6, 'Murali', 1500000)]
cursor = connection.cursor()
cursor.executemany(mySql_insert_query, records_to_insert)
connection.commit()
print(cursor.rowcount, "Record inserted successfully")
except mysql.connector.Error as error:
print("Insertion of record into MYSQL table got failed {}".format(error))
finally:
if connection.is_connected():
cursor.close()
connection.close()
Output
Note:
We insert several rows (from a List) into the table using a cursor.executemany(sql_insert_query, records_to_insert).
We can determine how many records were entered by using the cursor.rowcount function.
Insert DateTime and timestamp into a MySQL table using Python
Consider a MySQL table with a date column. Let's see how to construct a Python insert query to add DateTime to a table.
For this, let us consider one more column for the Employee table, i.e., the joining date of the Employee in the company, and name it "joining_date".
Code:
from datetime import datetime
import mysql.connector
try:
connection = mysql.connector.connect(host='localhost',
database='JTP',
user='root',
password='root')
mySql_insert_query = """INSERT INTO Employee (id, name, salary, joining_date)
VALUES (%s, %s, %s) """
cursor = connection.cursor()
current_Date = datetime.now()
# convert the date in your wish format
formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')
insert_tuple = (7, 'Devi', 1000000, current_Date)
result = cursor.execute(mySql_insert_query, insert_tuple)
connection.commit()
print("Date Record inserted successfully")
except mysql.connector.Error as error:
connection.rollback()
print("Insertion of record into MYSQL table got failed {}".format(error))
finally:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")
Output
