INTERFACE OF PYTHON WITH AN SQL DATABASE
1. Connection Object
A connection object is used to establish a connection between Python and the MySQL database. It helps in
sending SQL commands and receiving results.
2. connect()
connect() is a function used to connect Python with a MySQL database server.
Example:
import [Link]
con = [Link](
host="localhost",
user="root",
password="root"
)
3. cursor()
cursor() creates a cursor object which is used to execute SQL queries.
Example
import [Link]
con = [Link](
host="localhost",
user="root",
password="root",
database="SSA"
)
cur = [Link]() # cursor object
4. A Cursor Object is an object used to execute SQL queries and fetch data from the MySQL database in Python.
It acts as a bridge between the Python program and the database.
cur = [Link]() # cursor object
Here, cur is the cursor object used to run SQL commands like SELECT, INSERT, UPDATE, and DELETE.
5. execute()
execute() is used to run SQL commands like SELECT, INSERT, UPDATE, DELETE, etc.
Example:
[Link]("SELECT * FROM STUDENT")
6. commit()
commit() saves the changes made in the database permanently.
Example:
[Link]()
7. fetchone()
fetchone() retrieves only one row from the query result.
Example:
data = [Link]()
print(data)
8. fetchall()
fetchall() retrieves all rows from the query result.
Example:
data = [Link]()
print(data)
9. fetchmany()
fetchmany() is used to retrieve a specified number of rows from the query result at a time.
[Link](size)
• size → Number of rows to fetch.
Example
data = [Link](3)
10. rowcount
rowcount returns the number of rows affected by a query.
Example:
print([Link])
Creating Database Connectivity Applications
Database connectivity applications are Python programs that connect Python with MySQL to perform database
operations such as storing, retrieving, updating, and deleting data.
Steps:
1. Import MySQL connector
2. Establish connection
3. Create cursor
4. Execute query
5. Commit changes if needed
6. Fetch data
7. Close connection
Use of %s Format Specifier in Queries
%s is used to pass values safely into SQL queries.
Example:
sql = "INSERT INTO STUDENT VALUES (%s, %s)"
val = (1, "Aman")
[Link](sql, val)
[Link]()
Use of format() in Queries
format() is used to insert values into SQL queries dynamically.
Example:
name = "Aman"
query = "SELECT * FROM STUDENT WHERE Name='{}'".format(name)
[Link](query)
NOTE: If there are NULL entries in the Database Tables, then the NULL entries appear as None when displayed in
Python Connectivity.