MakeDBfromCSV.py

import sqlite3
import csv
from datetime import datetime

csv_file = 'Canteens.csv'

"""
open SQLite database
"""
global conn
conn = sqlite3.connect('diners.db')
if(conn):
    print ("Opened database successfully")

def splittimes(timestring):
    #Split the timestring
    time_open, time_closed = timestring.split('-')
    # Strip trailing whitespace and convert to . to :
    time_open = time_open.strip().replace('.', ':')
    time_closed = time_closed.strip().replace('.', ':')

    #Format to HH:MM
    time_open_formatted = datetime.strptime(time_open, "%H:%M").strftime("%H:%M") # convert to datetime object and back to a string
    time_closed_formatted = datetime.strptime(time_closed, "%H:%M").strftime("%H:%M")

    #Error check
    if time_open_formatted and time_closed_formatted:
        return time_open_formatted, time_closed_formatted

    else:
        return None, None
        
def create_table():
    #create a table 
    conn.execute('''CREATE TABLE CANTEEN(
        ID INTEGER PRIMARY KEY,
        NAME TEXT NOT NULL,
        Location TEXT NOT NULL,
        time_open TIME NOT NULL,
        time_closed TIME NOT NULL);
    ''')
    print ("Table created successfully")


def createRecords(namevar,locaitonvar,timeopenvar,timeclosedvar):
    """
    create some records in the CANTEEN table
    """
    conn.execute("INSERT INTO CANTEEN (Name, Location, time_open, time_closed) \
                 VALUES (?, ?, ?, ?)", (namevar, locaitonvar, timeopenvar, timeclosedvar));
   
    conn.commit()
    print ("Records created successfully")

    

#opendb()
create_table()

with open(csv_file, encoding='iso-8859-13', mode ='r')as csvfile:
    reader = csv.DictReader(csvfile, delimiter=';')
    # for row in reader:
    #     print(row)
    for row in reader:
        name = row['Name']
        location = row['Location']
        times = row['Open']
        opent, closet = splittimes(times)
        if(name and location and opent and closet):
            createRecords(name,location,opent,closet) # send data to db