python mysql connector
python mysql connector

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.

Library Management System Project I...
Library Management System Project In Python and MySQL with Source Code 2021
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 .

To Create a Connection with MYSQL and Python, Use Connect() method of mysql.connector Module and then add the details with connection like Host IP, UserName, Password & Database Name, refer below snippet code for the same.

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)
#OUTPUT

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 :-