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 modulesimport mysql.connector # create connection objectcon = mysql.connector.connect( host="localhost", user="root", password="", database="GEEK") # create cursor objectcursor = con.cursor() # assign data queryquery1 = "desc Lazyroardemo" # executing cursorcursor.execute(query1) # display all recordstable = cursor.fetchall() # describe tableprint('\n Table Description:')for attr in table: print(attr) # assign data queryquery2 = "select * from Lazyroardemo" # executing cursorcursor.execute(query2) # display all recordstable = cursor.fetchall() # fetch all columnsprint('\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 connectioncursor.close() # closing connection objectcon.close() |
Output:
Note: XAMPP Apache and MySQL should be kept on during the whole process.
