Python Forum
Deleting from a database...
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Deleting from a database...
#1
Hi Guys,

I am just starting with Python and am making a small "app" for my first project.

One of the problems I am having is that when I run this it does exactly what i need it to do BUT when I shut it down and then re-run it it adds the data to the file again rather than over writing it, which makes it tricky to then use the data for the next part of my cunning plan.

I realise this is probably pretty easy but all the examples I have seen are just over my head as of yet, can anyone shed some light on this for me please?




import sqlite3, csv

conn = sqlite3.connect("LE2.db")
c = conn.cursor()

sql = """
        CREATE TABLE IF NOT EXISTS TokenTable (
            
    ad1 TEXT,
    ad2 TEXT,
    ad3 TEXT


        )"""

c.execute(sql)

with open("LE.csv", "r") as file:    
    no_records = 0
    for row in file:

        c.execute("INSERT INTO TokenTable VALUES (?,?,?)", row.split(","))
        conn.commit()
        no_records += 1


Thanks,

Mort
Reply
#2
Of course it does - "insert" means to insert a row. Should You be really using "update"? You don't have any constraints I the table either, so it guess you don't care about having duplicate rows? It might help if you said what the data are and what your end goal is.
Reply
#3
(Aug-20-2020, 07:46 PM)ndc85430 Wrote: Of course it does - "insert" means to insert a row. Should You be really using "update"? You don't have any constraints I the table either, so it guess you don't care about having duplicate rows? It might help if you said what the data are and what your end goal is.


Hi ndc85430,


Thanks for the help! Smile

The whole idea is to take the information I am sent (in the form of roughly 50 .csv files) and output the bits I need to a GUI (probably in Tkinter). It will need to update from the csv file so that when it appears in the interface it only shows the new data (currently being handled in the spreadsheet) that I need. I have to do this every 24 hours or so (currently manually) and it is a very boring task that I hope to automate.

The above code is literally my first attempt to learn how python works that isn't just a "copy someone else" type deal. It is also a pared down version of the first step, second step being to place it in the GUI and actually figure out how to lay it out so it is readable! I guess I will also try to write all the equations, currently handled by the speadsheets, into the program soon.

Next last step will be to import this all from a couple of seperate APIs but that is a bit more complex as this is my first go at coding anything at all and 3 days or youtube "training" is not going to get me there quite yet but everyone has to start somewhere, right?


Cheers,

Mort
Reply
#4
To get you through the problem at hand, remove where you say IF NOT EXISTS. You will then get an empty table.
Reply
#5
(Aug-20-2020, 11:12 PM)Mort101 Wrote: The above code is literally my first attempt to learn how python works that isn't just a "copy someone else" type deal.

Well done Mort! You are doing fine.

(Aug-22-2020, 06:52 PM)jefsummers Wrote: To get you through the problem at hand, remove where you say IF NOT EXISTS. You will then get an empty table.

This is in the category: "Want to remove a stain from your shirt? Use scissors!". Most often people using a database want to keep their data. Smile

The way to solve this is for every row to be inserted, do a lookup to check if the row is already in the table. If it is: don't insert.
Professionally designed databases and the files providing data will have a primary key. This key will be indexed so the lookup can be very quick. But if you don't have a primary key then you need to check on all columns. Like this:

...
countrecs = "SELECT COUNT(*) FROM TokenTable WHERE ad1 = ? AND ad2 = ? AND ad3 = ?"
insertrecs = "INSERT INTO TokenTable VALUES (?,?,?)"

with open("LE.csv", "r") as file:
    no_records = 0
    for rawrow in file:
        row = rawrow.strip().split(",")
        # number already in database should be 0 or 1.
        nr_already = c.execute(countrecs, row).fetchone()[0]
        # if no rows already in database, insert the row
        if nr_already == 0:
            c.execute(insertrecs, row)
            conn.commit()
            no_records += 1
            print(f"Inserted: {row}")
        else:
            print(f"Already in database: {row}")

c.close()
conn.close()
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020