Python Forum

Hello,

I'm trying to add default values to my SQLite table when it first gets created using and INSERT statement.
Right now, every time I run the program the default values keep getting inserted into the table. So now I have 10 rows of the same values.

Is there a way to program this so the values only get inserted when the table is first created (Table does not exist), and if the table does exist, don't insert those values because they're already there.

Thanks in advance

This is what I have:

#----------------------------------------------------------------------------------------------
#                          Create Category Database
#----------------------------------------------------------------------------------------------
def createCategoryDatabase():
        #Create a database (users.db)
        connection = sqlite3.connect("categories.db")
        cursor = connection.cursor()
 
        table = """CREATE TABLE IF NOT EXISTS Categories
                (ID INTEGER PRIMARY KEY  AUTOINCREMENT,
                Category            TEXT    NOT NULL,
                Low_Quantity_Value  INT     NOT NULL);"""
 
        #Execute the creation of the table
        cursor.execute(table)
        #print("The database has been created")
        #Commit the changes
        connection.commit()
        
        #Add a default category
        cursor.execute('''
        insert into Categories (Category, Low_Quantity_Value)
        values ('N/A','0')
        ''')
        connection.commit()
        
        #Close the connection
        connection.close() 
#----------------------------------------------------------------------------------------------

(Jun-26-2022, 10:44 PM)Extra Wrote: [ -> ]Is there a way to program this so the values only get inserted when the table is first created (Table does not exist), and if the table does exist, don't insert those values because they're already there.

Have you tried 'SELECT' on said data values, as a test, before running the 'INSERT' statement? The idea being that if 'SELECT' returns 'True', then the data is there and as such you can skip over the 'INSERT'. If it returns 'False' (or possibly, 'Null'), then have the 'INSERT' do its thing.

Thanks. I will try that.

If you want to create a table with initial values you should query if the table exists. If the table does not exist you can then create the table and add whatever initial data you want. If the table does exist you do nothing.

tables = cursor.execute(
  """SELECT tableName FROM sqlite_master WHERE type='table'
  AND tableName='Categories'; """).fetchall()
if len(tables) == 0:
    # Create and initialize table.

I never understood why there is a need for IF NOT EXISTS.

Create a table you need for a purpose. Check first if a table with that name exists.

Once you create a table in your db, unless you will only import data with fixed ids (I do that often), you need to set the initial value for the primary key:

Quote:ALTER TABLE my_table AUTO_INCREMENT = 1;

for example.

The first INSERT query will then take the id value 1. Further INSERT queries will increment id from 1.

Why would you run the same CREATE TABLE again? Not a realistic scenario.