Python MySql Database connection

Hello Guys, Welcome to Proto Coders Point. In this tutorial will discuss regarding python mysql connector i.e how to connect Mysql database using Python mysql connector Module.

MySql WorkBench Setup

First we will setup MYSQL WorkBench by creating the Database, For this we are going to use MYSQL WorkBench for Working with Database.

I have already created a Database so we can connect directly to database. Follow the below steps:

• Open MYSQL WorkBench Application. Go to Database and Click on Connect To Database
• Fill all details that are shown below like Hostname, Port no, userName and click on OK if password no credential added.

Otherwise if you add password Credential for Database connectivity, Then Follow Steps: –

• Add Connection Details(hostName, Port, UserName) etc as before.
• Click on Store in Vault…
• Enter the password and Click OK

After successfully connection with database you will get Query Windows for work with SQL Query.

Query 1: show databases;

Query 2: Use inc ; //inc is my database name.

Query 3: select * from user_details;

We successfully done with the MYSQL Connection, Now we have to Access by using Jupyter Notebook.

So with this you need to Install MySQL Installer.msi file and Jupyter Notebook else You can use different IDE with execution like Pycharm, Python IDLE.

How to connect and fetch mysql database in python

1. Import mysql.connector Module :

python mysql.connector Module that enable the access of MYSQL Database .

import mysql.connector
if __name__ == "__main__":
    connection = mysql.connector.connect(host='localhost',
                                         database='inc',
                                         user='admin',
                                         password='amazon123')

Code Screenshot Example:

If MYSQL Installer is not available in your System then it will display ModuleNotFoundError like.

So you need to download and install MYSQL Installer .msi file

Link :- https://dev.mysql.com/downloads/connector/python/

2. Create Cursor() object :

The MySQLCursor of mysql-connector-python is used to execute statements to communicate with the MySQL database. and creating cursor object to perform SQL CRUD operation.

import mysql.connector
if __name__ == "__main__":
    connection = mysql.connector.connect(host='localhost',
                                         database='inc',
                                         user='admin',
                                         password='amazon123')
    
    cur = connection.cursor()
    print(cur)

#OUTPUT
CMySQLCursor: (Nothing executed yet)

3. Execute the Query using execute()

The execute () methods run the SQL query and return the result provided by database data response.

use cursor.fetchall() or fetchone() or fetchmany() to read query result. This method executes the given database operation. The parameters found in the tuple or dictionary are bound to the variables in the operation.

cur.execute("select * from user_details")
data=cur.fetchall()
#print(data)

for i in data:
print(i)

Complete Source Code – Python Program To fetch data from MySQL Database

import mysql.connector
if __name__ == "__main__":
    connection = mysql.connector.connect(host='localhost',
                                         database='inc',
                                         user='admin',
                                         password='amazon123')

cur = connection.cursor()
cur.execute("select * from user_details")
data=cur.fetchall()
#print(data)

for i in data:
print(i)

Output of above python sql program :-