Python MySQL Database Connectivity - MySQL Connector 2022 Complete Guide

 Python MySQL Connector:

To build the real-world applications, connecting with the databases is the necessity for the programming languages. However, python allows us to connect our application to the databases like SQLite, MySQL, Oracle, Sybase, PostgreSQL etc.Python also supports Data Definition Language (DDL), Data Manipulation Language (DML) and Data Query Statements. Data is stored in a collection of tables with each table consisting of a set of rows and columns. This is similar to how data is stored in SQLite. To interact with the data stored in tables we use a special-purpose programming language called SQL.MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases.To connect Python with MySQL, follows the below steps

o   Step-1: Install Python + PIP

o   Step-2: Install MySQL

o   Step-3: Integrate / Connect Python and MySQLusing MySQL Connector

A] Install Python + PIP:

PIP is a package management system used to install and manage software packages/libraries written in Python. These files are stored in a large “on-line repository” termed as Python Package Index (PyPI).

1.     Open Google Chrome Browser

2.     Type https://www.python.org/downloads/

3.     Download the latest version for windows

4.     Now .exe file will be downloaded in your local system

5.     Install by double clicking on it

6.     Click on Customize Installation

7.     Click on Next

8.     Select Install python 3.11 for all users

9.     Click on Install

10.  Open cmd and type python --version and pip --version

B] Install MySQL:

a)     Install MySQL Server 8.0.31

b)    Install MySQL Workbench 8.0.31

c)     Install MySQL Shell 8.0.31

1.     Open Google Chrome Browser

2.     Type https://www.mysql.com/   

3.     Go to Downloads

4.     Scroll down the page and find MySQL Community (GPL) Downloads 

5.     Search for MySQL Installer for Windows

6.     Select the second option

7.     Select No thanks, just start my download

8.     Install mysql-installer-community-8.0.31.0 by double clicking on it

9.     Select Custom Installation and click on Next

10.  Select the Products and Click on Next

11.   Click on Execute

12.   Once installation completed then click on Next

13.  Install Microsoft Visual C++ Redistributable for Visual Studio 2019 which is required for MySQL Installation. Since it is already available in our system, so it did show us

14.  Click on Next

15.  Enter MySQL Root Password and click on Next

16.  Click on Execute and finish

17.  Before Clicking on Finish Button in below screenshot, set the path, first find in which location MySQL installed. For example, C:\Program Files\MySQL\MySQL Server 8.0\bin

18.  To set the path Go to View Advanced System Settings >>> Environmental Variables >>> System Variable >>> Double click on Path  >>>

19.  Click on New and Paste the Path

20.  Click on Finish

21.  Open  cmd

22.  Type mysql –version

23.  Type mysql -u root -p

24.  Enter Password then MySQL will run

25.  Type show databases; then default databases will be displayed

26.  Type create database RaisDB

27.  Open MySQL Workbench for creating Databases and practice

C] Integrate / Connect Python and MySQL using MySQL Connector

To install python connector Open cmd and type pip install mysql-connector-python

Ø  A connector is employed when we have to use MySQL with other programming languages.

Ø  The work of mysql-connector is to provide access to MySQL Driver to the required language. Thus, it generates a connection between the programming language and the MySQL Server.

Ø  Python MySQL Connector is a Python driver that helps to integrate Python and MySQL. This Python MySQL library allows the conversion between Python and MySQL data types.

# Python program to connect to mysql database

import mysql.connector

# Connecting from the server

conn = mysql.connector.connect(user='root',host = 'localhost',database = 'raisdb',password='Mysql_Server@312')

 

dict = {

'Name': 'Online Smart Trainer', 'ID': 'LRDS1605016','College Name': 'LIET'

}

 

a=10

b=20

if conn.is_connected():

    print("Connection is established....")

print(dict)

print("Addition of numbers", a+b)

# Disconnecting from the server

conn.close()

 Save Above program in notepad file with .py extension

Open Python IDLE (3.11 64-bit) >>> File >>> Open >>> run

Output: