Running SQL Insert query in Python code

Running an SQL INSERT query in Python is essential for adding new records to your database. This operation is similar across various database systems, with slight differences in syntax and connection methods. Below is a comprehensive guide on how to perform SQL INSERT operations using different database connectors, including sqlite3, mysql-connector-python, and mariadb.

Using sqlite3

sqlite3 is a built-in Python module that allows you to interact with SQLite databases.

Example Code:

import sqlite3

# Connect to the SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('example.db')

# Create a cursor object
cursor = connection.cursor()

# Define the SQL INSERT query
insert_query = "INSERT INTO Users (name, email) VALUES (?, ?)"

# Define the data to insert
data = ("John Doe", "johndoe@example.com")

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

# Commit the changes
connection.commit()

# Close the cursor and connection
cursor.close()
connection.close()

Using MySQL Connector (mysql-connector-python)

For MySQL databases, you can use the mysql-connector-python module.

Example Code:

import mysql.connector

# Define the connection parameters
connection = mysql.connector.connect(
    host="your_host_name",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

# Create a cursor object
cursor = connection.cursor()

# Define the SQL INSERT query
insert_query = "INSERT INTO Users (name, email) VALUES (%s, %s)"

# Define the data to insert
data = ("John Doe", "johndoe@example.com")

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

# Commit the changes
connection.commit()

# Close the cursor and connection
cursor.close()
connection.close()

Using MariaDB Connector (mariadb)

For MariaDB databases, you can use the mariadb module.

Example Code:

import mariadb

# Define the connection parameters
try:
    connection = mariadb.connect(
        user="your_username",
        password="your_password",
        host="your_host_name",
        port=3306,
        database="your_database_name"
    )

    # Create a cursor object
    cursor = connection.cursor()

    # Define the SQL INSERT query
    insert_query = "INSERT INTO Users (name, email) VALUES (?, ?)"

    # Define the data to insert
    data = ("John Doe", "johndoe@example.com")

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

    # Commit the changes
    connection.commit()

except mariadb.Error as e:
    print(f"Error connecting to MariaDB: {e}")

finally:
    # Close the cursor and connection
    if cursor:
        cursor.close()
    if connection:
        connection.close()

Explanation of the Code

• Connection Parameters: These include host, user, password, and database. Replace these placeholders with your actual database credentials.
• Establishing Connection: Establish the connection to the database using the appropriate connector’s connect method.
• Creating Cursor: Create a cursor object using connection.cursor(). The cursor is used to execute SQL commands.
• Defining SQL INSERT Query: Define the INSERT query as a string. Use placeholders (? for SQLite and MariaDB, %s for MySQL) to parameterize the query.
• Executing SQL INSERT Query: Use cursor.execute(insert_query, data) to execute the query with the provided data.
• Committing Changes: Use connection.commit() to save the changes to the database.
• Closing Connection: Always close both the cursor and the connection to free up resources.

Best Practices

Parameterized Queries: Always use parameterized queries to avoid SQL injection attacks.

cursor.execute("INSERT INTO Users (name, email) VALUES (%s, %s)", ("John Doe", "johndoe@example.com"))

Error Handling: Implement error handling using try-except blocks to manage potential database errors gracefully.

try:
    connection = mariadb.connect(
        user="your_username",
        password="your_password",
        host="your_host_name",
        port=3306,
        database="your_database_name"
    )
    cursor = connection.cursor()
    insert_query = "INSERT INTO Users (name, email) VALUES (?, ?)"
    data = ("John Doe", "johndoe@example.com")
    cursor.execute(insert_query, data)
    connection.commit()
except mariadb.Error as e:
    print(f"Error connecting to MariaDB: {e}")
finally:
    if cursor:
        cursor.close()
    if connection:
        connection.close()

Summary

By following these steps and best practices, you can effectively run SQL INSERT queries in Python across different types of databases. This allows you to add new records to your database within your Python applications seamlessly, ensuring your data remains updated and accurate.