Python project 1: Login

1. Requirement

 chyper-code.xlsx

2. Implementation

2.1 custom encoding

• read from excel
• rename columns
• uniform type
• data in excel => dict
• how to elegantly concatenate string?

import pandas as pd

states_df = pd.read_excel('chyper-code.xlsx',usecols='A:B')
states_df.rename(columns={'USER TYPE':'USER_TYPE','SYSTEM CONVERT':'SYSTEM_CONVERT'},
inplace=True)
states_df['USER_TYPE'] = states_df['USER_TYPE'].astype(str)
code_dict = dict(zip(states_df.USER_TYPE, states_df.SYSTEM_CONVERT))

def custom_encoding(_str):
    values = [str(code_dict[element]) for element in _str.upper()]
    return ''.join(values)

2.2 Sqlite3

• insert/update/delete/  must commit, otherwise database will not be changed
• save df to .csv file
• sqlite3 operations 

import Encoding as en
from contextlib import closing
import sqlite3

connection = sqlite3.connect("USER.DB")
cursor = connection.cursor()

def initialize():
            cursor.execute("DROP TABLE IF EXISTS TB_USER")
            cursor.execute("CREATE TABLE TB_USER (USER_ID INTEGER PRIMARY KEY AUTOINCREMENT, LOGIN TEXT, CRYPTOGRAPHIC_PASSWORD TEXT,ACCESS_COUNT INTEGER)")

def ifNew(login):
            rowsCursor = cursor.execute("SELECT count(*) FROM TB_USER WHERE LOGIN = ?",(login,))
            numberOfRows = rowsCursor.fetchone()[0]
            if numberOfRows > 0:
                return False
            else:
                return True

def insert(usr):
            cursor.execute(
                "INSERT INTO TB_USER(LOGIN,CRYPTOGRAPHIC_PASSWORD,ACCESS_COUNT) VALUES (?, ?, 0)",
                (usr.login,usr.encry_pwd)
            )
            connection.commit()
            rows = cursor.execute("SELECT * from TB_USER").fetchall()
            print(rows)

def update_and_select(login):
    with closing(sqlite3.connect("USER.DB")) as connection:
        with closing(connection.cursor()) as cursor:
            cursor.execute(
                "UPDATE TB_USER SET ACCESS_COUNT = ACCESS_COUNT + 1 WHERE LOGIN = ?",
                (login,)
            )
            rows = cursor.execute("SELECT LOGIN,ACCESS_COUNT FROM TB_USER WHERE LOGIN = ?",(login,)).fetchall()
            connection.commit()
            print(rows)
            import pandas as pd
            db_df = pd.read_sql_query("SELECT * FROM TB_USER", connection)
            db_df.to_csv('usersdb-backup.csv', index=False)
            print("*** successfully generate .csv file ***")

class Usr:
  def __init__(self, login, encry_pwd):
    self.login = login
    self.encry_pwd = encry_pwd

def createUser():
    while 1 == 1:
        email = input("Please enter your email: ")
        ifNewFlag = ifNew(email)
        #isNewUser = input("Are you a new user?(Y/N)")
        if ifNewFlag:
            pwd = input("Please enter your password: ")
            encry_pwd = en.custom_encoding(pwd)
            usr = Usr(email,encry_pwd)
            insert(usr)
            if (connection):
                connection.close()
            break
        else:
            update_and_select(email)
            continue


if __name__ == '__main__':
    # initialize() function can only run once, to generate TB_USER table
    #initialize() 
    createUser()