Chapter 12: Interface Python with an SQL database
Database connectivity-Database connectivity refers to connection and communication between an application
and a database system.
[Link]-Library or package to connect from python to MySQL.
Command to install connectivity package:- pip install mysql-connector-python
Command to import connector:- import [Link]
Steps for python MySQL connectivity
1 . Install Python
2. Install MySQL
3. Open Command prompt
4. Switch on internet connection
5. Type pip install mysql-connector-python and execute
6. Open python IDLE
7. import [Link]
Multiple ways to retrieve data:
fetchall()-Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of
tuples)
fetch many (size)-Fetch the next set of rows of a query result, returning a sequence of sequences. It will return
number of
rows that matches to the size argument.
fetchone()-Fetch the next row of a query result set, returning a single sequence or None when no more data is
available
Functions to execute SQL queries
#CREATE DATABASE
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345")
mycursor=[Link]()
[Link]("CREATE DATABASE SCHOOL")
# SHOW DATABASE
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345")
mycursor=[Link]()
[Link]("SHOW DATABASE")
for x in mycursor:
print (x)
# CREATE TABLE
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
[Link]("CREATE TABLE FEES (ROLLNO INTEGER(3),NAME VARCHAR(20),AMOUNT
INTEGER(10));")
# SHOW TABLES
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
[Link]("SHOW TABLES")
for x in mycursor:
print(x)
#DESCRIBE TABLE
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
[Link]("DESC STUDENT")
for x in mycursor:
print(x)
# SELECT QUERY
import [Link]
conn=[Link](host="localhost",user="root",passwd="12345",database="student")
c=[Link]()
[Link]("select * from student")
r=[Link]()
while r is not None:
print(r)
r=[Link]()
#WHERE CLAUSE
import [Link]
conn=[Link](host="localhost",user="root",passwd="12345",database="student")
if conn.is_connected==False:
print("Error connecting to MYSQL DATABASE")
c=[Link]()
[Link]("select * from student where marks>90")
r=[Link]()
count=[Link]
print("total no of rows:",count)
for row in r:
print(row)
# DYNAMIC INSERTION
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
r=int(input("enter the rollno"))
n=input("enter name")
m=int(input("enter marks"))
[Link]("INSERT INTO student(rollno,name,marks) VALUES({},'{}',{})".format(r,n,m))
[Link]()
print([Link],"RECORD INSERTED")
# UPDATE COMMAND
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
[Link]("UPDATE STUDENT SET MARKS=100 WHERE MARKS=40")
[Link]()
print([Link],"RECORD UPDATED")
# DELETE COMMAND
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
[Link]("DELETE FROM STUDENT WHERE MARKS<50")
[Link]()
print([Link],"RECORD DELETED")
# DROP COMMAND
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
[Link]("DROP TABLE STUDENT")
# ALTER COMMAND
import [Link]
mydb=[Link](host="localhost",user="root",passwd="12345",database="student")
mycursor=[Link]()
[Link]("ALTER TABLE STUDENT ADD GRADE CHAR(3)")
Chapter -12 Data Base Connectivity (Questions and answers)
(1 mark question)
Q.1.1 What is database.
Ans. The database is a collection of organized information that can easily be used, managed, update, and they are
classified according to their organizational approach.
Q.1.2 Write command to install connector.
Ans. pip install mysql-connector-python
Q.1.3 Write command to import connector.
Ans. import [Link]
(2 mark question)
Q.2 write the steps of connectivity between SQL and Python
Ans. import,connect,cursor,execute
Q.3 What is result set? Explain with example.
Ans. Fetching rows or columns from result sets in Python. The fetch functions in the ibm_db API can iterate through
the result set. If your result set includes columns that contain large data (such as BLOB or CLOB data), you can
retrieve the data on a column-by-column basis to avoid large memory usage.
Q.4 Use of functions in connectivity - INSERT, UPDATE, DELETE, ROLLBACK
Ans.
Q.5 Write code for database connectivity
Ans. # importing the module
import [Link]
# opening a database connection
conn = [Link] ("localhost","testprog","stud","PYDB")
# define a cursor object
mycursor = [Link]
# drop table if exists
Environment
Variables
Description
INSERT It is an SQL statement used to create a record into a table.
UPDATE It is used update those available or already existing record(s).
DELETE It is used to delete records from the database.
ROLLBACK It works like "undo", which reverts all the changes that you have made.
[Link]("DROP TABLE IF EXISTS STUDENT”)
# query
sql = "CREATE TABLE STUDENT (NAME CHAR(30) NOT NULL, CLASS CHAR(5), AGE INT,
GENDER CHAR(8), MARKS INT)"
# execute query
[Link](sql)
# close object
[Link]()
# close connection
[Link]()
Q.6 Which method is used to retrieve all rows and single row?
Ans:-Fetchall(),fetchone()
Q.7 Write python-mysql connectivity to retrieve all the data of table student.
Ans:-import [Link]
mydb=[Link](user="root",host="localhost",passwd="123",database="inservice")
mycursor=[Link]()
[Link]("select * from student")
for x in mycursor:
print(x)
******