In this hands-on project, I'll guide you through the process of inserting data into a MySQL database using a Python script. Let's get started!
1. Launch an AWS EC2 Instance
Begin by launching an AWS EC2 instance with Ubuntu as the distribution.
2. Install MySQL Server
sudo apt-get update
sudo apt-get install mysql-server -y
3. Enable MySQL on Boot
sudo systemctl enable --now mysql
4. Install Python3 and MySQL Connector
Install Python3 and MySQL Connector to facilitate Python's interaction with MySQL databases:
sudo apt-get install python3 -y
sudo apt-get install python3-mysql.connector -y
5. Create a MySQL Database
Next, we need to create a database. Log into the MySQL console with the command:
sudo mysql
CREATE DATABASE clients;
6. Create a User and Grant Access
Now, we can create a user (we’ll name it “manager”) and give that user permission to use the new database
CREATE USER 'manager'@'localhost' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON staff.* TO 'manager'@'localhost';
FLUSH PRIVILEGES;
Make sure PASSWORD is a strong, unique password. In this case we are keeping password as PASSWORD only.
7. Create a Table
We now have to create a table. First, change to the clients database and then Create a table with two columns, 'name' and 'email':
USE clients;
CREATE TABLE info (name VARCHAR(255), email VARCHAR(255));
We now have a database, a user, and a table ready for action.
8. Create a Python Script
This is where the fun starts. We’re going to create a Python application that injects data into the name and email columns of the info columns.
Create the script with the command:
nano insert.py
In that file, paste the following content:
import mysql.connector
name = input("Type a name: ")
email = input("Type an email: ")
mydb = mysql.connector.connect(
host="localhost",
user="manager",
password="PASSWORD",
database="client"
)
mycursor = mydb.cursor()
sql = "INSERT INTO info (name, email) VALUES (%s, %s)"
val = (name, email)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
Here’s a bit more explanation:
The first line imports the required function that allows Python to connect to MySQL.
The input() function, which allows the script to accept input from a user.
The mydb section configures the information for the database.
mydb.cursor() is the function that allows the insertion of data into the database.
The sql line is our first MySQL query.
The val line defines our columns for the database.
The mycursor.execute executes the above operations.
The mydb.commit() confirms the changes made by mycursor.execute.
The print line prints output to indicate success or failure.
Save and close the file with the Ctrl+X key combination.
9. Run the Application
We can now run our new Python app that will inject the data into the table that you specified in the script. The run command for this would be:
python3 insert.py
10. Now, when you run the application, it will prompt you for name and email input before adding the data to the MySQL database.
Congratulations! You've successfully inserted data into a MySQL database using a Python script