Python + SQL Database help!


#1

Hello all,

I am currently attempting to add a bunch of financial information that I have compiled on a csv file to a database using python. The intention of this program is to add any NEW entries in my csv file to the database. However, what I am running into right now is that every time I run this program, it re adds the entire CSV to my database, and I end up with the same entries again. how can I fix this program so that If the entries already exist in the database, it will go to the next one. I want it to add only the NEW entries every time I run the program. So the program should be able to start adding entries from where it left off last time. PLEASE HELP!

import csv
import sqlite3
import re

#Create a connection to the database
conn= sqlite3.connect("SkilliDB.sqlite")   
cur= conn.cursor()

cur.executescript('''
 CREATE TABLE IF NOT EXISTS Date ( 
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    date TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS TransactionType (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS Account (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS Approver (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS Transactions (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    amount TEXT, paidto TEXT, depoby TEXT, description TEXT,
    paymethod TEXT, rbcref TEXT, transactiontype_id,
    date_id INTEGER, account_id INTEGER, approver_id INTEGER
);
''')

 #To pick up where the program last ended loading data
start= None
cur.execute("SELECT MAX(id) from Transactions")

try:
    maxrow = cur.fetchone()
    if maxrow is None:
        start=0
    else:
        start= maxrow[0]
except: 
    start=0

if start is None : start = 0

while True:

#Create tables in the database they do not already exist
    url= input("Enter file name to analyze: ")
    with open (url,newline="") as csvfile:
    #reader=csv.reader(csvfile)  #parse using reader function
        reader= csv.DictReader(csvfile)  #parse using a dictionary method
        entrycount = 0
        for row in reader:

            start=start+1
            cur.execute("SELECT id FROM Transactions WHERE id= ?",(start,))
            try:
                maxrow = cur.fetchone()
                if maxrow is not None :continue
            except:
                maxrow = None

            date= str(row.get("\ufeffDate"))
            paidto = str(row.get("Paid To"))
            depoby= str(row.get("Deposited By"))
            approver= str(row.get("Approver"))
            paymethod= str(row.get("Method"))
            rbcref= str(row.get("Reference"))
            description= str(row.get("Description"))
            account= str(row.get("Account"))
            transactiontype= str(row.get("Type"))

            amountstring= str(row.get("Amount"))[1:] #Returns the string without the "$"
            amount= float(amountstring.replace(",",""))    
            print(amount) 
            
            #This SQL Command adds the date into the date table, 
            #and selects the ID for foreign key assignment
            cur.execute(''' INSERT OR IGNORE INTO Date (date) VALUES (?)''',(date,))
            cur.execute('SELECT id FROM Date WHERE date= ?', (date,))
            date_id= cur.fetchone()[0]

            cur.execute(''' INSERT OR IGNORE INTO TransactionType (name) VALUES (?)''', (transactiontype,))
            cur.execute('SELECT id FROM TransactionType WHERE name= ?', (transactiontype,))
            transactiontype_id= cur.fetchone()[0]

            #Adds the account name into the account table and selects ID
            #for foreign key assignment
            cur.execute(''' INSERT OR IGNORE INTO Account (name) VALUES (?)''', (account,))
            cur.execute('SELECT id FROM Account WHERE name= ?', (account,))
            account_id= cur.fetchone()[0]

            #Adds the approver name into the Approver table and seelcts ID
            #for foreign key assignment
            cur.execute(''' INSERT OR IGNORE INTO Approver (name) VALUES (?)''', (approver,))
            cur.execute('SELECT id FROM Approver WHERE name= ?', (approver,))
            approver_id= cur.fetchone()[0]

            #SQL adds all records into the common Transactions table
            cur.execute('''INSERT OR IGNORE INTO Transactions(id,amount,paidto,depoby,description, 
            paymethod,rbcref,date_id, account_id, approver_id,transactiontype_id) 
            VALUES (?,?,?,?,?,?,?,?,?,?,?)''',(start,amount, paidto, depoby, description,paymethod,rbcref,
            date_id,account_id,approver_id,transactiontype_id,))
                    
            entrycount=entrycount+1  #Records how many transactions have been added to the database
        conn.commit()
        print(entrycount,"transactions entered")

        print("finished with all the rows")

            #print(",".join(row)