Interface Python with SQL

fetchone() methods will return only one row from the result set in the form of a tuple containing a record.

fetchall() method returns the result set in the form of tuples containing the records or rows returned by the SQL table.

A Resultset refers to a logical set of records that is fetched from the database.

A session between the application program and the database is called Connection.

A database cursor is a special control structure that facilitates the row-by-row processing of records in the result set.

A SELECT query is used to check if data has been added to the table or not.

To connect MySQL database, Python script is used.

The rowcount is a property of cursor object that returns the number of rows allowed from the cursor so far.

MySQLdb package can be imported in place of mysql-connector.

A Database is a collection of organized information that can be easily used, managed, updated and is classified according to its organizational approach.

Read operation on any table means to fetch some useful information from the table.

Answer

True

Reason — A read operation in database refers to retrieving or fetching data from a table. It's aimed at accessing and obtaining useful information stored in the table.

Use fetchall() method to retrieve only one value from a database table.

Answer

False

Reason — The fetchall() method retrieves all the rows in a result set and returns a list of tuples. On the other hand, the fetchone() method fetches only one row from the result set in the form of a tuple or a list.

rowcount is a read-only attribute.

Answer

True

Reason — The rowcount is a read-only attribute that returns the number of rows affected by an execute() method and retrieved from the cursor.

To disconnect database connection, use connect() method.

Answer

False

Reason — The correct method to disconnect from a database connection is connection.close() or cursorobject.close(). On the other hand, the connect() method is used to establish a connection to the database.

Update statement is used to insert data into the table.

Answer

False

Reason — The UPDATE statement is used to modify or update existing records in a table.

A result set is an object that is returned when a cursor object is used to query a table.

Answer

True

Reason — A result set is an object that is returned when a cursor object is used to query a table or execute an SQL statement.

After establishing connection, execute() method is used.

Answer

True

Reason — After establishing a connection to a database using a connection object, the execute() method is used to execute SQL statements or queries through a cursor object.

fetchone() fetches the next row of a query result set.

Answer

True

Reason — The fetchone() method fetches only one row from the result set in the form of a tuple or a list. This function returns one record from the result set each time it is called. The first call will return the first record, the next call will return the second record, and so on.

DB-API is the standard for Python's database interface.

Answer

True

Reason — The DB-API (Database Application Programming Interface) is the standard for Python's database interface.

connect() method does not require any parameters.

Answer

False

Reason — The connect() method accepts four required parameters: Host, Database, User, and Password.

Which connector is used for linking the database with Python code?

1. MySQL-connector
2. YesSQL: connector
3. PostSQL: connector
4. None of these

Answer

MySQL-connector

Reason — MySQL-connector is used for linking the database with Python code. It facilitates the connection and interaction between Python programs and MySQL databases, allowing for data management and query execution.

To open a connector to MySQL database, which statement is used to connect with MySQL?

1. connector
2. connect
3. password
4. username

Answer

connect

Reason — The connect() function establishes a connection to the MySQL database from Python application and returns a MySQLConnection object, which we can then use to interact with the database.

Which software is used for coding the Python language?

1. Idea
2. IDLE
3. Python Script
4. Connector

Answer

IDLE

Reason — IDLE software is used for coding the Python language.

Which command is used for counting the number of rows in a database?

1. row
2. rowcount
3. count()
4. row_count

Answer

rowcount

Reason — The rowcount is a read-only attribute that returns the number of rows affected by an execute() method and retrieved from the cursor.

Name the method which is used for displaying only one record from the result set.

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

Answer

fetchone()

Reason — The fetchone() method fetches only one record from the result set in the form of a tuple or a list.

To execute all the rows from the result set, which method is used?

1. fetchall()
2. fetchone()
3. fetchmany()
4. none of these

Answer

fetchall()

Reason — The fetchall() method retrieves all the rows in a result set and returns them as a list of tuples.

Pick the correct username used for logging in database.

1. root
2. local
3. directory
4. host

Answer

root

Reason — In MySQL database, "root" is the default username.

Name the host name used for signing in the database.

1. localhost
2. localpost
3. localcost
4. none of these

Answer

localhost

Reason — The "localhost" is the host name used for signing into a MySQL database.

The command used for modifying the records is:

1. update()
2. add()
3. updateall()
4. none of these

Answer

update()

Reason — The command used for modifying or updating the records in a table is update().

Which command is used for cleaning up the environment?

1. my.closed()
2. is.end()
3. con.quit()
4. mycon.close()

Answer

mycon.close()

Reason — The mycon.close() command is used for cleaning up the environment by closing the database connection.

Which function of connection is used to check whether connection to MySQL is successfully done or not?

import mysql.connector as mycon
con = mycon.connect               #ConnectionString
if ...............:
    print("Connected!")
else:
    print("Error! Not Connected")

1. con.connected()
2. con.isconnected()
3. con.is_connected()
4. con.is_connect()

Answer

con.is_connected()

Reason — In the provided code using mysql.connector, the correct function to check whether the connection to MySQL is successfully established or not is con.is_connected(). If the connection is active, it prints "Connected!". Conversely, if the connection is not active, it prints "Error! Not Connected".

Identify the correct statement to create cursor:

import mysql.connector as msq
con = msq.connect()              #ConnectionString
mycursor = ...............

1. con.cursor()
2. con.create_cursor()
3. con.open_cursor()
4. con.get_cursor()

Answer

con.cursor()

Reason — In MySQL connector for Python, the correct statement to create a cursor is con.cursor(). This method creates and returns a cursor object associated with the connection con, allowing to execute SQL queries.

Explain the following 'results' retrieval methods with examples.

(a) fetchone()

(b) rowcount

(c) fetchall()

Answer

(a) fetchone() — It fetches one row from the result set in the form of a tuple or a list. This function will return one record from the result set, i.e., first time it will return the first record, next time it will return the second record and so on. If no more record is left in the table, it will return None.

Example:

Let us consider the table student:

import mysql.connector
mydb = mysql.connector.connect(host = "localhost", user = "root", passwd = "tiger", database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * from student")
myrecords = mycursor.fetchone()
print(myrecords)

(10, 7, 'Sameer', 'Cricket', 'B', 'Photography', 'A')

(b) rowcount — This is read-only attribute and returns the number of rows that were affected by an execute() method and retrieved from the cursor.

Example:

import mysql.connector
mydb = mysql.connector.connect(host = "localhost", user = "root", passwd = "tiger", database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * from student")
myrecords = mycursor.fetchone()
affected_rows = mycursor.rowcount
print("Number of affected rows: ", affected_rows)

Number of affected rows:  1

(c) fetchall() — It fetches all the rows in a result set and returns a list of tuples. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set. If no more rows are available, it returns an empty list.

Example:

import mysql.connector
mydb = mysql.connector.connect(host = "localhost", user = "root", passwd = "tiger", database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * from student")
myrecords = mycursor.fetchall()
for x in myrecords:
    print(x)

(11, 8, 'Sujit', 'Tennis', 'A', 'Gardening', 'C')
(12, 7, 'Kamal', 'Swimming', 'B', 'Photography', 'B')
(13, 7, 'Veena', 'Tennis', 'C', 'Cooking', 'A')
(14, 9, 'Archana', 'Basket Ball', 'A', 'Literature', 'A')
(15, 10, 'Arpit', 'Cricket', 'A', 'Gardening', 'C')

What is the significance of connecting Python with MySQL?

Answer

Python is a flexible, portable, easy to learn and modifiable language. So, we are integrating MySQL with Python interface for executing any database applications. The various reasons to use Python for programming database applications are:

1. Programming in Python is arguably more efficient and faster as compared to other languages.
2. Python is known for its portability.
3. It is platform-independent.
4. Python supports SQL cursors that allows executing SQL queries and processing the results.
5. In many programming languages, the application developer needs to take care of the open and closed connections of the database to avoid further exceptions and errors. In Python, these connections are taken care of.
6. Python supports relational database management systems.
7. Python database APIs are compatible with various databases, so it is very easy to migrate and port database application interfaces.

Why do we use connect() function?

Answer

The connect() function is used to establish a connection to the MySQL database from a Python application. It returns a MySQLConnection object, which we can then use to perform various operations on the MySQL database.

Explain the steps for establishing MySQL connection with Python.

Answer

The steps for establishing MySQL connection with Python are as follows:

Step 1 — To connect Python to MySQL, we need to install mysql-connector using 'pip' command on the command prompt (cmd). Do ensure that MySQL-connector is installed in the same folder as Python.

Step 2 — Once the path is set, type the command as — python -m pip install mysql-connector or pip install mysql-connector-python. MySQL shall download and will be installed on our system. Now, we need to check whether it has been properly installed or not.

Step 3 — To do this, type import mysql.connector in Python shell. If no error message gets displayed, this signifies that driver has been successfully installed.

Step 4 — The next step to using MySQL in Python scripts is to make a connection to the database. All Python DB-API modules implement a function: conn = module_name.connect(host = "localhost", user = "root", passwd = "password", database = "database_name").

Step 5 — Once the connection is established, create a cursor object using the cursor() method of the connection object, mycursor = conn.cursor().

Step 6 — We can now execute SQL queries using the execute() method of the cursor object, mycursor.execute("SHOW DATABASES")

Step 7 — If our query retrieves data, we can fetch the results using methods like fetchone(), fetchall(), or fetchmany() on the cursor object.

Step 8 — If we make any changes to the database, then commit those changes using commit() on the connection object, conn.commit(). Finally, close the connection, mycursor.close().

Explain the transaction keywords used with MySQL-Python connectivity.

Answer

The transaction keywords used with MySQL-Python connectivity are as follows:

1. commit — The COMMIT keyword is used to permanently apply the changes made during a transaction to the database. The MySQLConnection.commit() method sends a COMMIT statement to the MySQL server, finalizing and committing the current transaction. It's important to commit transactions only when all operations within the transaction have been successfully completed and verified.
2. rollback — The ROLLBACK keyword is used to undo or revert changes made during a transaction. When a transaction fails to execute and we want to revert all the changes, the MySQLConnection.rollback() method can be used to revert the changes.
3. autocommit — The MySQLConnection.autocommit value can be assigned as True or False to enable or disable the auto-commit feature of MySQL. By default, its value is False. This feature controls whether each SQL statement is automatically committed as a separate transaction or if manual transaction control is required.

Give the significance of using execute() function.

Answer

The execute() function is significant for executing SQL queries and commands. It enables us to send SQL statements to the database and perform various operations such as creating, inserting, updating, and deleting data.

Differentiate between commit() and rollback() statements.

Answer

Which function is used to connect to database?

Answer

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

Which function is used to run the SQL query?

Answer

The execute() function is used for executing SQL query.

Which function is used to read one record from the database?

Answer

The fetchone() function fetches only one row from the result set in the form of a tuple or a list.

Write a program to display all records in ascending order of their salary from table employee.

Answer

Table Employee:

import mysql.connector
mydb = mysql.connector.connect(host = "localhost",
                                user = "root",
                                passwd = "tiger",
                                database = "School")
mycursor = mydb.cursor()
mycursor.execute("select * FROM EMPLOYEE ORDER BY SALARY")
myrecords = mycursor.fetchall()
for row in myrecords:
    print(row)
mycursor.close()    
mydb.close()

(4, 'ABHAY', 'FASHION STUDIES', 45000.0)
(1, 'RAJESH', 'IT', 60000.0)
(2, 'MANOJ KUMAR', 'HISTORY', 65000.0)
(3, 'ANUSHA', 'MARKETING', 70000.0)

Write a program to increase salary of the employee, whose name is "MANOJ KUMAR", by 3000.

Answer

import mysql.connector
mydb = mysql.connector.connect(host = "localhost",
                                user = "root",
                                passwd = "tiger",
                                database = "School")
mycursor = mydb.cursor()
mycursor.execute("UPDATE employee SET salary = salary + 3000 WHERE Ename = 'MANOJ KUMAR'")
mydb.commit()

Write a program to delete the employee record whose name is read from keyboard at execution time.

Answer

import mysql.connector
mydb = mysql.connector.connect(host = "localhost",
                                user = "root",
                                passwd = "tiger",
                                database = "School")
mycursor = mydb.cursor()
employee_name = input("Enter the name of the employee to delete: ")
mycursor.execute("DELETE FROM employee WHERE ENAME = %s", (employee_name,))
print(mycursor.rowcount, "Record Deleted")

Enter the name of the employee to delete: RAJESH
1 Record Deleted

Create a database TESTDB.

Answer

import mysql.connector
conn = mysql.connector.connect(host = 'localhost', 
                                user = 'root', 
                                passwd = 'tiger')
cursor = conn.cursor()
cursor.execute("CREATE DATABASE TESTDB")
conn.commit()
conn.close()

Create a table EMPLOYEE with Fields FIRST_NAME, LAST_NAME, AGE, GENDER and INCOME.

Answer

import mysql.connector
mydb = mysql.connector.connect(host = 'localhost', 
                               user = 'root', 
                               passwd = 'tiger', 
                               database = "kboat_cbse_12")
mycursor = mydb.cursor()
mycursor.execute("CREATE table EMPLOYEE (FIRST_NAME VARCHAR(45), 
                LAST_NAME VARCHAR(45), 
                AGE INTEGER, 
                GENDER VARCHAR(10), 
                INCOME FLOAT)")
mydb.commit()
mycursor.close()
mydb.close()