A Beginner’s Guide on how to use Python to interact with MySQL and Query the Database Tables

As the amount of data we handle grows, it becomes increasingly important to store it in an organized and efficient manner. Database Management Systems (DBMS) allow us to do just that. MySQL is a popular open-source DBMS that can help you store and retrieve your data efficiently. In this tutorial, we will walk you through the steps to connect to MySQL using Python and query the database tables. By the end of this article, you will have a better understanding of how to use Python to interact with MySQL.

What is MySQL

MySQL is an open-source relational DBMS that was first released in 1995. It was created by Swedish founders Michael Widenius and David Axmark and was initially released under the name of ‘MiniSQL’. In 1996, it was renamed MySQL and became available under the GPL license. MySQL has been widely adopted across the world, making it one of the most popular open-source DBMS. It is used by many websites and applications such as WordPress, Facebook, and Twitter.

MySQL Queries

MySQL Queries: Before we dive into connecting to MySQL using Python, let’s first understand how to perform basic MySQL queries.

Step 1: Installing MySQL

The first step is to install MySQL on your machine. You can download the latest version of MySQL from the official website (https://dev.mysql.com/downloads/). Choose the appropriate version for your operating system and follow the installation wizard to complete the installation process. If you are looking for a detailed installation guide step by step please follow this article https://keepforyourself.com/how-to-install-mysql-on-your-linux-system/

Step 2: Creating a Database and Tables

After installing MySQL, the next step is to create a database and tables. You can do this by logging in to MySQL using the following command:

mysql -u root -p

This command will prompt you to enter the root password you set during the installation process.

Once you have logged in to MySQL, you can create a database using the following command:

CREATE DATABASE mydatabase;

Replace “mydatabase” with the name of your database.

After creating a database, you can create a table using the following command:

USE mydatabase;

CREATE TABLE mytable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  age INT,
  email VARCHAR(255)
);

This command creates a table called “mytable” with four columns: “id”, “name”, “age”, and “email”. The “id” column is set to auto-increment, which means that a unique ID will be generated automatically for each record.

Step 3: Basic MySQL Queries

Now that you have created a table, you can perform basic MySQL queries on it.

Inserting data into the table:

INSERT INTO mytable (name, age, email)
VALUES ('John', 30, 'john@example.com');

This command inserts a record into the “mytable” table with the name “John”, age 30, and email “john@example.com“. You can insert more records into the table using the same command.

Selecting data from the table:

SELECT * FROM mytable;

This command selects all records from the “mytable” table.

Updating data in the table:

UPDATE mytable SET name='Jane' WHERE id=1;

This command updates the name of the record with ID 1 to “Jane”.

Deleting data from the table:

DELETE FROM mytable WHERE id=1;

This command deletes the record with ID 1 from the “mytable” table.

Step 4: Connecting to MySQL using Python

To connect to MySQL using Python, you need to install the mysql-connector-python package. You can do this using pip, a package installer for Python, by running the following command in your terminal or command prompt:

pip install mysql-connector-python

Once you have installed the package, you can use the following Python code to connect to MySQL:

import mysql.connector

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

print(mydb)

Replace “yourpassword” with the password you set during the MySQL installation process and “mydatabase” with the name of your database.

The code above creates a connection object to MySQL and prints it to the console. You can use this connection object to execute SQL queries on your database.

Step 5: Querying the Database using Python

Now that you have established a connection to your MySQL database using Python, you can perform SQL queries on it.

Inserting data into the table:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "INSERT INTO mytable (name, age, email) VALUES (%s, %s, %s)"
val = ("John", 30, "john@example.com")

mycursor.execute(sql, val)

mydb.commit()

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

This code inserts a record into the “mytable” table with the name “John”, age 30, and email “john@example.com“. The commit() method is used to save the changes to the database.

Selecting data from the table:

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM mytable")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

This code selects all records from the “mytable” table and prints them to the console.

Updating data in the table:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "UPDATE mytable SET name = %s WHERE id = %s"
val = ("Jane", 1)

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

This code updates the name of the record with ID 1 to “Jane”.

Deleting data from the table:

import mysql.connector

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

mycursor = mydb.cursor()

sql = "DELETE FROM mytable WHERE id = %s"
val = (1,)

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

This code deletes the record with ID 1 from the “mytable” table.

Conclusion

In this article, we have walked you through the steps to connect to MySQL using Python and query the database tables. We started by explaining the importance of storing information in a database and then discussed MySQL, its history, and adoption across the world. We then covered the basic MySQL queries, including how to install MySQL, create a database and tables with test data, and how to query the database using SQL. Finally, we showed you how to use