Python Connectivity

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:
Python MySQL connectivity class 12
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:
pip install mysql connector python
After installation just write the import statement to import the package in
python code.
import mysql.connector as msql
importing package mysql connector in python
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:

cn=msql.connect(host='localhost',user='root',passwd='root',databas
e='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:

cn=msql.connect(host='localhost',user='root',passwd='root',databas
e='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:

cn=msql.connect(host='localhost',user='root',passwd='MySQL@123',da
tabase='Tutorialaicsip')
cur=cn.cursor()
cur.execute("insert into students values(1111,'Asmita',78.50,'B1')
cn.commit()
update command

cur=cn.cursor()
cur.execute("update students set marks=80.5 where rollno=1111")
cn.commit()
delete command

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:
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:
cur=cn.cursor()
d=cursor.fetchmany(3)
for r in d:
print(r)
Above code will return 3 rows from the database.
import time
cur=cn.cursor()
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 time
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 time
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()

Python Connectivity

Uploaded by

Copyright:

Available Formats

Python Connectivity

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Python Connectivity

Uploaded by

Copyright:

Available Formats

Basically the process of transfer data between python programs and

MySQL database is known as Python Database Connectivity. There few

1. Import the required packages

Import the required packages

To perform the Python MySQL Database Connectivity you need to install

Python MySQL connectivity class 12

In the above screenshot, I have mysql-python-connector already installed. If

import mysql.connector as msql

importing package mysql connector in python

Here I have instantiated msql to mysql.connector which can be work as an

To establish a connection you need to create a connection object in

import mysql.connector as msql

import mysql.connector as msql

Execute SQL command and fetch rows

Performing DML operations (insert, update and delete)

Observe the following code:

import mysql.connector as msql

import mysql.connector as msql

import mysql.connector as msql

1. fetchall(): It will retrieve all data from a database table in form of

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

import mysql.connector as msql

cur.execute("select * from students")

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

import mysql.connector as msql

cur.execute("select * from students")

Above code will return 3 rows from the database.

import mysql.connector as msql

cur.execute("select * from students")

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

In the next section of Python MySQL connectivity class 12 we are going to

1. with % formatting pattern

with % formatting pattern

import mysql.connector as msql

#display records more than 80%

cur.execute("select * from students where marks >%s" %(80,))

#display records having B1 grade

cur.execute("select * from students where grade='%s'" %('B1',))

with {}.format pattern

import mysql.connector as msql

cur.execute("select * from students where marks >{}" .format(80))

cur.execute("select * from students where

Close the connection

You might also like