Developing applications in Python can involve use of database to store data. If you are looking forward to develop web applications using Python then you have to understand using DB as backend and Python to implement application logic. So, it is important to understand Python MySQL interaction. If you understand this, you can easily learn Flask framework for web application development in Python.
The concept of using Python MySQL is not different from creating Web Applications in PHP MySQL. It is similar to use any RDBMS with other frameworks or web application development language. The key concepts involved are-
- Connection- is a facility to make a program and DB communicate to send data to DB for storage and accessing the stored data.
- Query/Command- It is the task to be done on database like inserting a record, updating an existing record, deletion of a record or displaying stored data
- Cursor- can be understood as a pointer to represent data fetched from database into a form understandable by the program
Making a database application in Python MySQL
The first step is to install Python MySQL connector module in your Python installation. For that you can use the pip install command in command prompt.
python -m pip install mysql-connector
Note: This is for Windows OS. You can find the command for your OS here
After installing MySQL connector you are ready to develop this small CRUD application in Python. Let’s Begin!!!
Create the database in MySQL
- Run the WAMP/XAMPP/LAMP control panel
- Start Apache and MySQL services.
- Go to phpMyAdmin interface
- Create a new database and a table where you want to store the data.
We have created a database bikerentdb and a table customer with this structure.
CREATE TABLE `customer` ( `CustID` int(11) NOT NULL, `CustName` varchar(50) NOT NULL, `CustAddress` varchar(200) DEFAULT NULL, `CustPhone` varchar(20) DEFAULT NULL, `CustEmail` varchar(50) DEFAULT NULL, `CustCity` varchar(50) DEFAULT NULL )
You can copy this CREATE statement and execute it in the SQL tab in phpMyAdmin Interface
Import the mysql.connector Module
Write the import statement at the top of your code.
#import the module import mysql.connector
Make Python MySQL connection
We will do this in the main function of our program. Create a variable con and call method mysql.connector.connect.
Its Syntax of this method is
Con-variable= mysql.connector.connect( host=”Host-name”, user=”username”, passwd=”password”, database=”databasename” )
This is the main function code. In this we are using a while loop that displays a menu of all actions that you can do with the customer table. You can type one number from option after the prompt to do the activity. Make sure the main fucntion is at the end of all the functions that we are going to discuss next.
def main(): #make connection to database using localhost, root as username, no password so "" and database name con = mysql.connector.connect( host="localhost", user="root", passwd="", database="bikerentdb" ) #opne cursor cur = con.cursor() ch=0 #diaplay menu until user presses 5 while(ch<=4): #menu options print("1. INSERT") print("2. UPDATE") print("3. DELETE") print("4. DISPLAY") print("5. EXIT") # ask user to enter what he wants to do ch=int(input("Enter Your choice:")) #call relevant fucntions defined above if (ch==1): insert(con, cur) if (ch==2): update(con, cur) if (ch==3): delete(con, cur) if (ch==4): display(cur) #call main main()
Insert Data into Table
For this activity, you need an open connection and a cursor that is ready to accept an SQL command to be executed. An SQL DML statement can be executed by calling execute function on the opened cursor.
Syntax to open a cursor is
Cur-variable = Con-variable.cursor()
To execute a statement defined as a string with the cursor is
Cur-variable.execute(sql-string)
The complete code for Insertion using INSERT statement is defined as a function insert() to be called when required.
def insert(con,cur): #read values to be inserted cid=input("Enter customer ID:") cnm=input("Enter customer Name:") cad=input("Enter customer Address:") cph=input("Enter customer Phone:") cem=input("Enter customer Email:") cct=input("Enter customer City:") #create the Insert query sql = "INSERT INTO customer (CustID, CustName, CustAddress,CustPhone, CustEmail, CustCity) VALUES (%s, %s,%s, %s,%s, %s)" #create list of values typed from user to insert in customer table val = (cid,cnm,cad,cph,cem,cct) #Execute query with values cur.execute(sql, val) #commit for permanent storage in database con.commit() #display success message print(cur.rowcount, "Record inserted.")
Update Data in Table
An existing record can be updated with UPDATE SQL statement. In this application you will update a record for a given customer ID.
def update(con,cur): #read values to be updated cid=input("Enter customer ID:") cnm=input("Enter customer Name:") cad=input("Enter customer Address:") cph=input("Enter customer Phone:") cem=input("Enter customer Email:") cct=input("Enter customer City:") #create update query sql = "update customer set CustName='"+cnm+"', CustAddress='"+cad+"',CustPhone='"+cph+"', CustEmail='"+cem+"', CustCity='" +cct+"' where CustID="+cid #Execute Update query on opened cursor cur.execute(sql) #commit Changes to DB con.commit() #display success message print(cur.rowcount, "Record updated.")
Delete Record in Table
An existing record can be deleted with DELEET SQL statement. In this application you will delete a record for a given customer ID.
def delete(con,cur): #read the customer ID for which record to be deleted cid=input("Enter customer ID to delete:") #Create Delete Query sql = "delete FROM customer where CustID = '"+cid+"'" #execute delete query cur.execute(sql) #commit changes to DB con.commit() #display success message print(cur.rowcount, "Record deleted.")
Display all Records in Table
All the records saved in the table can be displayed by SELECT SQL statement. They are fetched with fetchAll() function and stored in variable that considers each row as a tuple of column values.
def display(cur): #Execute SELECT statement cur.execute("SELECT * FROM customer") #Fetch all records from table res = cur.fetchall() #print print("------------------------------------------------------------------------") print("CustID CustName CustAddress CustCity CustPhone CustEmail") print("------------------------------------------------------------------------") for x in res: print(str(x[0])+" "+x[1]+" "+x[2]+" "+x[5]+" "+x[3]+" "+x[4]) print("------------------------------------------------------------------------")
Complete Code
#import the module import mysql.connector def insert(con,cur): #read values to be inserted cid=input("Enter customer ID:") cnm=input("Enter customer Name:") cad=input("Enter customer Address:") cph=input("Enter customer Phone:") cem=input("Enter customer Email:") cct=input("Enter customer City:") #create the Insert query sql = "INSERT INTO customer (CustID, CustName, CustAddress,CustPhone, CustEmail, CustCity) VALUES (%s, %s,%s, %s,%s, %s)" #create list of values typed from user to insert in customer table val = (cid,cnm,cad,cph,cem,cct) #Execute query with values cur.execute(sql, val) #commit for permanent storage in database con.commit() #display success message print(cur.rowcount, "Record inserted.") def update(con,cur): #read values to be updated cid=input("Enter customer ID:") cnm=input("Enter customer Name:") cad=input("Enter customer Address:") cph=input("Enter customer Phone:") cem=input("Enter customer Email:") cct=input("Enter customer City:") #create update query sql = "update customer set CustName='"+cnm+"', CustAddress='"+cad+"',CustPhone='"+cph+"', CustEmail='"+cem+"', CustCity='" +cct+"' where CustID="+cid #Execute Update query on opened cursor cur.execute(sql) #commit Changes to DB con.commit() #display success message print(cur.rowcount, "Record updated.") def delete(con,cur): #read the customer ID for which record to be deleted cid=input("Enter customer ID to delete:") #Create Delete Query sql = "delete FROM customer where CustID = '"+cid+"'" #execute delete query cur.execute(sql) #commit changes to DB con.commit() #display success message print(cur.rowcount, "Record deleted.") def display(cur): #Execute SELECT statement cur.execute("SELECT * FROM customer") #Fetch all records from table res = cur.fetchall() #print print("------------------------------------------------------------------------") print("CustID CustName CustAddress CustCity CustPhone CustEmail") print("------------------------------------------------------------------------") for x in res: print(str(x[0])+" "+x[1]+" "+x[2]+" "+x[5]+" "+x[3]+" "+x[4]) print("------------------------------------------------------------------------") def main(): #make connection to database using localhost, root as username, no password so "" and database name con = mysql.connector.connect( host="localhost", user="root", passwd="", database="bikerentdb" ) #opne cursor cur = con.cursor() ch=0 #diaplay menu until user presses 5 while(ch<=4): #menu options print("1. INSERT") print("2. UPDATE") print("3. DELETE") print("4. DISPLAY") print("5. EXIT") # ask user to enter what he wants to do ch=int(input("Enter Your choice:")) #call relevant fucntions defined above if (ch==1): insert(con, cur) if (ch==2): update(con, cur) if (ch==3): delete(con, cur) if (ch==4): display(cur) #call main main()
Be First to Comment