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():- Fetchall (remaining) rows of a query result, returning them as a sequence of sequences (e.g.
a list of tuples).
fetchmany (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] as my
mydb = [Link](host = "localhost", user = "root", password = "123456")
mycor = [Link]()
[Link]("CREATE DATABSE Pathwalla")
# SHOW DATABASE
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456")
mycor = [Link]()
[Link]("SHOW DATABSES")
for i in mycon:
print (i)
By PathWalla / Portal Express
# CREATE TABLE
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("CREATE TABLE student (Name char (25), roll int(10), class int(10))")
[Link]()
[Link]()
# SHOW TABLE
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("SHOW TABLES")
for i in mycon:
print (i)
# DESCRIBE TABLE
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("DESC student")
data = [Link]()
for i in data:
print (i)
# SELECT QUERY
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("SELECT * FROM student")
data = [Link]()
for i in data:
print (i)
# WHERE CLAUSE
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("SELECT * FROM student")
By PathWalla / Portal Express
data = [Link]()
for i in data:
print (i)
# DYNAMIC INSERTION
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
name = input("Enter Name :-")
roll = input("Enter Roll :-")
class = input("Enter Class :-")
[Link]("INSERT INTO student VALUES ('{}', '{}', '{}',)".format (name, roll, class))
[Link]()
[Link]()
# UPDATE COMMAND
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("UPDATE student SET roll = 5 WHERE Name = 'Ram'")
[Link]()
[Link]()
# DELETE COMMAND
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("DELET FROM student WHERE roll = 26")
[Link]()
[Link]()
# DROP COMMAND
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
By PathWalla / Portal Express
[Link]("DROP TABLE student")
[Link]()
[Link]()
# ALTER COMMAND
import [Link] as my
mydb = [Link](host = "localhost", user = "root", password = "123456", database = "Pathwalla")
mycor = [Link]()
[Link]("ALTER TABLE student ADD Marks int (10)")
[Link]()
[Link]()
Some Important Question
Q1. What is database?
Answer = The database is a collection of organized information that can easily be used, managed,
update, and they are classified according to their organizational approach.
Q2. Write command to install connector.
Answer = pip install mysql-connector-python
Q3. write the steps of connectivity between SQL and Python.
Answer = import, connect, cursor, execute
Q4. What is result set? Explain with example.
Answer = 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.
By PathWalla / Portal Express
Q5. Write code for database connectivity.
Answer =
# importing the module
import [Link]
# opening a database connection
conn = [Link] ("localhost", "root", "1234", "student")
# define a cursor object
mycursor = [Link]()
# drop table if exists
[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)
[Link]()
# close connection
[Link]()
Q6. Use of functions in connectivity - INSERT, UPDATE, DELETE, ROLLBACK.
Answer =
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.
Q7. Which method is used to retrieve all rows and single row?
Answer = Fetchall, fetchone()
By PathWalla / Portal Express
Q8. Write python-mysql connectivity to retrieve all the data of table student.
Answer =
import [Link]
mydb = [Link]( host="localhost", user="root", passwd="123" database="school")
mycursor = [Link]()
[Link]("select * from student")
for x in [Link]() :
print(x)
Thankyou!!!!!
For Solution of ‘Sumita Arora’ visit on Path Walla
For ‘Sumita Arora Type C’ solution (Programing Question) Visit our
YouTube Channel Portal Express
By PathWalla / Portal Express