1
12 C
Day – 2
Structured Query Language
• Structured Query Language
• Interface MySQL with Python.
• Display
• Insert
• Update
• Delete
Learning Objective : To interface Python with MySQL
2
RECALL
• In your notebook , write the command to connect to MySQL database ‘GRADE12’
from Python. (2 mins)
Write a remark on your
performance.
Learning Objective : To interface Python with MySQL
3
TO CHECK CONNECTIVITY is_connected()
Learning Objective : To interface Python with MySQL
4
ACTIVITY – 1
• Create database GRADE12 with table MARKS.
• Insert the first 2 records.
Learning Objective : To interface Python with SQL
5
MAIN PROGRAM
Learning Objective : To interface Python with MySQL
6
DISPLAY RECORDS
➢ Display data in tuple in table format using String formatting
Learning Objective : To interface Python with MySQL
7
MANAGE DB TRANSACTIONS IN PYTHON
➢ commit( ) – MySQLConnection.commit() method sends a COMMIT statement to MySQL
server, communicating the current transaction.
Commit() commits to the changes on the database.
Eg: - mydb.commit()
➢ rollback( ) – To revert / undo all changes
rollback( ) is called when a transaction fails to execute and you want to revert the changes.
Eg: - mydb.rollback( )
After executing insert or update query we must commit our query using connection object with
commit( ).
Eg: - mydb.commit()
Learning Objective : To interface Python with MySQL
8
PLACE HOLDER - In Python, Placeholder is a word, characters or a string of characters to hold a temporary place.
The placeholder behaves as a dynamic place holder in such a way that you can pass a particular value for that
placeholder
Only for your
information about the
concept of place holder
in Python
Learning Objective : To interface Python with MySQL
9
INSERT RECORDS – VALUES THROUGH PROGRAM
➢Initialize values in table through program.
Eg:-
"INSERT INTO stdetails VALUES (1,'ABDULLA AMEEN',97)"
Learning Objective : To interface Python with MySQL
10
INSERT RECORDS – USER INPUT
➢Method – 1 :
"INSERT INTO stdetails VALUES (%s,%s,%s)",(a,b,c)
# a→id , b→name , c→marks
# all data are accepted as string Syllabus :
use of
1. %s format specifier or
2. format( ) to perform queries
➢Method – 2 :
"INSERT INTO stdetails VALUES ({},'{}',{})”.format(a,b,c)
# a→id(int) , b→name(string) , c→marks(int)
Learning Objective : To interface Python with MySQL
11
INSERT RECORD
Method 1 : Using
%s format (string
format)
Method 2 : Using
.format( )
Learning Objective : To interface Python with MySQL
12
DAY - 3
Learning Objective : To interface Python with MySQL
13
UPDATE RECORDS
➢ Search by id and update mark
'UPDATE stdetails set marks={} where id={}'.format(newm,idno)
Learning Objective : To interface Python with MySQL
14
DELETE RECORDS
➢Search by id and delete
'Delete from stdetails where ID = {}'.format(idno)
Learning Objective : To interface Python with MySQL
