Python MySQL connectivity class 12 in 4 easy steps

The comprehensive notes Python MySQL connectivity class 12 help to the students of class XII CBSE who opted the subject Computer Science and Informatics Practices. So here we begin!

Python MySQL connectivity class 12

As you know to store data we have either database or flat file system. We have already seen the file handling with text files, binary files and CSV file. Now in this article Comprehensive notes Python MySQL connectivity class 12, we will see the second method.

Basically the process of transfer data between python programs and MySQL database is known as Python Database Connectivity. There few steps you have to follow to perform Python Database Connectivity. These steps are as follow:

1. Import the required packages
2. Establish a connection
3. Execute SQL command
4. Process as per the requirements

Import the required packages

To perform the Python MySQL Database Connectivity you need to install mysql-connector-python or pymysql package using pip command. First of all check it is installed or not using pip list command. Observe the following screen shot:

In the above screenshot, I have mysql-python-connector already installed. If it is not there, use pip install mysql-connector-python statement to install it. This command can be used in CMD window only. Observe this screenshot:

After installation just write the import statement to import the package in python code.

import mysql.connector as msql

Here I have instantiated msql to mysql.connector which can be work as an alias name for the connector.

Now in the next section of Python MySQL connectivity class 12 you will learn how to establish a connection between Python and MySQL.

Establish a connection

To establish a connection you need to create a connection object in Python. Take a variable as a connection object and use connect() function with MySQL database specification like host name, username, passoword or passwd and database itself. For example cn. Observe the code:

import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='root',database='Tutorialaicsip')

Please ensure that you have provided appropriate username, password and database name available in your MySQL interface.

After doing this, check for the errors if any. If your program runs without errors that means connection is established. Although you can use is_connected() function to check whether the connection is established or not! Observe this code:

import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='root',database='Tutorialaicsip')
if cn.is_connected():
   print("Connection Established")
else:
   print("Connection Errors! Kindly check!!!")

Execute SQL command and fetch rows

The next step after the successful connection is to write SQL command and fetch rows. The SQL commands are used to perform DML operations and fetch rows read data from table. So we will see them in detail later.

You have to create a cursor object for executing SQL command and fetch rows. Cursor object is a special kind of structure that processes the data row by row in database. You can create cursor object in the following manner.

cur=cn.cursor()

In the next section of Python MySQL connectivity class 12 you will learn how to perform DML opeations.

Performing DML operations (insert, update and delete)

To perform the DML operations like insert, update or delete follow these steps:

1. Create a cursor object
2. Write command as parameters for execute() function
3. Use commit() function to save the changes and reflect the data in the table.

Let’s DML operations with insert command for Python MySQL connectivity class 12.

insert command

Observe the following code:

import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()
cur.execute("insert into students values(1111,'Asmita',78.50,'B1')
cn.commit()

update command

import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()
cur.execute("update students set marks=80.5 where rollno=1111")
cn.commit()

delete command

import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()
cur.execute("delete from students where rollno=1111")
cn.commit()

In the next section of Python MySQL connectivity class 12 we will discuss about the select command.

Select Command

As you know the select command is used retrieve records from the database. The result is available in the resultset or dataset. You can store the select the command in cursor object in python. Then for resultset you can use the fetch…() function. These are:

1. fetchall(): It will retrieve all data from a database table in form of record or tuple or a row.
2. fetchone(): It will retrieve one record from the resultset as a tuple or a list. It returns the records in a specific order like first record, the next time next record and so on. If records are not available then it will return None.
3. fetchmany(): It will retrieve a number of records from the database. If records are not available then it will return an empty tuple.
4. rowcount: It is one of the properties of cursor object that return number of rows fetched from the cursor object.

Observe the below-given code for fetchall() function:

import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()
cur.execute("select * from students")
d=cursor.fetchall()
for r in d:
   print(r)

Observe the below-given code for fetchmany(n) function:

import mysql.connector as msql
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()
cur.execute("select * from students")
d=cursor.fetchmany(3)
for r in d:
   print(r)

Above code will return 3 rows from the database.

import mysql.connector as msql
import time
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()
cur.execute("select * from students")
d=cur.fetchone()
print(d)
time.sleep(3)
d=cur.fetchone()
print(d)
time.sleep(3)
d=cur.fetchone()
time.sleep(3)
print(d)

I have used time.sleep() method to apply pause in between the data retrieval.

In the next section of Python MySQL connectivity class 12 we are going to discuss parametrized queries.

Parametrized Queries

Sometimes we need to access values as per the user’s input. The query result is based on the values user has passed. So for that we have this option parameterized queries. There are two ways to use parameterized queries:

1. with % formatting pattern
2. with {}.format pattern

with % formatting pattern

This pattern takes the general form – f % v, where f is a format and v is the value. Consider the following code:

import mysql.connector as msql
import time
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()

#display records more than 80%
cur.execute("select * from students where marks >%s" %(80,))
d=cur.fetchall()
for r in d:
    print(r)

#display records having B1 grade
cur.execute("select * from students where grade='%s'" %('B1',))
d=cur.fetchall()
for r in d:
    print(r)

with {}.format pattern

In this pattern you can write {} where the value is placed followed by .format(values). Consider the following code:

import mysql.connector as msql
import time
cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',database='Tutorialaicsip')
cur=cn.cursor()
cur.execute("select * from students where marks >{}" .format(80))
d=cur.fetchall()
for r in d:
    print(r)
cur.execute("select * from students where grade='{}'".format('B1'))
d=cur.fetchall()
for r in d:
    print(r)

The last step for Python MySQL connectivity class 12 is closing the connection.

Close the connection

Finally, you have to close the established connect using close() function. It will help to clean up the memory. Observe the following code:

con.close()

So I hope you have enjoyed this article for Python MySQL connectivity class 12. You can ask your doubts or queries in the comment section for this article Python MySQL connectivity class 12.

Thank you very much for reading this article. Follow the below given link to access the complete study material.