Interface Python with MySQL

How is database connectivity useful ?

Answer

When designing real-life applications, it's common to encounter scenarios where data stored in a database needs to be manipulated, retrieved, or updated through the application's interface. Database connectivity allows the application to establish a connection with the database, enabling seamless communication and interaction between the two.

What is a connection ?

Answer

A connection (database connection object) controls the connection to the database. It represents a unique session with a database connected from within a script/program.

What is a result set ?

Answer

The result set refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

What is the package used for creating a Python database connectivity application.

Answer

mysql.connector is the package used for creating a Python database connectivity application.

Which function/method do you use for establishing connection to database ?

Answer

The connect() function of mysql.connector is used for establishing connection to a MYSQL database.

Which function/method do you use for executing an SQL query ?

Answer

The execute() function with cursor object is used for executing an SQL query.

Which method do you use to fetch records from the result set ?

Answer

The fetchall() method, fetchmany() method, or fetchone() method can be used to fetch records from the result set.

In order to open a connection with MySQL database from within Python using mysql.connector package, ............... function is used.

1. open()
2. database()
3. connect()
4. connectdb()

Answer

connect()

Reason — The connect() function of mysql.connector is used for establishing connection to a MYSQL database.

A database ............... controls the connection to an actual database, established from within a Python program.

1. database object
2. connection object
3. fetch object
4. query object

Answer

connection object

Reason — A database connection object controls the connection to the database. It represents a unique session with a database connected from within a script/program.

The set of records retrieved after executing an SQL query over an established database connection is called ............... .

1. table
2. sqlresult
3. result
4. resultset

Answer

resultset

Reason — The result set refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

A database ............... is a special control structure that facilitates the row by row processing of records in the resultset.

1. fetch
2. table
3. cursor
4. query

Answer

cursor

Reason — A database cursor is a special control structure that facilitates the row by row processing of records in the resultset, i.e., the set of records retrieved as per query.

Which of the following is not a legal method for fetching records from database from within Python?

1. fetchone()
2. fetchtwo()
3. fetchall()
4. fetchmany()

Answer

fetchtwo()

Reason — The fetchall() method, fetchmany() method, or fetchone() method are the legal methods used for fetching records from the result set.

To obtain all the records retrieved, you may use <cursor>. ............... method.

1. fetch()
2. fetchmany()
3. fetchall()
4. fetchmultiple()

Answer

fetchall()

Reason — The <cursor>.fetchall() method will return all the rows from the resultset in the form of a tuple containing the records.

To fetch one record from the resultset, you may use <cursor>. ............... method.

1. fetch()
2. fetchone()
3. fetchtuple()
4. none of these

Answer

fetchone()

Reason — The <cursor>.fetchone() method will return only one row from the resultset in the form of a tuple containing a record.

To fetch multiple records from the resultset, you may use <cursor>. ............... method.

1. fetch()
2. fetchmany()
3. fetchmultiple()
4. fetchmore()

Answer

fetchmany()

Reason — The <cursor>.fetchmany(<n>) method will return only the <n> number of rows from the resultset in the form of a tuple containing the records.

To run an SQL query from within Python, you may use <cursor>. ............... method().

1. query()
2. execute()
3. run()
4. all of these

Answer

execute()

Reason — The <cursor>.execute() method is used to run an SQL query from within Python.

To reflect the changes made in the database permanently, you need to run <connection>. ............... method.

1. done()
2. reflect()
3. commit()
4. final()

Answer

commit()

Reason — The <connection>.commit() method is used to permanently reflect the changes made in the database when working with database connections in Python.

A database connection object controls the connection to the database. It represents a unique session with a database connected from within a script/program.

A database cursor is a special control structure that facilitates the row by row processing of records in the resultset, i.e., the set of records retrieved as per query.

The resultset refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

After importing mysql.connector, first of all database connection is established using connect().

After establishing database connection, database cursor is created so that the sql query may be executed through it to obtain resultset.

The cursor.rowcount returns how many rows have been fetched so far using various fetch methods.

The running of sql query through database cursor results into all the records returned in the form of resultset.

A connectivity package such as mysql.connector must be imported before writing database connectivity Python code.

connect() method establishes a database connection from within Python.

cursor() method creates a cursor from within Python.

execute() method executes a database query from within Python.

With creation of a database connection object from within a Python program, a unique session with database starts.

Answer

True

Reason — A database connection object controls the connection to the database, representing a unique session initiated from within a script or program.

The sql query upon execution via established database connection returns the result in multiple chunks.

Answer

False

Reason — When an SQL query is executed via an established database connection, the result is returned as a single result set. The result set may contain multiple rows of data, but it is presented as a single unit rather than in multiple chunks.

The cursor.rowcount gives the count of records in the resultset.

Answer

False

Reason — The cursor.rowcount returns how many rows have been so far retrieved through fetch...() methods from the cursor.

The cursor.rowcount returns how many rows have been so far retrieved through fetch..() methods from the cursor.

Answer

True

Reason — The cursor.rowcount returns how many rows have been so far retrieved through fetch...() methods from the cursor.

A DELETE or UPDATE or INSERT query requires commit() to reflect the changes in the database.

Answer

True

Reason — We need to run commit() with the connection object for DELETE, UPDATE, or INSERT queries that change the data of the database table, so that the changes are reflected in the database.

Assertion. A database connection object controls the connection to a database.

Reason. A connection object represents a unique session with a database, connected from within a script/program.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
A database connection object controls the connection to the database, ensuring that the script or program can communicate effectively with the database. This connection object represents a unique session with a database connected from within a script/program.

Assertion. A database cursor receives all the records retrieved as per the query.

Reason. A resultset refers to the records in the database cursor and allows processing of individual records in it.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
A database cursor is a special control structure that facilitates the row-by-row processing of records in the result set, which is the set of records retrieved as per the query. On the other hand, the result set refers to a logical set of records fetched from the database by executing an SQL query. The database cursor facilitates the processing of these records by allowing access to them individually.

Assertion. The database cursor and resultset have the same data yet they are different.

Reason. The database cursor is a control structure and the resultset is a logical set of records.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The database cursor and result set both have data from the database but serve different purposes and are distinct entities. A database cursor is a special control structure that facilitates the row-by-row processing of records in the result set, i.e., the set of records retrieved as per the query. On the other hand, the result set refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

Assertion. One by one the records can be fetched from the database directly through the database connection.

Reason. The database query results into a set of records known as the resultset.

Answer

(d)

Assertion is false but Reason is true.

Explanation
Records can be fetched from the database using a database connection. To fetch multiple records from the result set, we use the .fetchmany() method. To fetch one record from the result set, we use the .fetchone() method. To fetch all the records, we use the .fetchall() method. The result set refers to a logical set of records fetched from the database by executing an SQL query and made available to the application program.

Assertion. The cursor rowcount returns how many rows have been retrieved so far through fetch...() methods.

Reason. The number of rows in a resultset and the rowcount are always equal.

Answer

(c)

Assertion is true but Reason is false.

Explanation
The cursor.rowcount returns how many rows have been so far retrieved through fetch...() methods from the cursor. However, the number of rows in a result set and the rowcount may not always be equal. This is because the rowcount attribute of the cursor only reflects the number of rows fetched by the fetch...() methods, not necessarily the total number of rows in the entire result set.

What are the steps to connect to a database from within a Python application ?

Answer

The steps to connect to a database from within a Python application are as follows :

Step 1 : Start Python.

Step 2 : Import the packages required for database programming.

Step 3 : Open a connection.

Step 4 : Create a cursor instance.

Step 5 : Execute a query.

Step 6 : Extract data from result set.

Step 7 : Clean up the environment.

Write code to connect to a MySQL database namely School and then fetch all those records from table Student where grade is ' A' .

Answer

Table Student of MySQL database School

import mysql.connector as mysql

db_con = mysql.connect(
    host = "localhost",
    user = "root",
    password = "tiger",
    database = "School"
)

cursor = db_con.cursor()

cursor.execute("SELECT * FROM Student WHERE grade = 'A'")
student_records = cursor.fetchall()

for student in student_records:
    print(student)

db_con.close()

(101, 'RUHANII', 76.8, 'A', 'A', 'PENDING')
(103, 'SIMRAN', 81.2, 'A', 'B', 'EVALUATED')

Predict the output of the following code :

import mysql.connector
db = mysql.connector.connect(....)
cursor = db.cursor()
sql1 = "update category set name = '%s' WHERE ID = %s" % ('CSS',2)
cursor.execute(sql1)
db.commit()
print("Rows affected:", cursor.rowcount)
db.close()

Answer

Table category

Rows affected: 1

SELECT * FROM category ;

+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | CSS  |
|  3 | xyz  |
+----+------+

This Python script uses the mysql.connector module to connect to MySQL database. It updates the 'name' field in the 'category' table where ID is 2 to 'CSS'. The cursor.execute() method executes the SQL query, db.commit() commits the changes, and cursor.rowcount gives the number of affected rows. Finally, db.close() closes the database connection, ending the Python interface with the MySQL database.

Explain what the following query will do ?

import mysql.connector
db = mysql.connector.connect(....)
cursor = db.cursor()
person_id = input("Enter required person id")
lastname = input("Enter required lastname")
db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}')".format(person_id, lastname))
db.commit()
db.close()

Answer

This Python script uses the mysql.connector package to connect to MySQL database. Then it prompts users for person ID and last name, inserts these values into the 'staff' table, using the INSERT INTO SQL statement. After that, it executes the SQL query using the db.execute method. The changes made by the query are then committed to the database using db.commit(), ensuring that the changes are saved permanently. Finally, db.close() closes the database connection, ending the Python interface with the MySQL database.

Explain what the following query will do ?

import mysql.connector
db = mysql.connector.connect(....)
cursor = db.cursor()
db.execute("SELECT * FROM staff WHERE person_id in {}".format((1, 3, 4))) 
db.commit()
db.close()

Answer

This Python script uses the mysql.connector package to connect to MySQL database. It executes an SQL SELECT query on the 'staff' table, retrieving all rows where the 'person_id' is 1, 3, 4 (using the IN clause). The db.commit() is unnecessary for a SELECT query since it doesn't modify the database, and db.close() closes the database connection, ending the Python interface with the MySQL database.

Design a Python application that fetches all the records from Pet table of menagerie database.

Answer

import mysql.connector

db_con = mysql.connector.connect(host = "localhost", 
                                user = "root",
                                passwd = "lion",
                                database = "menagerie")
cursor = db_con.cursor()

cursor.execute("SELECT * FROM Pet")

records = cursor.fetchall()
for record in records:
    print(record)

db_con.close()

('Fluffy', 'Harold', 'cat', 'f', datetime.date(1993, 2, 4), None)
('Claws', 'Gwen', 'cat', 'm', datetime.date(1994, 3, 17), None)
('Buffy', 'Harold', 'dog', 'f', datetime.date(1989, 5, 13), None)
('Fang', 'Benny', 'dog', 'm', datetime.date(1990, 8, 27), None)
('Bowser', 'Diane', 'dog', 'm', datetime.date(1979, 8, 31), datetime.date(1995, 7, 29))
('Chirpy', 'Gwen', 'bird', 'f', datetime.date(1998, 9, 11), None)
('Whistler', 'Gwen', 'bird', None, datetime.date(1997, 12, 9), None)
('Slim', 'Benny', 'snake', 'm', datetime.date(1996, 4, 29), None)

Design a Python application that fetches only those records from Event table of menagerie database where type is Kennel.

Answer

import mysql.connector

db_con = mysql.connector.connect(host = "localhost",
                                 user = "root",
                                 passwd = "lion",
                                 database = "menagerie")
cursor = db_con.cursor()

cursor.execute("SELECT * FROM event WHERE type = 'kennel'")

records = cursor.fetchall()
for record in records:
    print(record)

db_con.close()

('Bowser', datetime.date(1991, 10, 12), 'kennel', None)
('Fang', datetime.date(1991, 10, 12), 'kennel', None)

Schema of table EMPL is shown below :

EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

Design a Python application to obtain a search criteria from user and then fetch records based on that from empl table. (given in chapter 13, Table 13.5)

Answer

Table Empl

import mysql.connector

db_con = mysql.connector.connect(host = "localhost",
                                 user = "root",
                                 passwd = "fast",
                                 database = "employeedb")
cursor = db_con.cursor()

search_criteria = input("Enter search criteria : ")
sql1 = "SELECT * FROM EMPL WHERE {}".format(search_criteria)
cursor.execute(sql1)

records = cursor.fetchall()
print("Fetched records:")
for record in records:
    print(record)

db_con.close()

Enter search criteria : job = 'clerk'

Fetched records:

(8369, 'SMITH', 'CLERK', 8902, datetime.date(1990, 12, 18), 800.0, None, 20)
(8886, 'ANOOP', 'CLERK', 8888, datetime.date(1993, 1, 12), 1100.0, None, 20)
(8900, 'JATIN', 'CLERK', 8698, datetime.date(1991, 12, 3), 950.0, None, 30)
(8934, 'MITA', 'CLERK', 8882, datetime.date(1992, 1, 23), 1300.0, None, 10)