96 lines
2.5 KiB
Python
96 lines
2.5 KiB
Python
import sqlite3
|
|
from sqlite3 import Error
|
|
from datetime import datetime
|
|
|
|
database = 'authorized_cards.db'
|
|
|
|
sql_create_user_table = """ CREATE TABLE IF NOT EXISTS users (
|
|
id integer PRIMARY KEY,
|
|
name text NOT NULL,
|
|
card_id integer NOT NULL,
|
|
counter integer NOT NULL,
|
|
first_seen date NOT NULL,
|
|
last_seen date NOT NULL
|
|
); """
|
|
|
|
|
|
def create_connection(db_file):
|
|
""" create a database connection to the SQLite database
|
|
specified by the db_file
|
|
:param db_file: database file
|
|
:return: Connection object or None
|
|
"""
|
|
conn = None
|
|
try:
|
|
conn = sqlite3.connect(db_file)
|
|
except Error as e:
|
|
print(e)
|
|
|
|
return conn
|
|
|
|
|
|
def create_table(conn, create_table_sql):
|
|
""" create a table from the create_table_sql statement
|
|
:param conn: Connection object
|
|
:param create_table_sql: a CREATE TABLE statement
|
|
:return:
|
|
"""
|
|
try:
|
|
c = conn.cursor()
|
|
c.execute(create_table_sql)
|
|
except Error as e:
|
|
print(e)
|
|
|
|
|
|
def create_user(conn, user, card_id, counter, first_seen, last_seen):
|
|
"""
|
|
Create a new task
|
|
:param conn:
|
|
:return:
|
|
"""
|
|
|
|
sql = ''' INSERT INTO users(name, card_id, counter, first_seen, last_seen)
|
|
VALUES(?,?,?,?,?) '''
|
|
cur = conn.cursor()
|
|
cur.execute(sql, (user, card_id, counter, first_seen, last_seen))
|
|
return cur.lastrowid
|
|
|
|
|
|
def select_all_users(conn):
|
|
"""
|
|
Query all rows in the tasks table
|
|
:param conn: the Connection object
|
|
:return:
|
|
"""
|
|
cur = conn.cursor()
|
|
cur.execute("SELECT * FROM users")
|
|
|
|
rows = cur.fetchall()
|
|
|
|
return rows
|
|
|
|
def register_access(conn, id):
|
|
"""
|
|
increment the authentication counter in the database for the given id and save time of the last access
|
|
"""
|
|
sql = ''' UPDATE users
|
|
SET counter = counter + 1 ,
|
|
last_seen = ?
|
|
WHERE id = ?'''
|
|
cur = conn.cursor()
|
|
cur.execute(sql, (str(datetime.utcnow()), id,))
|
|
conn.commit()
|
|
|
|
|
|
def setup_db():
|
|
# create a database connection
|
|
conn = create_connection(database)
|
|
|
|
# create tables
|
|
if conn is not None:
|
|
# create projects table
|
|
create_table(conn, sql_create_user_table)
|
|
else:
|
|
print("Error! cannot create the database connection.")
|
|
|
|
return conn |