In this section, I'll explain how you can connect your MySQL database to Python and query it. To connect MySQL with Python, you will need to have both MySQL and Python installed on your system.

To access the MySQL database, Python needs a MySQL driver called MySQL Connector, So you must first install the MySQL connector package on your computer. The MySQL connector package is available on the Python Package Index (PyPI), and it can be easily installed using the pip package manager.

You can do this by running the following command in your terminal:

pip install mysql-connector-python

Import the MySQL connector module into your Python code. This module provides a Python API for connecting to and interacting with a MySQL database.

To import the module, use the following syntax:

import mysql.connector

For purposes of example, we will need a sample database in MySQL that you can connect to. If you don't have a database to do so, follow the below steps:

• First, open a MySQL client tool like MySQL Workbench or use a terminal.
• Second login to the database using your credentials.
• Finally, run the following command to create a database (for example, company).

CREATE DATABASE company;

Create a new MySQLConnection object by calling the connect() function from the mysql.connector module. This function takes a number of parameters that specify details about the database you want to connect to, such as:

• host: the IP address or hostname of the MySQL server. By default, this is localhost.
• user: the username that you want to use to connect to MySQL.
• password: the password associated with the username.
• database: the name of the database that you want to connect to.

For example:

mydb = mysql.connector.connect(
  host="localhost",
  user="<your_username>",
  password="<your_password>",
  database="<your_database>"
)

Once you have successfully established a connection to the database, you can start running SQL queries and performing other operations.

You must create or include a cursor in your python code in order to run MySQL queries.

mycursor = mydb.cursor()

A cursor is a pointer to the result set of a query, and it is used to iterate over the rows of the result set. Cursors are useful for breaking up large result sets into smaller pieces and processing them one row at a time.

By using the CREATE TABLE statement, it creates a new table called employee with three columns:

mycursor.execute('''CREATE TABLE employee(
    EmployeeID int,
    Name varchar(255),
    Email varchar(255));
''')

Now your table has been created.

By using the INSERT INTO statement to insert some records into your employee table, you just created

mycursor.execute('''
  INSERT INTO employee (EmployeeID, Name, Email)
    VALUES (101, 'Mark', '[email protected]'),
           (102, 'Robert', '[email protected]'),
           (103, 'Spencer', '[email protected]');
''')

It's important to remember to save your changes to the database using the commit() method every time you run the query.

mydb.commit()

This is necessary because MySQL uses a transactional storage engine, which means that changes are not visible to other connections until they are committed.

Now, you have to use the execute() method of the cursor object to execute a SELECT statement that retrieves all rows from the employee table. Then you have to use a for loop to iterate over the rows returned by the query and print them to the console.

mycursor.execute("SELECT * FROM your_table")

for x in mycursor:
  print(x)

If the above code was executed without errors, you have successfully viewed table records. Now that your cursor is working, you can also run other SQL commands to query the database.

And finally, once you're done, you need to close the cursor and the connection to free up resources and prevent potential issues.

mycursor.close()
mydb.close()

You can also get code snippet from here.

I am Kishlay, and I have written this tutorial, but other people have also helped me with it. If you have any trouble with this tutorial, please tell me about it, and I will make it better. If you like this tutorial and this helped you out, then please give it a star.

If you have any feedback, please reach out to me at [email protected]