Connect to MySQL with Python! with Simple Example
Follow these simple steps to connect to MySQL with Python
While working on a python project it will be difficult to collect all the data from the console to an excel sheet for analyzing the results and drawing conclusions from what we have done. Tabulating the data simplifies everyone's tasks to debug the issues and go further in the code, it happened with me also. Then a wonderful question came into my mind “Why can’t I connect my IDE with the MySQL DB to collect data and analyze it later?” because when we connect with DB it collects every piece of data that is necessary for us to conclude the results.
My software specification
- Anaconda Individual Edition Python 3.9 (You can use any latest version of Python) https://repo.anaconda.com/archive/Anaconda3-2021.11-Windows-x86_64.exe If you want you can download from this link.
- The latest version of MySQL 8.0.28, If you want you can download from this link - https://dev.mysql.com/downloads/file/?id=510038
- VScode latest version
Steps to connect MySQL with python
Follow these steps to connect with Mysql via python
1. Install the python — MySQL Connector
Once you have installed the Anaconda (or any other python versions) open the anaconda shell and install the MySQL connector with the PIP command. If PIP is not configured in the system then you can install using CONDA command also.
pip install mysql-connector-pythonorconda install -c anaconda mysql-connector-python
2. Connect to the MySQL from python
Open the vscode and add a new folder to the workspace for all the workings we are going to do in the process. Import the MySQL connector and connect to the MySQL by passing the connect command. Provide all the required credentials required for the connection. In host, it is commonly localhost for many in case if it is different you can change it. Even the user, password should be replaced by your user name, password.
If you want to check whether the connection is established or not, then you can execute the print(db). If you see like below statement in the console the connection is established, if not check the credentials provided by yourself.
3. Create the Database and connect with it
Creating an instance of the ‘cursor’ class which is used to execute the ‘SQL’ statements in ‘Python’. Line 10 passes a execute command which creates a database in MySQL db with some name. Now you can check whether we can connect to the new database created by us just by calling the object of connection.
You will see something like this in the console of VScode which means a connection is established with the database and the python.
#Tip : How to check all the databases in the MySQL ? Its quite simple follow these steps cursor.execute(“SHOW DATABASES”) databases = cursor.fetchall() print(databases)# This will show all available databases in MySQL Environment
4. Time to create table
Once the database is created we are all ready to create the table for our use. Before that, we should have an idea regarding the format of the table, columns names, and datatypes of those columns. Remember to have a primary key (Most likely the serial number) in the table format. In My case, the table format is as below. Here SL_No is the “primary key” with data type as “int”, and person name is the data type of “varchar” and the last phone_number is the data type of “int”. Like this we should have the idea of table format.
Now, let’s execute it and create the table in python.
if you run the above code you will find the table in the workspace of Mysql. To check this you have to open MySQL workspace and execute a new query like this
# Do this in the MySQL workbench application
use database_name;
SELECT * FROM office_staff;
5. Insert the data into the table via python
To insert the data into the table you need to pass a query in python with the “INSERT INTO” command, make sure to write the exact names of table name and columns names. Now, you need to give values of those columns in the variable called values here. Execute the query and values and commit all the changes by passing a commit command appended with the object name. You can check whether the values are inserted or not? by refreshing the table.
So, this is how we have established a connection with the MySQL database via Python and successfully inserted the data into the table.
That’s all for now, stay tuned to my profile for further interesting topics!
See my previous blogs on What if technology has Feelings and how to prevent costs on AWS free tire?
https://medium.com/@sreevishnu.dharmavaram/get-your-free-onedrive-5tb-storage-now-e35bc1634506
https://medium.com/@sreevishnu.dharmavaram/what-if-technology-has-feelings-9c81adb54518
