Interface Python with MySQL

(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.

(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.

(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.

(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.

(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.

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.

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.

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.

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

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

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

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

connect()

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

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.

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.

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.

fetchtwo()

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

fetchall()

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

fetchone()

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

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.

execute()

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

commit()

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

True

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

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.

False

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

True

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

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.

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.

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')

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.

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.

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.

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)

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)

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)