Python Language Tutorial: Database Handling - Part Eight

Introduction

 
Today, I will take on a new part of Python for my tutorial series. In this part, you will learn about Database Handling in Python.
 
I already told you about the following:
Python language supports many database servers as:
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • Oracle
  • SQLite
Note
 
We can work with any database without changing code because mostly the Database works with the same method for handling databases that give flexibility to change the database any time without any code change.
 
Python Also Support various databases .you can see support database to python.
  1. Now today I will tell database handling with the SQLite database.
     
  2. SQLite database is light weighted and it can easily embed with embedded applications.
     
  3. Now we are going to implement a database using python.
     
  4. SQLite library are already in-built with python.
     
  5. Now open Command Prompt and open python terminal for handling database operations.
     
  6. Now we create a database using the below code when we write in python terminal.
    1. import sqlite3  
    2. conn = sqlite3.connect('article.db')  
    3. c=conn.cursor()  
    4. conn.close  
    The above code creates a database whose name is article.db in that directory in which python terminal opens.
     
    db
     
    Cursor() method is used to do any operations on the database.
     
  7. Now we create a table in article.db database as below code:
    1. import sqlite3  
    2. conn = sqlite3.connect('article.db')  
    3. c=conn.cursor()  
    4. c.execute("create table example(Software VARCHAR, Version Real,Price Real )")  
    In the above code, we execute create command so you can now understand the use of a cursor in python. We create an example table with three attributes.
     
    code
     
  8. Now we inserting the data into an example table as below.
    1. c.execute("Insert into example Values('Python',3.4,'100')")  
    2. c.execute("Insert into example Values('Adobe',10.2,'1000')")  
    3. c.execute("Insert into example Values('Office',16,'1000')"
    code
     
    In last we commit the connection to make proper connect between python to a database.
     
  9. Now we read the data from databse using code:
    1. sql = "select * from example"  
    2. for row in c.execute(sql):  
    3.     print("Software: "+row[0]  
    4.     print("Version: "+str(row[1])  
    5.     print("Price: "+str(row[2]))  
    Output:Software: Python
     
    Version: 3.4
     
    Price: 100.0
     
    Software: Adobe
     
    Version: 10.2
     
    Price: 1000.0
     
    Software: Office
     
    Version: 16.0
     
    Price: 1000.0
     
    code
     
  10. Now we Insert data dynamically in a database using the make method in python:
    1. def dynamic_data():   
    2.     soft = input("Write Software Name : ")  
    3.     version = input("Write Versio : ")  
    4.     Price= input("Write Price")  
    5.    
    6. c.execute("insert into example(Software,Version,Price) values(?,?,?)" ,(soft,version,Price))  
    7. conn.commit()  
    8. dynamic_data() 
     Output
     
    • Write Software Name: Google Chrome
     
    • Write Version: 46.343
     
    • Write Price100
     
    code
     
  11. Now we take another database handling with update the data in the database,
    1. sql="update example set Version = 3.5 where Software = 'Python'"  
    2. c.execute(sql)  
    3. sql = "select * from example"  
    4.    
    5. for row in c.execute(sql):  
    6.    print(row) 
    Output
     
    ('Python', 3.5, 100.0)
     
    ('Adobe', 10.2, 1000.0)
     
    ('Office', 16.0, 1000.0)
     
    ('Google Chrome', 46.343, 100.0)
     
    code
     
  12. Another Example of perform detele operation on database:
    1. sql="delete from example where Software = 'Python'"  
    2. c.execute(sql)  
    3. sql = "select * from example"  
    4. for row in c.execute(sql):  
    5.     print(row) 
    Output:
     
    ('Adobe', 10.2, 1000.0)
     
    ('Office', 16.0, 1000.0)
     
    ('Google Chrome', 46.343, 100.0)
     
    code
     
    It deletes the python row entry.
So in this article, you will have learned Python handling with the SQLite database.
 
We used methods that are used in SQLite that also work the same as other databases like MySql, MSSQL.