How to Insert Data into a MySQL Database using Python

Introduction

In this tutorial, we will learn how to insert data into a MySQL database using Python. We will explore different methods to generate SQL insert queries and execute them in Python.

Prerequisites

Before we begin, make sure you have the following:

• Python installed on your machine
• MySQL installed on your machine
• MySQL Connector/Python library installed (can be installed using pip: pip install mysql-connector-python)

Step 1: Establish a Connection to MySQL

To connect to a MySQL database, we need to install the MySQL Connector/Python library. Once installed, we can use the following code to establish a connection:

import mysql.connector

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

mycursor = mydb.cursor()

Make sure to replace yourusername, yourpassword, and yourdatabase with your own credentials.

Step 2: Generate the Insert Query

To insert data into a MySQL database, we need to generate an SQL insert query. There are multiple ways to generate this query in Python. Here are a few examples:

Example 1: Inserting a Single Row

To insert a single row of data into a MySQL table, you can use the following code:

sql = "INSERT INTO table (column1, column2, column3) VALUES (%s, %s, %s)"
values = ("value1", "value2", "value3")

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

Make sure to replace table with the name of your table, column1, column2, and column3 with the names of your table’s columns, and "value1", "value2", and "value3" with the actual values you want to insert.

Example 2: Inserting Multiple Rows

To insert multiple rows of data into a MySQL table, you can use the following code:

sql = "INSERT INTO table (column1, column2, column3) VALUES (%s, %s, %s)"
values = [("value1", "value2", "value3"), ("value4", "value5", "value6"), ("value7", "value8", "value9")]

mycursor.executemany(sql, values)
mydb.commit()

Make sure to replace table with the name of your table, column1, column2, and column3 with the names of your table’s columns, and "value1", "value2", "value3", "value4", "value5", "value6", "value7", "value8", and "value9" with the actual values you want to insert.

Example 3: Generating Insert Query with User Input

To generate an insert query with user input, you can use the following code:

table = input("Enter the table name: ")
columns = input("Enter the column names (separated by commas): ")
values = input("Enter the values (separated by commas): ")

columns = columns.split(",")
values = values.split(",")

columns = ", ".join(columns)
placeholders = "%s, " * len(values)
placeholders = placeholders[:-2]

sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
mycursor.execute(sql, values)
mydb.commit()

This code will prompt the user to enter the table name, column names, and values. It will then generate the insert query based on the user input.

Conclusion

In this tutorial, we learned how to insert data into a MySQL database using Python. We explored different methods to generate SQL insert queries and execute them in Python. Now you can confidently insert data into your MySQL tables using Python!

• Python
• MySQL
• Database
• Data Insertion