2

I am using Sqlite to show who's address is school. This works and creates a table, however the next time the code is run it prints the table from last time as well so in this case where 3 people 'live' at school the first time it prints 3 people, the next time it is run it prints each name twice (total of 6) and so on. How would I change this code so that only one table is created and nothing is added to it each time I run the code. (In this case I want the result always to only have 3 lines).

import sqlite3 

conn = sqlite3.connect('dbtest.db') 
c = conn.cursor() 

param = "School" 


def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS myTestTable(name TEXT, age REAL, address TEXT)')

def data_entry():
    c.execute("INSERT INTO myTestTable VALUES('James', 45, 'School')") 
    c.execute("INSERT INTO myTestTable VALUES('Jack', 15, 'School')")
    c.execute("INSERT INTO myTestTable VALUES('Jeff', 14, 'Home')")
    c.execute("INSERT INTO myTestTable VALUES('Bob', 14, 'School')")
    conn.commit() 

def query1():
    c.execute("SELECT * FROM myTestTable WHERE address = ?",(param,))  
    while True:
        row=c.fetchone()
        if row == None:
            break
        print(row) 

    c.close()
    conn.close()

create_table()

data_entry()

query1()
1
  • 3
    don't call create_table and data_entry each time. Commented Aug 1, 2017 at 20:06

2 Answers 2

2

If you really don't want the data to be duplicated you can delete the file dbtest.db each time you call it, you could use "DROP TABLE if EXISTS myTestTable" to delete the table in dbtest.db each time, or you could add logic where you only call data_entry if the table is empty.

Depends on what the end goal of the project is really.

Sign up to request clarification or add additional context in comments.

Comments

0

You keep adding new rows even when they already exist. Use WHERE NOT EXISTS (SELECT THE SAME ENTRY) Your statements could have the form

INSERT INTO myTestTable(name,age,address) SELECT 'James', 45, 'School' WHERE NOT EXISTS (SELECT 1 FROM myTestTable WHERE name ='james' AND age =45 AND address ='school')

This searches if the entry is already present, if not it will insert it

EDIT: Modified code. Should have fixed issue.

2 Comments

Added this and the error below came up. Traceback (most recent call last): line 33, in <module> data_entry() line 14, in data_entry c.execute("INSERT INTO myTestTable VALUES('James', 45, 'School') WHERE NOT EXISTS (SELECT 1 FROM myTestTable WHERE name ='james' AND age =45 AND address ='school')") sqlite3.OperationalError: near "WHERE": syntax error
Edit should have fixed it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.