Python – MySQL – Insert Data

Inserting data into a MySQL table in Python is straightforward. This tutorial will guide you through the process of inserting data into a MySQL database using Python.

Prerequisites

• Python installed (preferably Python 3.x)
• MySQL installed on your machine (you can use MySQL running on a remote server as well)
• The mysql-connector-python module installed (Use pip)

Connecting to MySQL Database

First, you need to establish a connection with your MySQL Database in Python. For this, you can use the following code:

import mysql.connector

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

print(mydb)

mycursor = mydb.cursor()

The above code creates a connection object that you can then use to interact with your MySQL database. The host, user, password and database parameters will be specific to your MySQL database configuration.

Create a Table (Optional Step)

If you have not created a table in your database yet, you can use the following code snippet. Please change the table name and column names as per your requirement. Note that this step is optional.

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

This code creates a table called Customers with two columns: Name and Address.

Insert Data into the Table

After creating the table, you can insert data into it using the INSERT INTO statement. Use the following code:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")

mycursor.execute(sql, val)

mydb.commit()

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

The above code will insert a row with Name as John and Address as Highway 21 in the Customers table.

Insert Multiple Rows

You can also insert multiple rows into your table in one go. Use the following code:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

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

The above code will insert multiple rows in one go. You just need to pass the values as a list of tuples to the executemany method.

Conclusion

That’s it! You should now be able to insert data into your MySQL table using Python. This technique can be extremely useful when working with large data sets or when you need to programmatically insert data into a MySQL database. If you have any doubts, feel free to leave a comment below.