Interface Python with SQL

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.

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.

IDLE

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

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.

fetchone()

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

fetchall()

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

root

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

localhost

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

update()

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

mycon.close()

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

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

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.

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

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.

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.

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

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.

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.

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

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

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

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)

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

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.

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.

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.

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.

False

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

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.

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.

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.

True

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

False

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