Tag: small

This is my second project since I started with Python, this time I learned a little bit of SQL that is so useful and beautifully simple.

Starting idea: Generate a program that will store information in DataBase(SQL).
Goal: Learn SQL basics and expand my knowledge in Python.
After project thoughts: The more basic it look the most difficult to find the BUG in the code. Spent 1 week finding a bug to finish the project and it was a variable repeated in two for loops.

Code purpose: During this time in the world I’ve seen a lot of paper used to do this job while at the same time they use a computer or tablet to charge you or book you in. What if we get rid of the paper and we only use digital?
This is supposed to be used by the employee not the user. Any confirmed case will be searched and this will allow you to send an email to all the people that were in the same place at the same time or +1 hour.

Really happy I hope yous like it, I know it needs Validation of User Input I tried to focus more on the PEP8 and mixture between Python and SQL.

Will appreciate any recommendations. Thank you.

Code:

import sqlite3
import datetime
import time
import smtplib
import imghdr
from email.message import EmailMessage

conn = sqlite3.connect('covid.db')
c = conn.cursor()

notify_group = list()

def enter_data():
    def create_table():
        c.execute('''CREATE TABLE IF NOT EXISTS
        covidTrack(
        name TEXT,
        email TEXT,
        ph_number INTEGER,
        datestamp TEXT,
        keyword TEXT)''')

    i_name = str(input('Please insert FULL NAME : n ...'))

    i_email = str(input('Please insert EMAIL : n ...'))

    i_number = int(input('Please insert PHONE NUMBER : n ...'))


    print('Your data has been saved for acelerated contact, thank you. n')
    time.sleep(1)


    def data_entry():
        date, keyword = dynamic_data_entry()
        c.execute('''INSERT INTO covidTrack
        VALUES(?, ?, ?, ?, ?)''', (i_name, i_email, i_number, date, keyword))
        conn.commit()


    def dynamic_data_entry():
        keyword = 'nameofvenue'
        date = str(datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S'))
        return date, keyword
        conn.commit()

    def read_from_db():
        c.execute('''SELECT * FROM covidTrack''')
        conn.commit()

    create_table()
    data_entry()
    read_from_db()
    menu()

def data_search():
    x = input('''Select desired search: n
    Search by FULL NAME. n
    Search by DATE AND TIME (EXTREMELY PRECISE NOT RECOMMENDED.). n
    Exit.
    ~
    ''')

    if x.lower() == 'full name':
         specify_name = input('Please insert full name. n ')
         select_query = c.execute('''SELECT * FROM covidTrack WHERE NAME ==(?) ''', (specify_name,))
         for row in c.fetchall():
             print('n')
             print('Name:', row(0))
             print('Email:', row(1))
             print('Phone Number:', row(2))
             print('Date and Time:', row(3))
             print('Venue:', row(4))
             print('n')
             c.execute('''SELECT *
             FROM covidTrack
             WHERE datestamp >= ?
             AND datestamp <= datetime(?, '+1 hours')  ''', (row(3),row(3)))
             print('Matching Results: n ')

             for row2 in c.fetchall():
                 print(row2)
                 notify_group.append(row2(1))
                 print('n')

             add_people = input('Would you like to notify this group?Y/N')

             if add_people.lower() == 'y':
                 SendMail()
                 print('All the people in this search has been advised.')
             if add_people.lower() == 'n':
              menu()


    if x.lower() == 'date and time':
        specify_datestamp = input('''Please insert full date as shown.
        n
        Please follow this format...
        n
        YYYY-MM-DD HH:MM:SS ''')

        c.execute('''SELECT * FROM covidTrack WHERE datestamp == ? ''', (specify_datestamp,))
        for row in c.fetchall():
             print('nName:', row(0))
             print('Email:', row(1))
             print('Phone Number:', row(2))
             print('Date and Time:', row(3))
             print('Venue:', row(4))
             print('n')
             d = row(3)

             c.execute('''SELECT *
             FROM covidTrack
             WHERE datestamp >= ?
             AND datestamp <= datetime(?, '+1 hours')  ''',(d,d))
             print('Matching Results: n')
             for row2 in c.fetchall():
                 print(row2(0:4))
             menu()

    if x.lower() == 'exit':
        exit()

def SendMail():
    msg = EmailMessage()
    msg('Subject') = 'covidTrack notification.'
    msg('From') = '#############'
    msg('To') = notify_group
    msg.set_content('It has been confirmed that you were a close contact in. Please contact us as soon as possible.')

    print(notify_group)

    with open('covidTrackNotification.png', 'rb') as f:
        file_data = f.read()
        file_type = imghdr.what(f.name)
        file_name = f.name
    msg.add_attachment(file_data, maintype='image', subtype=file_type, filename=file_name)

    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as smtp:
        smtp.login('###########', '#######')

        smtp.send_message(msg)


def menu():
    choose_funtion = input('''Please choose action: n
    A TO ENTER DATA. n
    B TO SEARCH DATA. n
    EXIT n ''')

    if choose_funtion.lower() == 'a':
        print('You choose enter data.')
        enter_data()
    if choose_funtion.lower() == 'b':
        print('You choose search data.')
        data_search()
    if choose_funtion.lower() == 'exit':
        exit()
menu()
c.close()
conn.close()