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.
- python-dotevn
- the
- python-mysql-connector
- 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:
Python: show my TCP and UDP ports
Top Python Libraries for Data Science
Link to this project’s portfolio page – Videos:
Juliana Mascarenhas
Data Scientist and Master in Computer Modeling by LNCC.
Computer Engineer
Build Your Own DNS Server: A Step-by-Step Guide using BIND9
In this step-by-step guide, we’ll walk you through the entire process of setting up and…
Tutorial for SSH Public Key Authentication
In this tutorial, we will teach you how to create and configure access to an…
Socket UDP Python Chat
Tutorial for creating a simple chat using UDP sockets in Python 3. The goal is…
Socket TCP Python make a chat
Tutorial for creating a simple chat using TCP sockets in Python 3. The goal is…
apt get behind proxy
Over time, I have often come across the need to configure apt get to work…
Best IDE for Python?
Finding the perfect IDE is a personal journey that depends on various factors such as…