CHAPTER 8
Interface Python with My
SQL
ESTABLISHING CONNECTION
Command to check user details of MySQL
mysql>select current_user();
Cord snippet to check if Python is connected to MySQL.
New connection object (mydb) created using
OUTPUT
connect()
CREATING CURSOR OBJECT
cursor() is used to create cursor object.
Cursor objects are created to send SQL queries and bring back the
result.
Here, ‘mycursor’ is the name of cursor object, created with the help of
connection object “mydb”.
object function
connection object -mydb connect()
cursor object - mycursor cursor()
THINK - Name the mandatory parameters to connect Python to MySQL ?
Ans: hostname, user and password.
EXECUTING MySQL query
Note - Database name is optional.
execute() - is used to execute MySQL queries. SQL queries are enclosed in
double quotes.
To check if query is executed, we need to display using print()
OUTPUT
OUTPUT
Q1. WAP to create a database “SCHOOL” in MySQL using Python.
1.1 WAP to check if the database “SCHOOL” is created.
1.2 Write a Python - SQL connectivity program to create a table
“STUDENT” in database SCHOOL with following details.
SID - int
Sname - varchar(20)
DON’T FORGET
Age - int TO EXECUTE
“USE DATABASE”
City - varchar (10)
1.3 Also check if the table is created .
1.4 Write execute() to set SID as primary key.
ALTER TABLE STUDENT ADD PRIMARY KEY (SID);
1.5 Add a column marks - int.
ALTER TABLE STUDENT ADD marks(int);
1.6 Check if the change is updated in table.
DESC STUDENT;
20-Sep-2024, Friday
Program to check if connection is present, is_connected() is used.
This function returns True if connection exists.
Methods to Manage MySQL Database Transactions in Python
● commit() - to commit the current changes in database permanently.
([Link]())
● rollback() - reverts (undo) the changes made by the current transaction
● Autocommit - a value that can be assigned as True or False. By default autocommit is
False
Property of Cursor Object
1. rowcount - is the property of cursor object that returns the
number of records inserted/updated. →records that are
affected by execute() .
Q 1- DIY
Wap to insert records and print number of records inserted
using rowcount.
Q 2 - DIY
Update marks of SID = 1 as 499 and SID = 2 as 410 and display the
number of records updated using rowcount.
OUTPUT
TRY:
To display first two records of table student
SELECT * FROM STUDENT WHERE ROLLNO IN(1,2);
READ OPERATION
SQL
● Read operation means to fetch useful information from result set .
● A resultset is an object that is returned when a cursor object is used to query
a table.
● fetchone() - fetches one row from the result set in the form of tuple or list.
→ first time fetchone() returns first record, second time it returns second
record and so on.
→ if no more record is left in the table, it will return None .
Program showing working of fetchone()
● fecthall() - fetches all rows from a resultset and returns a list of tuples.
→If some rows were already extracted from result set, then it retrieves remaining
from result set.
→ If no more rows are available, it returns an empty list.
● fetchmany(size) - It fetches specified number of rows as a list of tuples.
→ By default, size = 1.
→ If there are no rows in result set, it returns empty list.
Program showing working of fetchall()
THINK : How can you present it as a table?
Question :
Write a Python Program to display first 3 records from table
student.
DIY
Selection Using WHERE CLAUSE
Question
Write a Python program to select details of students whose
marks is more than 50.
DIY
CLOSING CURSOR AND CONNECTION
The databases can keep open, only a limited number of connections.
Hence, we must close any connection when not in use, using cursor
object,after which we cannot execute any query.
>>>[Link]()
Database also has to be disconnected. This is done by-
>>> [Link]()
HOMEWORK
1. Delete student whose roll no is 1.
2.
PARAMETERIZED QUERIES
To incorporate user input values in query of execute()
,parameterised queries are used.
This can be done in 3 ways.
1. Concatenating dynamic variable to query in which
the values are entered.
2. String template with %s formatting.
3. String template with {} and format()