Python Connectivity with MySQL

Python can be used in database applications.

One of the most popular SQL database is MySQl.

HOW TO CONNECT
• To add Python with MySQL, first install MySQL Driver by pip command:
C:\Users\hp\AppData\Local\Programs\Python\Python36-32\Scripts> pip install mysql-connector
• After installation, check it in python prompt with the help of import command. If no error occurred then it
means driver installed successfully. E.g:

HOW TO KNOW THE USER OF MYSQL

CONNECTION WITH DATABASE IN PYTHON

• Make a page in python and write the following code and run it:

• If the following output displayed that understood, connection made:

TO MAKE NEW DATABASE IN MYSQL FROM PYTHON

• Write the following code for making new database in MYSQL:

• Recheck by see the database in MYSQL:

 • If you want to check that how many databases in MYSQL then write the following code in PYTHON file:

• OUTPUT:

TO MAKE NEW TABLE IN MYSQL FROM PYTHON

• If you want to create table in MYSQL from PYTHON then use following commands:

NOTE: Write the above connection string in one line

• To show all the tables in PYTHON use following commands:

• Output:

TO RUN ALTER COMMAND IN MYSQL FROM PYTHON

• Alter Table
 Output:

TO RUN INSERT COMMAND IN MYSQL FROM PYTHON

• Insert into command

OUTPUT:

TO RUN SELECT COMMAND IN MYSQL FROM PYTHON

• Select command
 Output:

• Select query with where clause:

OUTPUT:

NOTE: Just like that, we can run all the select command

TO RUN DELETE COMMAND IN MYSQL FROM PYTHON

• Delete from table with where clause:

Output:
 • Delete from table with where clause(2nd type code):

TO RUN UPDATE COMMAND IN MYSQL FROM PYTHON

• Update command with where clause:

• Update command with where clause(2nd type code):

• Output:
Make a Menu Driven Program to print the following details of given information using Python with MYSQL
Connectivity queries:
Database: COMPANY
Relation: EMPLOYEE
EMPN NAME FNAME ADDRESS CONTACT_NO DOJ SALARY
O(PK)
101 ANIL SINGH VISWANATH SINGH LUCKNOW 987654123 1980-02-12 50000
202 RAJ TIWARI KAMAL NATH LUCKNOW 963852741 1976-12-15 52550
303 SEEMA SINGH RAMANAND SINGH ALLAHABAD 789456123 1985-04-16 65000
404 ARTI DUBEY JAYNANDRA DUBEY KANPUR 741258963 1982-10-26 45000
505 REEMA KASHYAP SRIKANT KASHYAP ALLAHABAD 896523147 1987-11-04 48000

• 1 FOR CREATE TABLE STUDENT USING FUCNTION CREATE_TABLE_EMPLOYEE()

• 2 FOR INSERT ABOVE VALUEs IN THE TABLE USING FUCNTION INSERT_VALUES_ EMPLOYEE ()
• 3 FOR DISPLAY ALL THE INFORMATON FROM THE TABLE IN PYTHON SCREEN USING FUCNTION DISPLAY_ALL()
• 4 FOR ADD A NEW ATTRIBUTE DESIGNATION WITH APPROPRIATE DATA TYPE USING FUCNTION
ADD_NEW_FILEDS()
• 5 FOR UPDATE ALL THE VALUES OF DESIGNAITON THE RELATION STUDENT USING FUCNTION UPDTAE_VALUE_
EMPLOYEE () (DESIGNATION AS DIRECTOR OR CLERK OR MANAGER OR JOIN DIRECTOR, HEAD CLERK, PEON
• 6 FOR SEARCH THE INFORMTON VIA EMPNO INPUT BY THE USER USING FUCNTION SEARCH_VIA_EMPNO()
• 7 FOR SEARCH THE INFORMTON VIA NAME INPUT BY THE USER USING FUCNTION SEARCH_VIA_EMPNAME()
• 8 FOR DISPLAY ALL THE INFORMATION OF THE EMPLOYEE WHOSE SALARY IN BETWEEN 40000 TO 50000
FUNCITON DISPLAY_VIA_SALARY()
• 9 FOR DELETE THE TUPLE WHOSE EMPLOYEE NO IS INPUT BY THE USER, USING FUNCTION DEL_VIA_EMPNO()
• 10 DISPLAY THE EMPLOYEE DETAILS WHOSE ADDRESS IS INPUT BY THE USER USING FUNCTION
SEARCH_VIA_ADDRESS()
• PRINT THE EMPLOYEE DETAILS OF THE BELOW FORMAT OF THOSE WHICH EMP NO IS GIVEN BY THE USER
If input EMPNO IS 404 using function display_format()
******************************************************************************************
ROLLNO NAME FNAME ADDRESS CONTACT NO DOJ SALARY
404 ARTI DUBEY JAYNANDRA KANPUR 741258963 1982-10-26 45000
DUBEYraj
 Make a Menu Driven Program to print the following details of given information using Python with MYSQL
Connectivity queries:
Database: BUSINESS
Relation: COMP_SHOP
PRODUCT_ID(PK) PRODUCT_NAME QTY PRICE_PER_UNIT COMPANY_NAME
P1001 WIRED KEYBOARD 10 450 LOGITECH
P1002 WIREED MOUSE 20 250 LOGITECH
P1003 WIRELESS KEYBOARD 30 750 QUANTUM
P1004 WIRELESS MOUOSE 30 550 HP
P1005 MONITOR 50 5500 HP
P1006 RAM 100 1250 MICRON
P1007 PROCESSOR 50 2500 INTEL
P1008 SPEAKER 5 3500 JBL
P1009 PRINTER 10 6500 HP
P1010 HEAD PHONE 100 650 JBL

• 1 FOR CREATE TABLE COMP_SHOP USING FUCNTION COMPSHOP()

• 2 FOR INSERT ABOVE VALUES IN THE TABLE USING FUCNTION INSERT_VALUES_PERIPHERALS()
• 3 FOR DISPLAY ALL THE INFORMATON FROM THE TABLE IN PYTHON SCREEN USING FUCNTION DISPLAY_ALL() IN BELOW
FORMAT:
COMP_SHOP
PRODUCT ID PRODUCT NAME QTY UNIT PRICE COMPANY NAME
P1001 WIRED KEYBOARD 10 450 LOGITECH
P1002 WIRED MOUSE 20 250 LOGITECH
P1003 WIRELESS KEYBOARD 30 750 QUANTUM
P1004 WIRELESS MOUSE 30 550 HP
P1005 MONITOR 50 5500 HP
P1006 RAM 100 1250 MICRON
P1007 PROCESSOR 50 2500 INTEL
P1008 SPEAKER 5 3500 JBL
P1009 PRINTER 10 6500 HP
P1010 HEAD PHONE 100 650 JBL
*********************************************************************************************
• 4 FOR ADD A NEW ATTRIBUTE TOTAL PRICE APPROPRIATE DATA TYPE USING FUCNTION ADD_TOTAL_PRICE()
• 5 FOR UPDATE ALL THE VALUES OF TOTAL PRICE AS TOTAL PRICE = QTY * UNIT PRICE IN THE RELATION COMP_SHOP
USING FUCNTION UPDTAE_TOTAL_PRICE()
• 6 FOR SEARCH THE INFORMTON VIA PRODUCT ID INPUT BY THE USER USING FUCNTION SEARCH_VIA_PROID()
• 7 FOR SEARCH THE INFORMTON VIA PRODUCT NAME INPUT BY THE USER USING FUCNTION SEARCH_VIA_PRONAME()
• 8 FOR DISPLAY ALL THE INFORMATION OF THE STUDENTS WHOSE PRICE IS BETWEEN 500 TO 1000 USING FUNCITON
DISPLAY_VIA_PRICE()
• 10 DISPLAY THE COMP_SHOP DETAILS WHOSE COMPANY NAME IS INPUT BY THE USER USING FUNCTION
SEARCH_VIA_COMPANY_NAME()
• PRINT THE BILL OF PRODCUT WITH FOLLOWING SPECIFIACTION:
*************************************************************************************************
COMPUTER SHOP
BILL GENERATION
*************************************************************************************************
PRODCUT ID PRODUCT NAME QUNATITY PRICE PER PIECE TOTAL PRICE
P1007 PROCESSOR 20 2500 50000
AMOUNT TO PAID: 50000/-
Make a Menu Driven Program to print the following details of given information using Python with MYSQL
Connectivity queries:
Database: SCHOOL
Relation: STUDENT
RNO(PK) NAME FNAME ADDRESS CONTACT_NO DOB
1 ANIL SINGH VISWANATH SINGH HUSSAIN GUNJ 987654123 2001-02-12
2 RAJ TIWARI KAMAL NATH HAZARAT GANJ 963852741 2000-12-15
3 SEEMA SINGH RAMANAND SINGH GOMTI NAGAR 789456123 2002-04-16
4 ARTI DUBEY JAYNANDRA DUBEY AMINABAD 741258963 2001-10-26
5 REEMA KASHYAP SRIKANT KASHYAP NEELMATHA 896523147 2000-11-04

Relation: ACADEMICS
RNO(PK) CLASS SECTION CLASS HINDI ENGLISH MATH SCIENCE SST MARKS
TEACHER MARKS MARKS MARKS MARKS
NAME
1 XII B PUNIT 98 98 100 92 94
KANDPAL
2 XII B DINKAR 96 97 99 96 95
SINGH
3 XI A RAMAN 89 80 81 90 76
TRIPATHI
4 XI B JOYTI 58 75 80 58 62
SINGH
5 XII C KANISH 78 75 68 45 78
MOHAN

• 1 FOR CREATE TABLE STUDENT USING FUCNTION CREATE_TABLE_STUDENT()

• 2 FOR INSERT ABOVE VALUEs IN THE TABLE USING FUCNTION INSERT_VALUES_STUDNET()
• 3 FOR DISPLAY ALL THE INFORMATON FROM THE TABLE IN PYTHON SCREEN USING FUCNTION DISPLAY_ALL()
• 4 FOR ADD A NEW ATTRIBUTE TOTAL MARKS AND PERCENTAGE WITH APPROPRIATE DATA TYPE USING
FUCNTION ADD_NEW_FILEDS()
• 5 FOR UPDATE ALL THE VALUES OF TOTAL MARKS AND PERCENTAGE IN THE RELATION STUDENT USING
FUCNTION UPDTAE_VALUE_STUDENT()
• 6 FOR SEARCH THE INFORMTON VIA ROLL NO INPUT BY THE USER USING FUCNTION SEARCH_VIA_ROLLNO()
• 7 FOR SEARCH THE INFORMTON VIA NAME INPUT BY THE USER USING FUCNTION SEARCH_VIA_NAME()
• 8 FOR DISPLAY ALL THE INFORMATION OF THE STUDENTS WHOSE PERCENTAGE IS GREATER THAN 75 USING
FUNCITON DISPLAY_VIA_PERCENTAGE()
• 9 FOR DELETE THE TUPLE WHOSE ROLL NO IS INPUT BY THE USER, USING FUNCTION DEL_VIA_ROLLNO()
• 10 DISPLAY THE STUDENT DETAILS WHOSE CLASS IS INPUT BY THE USER USING FUNCTION
SEARCH_VIA_CLASS()
• PRINT THE STUDENT REPORT CARD OF THE BELOW FORMAT OF THOSE WHICH ROLL NO IS GIVEN BY THE USER
If input Roll no is 2
******************************************************************************************
ROLLNO NAME CLASS FNAME ADDRESS DOB PERCENTAGE
3 SEEMA SINGH XI RAMANAND GOMTI 2002-04-16 83.2
SINGH NAGAR