Open In App

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

Improve
Improve
Like Article
Like
Save
Share
Report

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. 



Last Updated : 01 Nov, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads