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.
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.