SQLITE DATABASE CONNECTIVITY IN PYTHON

SQLite Database Connectivity :

• First you need to install SQLite browser.
• To install SQLite browser click here.
• As a example here I am using Users for table name and Id, Name, Age, City for field names.
• The below program shows CRUD operation in SQLite using python.
• C - Create (INSERT)
• R - Read (SELECT)
• U - Update (UPDATE)
• D - Delete (DELETE)

Program :

# SQLITE DATABASE CONNECTIVITY
import sqlite3

# connecting the database file to sqlite
connection = sqlite3.connect("Enter exact sqlite database file location")

# function to INSERT data
def insert_data(name, age, city):
    # INSERT query
    query = "INSERT INTO Users (Name, Age, City) VALUES (?, ?, ?)"
    # to execute the query
    connection.execute(query, (name, age, city))
    # to save the executed query
    connection.commit()
    print("Data inserted successfully")

# function to READ data
def select_data():
    # SELECT query
    query = "SELECT * FROM Users"
    result = connection.execute(query)  # returns tuple value

    # to print the data
    for i in result:
        print(i)

# function to UPDATE data
def update_data(name, age, city, id):
    # UPDATE query
    query = "UPDATE Users SET Name=?, Age=?, city=? WHERE  Id=?"
    connection.execute(query, (name, age, city, id))
    connection.commit()
    print("Data updated successfully")

# function to DELETE data
def delete_data(id):
    # DELETE query
    query = "DELETE FROM Users WHERE Id=?"
    id = id, # tuple value
    connection.execute(query, id)
    connection.commit()
    print("Data deleted successfully")

ch = 1
while ch == 1:

    print("""
    1. INSERT
    2. SELECT
    3. UPDATE
    4. DELETE
""")

    choice = int(input("Enter your choice : "))

    if choice == 1:  # INSERT
        print("You have chosen INSERT command")
        
        name = input("Enter name : ")
        age = int(input("Enter age : "))
        city = input("Enter city : ")
        
        insert_data(name, age, city)
        
    elif choice == 2:  # SELECT
        print("You have chosen SELECT command")
        
        select_data()
        
    elif choice == 3:  # UPDATE
        print("You have chosen UPDATE command")
        
        id = input("Enter ID : ")
        name = input("Enter name : ")
        age = int(input("Enter age : "))
        city = input("Enter city : ")

        update_data(name, age, city, id)

    elif choice == 4:  # DELETE
        print("You have chosen DELETE command")
        
        id = int(input("Enter ID : "))
        
        delete_data(id)
        
    else:
        print("Enter valid choice : ")

    ch = int(input("Enter 1 to continue : "))

print("Thank You")

Output : 

INSERT

SELECT

UPDATE

DELETE

Also Refer : MySQL database connectivity in python