Python Connectivity with MySQL
In real-life applications, the data inputted by the
user and processed by the application needs to be saved permanently, so that it
can be used for future manipulation.
Usually, input and output data is displayed during
execution but not stored, as it resides in main memory, which is temporary
— i.e., it gets erased once the application is closed.
This limitation can be overcome by sending the data to
be stored in a database, which is made accessible to the user through a Front-End
interface.
Key Concepts
Database
A database is an organized collection of data
that is stored and accessed electronically from a computer system.
DBMS (Database Management System)
A DBMS is software that interacts with end-users,
applications, and the database to capture and analyze data.
Front-End
The Front-End is the user interface of the
application, responsible for input/output interaction with the user.
Back-End
The Back-End refers to the database that stores
data permanently. It processes requests from the front-end and sends results
back.
Python as Front-End
Python is a flexible, portable, and easy-to-learn
programming language that can be used as a Front-End for database
applications.
Why Python as Front-End?
- Easy
and efficient programming
- Platform
independent (portable)
- Supports
relational databases
- Handles
connection management automatically
- Supports
a wide range of APIs for database access
MySQL as Back-End
MySQL is a relational database management system
(RDBMS) based on SQL.
Features of MySQL:
- Free
and cross-platform
- Follows
client/server architecture
- Fast
and efficient
- Flexible,
supports embedded applications
Python Database Connectivity
The process of transferring data between a Python
program and a MySQL database is known as Python Database
Connectivity.
Requirements:
- Python
DB-API
- MySQL
Connector module (mysql.connector)
- MySQL
server
MySQL Query Operation
Query Operation on any
database means to fetch records from the tables stored in a database. Queries
enable users to search and retrieve records
that meet certain
criteria and to manipulate it
Once the database connection is established, a SQL statement can be given
to make a query using a cursor object and which
in turn can return a result set object containing the records. The query may
fetch one record or multiple records from the table; hence Result set can have
one or many records.
Methods
and attributes to manipulate from Resultset
The fetchone(), fetchall() and fetchmany() methods are used to fetch recods from the
resultset.
-
fetchone()
The fetchone() method is used to fetch a single record from the Result set as a tuple. When given, First time it will
return first record, next time it will return second record and so on. If no more record is left to be fetched in the resultset it will return None.
Syntax : object = Cursor_Object.fetchone()
-
fetchall()
The fetchall() method fetches all the records
present in a result
set in the form of List where each record is represented
as a tuple object. If some records,
have already been extracted from the result set, then it retrieves the
remaining rows from the result set.
Syntax : object = Cursor_Object.fetchall()
-
fetchmany(<n>)
The fetchmany() method accepts
number of records(n) to fetch as argument and returns a list where each
record is represented as a tuple object. When called repeatedly this method
fetches the next set of records present in the resultset. If no more records
are available in the resultset
it returns an empty
list.
Syntax : object = Cursor_Object.fetchmany(n)
-
rowcount
rowcount is a read-only
attribute of a cursor
Object, which returns
the number of rows
that were affected
by an execute() method and stored in the resultset. If no
records are existing in the resultset , then it will have return the value 0.
Syntax : <int object>
= Cursor_Object.rowcount
Closing Connection Object
Although, Connections are automatically closed
when the object go out of scope, it is a
good practice to close the connection object and cursor object. Once the database operation in Python
interface is done using the close() method
with the connection object, it can be closed.
Syntax : <Connection_object>.close()
Example : mydb.close()
Closing
Cursor Object
The close() method used with cursor
object closes the cursor, resets all results, and ensures that the cursor object has no reference to the original connection object.
Syntax : <Cursor _object>.close()
Example : mycur.close()
Importing My SQL connector
To import MySQL connector package, the import statement is required to be included in the program is - import mysql.connector
The above import statement
can also be given as : import mysql.connector as ms
Here “ms” is an alias and “ms” can be used in place of “mysql.connector”
Connecting Python To MySQL using
connect() Method
To create a connection between the MySQL database and the python
application, the connect() method
of mysql.connector module is used which creates a connection and returns a
MySQL connection object.
Syntax to use the connect() :
Connection-Object
= mysql.connector.connect(host = <hostname> , user = <username> , passwd = <pas
sword> )
-
Host Name – is the server
name or Ip address on which MySQL is running. When the database is in local
computer, host = “localhost” is used,
-
Username – The username refers to the user name in
MySQL Server. The default username for the MySQL database is root
-
Password – Password is
given by the user at the time of installing the MySQL database. When Password
is not there two blank as password (consecutive "") are given. The
user has to ensure that the user and password specified should match to the
user and password given for local MySQL installation.
-
The connection object is an identifier which can
be used for executing queries and other tasks on connected database
Note :
database = <database name> can also be given after passwd if the application is manipulating an existing database .
Example : import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="root", passwd
= "student")
PRACTICE
QUESTIONS
QUE 1 : WRITE CODE TO INSERT DATA
import mysql.connector
# Establishing connection
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
# Replace with actual DB name
)
mycur = mydb.cursor()
# Start loop
while True:
eno =
input("Enter Employee No - ")
name =
input("Enter Employee Name - ")
salary =
input("Enter Salary - ")
dept =
input("Enter Department - ")
dob =
input("Enter Date of Birth (YYYY-MM-DD) - ")
#
Parameterized insert query
st =
"INSERT INTO EMP (eno, name, salary, dept, dob) VALUES (%s, %s, %s, %s,
%s)"
val = (eno,
name, salary, dept, dob)
# Executing
and committing the query
mycur.execute(st, val)
mydb.commit()
print("Record inserted successfully.")
# Ask user if they want to continue
cont =
input("Do you want to insert another record? (y/n): ")
if
cont.lower() != 'y':
break
print("All records inserted. Exiting
program.")
QUE 2:Write program to display all records from
the EMP table along with the total number of records:
import mysql.connector
# Establishing connection
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
# Replace with actual DB name
)
mycur = mydb.cursor()
# Execute query to fetch all records
mycur.execute("SELECT * FROM EMP")
recs = mycur.fetchall()
# Count total number of records
ctr = mycur.rowcount
# Display records
print("Employee Records:\n")
for r in recs:
print(r[0], r[1], r[2], r[3], r[4],
sep="\t")
print("\nTotal records in the table -", ctr)
QUE 3:Code to displays the details of an employee
matching a user-given employee number, using parameterized query .
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
)
mycur = mydb.cursor()
# User input for employee number
seno = input("Enter employee number to search:
")
st = "SELECT * FROM EMP WHERE Empno = %s"
mycur.execute(st, (seno,))
# Fetch the result
rec = mycur.fetchone()
ctr = mycur.rowcount
# Check if record exists
if ctr == 0 or rec is None:
print("No such record found.")
else:
print("\nRecord Found...Details -")
print("Employee No
-", rec[0])
print("Name
-", rec[1])
print("Salary -",
rec[2])
print("Department
-", rec[3])
print("Date Of Birth
-", rec[4])
QUE 4:program to display employee details
matching a user-given employee name
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
)
mycur = mydb.cursor()
# Get user input
senm = input("Enter employee name to search:
")
st = "SELECT * FROM EMP WHERE name = %s"
mycur.execute(st, (senm,))
# Fetch all matching records
recs = mycur.fetchall()
ctr = mycur.rowcount
# Check and display results
if ctr == 0:
print("No such record found.")
else:
print("\n",
ctr, "record(s) found:")
for r in recs:
print("\nEmployee
No -", r[0])
print("Name
-", r[1])
print("Salary
-", r[2])
print("Department
-", r[3])
print("Date Of Birth
-", r[4])
QUE 5: program that displays the details of employees
whose date of birth is later than a user-given date.
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
)
mycur = mydb.cursor()
# Get date input from user
sdob = input("Enter date to search (YYYY-MM-DD):
")
st = "SELECT * FROM EMP WHERE dob > %s"
mycur.execute(st, (sdob,))
# Fetch results
recs = mycur.fetchall()
ctr = mycur.rowcount
# Display results
if ctr == 0:
print("No
such record found.")
else:
print("\n", ctr, "record(s) found:")
for r in
recs:
print("\nEmployee No
-", r[0])
print("Name
-", r[1])
print("Salary
-", r[2])
print("Department
-", r[3])
print("Date Of Birth
-", r[4])
QUE 6: Python program that counts and displays
employee details for a user-given department using a safe
parameterized query:
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
)
mycur = mydb.cursor()
# User input for department
sdpt = input("Enter Department name to search and
count: ")
st = "SELECT * FROM EMP WHERE Dept = %s"
mycur.execute(st, (sdpt,))
# Fetch all matching records
recs = mycur.fetchall()
ctr = mycur.rowcount
# Display results
if ctr == 0:
print("No records found.")
else:
print("\nEmployees
in Department '" + sdpt + "':\n")
for r in
recs:
print(r[0], r[1], r[2], r[3], r[4], sep="\t")
print("\nNumber of employees in Department", sdpt,
"is:", ctr)
QUE 7: Program that counts the number of employees
earning greater than or equal to a user-given salary
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
)
mycur = mydb.cursor()
# User input for salary
ssal = input("Enter salary to count employees
earning more than or equal to: ")
st = "SELECT * FROM EMP WHERE salary >=
%s"
mycur.execute(st, (ssal,))
# Fetch all matching records
rec = mycur.fetchall()
ctr = mycur.rowcount
# Display count
if ctr == 0:
print("No records found.")
else:
print("Number of employees earning >=", ssal,
"is:", ctr)
QUE 8:Program that displays all employee records
sorted in ascending order by name, along with the total number of
records:
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
)
mycur = mydb.cursor()
# Execute query to get records sorted by
name
mycur.execute("SELECT * FROM EMP ORDER BY name
ASC")
recs = mycur.fetchall()
ctr = mycur.rowcount
# Display records
print("Employee Records in Ascending Order of
Name:\n")
for r in recs:
print(r[0],
r[1], r[2], r[3], r[4], sep="\t")
# Display total count
print("\nTotal records -", ctr)
QUE 9 :Python program to delete employee records
based on the name entered by the user, with confirmation:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name"
)
mycur = mydb.cursor()
# Get employee name from user
senm = input("Enter employee name to delete:
")
# Delete query
delete_query = "DELETE FROM EMP WHERE name =
%s"
mycur.execute(delete_query, (senm,))
mydb.commit()
# Report how many records were deleted
print(mycur.rowcount, "record(s) deleted.")
QUE 10: Python program to edit the salary of an
employee by accepting the Emp No from the user, using parameterized
queries
import mysql.connector
# Connect to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="student",
database="your_database_name”
)
mycur = mydb.cursor()
# Get employee number from user
seno = input("Enter employee number to edit:
")
# Fetch the employee record
select_query = "SELECT * FROM EMP WHERE Empno =
%s"
mycur.execute(select_query, (seno,))
rec = mycur.fetchone()
# Check if record exists
if not rec:
print("No such record found.")
else:
print("\nRecord To Be Edited Found...Details -")
print("Employee No
-", rec[0])
print("Name
-", rec[1])
print("Salary
-", rec[2])
print("Department
-", rec[3])
print("Date Of Birth
-", rec[4])
confirm =
input("\nDo you want to edit the salary? (y/n): ")
if
confirm.lower() == 'y':
newsal =
input("Enter new salary: ")
update_query
= "UPDATE EMP SET salary = %s WHERE Empno = %s"
mycur.execute(update_query, (newsal, seno))
mydb.commit()
print("Salary updated successfully.")
else:
print("Update cancelled.")
Comments
Post a Comment