Python-MySQL Connectivity

INTRODUCTION:

The Python Programming language has powerful features for database programming. It supports various databases like MySQL, Oracle, Sybase, PostgreSQL etc. 
To establish connectivity between Python and MySQL, we require Python Database Interfaces and API. 

Steps to install the connector are as follows:-

• Go to the folder where Python is installed in your PC. 
• Open Folder Python 37-32
• Open > Scripts
• Press Shift+ Click Right Button of Mouse
• Click on option " open Command Window here"
• type>    python -m pip install mysql-connector

                                OR

• Go to command prompt and write path as  C:\Users\YourName\Appdata\Local\Python\Python37-21\Scripts\            python -m pip install mysql-connector

           Here Yourname means Username of your PC

after this wait for a while MySQL -Connector shall download and will be installed on your System. Now to check whether it has been properly installed or not :-

• Open Python Shell and Type   
• >>> import mysql.connector
• >>>

if no error message gets displayed , means driver has been installed successfully.

Before creating database connectivity kindly ensure MYSQL should be installed in your system. 

MySQL Module for PYthon Version can be downloaded from:

http://sourceforge.net/projects/mysql-python

ESTABLISHING CONNECTION

after completing the installation of MySQL connector , the next step to establishing connection between MySQLdb and Python. for this :-

• Create a new script fie in Python, type the following code and run it.

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root",passwd=   "root")

print (mydb)

if the output as shown below is obtained , means the connection has been established successfully.

 RESTART: C:\Users\Admin\AppData\Local\Programs\Python\Python36-32\dbconct.py 

<mysql.connector.connection.MySQLConnection object at 0x022607B0>

>>> 

Note : the default MySQL user name is : root and Password "" (Blank) if you have not defined at the time of MySQL installation in your PC.

UNDERSTANDING ABOVE CODE:

import mysql.connector //  importing mysql connector files 

mysql.connector.connect () //  method of MySQL connector Python with required                                                                       parameters.

host="localhost" // This is the server name or IP address non which MySQL is running .if you are running on localhost, then its Ip address is 127.0.0.0

User=" root"  // This is the username that you use to work with MySQL Server. The default username for the MySQL database is "root".

Password="root" // Password is given by the user at the time of installing the MySQL database. if no password is assigned then leave it blank " '.

Database="School"  // it is the name of database to which connectivity is to be established.

Cursor object :

The MySQLCursor class instantiates objects that can execute operations  such as SQL statements. Cursor objects interact with the MySQL server  using a MySQLConnection object.

How to create cursor object and use it

import mysql.connector  mydb=mysql.connector.connect(host="localhost",user="root",passwd= "root") 

mycursor=mydb.cursor()

mycursor.execute ("create database if  not  exists school")  

mycursor.execute("show databases")

for x in mycursor:

    print(x)

Through line 4 we are creating a database named school if it is already not  created with the help of cursor object.

Line 5 executes the sql query show databases and store result in mycursor

as collection ,whose values are being fetched in x variable one by one.

On execution of above program school database is created and a list of  available databases is shown.

How to create table at run time

Table creation is very easy ,if we are already well versed in sql table creation  then we have to just pass the create table query in execute() method of  cursor object. But before table creation we must open the database.Here we  are opening database school(through connect() method) before student table  creation.

import mysql.connector  mydb=mysql.connector.connect(host="localhost",user="root",passwd= "root" , database="school")
mycursor=mydb.cursor()
mycursor.execute("create table student(rollno int(3) primary key, name varchar(20),age int(2))")

On successful execution of above program a table named student with three  fields rollno,name,age will be created in school database.

We can check student table in mysql shell also,if required.

How to change table structure/(add,edit,remove colum of a table) at run time  

To modify the structure of the table we just have to use alter table  query.Below program will add a column mark in the student table.

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root" ,
database="school")  
mycursor=mydb.cursor()
mycursor.execute("alter table student add (marks int(3))")  mycursor.execute("desc student")
for x in mycursor:
    print(x)


Above program will add a column marks in the table student and will display  the structure of the table.

How to search records of a table at run time

Below statement demonstrate the use of select query for searching specific  record from a table.

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root",
database="school")  
mycursor=mydb.cursor() 
nm=input("enter name")
mycursor.execute("select * from student where name='"+nm+"'")
for x in mycursor:
    print (x)


Above statements will prompt a name from user,as user type the name ,that  name is searched into the table student with the help of select query .result  will be shown with the help of mycursor collection.

How to fetch all records of a table at run time 

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


MySQLCursor.fetchall() Method


The method fetches all (or all remaining) rows of a query result set and returns a  list of tuples. If no more rows are available, it returns an empty list.

How to fetch one record of a table at run time  

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")  
mycursor=mydb.cursor()  
mycursor.execute("select * from student")  
row=mycursor.fetchone()
while row is not None:
print(row)
row = mycursor.fetchone()

MySQLCursor.fetchone() Method

This method retrieves the next row of a query result set and returns a single  sequence, or None if no more rows are available. By default, the returned tuple  consists of data returned by the MySQL server, converted to Python objects.

MySQLCursor.fetchmany() Method  rows = cursor.fetchmany(size=1)

This method fetches the next set of rows of a query result and returns a list  of tuples. If no more rows are available, it returns an empty list.

How to delete record of a table at run time 

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"

,database="school")

mycursor=mydb.cursor()

mycursor.execute("delete from student where rollno=1")  

mydb.commit()

In above program delete query will delete a record with rollno=1.commit()

method is necessary to call for database transaction.

How to update record of a table at run time 

 

import mysql.connector

mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"

,database="school")  

mycursor=mydb.cursor()

mycursor.execute("update student set marks=99 where rollno=2")  

mydb.commit()

In above program update query update the marks with 99 of rollno=2  Students are advised to develop menu driven program using above concepts  for better understating of python mysql database interface.

Manage Database Transaction

Database transaction represents a single unit of work. Any operation  which modifies the state of the MySQL database is a transaction.

Python MySQL Connector provides the following method to manage  database transactions.

commit –MySQLConnection.commit() method sends a COMMIT  statement to the MySQL server, committing the current transaction. 

rollback – MySQLConnection.rollback revert the changes made by  the current transaction.

AutoCommit – 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.

Manage Database Transaction

try:

conn = mysql.connector.connect(host='localhost',  database='school',

user='root', password='root')

conn.autocommit = false  cursor = conn.cursor()

sql_update_query = """Update student set marks = 95 where rollno = 2"""  cursor.execute(sql_update_query)

print ("Record Updated successfully ")  #Commit your changes

conn.commit()

except mysql.connector.Error as error :

print("Failed to update record to database rollback: {}".format(error))  #reverting changes because of exception

conn.rollback()  finally:  #closing database connection. 

 if(conn.is_connected()):

cursor.close()  conn.close()

print("connection is closed")

In above program if update query is successfully executed then commit() method will be executed  otherwise exception error part will be executed where revert of update query will be done due to  error.At finally we are closing cursor as well as connection.To rollback or commit we have to set  autocommit=false,just like conn.autocommit = false in above program otherwise rollback will not work.

SQL Commands

Grouping Records in a Query

Some time it is required to apply a Select query in a group of  records instead of whole table.

We can group records by using GROUP BY <column> clause  with Select command. A group column is chosen which have  non-distinct (repeating) values like City, Job etc.

Generally, the following Aggregate Functions [MIN(), MAX(),  SUM(), AVG(), COUNT()] etc. are applied on groups.

Name	Purpose
SUM()	Returns the sum of given column.
MIN()	Returns the minimum value in the given column.
MAX()	Returns the maximum value in the given column.
AVG()	Returns the Average value of the given column.
COUNT()	Returns the total number of values/ records as per given  column.

Aggregate Functions & NULL

Consider a table Emp having following records as-

Null values are excluded while (avg)aggregate function is  used

Emp
Code	Name	Sal
E1	Mohak	NULL
E2	Anuj	4500
E3	Vijay	NULL
E4	Vishal	3500
E5	Anil	4000

SQL Queries                Result of query  
mysql> Select Sum(Sal) from EMP; 12000
mysql> Select Min(Sal) from EMP;  3500
mysql> Select Max(Sal) from EMP;  4500
mysql> Select Count(Sal) from EMP; 3
mysql> Select Avg(Sal) from EMP;  4000

mysql> Select Count(*) from EMP; 5

Aggregate Functions & Group

An Aggregate function may applied on a column with DISTINCT or ALL  keyword. If nothing is given ALL is assumed.
Using SUM (<Column>)

This function returns the sum of values in given column or expression:

mysql>	Select	Sum(Sal)  from EMP;
mysql>	Select	Sum(DISTINCT  Sal)  from EMP;
mysql>	Select	Sum  ( Sal)  from EMP  where City=‘Jaipur’;
mysql>	Select	Sum  ( Sal)  from EMP Group By City;
mysql>	Select	Job,  Sum(Sal)  from EMP Group By Job;

Using MIN (<column>)

This functions returns the Minimum value in the given column. mysql> Select Min(Sal)  from EMP; mysql> Select Min(Sal)  from EMP Group By City; mysql> Select Job,  Min(Sal) from EMP Group By Job; Aggregate Functions & Group Using MAX (<Column>) This function returns the Maximum value in given column mysql> Select Max(Sal) from EMP; mysql> Select Max(Sal) from EMP where City=‘Jaipur’; mysql> Select Max(Sal) from EMP Group By City; Using AVG (<column>) This functions returns the Average value in the given column mysql> Select AVG(Sal) from EMP; Using COUNT (<*|column>) This functions returns the number of rows in the given  column mysql> Select Count  ( * )  from EMP; mysql> Select Count(Sal)  from EMP  Group By City; mysql> Select Count(*),  Sum(Sal)  from EMP  Group By Job; Aggregate Functions & Conditions You may use any condition on group, if required. HAVING <condition>  clause is used to apply a condition on a group.   mysql>  Select Job,Sum(Pay) from EMP                    Group  By Job HAVING Sum(Pay)>=8000; mysql>  Select  Job,  Sum(Pay)  from  EMP                Group  By Job HAVING Avg(Pay)>=7000; mysql>  Select  Job,  Sum(Pay)  from  EMP  Group  By Job HAVING                 Count(*)>=5; mysql>  Select  Job, Min(Pay),Max(Pay),  Avg(Pay) from EMP Group  By Job HAVING Sum(Pay)>=8000; mysql> Select Job, Sum(Pay) from EMP Where City=‘Jaipur’ Note :- Where clause works in respect of whole table but Having works  on Group only. If Where and Having both are used then Where will be   executed first.   Ordering Query Result – ORDER BY Clause A query result can be orders in ascending (A-Z) or  descending (Z-A) order as per any column. Default is Ascending order.   mysql> SELECT * FROM Student ORDER BY City; To get descending order use DESC key word.   mysql> SELECT * FROM Student ORDER BY City  DESC; mysql> SELECT Name, Fname, City FROM Student  Where Name LIKE ‘R%’ ORDER BY Class; *********************