Scribd
Upload
2 views18 pages

Python Unit - 5

The document provides a comprehensive guide on connecting to a MySQL database using Python, focusing on the installation and usage of MySQL Connector/Python. It includes instructions for creating databases and tables, executing SQL queries, and inserting records, along with examples of using Python's Tkinter library for GUI applications. Additionally, it outlines the methods and properties of MySQL Cursor for effective database interaction.

Uploaded by

vishwasgroup
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views18 pages

Python Unit - 5

The document provides a comprehensive guide on connecting to a MySQL database using Python, focusing on the installation and usage of MySQL Connector/Python. It includes instructions for creating databases and tables, executing SQL queries, and inserting records, along with examples of using Python's Tkinter library for GUI applications. Additionally, it outlines the methods and properties of MySQL Cursor for effective database interaction.

Uploaded by

vishwasgroup
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Connecting With Database using Python

 Verifying the MySQL dB Interface Installation :


To access the MySQL database from Python, database driver needed. MySQL
Connector/Python is a standardized database driver provided by MySQL. MySQL
Connector/Python supports almost all features provided by MySQL. It allows to
convert the parameter’s value between Python and MySQL data types e.g., Python
datetime and MySQL DATETIME. MySQL Connector/Python is designed specifically to
MySQL. It supports all MySQL extensions such as LIMIT clause. MySQL
Connector/Python allows to compress the data stream between Python and MySQL
database server using protocol compression. It supports connections using TCP/IP
socket and secure TCP/IP connection using SSL. MySQL Connector/Python is an API
implemented using pure Python. It means that we don’t need to install any MySQL
client library or any Python modules except the standard library.
 Prerequisites :
Before installing MySQL Connector/Python, We need:
 Root or Administrator privileges in order to perform the installation.
 Python installed on system
(Note that the MySQL Connector/Python installation requires python to be in
the system’s PATH or it will fail.)
 Installation :
The pip command, allows to install MySQL Python connector on any OS.
Run this Command on Command Prompt:

pip install mysql-connector-python


 Verifying MySQL Connector/Python installation :
After installing the MySQL Python connector, you need to test it to make sure
that it is working correctly and you are able to connect to the MySQL database
server without any issues.
To verify the installation, you use the following steps:
 Open Python command line
 Type the following code :
>>> import [Link]
>>> [Link](host='localhost',
database='mysql', user='root',password='your pass')

 If It has been successfully installed the MySQL Connector/Python on


system, Following Code will be displayed.
<[Link] object at 0x0187AE50>

Python Notes By : Hiral Pandya Page : 1 of 18


 Working with MySQL Database :
 Syntax To Create Database in MySQL :
CREATE DATABASE databasename
 Syntax To Create Table in MySQL Database :
CREATE TABLE table_name (column1 datatype,
column2 datatype, column3 datatype,....);
 Syntax To Create Table Using Another Table in MySQL Database :
CREATE TABLE new_table_name AS
SELECT column1, column2,... FROM existing_table
WHERE ....;
 Using MySQL from Python :
Steps To Follow In Python To Work With MySQL :
 Connect to the database.
 Create an object for your database.
 Execute the SQL query.
 Fetch records from the result.
 Informing the Database if we make any changes in the table.

 MySQL Cursor :

 The MySQL Cursor of mysql-connector-python (and similar libraries)


is used to execute statements to communicate with the MySQL database.

 Using the methods of it we can execute SQL statements, fetch data from
the result sets, call procedures.
 We can create Cursor object using the cursor() method of the
Connection object/class.

Methods of MySQL Cursor


Methods Description
This method is used to call existing procedures MySQL
callproc()
database.
Close() This method is used to close the current cursor object.
Info() This method gives information about the last query.

Python Notes By : Hiral Pandya Page : 2 of 18


This method accepts a list series of parameters list.
executemany() Prepares an MySQL query and executes it with all the
parameters.
This method accepts a MySQL query as a parameter
execute()
and executes the given query.
This method retrieves all the rows in the result set of a
query and returns them as list of tuples. (If we execute
fetchall()
this after retrieving few rows it returns the remaining
ones)
This method fetches the next row in the result of a
fetchone()
query and returns it as a tuple.
This method is similar to the fetchone() but, it retrieves
fetchmany() the next set of rows in the result set of a query, instead
of a single row
This method returns the warnings generated by the last
fetchwarnings()
executed query.

Properties of MySQL Cursor

Properties Description

This is a read only property which returns the list


column_names
containing the column names of a result-set.
This is a read only property which returns the list
description
containing the description of columns in a result-set.
This is a read only property, if there are any auto-
incremented columns in the table, this returns the
lastrowid
value generated for that column in the last INSERT or,
UPDATE operation.
This returns the number of rows returned/updated in
rowcount
case of SELECT and UPDATE operations

statement This property returns the last executed statement.

Python Notes By : Hiral Pandya Page : 3 of 18


 Create Database In MySQL Using Python :
To create a database in MySQL, use the "CREATE DATABASE" statement.
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
# If Any Password is Set For MySQL
password="yourpassword"
)
mycursor = [Link]()
[Link]("CREATE DATABASE mydatabase")

 Create Table In MySQL Database Using Python :


To create a database in MySQL, use the "CREATE TABLE" statement.
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
# If Any Password is Set For MySQL
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link] "CREATE TABLE TableName
(FieldName DataType(Value), FieldName
DataType(Value),.....)")

Python Notes By : Hiral Pandya Page : 4 of 18


Example: Create DataBase and Create Table
import [Link]
print("DataBase Is Creating")
mydb = [Link]
(host="localhost",user="root")
mycursor = [Link]()
[Link]("CREATE DATABASE db_StudInfo")
[Link]()
print("DataBase Created Successfully..")
print("-"*40)

print("Table Is Creating")
mydb = [Link]
(host="localhost",user="root",database="db_StudInfo")
str = """CREATE TABLE tbl_StudPerInfoMaster
(
Stud_ID int(3) NOT NULL,
Stud_Name varchar(250) NOT NULL,
Stud_Address varchar(500) NOT NULL,
Stud_City varchar(100) NOT NULL,
Stud_ContNo varchar(15) NOT NULL,
Stud_EmailID varchar(50) NOT NULL,
Stud_Remarks varchar(150) NOT NULL
)"""
mycursor = [Link]()
[Link](str)
[Link]()
print("Table Created Successfully..")
print("-"*40)

Python Notes By : Hiral Pandya Page : 5 of 18


Example: Insert Record In Table
import [Link]

mydb = [Link]

(host="localhost",user="root",database="db_StudInfo")

mycursor = [Link]()

qry = """INSERT INTO tbl_StudPerInfoMaster

(Stud_ID, Stud_Name, Stud_Address, Stud_City,


Stud_ContNo, Stud_EmailID, Stud_Remarks)

VALUES

(1, 'Mr. AAA', 'Address of AAA', 'Jamnagar',


'9874563210', 'aaa@[Link]', 'No Remarks')"""

[Link](qry)

[Link]()

print("-"*40)

print([Link]," Record Inserted")

[Link]()
print("-"*40)

OutPut:

Python Notes By : Hiral Pandya Page : 6 of 18


Example: Insert Record With Single Parameter Using execute() Method

import [Link]

mydb = [Link]

(host="localhost",user="root",database="db_StudInfo")

mycursor = [Link]()

qry = """INSERT INTO tbl_StudPerInfoMaster

(Stud_ID, Stud_Name, Stud_Address, Stud_City,


Stud_ContNo, Stud_EmailID, Stud_Remarks)

VALUES(%s, %s, %s, %s, %s, %s, %s)"""

val= (1, 'Mr. AAA', 'Address of AAA', 'Jamnagar',


'9874563210', 'aaa@[Link]', 'No Remarks')

[Link](qry,val)

[Link]()

print("-"*40)

print([Link]," Record Inserted")

[Link]()
print("-"*40)

OutPut:

Python Notes By : Hiral Pandya Page : 7 of 18


Example: Insert Record Using executemany() Method

import [Link]

mydb = [Link]
(host="localhost",user="root",database="db_StudInfo")

mycursor = [Link]()

qry = """INSERT INTO tbl_StudPerInfoMaster


(Stud_ID, Stud_Name, Stud_Address, Stud_City,
Stud_ContNo, Stud_EmailID, Stud_Remarks)
VALUES(%s, %s, %s, %s, %s, %s, %s)"""

param = [(2, 'Mr. BBB', 'Address of BBB', 'BARODA',


'8523697410', 'bbb@[Link]', 'No Remarks'),
(3, 'Mr. CCC', 'Address of CCC', 'RAJKOT',
'3698521470', 'ccc@[Link]', 'No Remarks'),
(4, 'Mr. DDD', 'Address of DDD', 'SURAT',
'8794563210', 'ddd@[Link]', 'No Remarks'),
(5, 'Mr. DDD', 'Address of EE', 'Jamnagar',
'7987456312', 'eee@[Link]', 'No Remarks')]

[Link](qry,param)
[Link]()
print("-"*40)
print([Link]," Record Inserted")
[Link]()
print("-"*40)

OutPut:

Python Notes By : Hiral Pandya Page : 8 of 18


Example: Insert Record Using PYTHON GUI
from tkinter import *
import tkinter as tk
from tkinter import messagebox
import [Link] as font
import [Link]

def AddRecords():
Stud_ID = [Link]()
Stud_Name = [Link]()
Stud_Address = [Link]("0.0",END)
Stud_City = [Link]()
Stud_ContNo = [Link]()
Stud_EmailID = [Link]()
Stud_Remarks = [Link]()

mysqldb=[Link](host="localhost",user
="root",database="db_StudInfo")
mycursor=[Link]()

try:
sql = """INSERT INTO tbl_StudPerInfoMaster
(Stud_ID, Stud_Name, Stud_Address, Stud_City,
Stud_ContNo, Stud_EmailID, Stud_Remarks)
VALUES(%s, %s, %s, %s, %s, %s, %s)"""

val = (Stud_ID, Stud_Name, Stud_Address,


Stud_City, Stud_ContNo, Stud_EmailID, Stud_Remarks)
[Link](sql, val)
[Link]()
[Link]("Data Insert", "Record
inserted successfully...")

except Exception as e:
[Link]("Exception ", str(e))
[Link]()
[Link]()

root = Tk()
[Link]("Student Information")
[Link]("500x500")

global Stud_ID
global Stud_Name
global Stud_Address

Python Notes By : Hiral Pandya Page : 9 of 18


global Stud_City
global Stud_ContNo
global Stud_EmailID
global Stud_Remarks

myfont =
[Link](family='Verdana',size=12,weight="normal")

Label(root, text="ID :",font='myfont').grid(


column=0, row=0, padx=110, pady=10)

txtID =
Entry(root,font='myfont',bd=1,relief='solid',width=5)
[Link](x=140, y=10)

Label(root, text="Name :",font='myfont').place(x=84,


y=40)
txtName =
Entry(root,font='myfont',bd=1,relief='solid')
[Link](x=140, y=40)

Label(root, text="Address
:",font='myfont').place(x=66, y=70)
txtAdd =
Text(root,font='myfont',bd=1,relief='solid',height =
2.5, width = 20)
[Link](x=140, y=70)

Label(root, text="City :",font='myfont').place(x=96,


y=135)
txtCity =
Entry(root,font='myfont',bd=1,relief='solid')
[Link](x=140, y=135)

Label(root, text="Contact No
:",font='myfont').place(x=48, y=165)
txtContNo =
Entry(root,font='myfont',bd=1,relief='solid')
[Link](x=140, y=165)

Label(root, text="Email ID
:",font='myfont').place(x=66, y=195)
txtEmailID =
Entry(root,font='myfont',bd=1,relief='solid')

Python Notes By : Hiral Pandya Page : 10 of 18


[Link](x=140, y=195)

Label(root, text="Remarks
:",font='myfont').place(x=60, y=225)
txtRem =
Entry(root,font='myfont',bd=1,relief='solid')
[Link](x=140, y=225)

Button(root, text="Add",
command=AddRecords,bg='Navy',fg='White',font='myfont'
, width=15, height=2).place(x=140, y=270)

[Link]()

Example: Display Records Using PYTHON GUI


from tkinter import *
import tkinter as tk
from tkinter import messagebox
import [Link] as font
import [Link]

root = Tk()
[Link]("Student Information")
[Link]("700x400")

frameButton = Frame(root)
[Link](fill=tk.X,side = TOP )

frameData = Frame(root,pady=20)
[Link](side = TOP )

myfont =
[Link](family='Verdana',size=12,weight="normal")

def displayRecords():

try:

mysqldb=[Link](host="localhost",user
="root",database="db_StudInfo")
my_cursor=[Link]()
my_cursor.execute("SELECT * FROM
tbl_studperinfomaster")
global i
i=0

Python Notes By : Hiral Pandya Page : 11 of 18


for student in my_cursor:
for j in range(len(student)):
e =
Label(frameData,width=12,bg='tan',height=3,
text=my_cursor.column_names[j],
relief='solid',bd=1)

[Link](font=[Link](family='calibri',size=10,w
eight="normal"))
[Link](row=i, column=j)
k = j

e =
Label(frameData,width=10,bg='tan',height=3,
text="Edit",relief='solid',bd=1)

[Link](font=[Link](family='calibri',size=10,w
eight="normal"))
[Link](row=0, column=k+2)

my_cursor.execute("SELECT * FROM
tbl_studperinfomaster")
i = 1

for student in my_cursor:


for j in range(len(student)):

e =
Label(frameData,width=12,height=3, text=student[j],

relief='solid',bd=1,wraplength=80)

[Link](font=[Link](family='calibri',size=10,w
eight="normal"))
[Link](row=i, column=j)

e =
[Link](frameData,width=7,height=2,fg='yellow',bg='
Green', text='Edit',relief='solid',

font='myfont',bd=1,command=lambda
k=student[0]:edit_data(k))
[Link](row=i, column=len(student) + 1)
i=i+1
except Exception as e:
print(e)
[Link]()

Python Notes By : Hiral Pandya Page : 12 of 18


[Link]()

Button(frameButton, text="Display",
command=displayRecords,bg='tan',fg='Navy',font='myfon
t', height=1).pack(fill=tk.X)

[Link]()

Example: Edit / Update / Delete Records Using PYTHON GUI


from tkinter import *
import tkinter as tk
from tkinter import messagebox
import [Link] as font
import [Link]

root = Tk()
[Link]("Student Information")
[Link]("780x400")

frameButton = Frame(root)
[Link](fill=tk.X,side = TOP )

frameData = Frame(root,pady=20)
[Link](side = TOP )

frameDataEdit = Frame(root,pady=20)
[Link](side = TOP )

myfont =
[Link](family='Verdana',size=12,weight="normal")

global mydb
global Stud_Name
global Stud_Address
global Stud_City
global Stud_ContNo
global Stud_EmailID
global Stud_Remarks
global i

def displayRecords():
try:
frameDataEdit.pack_forget()
[Link](side = TOP)
mydb =
[Link](host="localhost",user="root",
Python Notes By : Hiral Pandya Page : 13 of 18
database="db_StudInfo")
my_cursor = [Link]()
my_cursor.execute("SELECT * FROM
tbl_studperinfomaster ORDER BY Stud_ID")
i=0

for student in my_cursor:


for j in range(len(student)):
e =
Label(frameData,width=12,bg='tan',height=3,
text=my_cursor.column_names[j],
relief='solid',bd=1)

[Link](font=[Link](family='calibri',size=10,w
eight="normal"))
[Link](row=i, column=j)
k = j

e =
Label(frameData,width=10,bg='tan',height=3,
text="Edit",relief='solid',bd=1)

[Link](font=[Link](family='calibri',size=10,w
eight="normal"))
[Link](row=0, column=k+2)

e =
Label(frameData,width=10,bg='RED',fg='white',height=3
, text="Delete",relief='solid',bd=1)

[Link](font=[Link](family='calibri',size=10,w
eight="bold"))
[Link](row=0, column=k+3)

my_cursor.execute("SELECT * FROM
tbl_studperinfomaster")

i = 1

for student in my_cursor:


for j in range(len(student)):
e =
Label(frameData,width=12,height=3, text=student[j],

relief='solid',bd=1,wraplength=80)

[Link](font=[Link](family='calibri',size=10,w

Python Notes By : Hiral Pandya Page : 14 of 18


eight="normal"))
[Link](row=i, column=j)

e =
[Link](frameData,width=7,height=2,fg='yellow',bg='
Green', text='Edit',relief='solid',

font='myfont',bd=1,command=lambda
k=student[0]:edit_data(k))
[Link](row=i, column=len(student) + 1)

e =
[Link](frameData,width=7,height=2,fg='yellow',bg='
RED', text='Delete',relief='solid',

font='myfont',bd=1,command=lambda
k=student[0]:Delete_data(k))
[Link](row=i, column=len(student) + 2)

i=i+1
my_cursor.close()
except Exception as e:
print(e)
[Link]()
[Link]()

def Delete_data(r):

mydb =
[Link](host="localhost",user="root",
database="db_StudInfo")
my_cursor = [Link]()
my_cursor.execute("DELETE FROM
tbl_StudperInfoMaster WHERE Stud_ID = "+ str(r))
my_cursor.close()
[Link]()
displayRecords()

def edit_data(r):

frameData.pack_forget()
[Link](side = TOP)

mydb =
[Link](host="localhost",user="root",
database="db_StudInfo")
my_cursor = [Link]()

Python Notes By : Hiral Pandya Page : 15 of 18


my_cursor.execute("SELECT * FROM
tbl_studperinfomaster WHERE Stud_ID = "+ str(r))
s = my_cursor.fetchone()

strName = StringVar(frameDataEdit)
[Link](s[1])

strCity = StringVar(frameDataEdit)
[Link](s[3])

strContNo = StringVar(frameDataEdit)
[Link](s[4])

strEmailID = StringVar(frameDataEdit)
[Link](s[5])

Label(frameDataEdit, text="Name :
",font='myfont',width=10,justify='right',anchor='e').
grid(column=0, row=1)
txtName =
Entry(frameDataEdit,textvariable=strName,font='myfont
',width=30,bd=1,relief='solid')
[Link](column=1, row=1,sticky='W')

Label(frameDataEdit, text="Address :
",font='myfont',width=10,anchor='e').grid(column=0,
row=2,pady=0)
txtAdd =
Text(frameDataEdit,font='myfont',bd=1,relief='solid',
height = 2.5, width = 30)
[Link](1.0,s[2])
[Link](column=1, row=2,pady=5,sticky=W)

Label(frameDataEdit, text="City :
",font='myfont',width=10,anchor='e').grid(column=0,
row=3)
txtCity =
Entry(frameDataEdit,textvariable=strCity,font='myfont
',width=30,bd=1,relief='solid')
[Link](column=1, row=3,pady=5,sticky=W)

Label(frameDataEdit, text="Contact No :
",font='myfont',width=10,anchor='e').grid(column=0,
row=4)
txtContNo =
Entry(frameDataEdit,textvariable=strContNo,font='myfo

Python Notes By : Hiral Pandya Page : 16 of 18


nt',width=30,bd=1,relief='solid')
[Link](column=1, row=4,pady=5,sticky=W)

Label(frameDataEdit, text="Email ID :
",font='myfont',width=10,anchor='e').grid(column=0,
row=5)
txtEmailID =
Entry(frameDataEdit,textvariable=strEmailID,font='myf
ont',width=30,bd=1,relief='solid')
[Link](column=1, row=5,pady=5,sticky=W)

Label(frameDataEdit, text="Remarks :
",font='myfont',width=10,anchor='e').grid(column=0,
row=6,pady=0)
txtRem =
Text(frameDataEdit,font='myfont',bd=1,relief='solid',
height = 2.5, width = 30)
[Link](1.0,s[6])
[Link](column=1, row=6,pady=5,sticky=W)

def UpdateData(r):

Stud_Name = [Link]()
Stud_Address = [Link]("0.0",END)
Stud_City = [Link]()
Stud_ContNo = [Link]()
Stud_EmailID = [Link]()
Stud_Remarks = [Link]("0.0",END)

mydb =
[Link](host="localhost",user="root",
database="db_StudInfo")
my_cursor = [Link]()

QRY = """UPDATE tbl_studperinfomaster SET


Stud_Name = %s,Stud_Address = %s, Stud_City = %s,
Stud_ContNo = %s, Stud_EmailID = %s, Stud_Remarks =
%s
WHERE Stud_ID =
"""
val = (Stud_Name, Stud_Address, Stud_City,
Stud_ContNo, Stud_EmailID, Stud_Remarks)
my_cursor.execute(QRY+ str(r),val)
my_cursor.close()
frameDataEdit.pack_forget()
[Link](side = TOP)

Python Notes By : Hiral Pandya Page : 17 of 18


displayRecords()

def CancelData():
displayRecords()

Button(frameDataEdit, text="Update",
command=lambda
:UpdateData(r),bg='darkOrange',fg='Navy',font='myfont
', width=15,
height=2,justify='center').grid(column=1,
row=7,padx=0,pady=10)
Button(frameDataEdit, text="Cancel",
command=CancelData,bg='Green',fg='White',font='myfont
', width=8, height=2).grid(column=0, row=7,pady=10)

Button(frameButton, text="Display",
command=displayRecords,bg='tan',fg='Navy',font='myfon
t', height=1).pack(fill=tk.X)

[Link]()

Python Notes By : Hiral Pandya Page : 18 of 18

You might also like