Extract Data from Database using MySQL-Connector and XAMPP in Python

Last Updated : 01 Nov, 2020

Prerequisites: MySQL-Connector, XAMPP Installation

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.

Requirements

XAMPP: Database / Server to store and display data.
MySQL-Connector module: For connecting the database with the python file. Use the below command to install this module.

pip install mysql-connector

Wheel module: A command line tool for working with wheel files. Use the below command to install this module.

pip install wheel

Step-by-step Approach:

Procedure to create a table in the database:

Start your XAMPP web server.
Type http://localhost/phpmyadmin/ in your browser.
Go to Database create database with name and click on Create.

Create a table with in GEEK database and click on Go.

Define column names and click on save.

Your table is created.

Insert data in your database by clicking on SQL tab then select INSERT.

The data in your table is:

Now you can perform operation IE display data in your web page using python

Procedure for writing Python program:

Import mysql connector module in your Python code.

import mysql.connector

Create connection object.

conn_object=mysql.connector.connect(hostname,username,password,database_name)

Here, you will need to pass server name, username, password, and database name)

Create a cursor object.

cur_object=conn_object,cursor()

Perform queries on database.

query=DDL/DML etc
cur_obj=execute(query)

Close cursor object.

cur_obj.close()

Close connection object.

conn_obj.close()

Below is the complete Python program based on the above approach:

Python3

# import required modules
import mysql.connector

# create connection object
con = mysql.connector.connect(
  host="localhost", user="root",
  password="", database="GEEK")

# create cursor object
cursor = con.cursor()

# assign data query
query1 = "desc geeksdemo"

# executing cursor
cursor.execute(query1)

# display all records
table = cursor.fetchall()

# describe table
print('\n Table Description:')
for attr in table:
    print(attr)

# assign data query
query2 = "select * from geeksdemo"

# executing cursor
cursor.execute(query2)

# display all records
table = cursor.fetchall()

# fetch all columns
print('\n Table Data:')
for row in table:
    print(row[0], end=" ")
    print(row[1], end=" ")
    print(row[2], end=" ")
    print(row[3], end="\n")
    
# closing cursor connection
cursor.close()

# closing connection object
con.close()