How to connect mysql using python

This project from playlist on Youtube, from the channel SR from Python with MySQL.

The objective of this project is to create a connection class and CRUD methods for retrieving, manipulating and removing data from the MySQL database in a local instance.

To browse database articles access our category: Database in Simplifying Networks

To see our selection of technical books click here

Libraries used in the project

You will notice that some methods were created for teaching purposes. For these, as well as others, you can make your own modifications.

1. python-dotevn
2. the
3. python-mysql-connector
4. Fastapi

But to get started, you just need python-mysql-connector. Run the command below in the terminal:

$ pip install python-mysql-connector

First step – Connecting to MySQL with Python

This article follows the evolution of the code created in the playlist. Therefore, we will start with simpler code and then increase the complexity.

Connection function

You can continue using functions, classes and methods, or execute everything within main. Make it as simple as possible for you to understand.

For your connection to be successful, after installing the packages, create the following code snippet in your application.

I use pycharm, but you can do it in VS code or even in Gedit. lol

def _connecting(user_var, passwd, host, database):
         return mysql_connector.connect(
             user=user_var
             password=passwd,
             host=host,
             database=database,
         )

This function returns the connection resource, where you will allocate the cursor and consequently execute the queries.

For those who run it inside main, the code looks like this:

import mysql.connector as mysql_connector

def connecting(user_var, passwd, host, database):
         return mysql_connector.connect(
             user=user_var
             password=passwd,
             host=host,
             database=database,
         )

if __name__ == '__main__':

    conn = connecting('your_user', 'your_password', 'localhost', 'your_database')
    if conn:
       print('Connection successful!')
   else:
       print('Error connecting! Check connection parameters')

Moving connection data from the main code – Environment variables

For this you will need to use the python-dotenv package. Therefore, use the command below to install via pip.

$ pip install python-dotenv

You can create a file containing all the connection data to the database, just name it .env.

To be able to use the data inside the file, you need to load it.

from dotenv import load_dotenv
import them

load_dotenv()

host = os.getenv("HOST")
username = os.getenv("USERNAME")
passwd = os.getenv("PASSWD")
database = os.getenv("DATABASE")

conn = connecting(username, passwd, host, database)

Here you instantiated load_dotenv(), loaded the data through the getenv method via li os, passing the name of the target variable as a parameter.

And why use environment variable with python?

Very well, now your data is decoupled from the code. When you share your project, you don’t need to pass the .env file. Just explain how the data was defined.

Creating the Database class

OK, let’s take what we’ve done so far and isolate it within a class. To make the connection even more protected, we will use privacy operators.

Adding the connection function to the MySQLDatabase class, we have the following code.

import os
import mysql.connector as mysql_connector
from dotenv import load_dotenv

load_dotenv()

class MySQLDatabase:

     def __init__(self):
         self._host = os.getenv("HOST")
         self._username = os.getenv("USERNAME")
         self._passwd = os.getenv("PASSWD")
         self._database = os.getenv("DATABASE")
         self.conn = self._connecting()

     def _connecting(self):
         return mysql_connector.connect(
             user=self._username,
             password=self._passwd,
             host=self._host,
             database=self._database,
         )

The _ operator before class attributes defines that they can only be accessed within the class. The same goes for methods.

Therefore, our _connecting() method can only be accessed by the MySQLDatabase class.

Returning MySQL connection parameters with Python

If you want to know if it really is in the chosen database, simply create the method below to return the name of the database, within the class.

def get_database_name(self):
return self._database

The same happens for user and the other connection parameters.

def get_database_user(self):
return self._username

Creating connection closure method

Ok, when you close the application the connection is closed. But let’s make sure it happens.

It’s quite simple, actually. Just use the close() method on the connection generated at the beginning of the code. How to put this in the method?

def closing(self):
     if self.conn.is_connected():
         self.conn.close()

Querying database tables in MySQL with Python

Before finishing, let’s create a method that returns the database tables that we are accessing within the DBMS, MySQL.

To do this we need to execute the SQL command: SHOW TABLES;

Let’s go to the code:

 def get_database_tables(self):
         dict_of_tables = self._querying('SHOW tables;')
         print(f'List of tables on {self._database} database!\n')
         for table in dict_of_tables:
             print(' '.join(['-', table['Tables_in_'+self._database]]))
         return dict_of_tables

The querying() method was created separately so that we can take advantage of the code in other methods. So, we just need to pass the string with the desired query.

Let’s go to the method….

How to execute a query with Python in MySQL?

We need to allocate the courses through the connection created, with MySQL using Python. So, using cursor (conn.cursor) we can execute the SQL query. You can see it in the method code.

def _querying(self, query: str):
     if (not self.conn.is_connected()) or self.conn is None:
         self.conn = self._connecting()

     cursor = self.conn.cursor(dictionary=True)
     cursor.execute(query)
     result = cursor.fetchall()
     cursor.close()

    return result

The cursor method receives dictionary as True, as it will be used in queries that have parameters to be passed. Such as id, name and another attribute that is a conditional for the query.

fetchall() will return a list of tuples from the SQL query. The result receives the list of data, so we can close the connection.

Well… that’s it, in the next article we will have CRUD created with Python and MySQL.

See you then!

See more:

Create API in Python MySQL

Python: Show Open TCP ports

Install Python on Windows 11

Python: show my TCP and UDP ports

Top Python Libraries for Data Science

Link to this project’s portfolio page – Videos:

Create API in Python MySQL