Insert Row into MySQL using Python

This blog article shows you how to Insert Row into MySQL using Python.

This Python script below defines a function to insert a new actor record into a MySQL database. Here’s a detailed explanation:

1. Imports:
   • from mysql.connector import Error: Imports the Error class from the mysql.connector module to handle exceptions.
   • from datetime import datetime: Imports the datetime class to work with date and time.
   • import mysql.connector: Imports the mysql.connector module to connect to a MySQL database.
2. Function Definition:
   • def insert_actor(actor_id, first_name, last_name): Defines a function named insert_actor that takes three parameters: actor_id, first_name, and last_name.
3. Try Block:
   • try: Starts a try block to handle potential exceptions.
4. Database Connection:
   • connection = mysql.connector.connect(…): Establishes a connection to the MySQL database using the provided host, database, user, and password.
   • if connection.is_connected(): Checks if the connection to the database is successful.
5. Cursor and Query Execution:
   • cursor = connection.cursor(): Creates a cursor object to execute SQL queries.
   • insert_query = “””INSERT INTO actor (actor_id, first_name, last_name, last_update) VALUES (%s, %s, %s, %s)”””: Defines an SQL insert query with placeholders for the values.
   • current_time = datetime.now(): Gets the current date and time.
   • record = (actor_id, first_name, last_name, current_time): Creates a tuple with the values to be inserted into the database.
   • cursor.execute(insert_query, record): Executes the insert query with the provided values.
   • connection.commit(): Commits the transaction to save the changes in the database.
   • print(“Record inserted successfully into actor table”): Prints a success message.
6. Exception Handling:
   • except Error as e: Catches any exceptions that occur during the database operations.
   • print(“Error while connecting to MySQL”, e): Prints an error message.
7. Finally Block:
   • finally: Ensures that the following code runs regardless of whether an exception occurred.
   • if connection.is_connected(): Checks if the connection is still open.
   • cursor.close(): Closes the cursor.
   • connection.close(): Closes the database connection.
   • print(“MySQL connection is closed”): Prints a message indicating that the connection is closed.
8. Example Usage:
   • insert_actor(201, ‘John’, ‘Doe’): Calls the insert_actor function with example values to insert a new actor record into the database.

from mysql.connector import Error
from datetime import datetime
import mysql.connector
def insert_actor(actor_id, first_name, last_name):
     try:
         connection = mysql.connector.connect(
             host=’localhost’,
             database=’Sakila’,
             user=’root’,
             password=’password’
         )
         if connection.is_connected():
             cursor = connection.cursor()
             insert_query = “””INSERT INTO actor (actor_id, first_name, last_name, last_update)
                               VALUES (%s, %s, %s, %s)”””
             current_time = datetime.now()
             record = (actor_id, first_name, last_name, current_time)
             cursor.execute(insert_query, record)
             connection.commit()
             print(“Record inserted successfully into actor table”)
     except Error as e:
         print(“Error while connecting to MySQL”, e)
     finally:
         if connection.is_connected():
             cursor.close()
             connection.close()
             print(“MySQL connection is closed”)

# Example usage
insert_actor(201, ‘John’, ‘Doe’)

Also: Read image file from Varchar field in MySQL database Python

Source code download: https://github.com/chanmmn/python/tree/main/2024/MySqlInsertActor?WT.mc_id=DP-MVP-36769

Reference: https://dev.mysql.com/doc/connector-python/en/?WT.mc_id=DP-MVP-36769