Python Sqlite3 - Data get overwritten
Question: When ever i try to insert data to my database, it's like it simply just
overwrite whole file or not saving it correctly. My thoughts about this script
was that if i ran it multiple times it would output this:

    
    
    (1, 126)
    (2, 127)
    (3, 126)
    (4, 127)
    

I also now that if i doesn't create table within the script, it gives me an
error that the table doesn't exist, even though the table was created, last
time i ran the script.

    
    
    import sqlite3
    
    # Connecting to the database file
    conn = sqlite3.connect("MyDB.db")
    c = conn.cursor()
    
    # Check if table exists
    c.execute("DROP TABLE IF EXISTS t1")
    
    
    # Create table
    c.execute('''CREATE TABLE t1(
                 a INTEGER PRIMARY KEY,
                 b INTEGER);''')
    
    # Insert value
    c.execute('''INSERT INTO t1 VALUES(NULL,126);''')
    c.execute('''INSERT INTO t1 VALUES(NULL,127);''')
    
    # Save changes
    conn.commit()
    
    for row in c.execute('SELECT * FROM t1'):
            print(row)
    
    # Output:
    # (1, 126)
    # (2, 127)
    conn.close()
    

I really want this to work, since i'm working on a school project where my
product is to make a functional store where you can add, remove products and
sort them by date they were added.


Answer: Two issues:

  1. You are dropping the table if exists, this will always deletes the table:
    
        c.execute("DROP TABLE IF EXISTS t1")
    

  2. You are re-creating the table after dropping:
    
        c.execute('''CREATE TABLE t1(
         a INTEGER PRIMARY KEY,
         b INTEGER);''')
    

You could comment above codes and re-run your script to get the output you
want. But, in the final version ensure to check db and table exists before
writing to it.

