Python_Mysql_Connectivity
Fetch Data From Mysql
1. Create database in Mysql:-
2. Use database:-
3. Create table Employee_rec_1:-
4. Using desc command check table attribute :-
5. Insert data into table Employee_rec_1:-
6. Check records from table Employee_rec_1:-
7. Building connection with Mysql server in Python:-
Import [Link] in python
Established connection with mysql by using .connect. Connection object is
“mycon”
Using parameter (Host, User, Password, Database)
Checking connection is successful or not by using statement(is_connected() )
Creating cursor with object name “cur”
8. Given a query to fetch all data from table ‘Employee_rec_1’:-
Built a query object named “query”.
Execute the query by using (“[Link]()”) command.
Fetching data type is fetchall
Command [ [Link]() ] with the object name data.
9. For getting output as simple manner using for loop:-
Create a for loop statement with data.
Using ([Link]) for generate information of rows.
10. Output when execute command:-
11. Output fetchall with condition :-
By using dept=’HOD’ condition
12. Using (fatchone) command:-
Command using (“data=[Link]()
print(data)”)
Output :-
13. While fetchone function multiple time executed:-
Syntax was repeated 3 times and it has select three consecutive rows.
Output:-
14. Fetchmany function without determine any number:-
Command of fetchmany function without any number.
Output:- It will retrieve the first row.
15. Fetchmany function with 5 row number:-
Command for fetchmany function with 5 row number. Using for loop for better
alignment.
Output:-
16. PARAMETARIZED QUERIES
Queries can be raised in two style OLD style and New style.
Old style based on string template with “%”. f%v
Where ‘f’ is template string and ‘v’ specifies the values to be formatted using the
template. v must be a tuple.
Example “select * from table_name where condition >%s” % (70,)
New style based on string template with “%” formatting.
Example “select * from table_name where condition >{ } and section = ‘{ }’ ” .format (70,
‘B’)
17. Command in Parameterized Queries based on Old style:-
Import [Link] and connect with server.
Create cursor
Given the input section from user.
Raised queries in two parts. First the string part and second the value part
Execution of command with for loop.
Output:-
18. Command in Parameterized Queries based on New style:-
As command in New style string templates with { } formatting.
Output:-
19. Perform Insert, Update and Delete queries using cursor.
Create a database name HIPS in Mysql, by using this database create a table name
s_data with the following columns.
Roll, Name, Class, Section and Marks.
20. Command for run time insertion of data:-
Create a python file a connect to HIPS database;
Create command for runtime insertion of data:
21. Run command for insertion data:
22. Output shows in mysql:
23. Run the same command for insertion data as 5 times:-
Output in mysql:-
24. Update marks through python:-
Command :-
Output:-
25. Delete a row in run time through python:-
Command:-
Python Output:-
Mysql Table update:-