How to initialize a database just once on Python using SQLite 3?

Question

I'm trying to make a python app where the user can add a row to a table and visualize all the rows. My problem is that it seems that every time I run the program, the database is created again, with no values. I say this because there is an autoincrement value that is always the same. When I write the program again on the cmd and insert the values by hand it does show me more than one value.

Here's the code:

import sqlite3

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

c.execute("CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT, bash TEXT)")

action = int(input("Insert an action: (1: Add row | 2: Close)"))
if(action == 1):
    url = input("URL: ")
    bash = input("BASH: ")
    values = (url,bash)
    c.execute("INSERT INTO images VALUES(null,?,?)",values)
else:
    conn.close()
    quit()

for row in c.execute("SELECT * FROM images"):
    print(row)

conn.close()

Probably unrelated, but AUTOINCREMENT in sqlite doesn't really do what most people think it does. It's not necessary unless you want to be absolutely sure that after a row is deleted, its ID will never be reused. — glibdud
– glibdud, Commented Jan 19, 2018 at 21:37

Talat C. · Accepted Answer · 2022-12-09 17:24:46Z

4

You need to commit the INSERT transaction before closing, or it will not be persisted:

import sqlite3

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

c.execute("CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY AUTOINCREMENT, url TEXT, bash TEXT)")

action = int(input("Insert an action: (1: Add row | 2: Close)"))
if(action == 1):
    url = input("URL: ")
    bash = input("BASH: ")
    values = (url,bash)
    c.execute("INSERT INTO images VALUES(null,?,?)",values)
    conn.commit()
else:
    conn.close()
    quit()

for row in c.execute("SELECT * FROM images"):
    print(row)

conn.close()

edited Dec 9, 2022 at 17:24

Talat C.

196 bronze badges

answered Jan 19, 2018 at 21:33

match

11.2k3 gold badges25 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

How to initialize a database just once on Python using SQLite 3?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related