Insert Record In MySQL Table

In Python, To insert a record into a MySQL table using Python, First, we need to install "pip" if haven't installed it already.

Python provides the "mysql-connector-python" library to connect the database with the python application.

Below is an example demonstrating how to insert a record into a table:

Install mysql connector library:

After installing the package, we can use it to establish a connection to our MySQL database and perform operations.

Example:

pip install mysql-connector-python

Import Library and Database configuration parameters:

Import `mysql.connector` library and create a configuration object with keys such as `user`, `password`, `host`, and `database`.

Example:

# import mysql connector library
import mysql.connector

# Configuration parameters
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',  # or your host address
    'database': 'your_database_name',
}

Insert Record with error handling:

Insert Record in Table using `INSERT INTO` command and with the help execute() method we can run operation to insert record in a table in MySQL database.

Example:

# Define the insert query
insert_query = "INSERT INTO your_table_name (name, age) VALUES (%s, %s)"

# Data to be inserted into the table
data = ("Alice", 21)

# Execute the insert query
cursor.execute(insert_query, data)

# Commit the transaction
connection.commit()

print("Record inserted successfully")

Close Connection:

Don't forget to close the connection once done using the "close()" method.

Example:

finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Code Snippet for Established MySQL Database Connection:

Example:

import mysql.connector

# Configuration parameters
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'localhost',  # or your host address
    'database': 'your_database_name',
}

try:
    # Establish a connection to the MySQL server
    connection = mysql.connector.connect(**config)

    if connection.is_connected():
        # Create a cursor object to execute SQL queries
        cursor = connection.cursor()

        # Define the insert query
        insert_query = "INSERT INTO your_table_name (name, age) VALUES (%s, %s)"

        # Data to be inserted into the table
        data = ("Alice", 21)

        # Execute the insert query
        cursor.execute(insert_query, data)

        # Commit the transaction
        connection.commit()

        print("Record inserted successfully")

except mysql.connector.Error as error:
    print("Error occurred while connecting to MySQL:", error)

finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Make sure to replace 'your_username', 'your_password', and 'your_database_name' with your actual MySQL credentials and database name.
Replace 'your_table_name' with the name of the table into which you want to insert the record.
This script connects to the MySQL server, inserts a record with the specified data (name as "Alice" and age as 21) into the table, and prints a success message if the operation is successful.
Remember to handle exceptions and close the connection properly. Additionally, ensure that the data you're inserting matches the schema of your table.

<-- Create Table

Select Record -->