Performing Database operations on MySQL using Python
Objective: Learn how to connect to MySQL Server and execute SQL Commands using python programming
Problem Description: Easy Shop retial application need a database application to maintain their customer and purchase details. Write a python program to establish a database connectivity between MySQL and python.
To Start, we need to install the driver mysql-python connector, download it from link below: - https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-8.0.11.zip
After Downloading, Extract the zip
Enter the following command to windows command line
cd <extracter zip path>\
python setup.py install
Note: You'll need to add python to *System Variables under *Environmental Variables to execute installation command
import mysql.connector;
connection = mysql.connector.connect(host='localhost',database='rdbms',user='root',password='') '''Connection String'''
cur = connection.cursor();
cur.execute("Select * from supplier;")
data = cur.fetchall();
connection.commit()
For rollback use: -
connection.rollback()
Summary of this assignment: To learn database connection of python with MySQL Server.
Create Commands
Objective: Given a real time problem, to search for a particular supplier and display the details
Problem Description: Consider the scenario to display supplier details using different fetch methods.
cur.fecthall()
cur.fecthone()
cur.fecthmany()
Objective: In continuation to Assignment 2 Hands-On and use SELECT operations to reterive and display records using python code.
Problem Description:
- Retrieve all the records of item table.
- Retrieve the supplier name and supplier’s contact number of the supplier ‘S1002’.
- Retrieve the quotation id and supplier id of the quotations which have been either ‘Accepted’ or ‘Rejected’.
- Retrieve supplier details like supplier id and supplier name whose names have ‘i’ as the second character
- Retrieve the quotation details like quotationid, quotationdate, quotedprice for those quotations which are quoted in the range of 1400 and 2150