DEPARTMENT OF COMPUTER SCIENCE AND ENGINEEERING
(DATA SCIENCE)
COURSE CODE: DJS22DSL305 DATE: 2/12/2023
COURSE NAME: Python Laboratory CLASS: SYBTECH
Name: Minal Joshi SAP ID: 60009220180
EXPERIMENT NO.10
CO/LO:
CO4 Apply database connectivity, client-server communication using python.
AIM : Write python programs to understand database operations
OBJECTIVE:
Write python programs to understand database CURD operations
DESCRIPTION OF EXPERIMENT:
MySQLdb
MySQLdb is an interface for connecting to a MySQL database server from Python. It
implements the Python Database API v2.0 and is built on top of the MySQL C API.
Installing MySQLdb
Before proceeding, you make sure you have MySQLdb installed on your machine. Just type the
following in your Python script and execute it −
#!/usr/bin/python
To install MySQLdb module, use the following command −
For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python
Note − Make sure you have root privilege to install above module.
Database Connection
Before connecting to a MySQL database, make sure of the followings −
• You have created a database TESTDB.
• You have created a table EMPLOYEE in TESTDB.
• This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and
INCOME.
• User ID "testuser" and password "test123" are set to access TESTDB.
• Python module MySQLdb is installed properly on your machine.
• You have gone through MySQL tutorial to understand MySQL Basics.
Following is the example of connecting with MySQL database "TESTDB"
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using fetchone() method.
data = cursor.fetchone() print "Database version
: %s " % data
# disconnect from server
db.close()
If a connection is established with the datasource, then a Connection Object is returned and
saved into db for further use, otherwise db is set to None. Next, db object is used to create a
cursor object, which in turn is used to execute SQL queries. Finally, before coming out, it
ensures that database connection is closed and resources are released.
Creating Database Table
Once a database connection is established, we are ready to create tables or records into the
database tables using execute method of the created cursor.
Example
Let us create Database table EMPLOYEE −
#!/usr/bin/python
import MySQLdb
# Open database connection
pip install mysql-connector-python
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# disconnect from server
db.close()
INSERT Operation
It is required when you want to create your records into a database table.
Example
The following example, executes SQL INSERT statement to create a record into EMPLOYEE
table −
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
Above example can be written as follows to create SQL queries dynamically −
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
('Mac', 'Mohan', 20, 'M', 2000)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
Example
Following code segment is another form of execution where you can pass parameters directly −
..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % \
(user_id, password))
..................................
READ Operation
READ Operation on any database means to fetch some useful information from the database.
Once our database connection is established, you are ready to make a query into this database.
You can use either fetchone() method to fetch single record or fetchall() method to fetech
multiple values from a database table.
• fetchone() − It fetches the next row of a query result set. A result set is an object
that is returned when a cursor object is used to query a table.
• fetchall() − It fetches all the rows in a result set. If some rows have already been
extracted from the result set, then it retrieves the remaining rows from the result set.
• rowcount − This is a read-only attribute and returns the number of rows that
were affected by an execute() method.
Example
The following procedure queries all the records from EMPLOYEE table having salary more
than 1000 −
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > '%d'" % (1000)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Now print fetched result
print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
(fname, lname, age, sex, income )
except:
print "Error: unable to fecth data"
# disconnect from server
db.close()
This will produce the following result −
fname=Mac, lname=Mohan, age=20, sex=M, income=2000
Update Operation
UPDATE Operation on any database means to update one or more records, which are already
available in the database.
The following procedure updates all the records having SEX as 'M'. Here, we increase AGE of
all the males by one year.
Example
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records sql
= "UPDATE EMPLOYEE SET AGE = AGE + 1
WHERE SEX = '%c'" % ('M') try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
DELETE Operation
DELETE operation is required when you want to delete some records from your database.
Following is the procedure to delete all the records from EMPLOYEE where AGE is more than
20 −
Example
#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
QUESTIONS:
Demonstrate the CRUD operations for your Mini project.
OBSERVATIONS / DISCUSSION OF RESULT:
Create database:
Creating table:
Operations on table:
CONCLUSION:
Therefore, we have learnt how to connect mysql and python and perform basic operations on
the database using python.
REFERENCES:
Website References:
[1] https:// www.tutorialspoint.com/python/python_database_access.htm
