Python MySQL Insert Into Table

To insert data into a MySQL table using Python, follow these steps:

1. Install MySQL Connector

If you haven’t already installed the MySQL connector, run this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

You need to connect to the database where the table is located.

Example Python Script to Insert Data into a Table

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to insert data into the table
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John Doe", "123 Elm Street")

# Execute the SQL query
mycursor.execute(sql, val)

# Commit the transaction to save the data in the table
mydb.commit()

# Output the number of rows affected
print(mycursor.rowcount, "record inserted.")

Explanation:

• mycursor.execute(sql, val): Executes the SQL query to insert data. The val tuple contains the values to be inserted into the name and address columns of the customers table.
• mydb.commit(): Commits the transaction to make the changes permanent in the database.
• mycursor.rowcount: Returns the number of rows affected (should be 1 after a successful insertion).

3. Inserting Multiple Rows at Once

To insert multiple rows of data into the table, you can use the executemany() method.

# List of tuples containing multiple rows of data
val = [
  ("Jane Doe", "456 Maple Avenue"),
  ("Steve Smith", "789 Oak Drive"),
  ("Alice Johnson", "101 Pine Road")
]

# Execute the query for multiple rows
mycursor.executemany(sql, val)

# Commit the transaction
mydb.commit()

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

This will insert all the rows in a single transaction.

4. Retrieve the Inserted Row’s ID

If you want to retrieve the ID of the last inserted row (especially useful when you have an auto-incremented primary key):

print("Last inserted ID:", mycursor.lastrowid)

Full Example

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to insert data into the customers table
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ("John Doe", "123 Elm Street"),
  ("Jane Doe", "456 Maple Avenue"),
  ("Steve Smith", "789 Oak Drive")
]

# Insert multiple rows
mycursor.executemany(sql, val)

# Commit the transaction
mydb.commit()

# Print the number of inserted rows
print(mycursor.rowcount, "records inserted.")

# Print the ID of the last inserted record
print("Last inserted ID:", mycursor.lastrowid)

# Close the connection
mydb.close()

5. Closing the Connection

Always remember to close the connection after the operation is complete:

mydb.close()

This will insert data into the specified table and handle multiple records or single records as needed.