Have you ever wanted to inject data into a MySQL database using Python? In this tutorial, you will learn how to smoothly inject data into MySQL tables with Python. You might be developing a web application, data pipeline, or just exploring databases; let’s dive in!

What You’ll Need

For this tutorial, we’ll use:

Ubuntu Server 22.04.3 (commands will vary slightly for other OS)
Python3
MySQL

Make sure you have sudo privileges on your system.

Step 1: Install MySQL and Python

First, let’s install MySQL and Python on your Ubuntu server.

Install MySQL Server:

sudo apt-get install mysql-server -y

Enable MySQL to run at boot:

sudo systemctl enable --now mysql

Install the MySQL Connector for Python:

sudo apt-get install python3-mysql.connector -y

Ensure Python3 is installed:

sudo apt-get install python3 -y

Step 2: Set Up MySQL Database and User

Now, let’s set up our database and user in MySQL.

Log into the MySQL console:

sudo mysql

Create a database named staff:

CREATE DATABASE staff;

Create a user and grant permissions:

CREATE USER 'jack'@'localhost' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON staff.* TO 'jack'@'localhost';

Use the new database:

USE staff;

Create a table named editorial with columns for id, name, and email:

CREATE TABLE editorial (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), email VARCHAR(30));

Step 3: Create the Python Script

Let’s create a Python script to insert data into the editorial table.

Create a new Python file:

nano insert.py

Add the following content to insert.py:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="jack",
  password="strongpassword",
  database="staff"
)

mycursor = mydb.cursor()

sql = "INSERT INTO editorial (name, email) VALUES (%s, %s)"
val = ("John Doe", "john.doe@example.com")
mycursor.execute(sql, val)

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

Explanation:

import mysql.connector: Imports the MySQL Connector module, which allows Python to interact with MySQL databases.
mydb = mysql.connector.connect(…): Establishes a connection to the MySQL database using the specified host, user, password, and database.
mycursor = mydb.cursor(): Creates a cursor object, which is used to execute SQL queries.
sql = “INSERT INTO editorial (name, email) VALUES (%s, %s)”: Defines the SQL query for inserting data into the editorial table.
val = (“John Doe”, “john.doe@example.com“): Specifies the values to be inserted into the name and email columns.
mycursor.execute(sql, val): Executes the SQL query with the specified values.
mydb.commit(): Commits the transaction to the database.
print(mycursor.rowcount, “record inserted.”): Prints the number of records inserted to the console.

Step 4: Run the Script

Run your Python script to insert data into the MySQL database.

python3 insert.py

You should see a confirmation message indicating the number of records inserted.

Step 5: Accepting User Input

For a more dynamic approach, modify your script to accept user input.

Update insert.py to use the input() function:

import mysql.connector

name = input("Type a name: ")
email = input("Type an email: ")

mydb = mysql.connector.connect(
  host="localhost",
  user="jack",
  password="strongpassword",
  database="staff"
)

mycursor = mydb.cursor()

sql = "INSERT INTO editorial (name, email) VALUES (%s, %s)"
val = (name, email)
mycursor.execute(sql, val)

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

Explanation:

name = input(“Type a name: “) and email = input(“Type an email: “): Prompts the user to input a name and email.
val = (name, email): Uses the input values to be inserted into the database.

Run the updated script

python3 insert.py

This time, the script will prompt you to enter a name and email, which will then be inserted into the database.

Conclusion

And there you have it! You’ve successfully connected Python to a MySQL database and inserted data into a table. With these steps, you can start building more complex applications that interact with databases, making your projects more dynamic and powerful. Happy coding!

Happy coding!

Leave a response to this article by providing your insights, comments, or requests for future articles.

Share the articles with your friends and colleagues on social media.

Let’s Get in Touch! Follow me on :