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

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()

Output:

Note: XAMPP Apache and MySQL should be kept on during the whole process.