Python - MySQL Insert Record

Insert a record into a MySQL database using Python:

import mysql.connector
# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)
# Create a cursor object to execute SQL queries
mycursor = mydb.cursor()
# Define the SQL query to insert a record
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John Smith", "123 Main St.")
# Execute the SQL query and commit the changes to the database
mycursor.execute(sql, val)
mydb.commit()
# Print a message to confirm the record was inserted successfully
print(mycursor.rowcount, "record inserted.")

Now let's break down each section of the code:

First, we import the mysql.connector module which allows us to connect to and interact with a MySQL database using Python.

import mysql.connector

Next, we connect to the MySQL database using the connect() method of the mysql.connector module. We provide the database host, username, password, and database name as parameters to the connect() method.

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

After connecting to the database, we create a cursor object using the cursor() method of the database connection object. The cursor object allows us to execute SQL queries and fetch results from the database.

mycursor = mydb.cursor()
We then define the SQL query to insert a record into the customers table. The %s placeholders indicate where values will be inserted later.
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"

Next, we create a tuple containing the values to be inserted into the name and address columns of the customers table.

val = ("John Smith", "123 Main St.")

We then execute the SQL query using the execute() method of the cursor object, passing the SQL query and the tuple of values as parameters. We then commit the changes to the database using the commit() method of the database connection object.

mycursor.execute(sql, val)
mydb.commit()

Finally, we print a message to confirm that the record was inserted successfully.

print(mycursor.rowcount, "record inserted.")

And that's it! We have successfully inserted a record into a MySQL database using Python.