Simple MYSQL-Python CRUD Operation Example using Tkinter

Python support connection with many RDBMS i.e. Oracle, Cybase, MYSQL etc. In this example we has selected MYSQL as DB and performed basic operation like Select, Insert, Update and Delete.

We had used tkinter as Python UI for user and MYSQLdb package for connecting MYSQL.

from tkinter import *
import tkinter
from tkinter import messagebox
from lib2to3.fixer_util import Number
import MySQLdb

top = tkinter.Tk()

L1 = Label(top, text="First Name")
L1.pack( side = LEFT)
E1 = Entry(top, bd =5)
E1.pack(side = LEFT)

L2 = Label(top, text="Second Name")
L2.pack( side = LEFT)
E2 = Entry(top, bd =5)
E2.pack(side = LEFT)

'''L3 = Label(top, text="Answer")
L3.pack( side = LEFT)
E3 = Entry(top, bd =5)
E3.pack(side = LEFT)'''

def buttonCallBack(selection):
print("E1.get()"+E1.get())
print("E2.get()"+E2.get())
print("selection"+selection)
a = E1.get()
b = E2.get()
if selection in ('Insert'):
# Open database connection
db = MySQLdb.connect("localhost","siddhu","siddhu","siddhutest" )

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

# execute SQL query 
cursor.execute("SELECT VERSION()")

# Fetch a single row 
data = cursor.fetchone()

print ("Database version : %s " % data)

# Drop table if it already exist 
cursor.execute("DROP TABLE IF EXISTS SIDDHU_TEST")

# Create table Example
sql = """CREATE TABLE SIDDHU_TEST (
FNAME CHAR(20) NOT NULL,
LNAME CHAR(20))"""

cursor.execute(sql)

# Inserting in Table Example:- Prepare SQL query to INSERT a record into the database and accept the value dynamic. This is similar to prepare statement which we create.
sql = "INSERT INTO SIDDHU_TEST(FNAME, \
LNAME) \
VALUES ('%s', '%s')" % \
('siddhu', 'dhumale')
try:
# Execute command
cursor.execute(sql)
# Commit changes
db.commit()
except:
# Rollback if needed
db.rollback()
# disconnect from server 
db.close() 
print("Data Inserted properly "+a +"--"+b) 
elif selection in ('Update'): 
# Open database connection
db = MySQLdb.connect("localhost","siddhu","siddhu","siddhutest" )

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

# execute SQL query 
cursor.execute("SELECT VERSION()")

# Fetch a single row 
data = cursor.fetchone()

print ("Database version : %s " % data)
# Update Exmaple:- Update record 
sql = "UPDATE SIDDHU_TEST SET LNAME = '%s'" % (b) +" WHERE FNAME = '%s'" % (a)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback() 
db.close() 
print("Data Updated properly "+a +"--"+b)
elif selection in ('Delete'): 
# Open database connection
db = MySQLdb.connect("localhost","siddhu","siddhu","siddhutest" )

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

# execute SQL query 
cursor.execute("SELECT VERSION()")

# Fetch a single row 
data = cursor.fetchone()

print ("Database version : %s " % data)
# Delete Operation :- Delete Opearations
sql = "DELETE FROM SIDDHU_TEST WHERE FNAME = '%s'" % (a)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
db.close()
print("Data Deleted properly "+a +"--"+b)
else: 
# Open database connection
db = MySQLdb.connect("localhost","siddhu","siddhu","siddhutest" )

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

# execute SQL query 
cursor.execute("SELECT VERSION()")

# Fetch a single row 
data = cursor.fetchone()

print ("Database version : %s " % data) 
# Select Query Example :- Selecting data from the table.
sql = "SELECT * FROM SIDDHU_TEST \
WHERE FNAME = '%s'" % (a)
try:
# Execute the SQL command
cursor.execute(sql)
lname = ""
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1] 
# Now print fetched result
E2.delete(0,'end')
print ("Value Fetch properly lname="+lname) 
E2.insert(0, lname)

except:
db.close()
print ("Value Fetch properly")

BInsert = tkinter.Button(text ='Insert', command=lambda: buttonCallBack('Insert'))
BInsert.pack(side = LEFT)

BUpdate = tkinter.Button(text ='Update', command=lambda: buttonCallBack('Update'))
BUpdate.pack(side = LEFT)

BDelete = tkinter.Button(text ='Delete', command=lambda: buttonCallBack('Delete'))
BDelete.pack(side = LEFT)

BSelect = tkinter.Button(text ='Select', command=lambda: buttonCallBack('Select'))
BSelect.pack(side = LEFT)

label = Label(top)
label.pack()

top.mainloop()

Simple Add/Sub/Div/Multiplication application in Python with Tk GUI

from tkinter import *
import tkinter
from tkinter import messagebox
from lib2to3.fixer_util import Number

top = tkinter.Tk()

L1 = Label(top, text="First Number")
L1.pack( side = LEFT)
E1 = Entry(top, bd =5)
E1.pack(side = LEFT)

L2 = Label(top, text="Second Number")
L2.pack( side = LEFT)
E2 = Entry(top, bd =5)
E2.pack(side = LEFT)

L3 = Label(top, text="Answer")
L3.pack( side = LEFT)
E3 = Entry(top, bd =5)
E3.pack(side = LEFT)

def helloCallBack(selection):
print("E1.get()"+E1.get())
print("E2.get()"+E2.get())
print("selection"+selection)
a = int(E1.get())
b = int(E2.get())
if selection in ('Addition'):
answer = a + b
print("answer"+str(answer))
E3.delete(0,'end')
E3.insert(0, answer)
elif selection in ('Substraction'): 
answer = a - b
print("answer"+str(answer))
E3.delete(0,'end')
E3.insert(0, answer)
elif selection in ('Division'): 
answer = a / b
print("answer"+str(answer))
E3.delete(0,'end')
E3.insert(0, answer) 
else: 
answer = a * b
print("answer"+str(answer))
E3.delete(0,'end')
E3.insert(0, answer) 
def sel():
selection = "You selected the option " + str(var.get())

label.config(text = selection) 
if var.get() == 1:
#B.destroy()
selection = 'Addition'
label.config(text = selection) 

elif var.get() == 2:
#B.destroy()
selection = 'Substraction'
label.config(text = selection)

elif var.get() == 3:
#B.destroy()
selection = 'Division'
label.config(text = selection)


else:
#B.destroy()
selection = 'Multiplication'
label.config(text = selection)

B = tkinter.Button(text =selection, command=lambda: helloCallBack(selection))
B.pack(side = LEFT) 

var = IntVar()
R1 = Radiobutton(top, text="Add", variable=var, value=1, command=sel)
R1.pack( anchor = W )

R2 = Radiobutton(top, text="Subtract", variable=var, value=2, command=sel)
R2.pack( anchor = W )

R3 = Radiobutton(top, text="Division", variable=var, value=3, command=sel)
R3.pack( anchor = W)

R4 = Radiobutton(top, text="Multiplications", variable=var, value=4, command=sel)
R4.pack( anchor = W)

label = Label(top)
label.pack()

top.mainloop()

Simple Addition of two value using Python and Tk GUI

Python provides various options for developing graphical user interfaces (GUIs). Tkinter, wxPython and JPython is wellknow.

Tkinter comes in build with Python package and it provide most of the GUI component like canvas, button, checkbox, radiobutton, button etc.

In below example we are using Eclipse IDE along with python plugin to develop gui

Code:-

from tkinter import *
import tkinter
from tkinter import messagebox
from lib2to3.fixer_util import Number

top = tkinter.Tk()

L1 = Label(top, text="First Number")
L1.pack( side = LEFT)
E1 = Entry(top, bd =5)

E1.pack(side = LEFT)

L2 = Label(top, text="Second Number")
L2.pack( side = LEFT)
E2 = Entry(top, bd =5)

E2.pack(side = LEFT)

def helloCallBack():
print("E1.get()"+E1.get())
print("E2.get()"+E2.get())
a = int(E1.get())
b = int(E2.get())
answer = a + b
#messagebox.showinfo( "Hello Python", "Hello World")
print("answer"+str(answer))
E3.insert(0, answer)

B = tkinter.Button(text ="Add", command = helloCallBack)

B.pack(side = LEFT)

L3 = Label(top, text="Answer")
L3.pack( side = LEFT)
E3 = Entry(top, bd =5)

E3.pack(side = LEFT)
top.mainloop()

How to resolve Python version 3.5 required, which was not found in the registry.

While trying to install cx_Oracle-5.2.1-12c.win-amd64-py3.5.exe (md5) for oracle from https://pypi.python.org/pypi/cx_Oracle/ using python we receive following below error

It indicate entry in your regedit is wrong.

Please follow below step to resolve the same.

Step 1:- Open your reg edit entry

run-->regedit

Step 2:- go insite HKEY_CURRENT_USER\SOFTWARE\Python\PythonCore\ and see what is the folder name in my case it was 3.5-32

Rename your folder from 3.5-32 to 3.5 and re run the cx_Oracle-5.2.1-12c.win-amd64-py3.5.exe (md5)

Simple CRUD operation on MYSQL using Python

To perform CRUD operation make sure you have installed MYSQLdb pacakge in your Python.

Like anyother programing language Python also need below step to perform CRUD on Database.

1-Import required API module for us as the data base is MYSQL we should import MYSQLdb in our Python file.

2- Acquiring connection with the database.

3- Performing SQL statments

4:- Closing the connection

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","siddhu","siddhu","testsiddhu" )

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

# execute SQL query 
cursor.execute("SELECT VERSION()")

# Fetch a single row 
data = cursor.fetchone()

print ("Database version : %s " % data)

# Drop table if it already exist 
cursor.execute("DROP TABLE IF EXISTS SIDDHU_TEST")

# Create table Example
sql = """CREATE TABLE SIDDHU_TEST (
FNAME CHAR(20) NOT NULL,
LNAME CHAR(20),
AGE INT, 
GENDER CHAR(1),
INCOME FLOAT )"""

cursor.execute(sql)

# Inserting in Table Example:- Prepare SQL query to INSERT a record into the database and accept the value dynamic. This is similar to prepare statement which we create.
sql = "INSERT INTO SIDDHU_TEST(FNAME, \
LNAME, AGE, GENDER, INCOME) \
VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
('siddhu', 'dhumale', 24, 'M', 1000)
try:
# Execute command
cursor.execute(sql)
# Commit changes
db.commit()
except:
# Rollback if needed
db.rollback()
# disconnect from server

# Select Query Example :- Selecting data from the table.
sql = "SELECT * FROM SIDDHU_TEST \
WHERE AGE > '%d'" % (4)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
gender = row[3]
income = row[4]
# Now print fetched result
print ("fname=%s,lname=%s,age=%d,gender=%s,income=%d" % (fname, lname, age, gender, income ))
print ("New fname=,lname=,age=,gender=,income=" % (fname, lname, age, gender, income ))
except:
print ("Value Fetch properly")


# Update Exmaple:- Update record 
sql = "UPDATE SIDDHU_TEST SET INCOME = 5000 WHERE GENDER = '%c'" % ('M')
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback() 

# Delete Operation :- Delete Opearations
sql = "DELETE FROM SIDDHU_TEST WHERE INCOME = '%d'" % (5000)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()


db.close()

How to install MYSQLDb for Python 3.6 and MYSQL 5.7.12

At present there are very few options for using Python 3* version with MYSQL

One of such option is to use

https://pypi.python.org/pypi/mysqlclient

- Django's, C based , most compatible and recommended library.

Go to above site and download required .whl file from the site as per your O/S need and run the same using following command

C:\Software>pip install mysqlclient-1.3.10-cp36-cp36m-win32.whl
Processing c:\software\mysqlclient-1.3.10-cp36-cp36m-win32.whl
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.3.10

Now you can use below import in your files

import MySQLdb

How to use PrimeNG in AngularJS2 project

Step 1:- Enter into your project folder
i.e we had project name as AddSubDivMultiProject
Open command prompt and enter into

C:\eclipse_workspace_angularjs2\AddSubDivMultiProject>

run following command
npm install primeng --save

This will install primeng folder inside your project node_modules folder

C:\eclipse_workspace_angularjs2\AddSubDivMultiProject>npm install primeng --save
add-sub-div-multi-project@0.0.0 C:\eclipse_workspace_angularjs2\AddSubDivMultiProject
`-- primeng@2.0.0

npm WARN optional SKIPPING OPTIONAL DEPENDENCY: fsevents@^1.0.0 (node_modules\chokidar\node_modules\fsevents):
npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for fsevents@1.0.17: wanted {"os":"darwin","arch":"any"} (current: {"os":"win32","arch":"x64"})
npm WARN @ngtools/webpack@1.2.4 requires a peer of webpack@2.2.0 but none was installed.
npm WARN extract-text-webpack-plugin@2.0.0-rc.2 requires a peer of webpack@^2.2.0 but none was installed.

PLEASE CONFIRM YOU GET primeng FOLDER INSIDE YOUR PROEJCT NODE_MODULES FOLDER

Step 2:- Lets say we want to use button of PrimeNg in our project i.e. http://www.primefaces.org/primeng/#/button

import the required module in our project using belwo line. This line need to be written in our app.modules.ts

import {ButtonModule} from 'primeng/primeng';

Also add the same in imports in @NgModule

Step 3:- Add following line in our *.HTML files

<! -- button pButton type="button" class="ui-button-info" label="Click ttt"><!--/button>

Step 4:- Add following line in our styles.css files please at the same level of index.html

/* You can add global styles to this file, and also import other style files */
@import '../node_modules/primeng/resources/primeng.min.css';
@import '../node_modules/primeng/resources/themes/omega/theme.css';