Hi
I wrote a small project to cover my previous articles.
Python SQLite Create Table
Python SQLite Insert Into Example
Python SQLite Delete Example
Python SQLite Update Example
Python SQLite Select And Criteria
This will be a simple contact book project. Console based. The preview looks like this:
Project Source Codes
db.py
import sqlite3
class ContactDB:
def __init__(self):
self.conn = sqlite3.connect("contact_list.db")
self.cursor = self.conn.cursor()
self.create_table()
def create_table(self):
self.cursor.execute("CREATE TABLE IF NOT EXISTS contact_table("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"name TEXT,"
"number TEXT,"
"city TEXT,"
"alias TEXT)")
def read_my_contacts(self):
self.cursor.execute("SELECT * FROM contact_table")
collect_data = self.cursor.fetchall()
print('{}\t{}'.format("ID", "Name", "Detail"))
for next_data in collect_data:
print('{}\t{} - {}'.format(next_data[0],next_data[1],(" Number: " + next_data[2] + " City: "+ next_data[3]+ " Alias: "+next_data[4])))
def save_or_update_my_contact(self, id, name, number, city, alias):
if id is None:
query = "INSERT INTO contact_table VALUES (?,?,?,?,?)"
params = (id, name, number, city, alias)
self.cursor.execute(query, params)
self.conn.commit()
else:
self.cursor.execute("SELECT * FROM contact_table WHERE id = ?",(id, ))
collect_data = self.cursor.fetchone()
name_ = ""
number_ = ""
city_ = ""
alias_ = ""
if name is None or name == "":
name_ = collect_data[1]
else:
name_ = name
if number is None or number == "":
number_ = collect_data[2]
else:
number_ = number
if city is None or city == "":
city_ = collect_data[3]
else:
city_ = city
if alias is None or alias == "":
alias_ = collect_data[4]
else:
alias_ = alias
query = "UPDATE contact_table SET name = ? , number = ? , city = ? , alias= ? WHERE id=?"
params = (name_, number_, city_, alias_, id)
self.cursor.execute(query, params)
self.conn.commit()
def delete_my_contact(self, id):
query = "DELETE from contact_table WHERE id = ?"
self.cursor.execute(query, (id, ))
self.conn.commit()
def get_record_count(self):
self.cursor.execute("SELECT COUNT() FROM contact_table")
count = self.cursor.fetchone()[0]
return count
main.py
import db
import os
contact_db = db.ContactDB()
operation = ''
runned_first = False
def write_bar_on_main_screen():
print("[L] List of contacts || [C] Close \n")
def write_bar_on_delete_and_add_and_edit():
os.system('cls')
print("[!C] Cancel || [E] Exit \n")
contact_db.read_my_contacts()
def write_bar_on_list_screen():
os.system('cls')
print("[A] Add New Contact || [E] Edit Contact || [D] Delete Contact || [M] Main Menu || [C] Close \n")
contact_db.read_my_contacts()
def write_stats_welcome_page():
print("###########################################################")
print("Welcome to Basic Contact List With Python SQLite")
print("Total Count is : {}".format(contact_db.get_record_count()))
print("www.langpy.com")
print("###########################################################")
def check_first_run():
global runned_first
if runned_first is False:
os.system('cls')
write_bar_on_main_screen()
write_stats_welcome_page()
runned_first = True
while operation.lower() != 'c':
check_first_run()
operation = input("")
if operation.lower() == 'l':
write_bar_on_list_screen()
elif operation.lower() == 'd':
write_bar_on_delete_and_add_and_edit()
id = input("id for delete records ?")
if id.lower() == "!c":
write_bar_on_list_screen()
continue
else:
contact_db.delete_my_contact(int(id))
write_bar_on_list_screen()
elif operation.lower() == 'e':
write_bar_on_delete_and_add_and_edit()
id = input("id for edit records ?")
if id.lower() == "!c":
write_bar_on_list_screen()
continue
name = input("Name ?")
if name.lower() == "!c":
write_bar_on_list_screen()
continue
number = input("Number ?")
if number.lower() == "!c":
write_bar_on_list_screen()
continue
city = input("City ?")
if city.lower() == "!c":
write_bar_on_list_screen()
continue
alias = input("Alias ?")
if alias.lower() == "!c":
write_bar_on_list_screen()
continue
contact_db.save_or_update_my_contact(id, name, number, city, alias)
write_bar_on_list_screen()
elif operation.lower() == 'a':
write_bar_on_delete_and_add_and_edit()
name = input("Name ?")
if name.lower() == "!c":
write_bar_on_list_screen()
continue
number = input("Number ?")
if number.lower() == "!c":
write_bar_on_list_screen()
continue
city = input("City ?")
if city.lower() == "!c":
write_bar_on_list_screen()
continue
alias = input("Alias ?")
if alias.lower() == "!c":
write_bar_on_list_screen()
continue
contact_db.save_or_update_my_contact(None, name, number, city, alias)
write_bar_on_list_screen()
elif operation.lower() == 'm':
os.system('cls')
write_bar_on_main_screen()
write_stats_welcome_page()
elif operation.lower() == 'c':
os.system('cls')
print("\nSee you <3")
else:
print("\nUnknown Operation :(\n")
