Best post : Database Connectivity in Python (Part 2)

Database connectivity in Python

In this blog we are going to establish MySQL database connectivity in python. Database connectivity plays important role in developing dynamic applications.

To interact with the database using python it is mandatory to establish database connectivity in python using any database in back-end.
Following are the steps to establish database connectivity in python.

  • Accessing Connector module
  • Using Connect
  • Using Cursor and Execute
  • Reading single and multi-line results
  • Using Close

Accessing Connector module

This is the first step to to write script for establishing database connection.
To access connector module we need to use import keyword.

Syntax:

import mysql.connector

You can also use:

import mysql.connector as ms

Using Connect

This is the second step in script which is used to create connection object for establishing connection.
To create connection object connect method is used.

The connection object is used to call many other methods like : cursor(), close(), rollback() and commit()

Syntax:

db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')

connect() has 4 major parameters.
Here:-
user : Username for the connecting database.
passwd : Password of the given username for the connecting database.If no password is mentioned during installation than null string (”) is passed. In our case password is ‘root’.
host : Network on which database is running.(We can use ‘localhost’ or ‘127.0.0.1’)
database : It is name of the database to which you wish to connect. Note that database must be already created in MySQL.

Using Cursor

A cursor is a object in Python which is used to work in database.
Connection object is used to call cursor method which is used to create a cursor object.
This object of cursor method will help will help Python to execute database commands.
Cursor object is used to call many methods : execute(), fetchone(), fetchall() and fetchmany(size)

Syntax:

cur = db.cursor()

Here:-
cur : cursor object (any variable name can be given).
db :connection object (same variable which is used with connect())
cursor() : Method of connection object

Execute SQL Queries

To execute SQL queries from python script we need execute().
This method is associated with cursor object.
We can either specify SQL query directly in parenthesis or we can save query in variable and pass that variable in parenthesis.

Syntax:

cur.execute("SQL QUERY")

Here:-
cur : cursor object (same variable which is used with cursor()).
execute() : Method of cursor object
SQL Query : Any SQL Query like select, insert, update etc.

Reading single and multi-line results

Fetching data from database.

To fetch data from the database cursor object provides fetchall(), fetchmany(size) and fetchone().
This can also be used to check if database connectivity in python is established or not.

fetchone()

This is one of the method of cursor object.
It is used to fetch only one row from query result i.e next row.
We can access one record at a time using fetchone method.
Fetched record is returned in tuple and the values in tuple are in same order as mentioned in the query.
If no more records are present in query result then fetchone() returns None.

Syntax

row=cursor_obj.fetchone()
fetchmany(arg)

This is one of the method of cursor object.
It is used to fetch as many rows from query result as mentioned in argument.
We can access many records at a time using fetchmany method.
Fetched records are returned in list of tuples as present in query result.

Syntax

row=cursor_obj.fetchmany(arg)
fetchall()

This is one of the method of cursor object.
It is used to fetch all rows from query result.
We can access all records at a time using fetchall method.
Fetched records are returned in list of tuples as present in query result.

Syntax

row=cursor_obj.fetchall()

Using Close

This is the last step in the process of creating a script.
After using cursor we use close(), this method closes cursor, resets all results and ensures that cursor object has no reference to its connection object.
This method can also be used with connection object to revoke connection after completion of task

Syntax

cursor_obj.close() or db.close()

To establish database connectivity first go to MySQL Command Line Client and create database and use it.

REFER :Best post : Database connectivity in Python (Part 1)

"""
Author : ITVoyagers (itvoyagers.in)

Date :26th April 2020

Description : Program to establish Database connectivity using Python script

"""
#mysql.connector is the module containing functions for database connectivity
import mysql.connector

#prepare connection object using connect()
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method. select version() will return the version of MySQL used
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)

# disconnect from server
db.close()

OUTPUT

Output of establishing database connectivity using python (itvoyagers.in)
Output of establishing database connectivity using python (itvoyagers.in)

Mind map of functions involved in database connectivity in python

Database connectivity in Python (itvoyagers.in)
Database connectivity in Python (itvoyagers.in)

You can also check out other posts related to python file handling, GUI, and basic python.

Leave a Comment