How to check if a database is empty in SQLite with Python

[Lunar Evolution]

The title pretty much says it, I've looked around and seen a few things about the fetchone() method or fetchall() or even the rowcount method but I haven't been able to make it work. The fetchone() method always returns None and the the others behave in similar ways. Does anyone have any idea what Im doing wrong? My current code for the check is this: 

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

full = 0
exist = c.fetchone()

if exist is None:
    full = 0
else:
    full = 1

 

[DXMember]

con = sqlite3.connect('database.db')
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

 

in your original example you are missing a statement for the cursor and the execution

.fetchall() will return all rows from the select and .fetchone() will return one row at a time

[Lunar Evolution]

16 minutes ago, DXMember said:

con = sqlite3.connect('database.db')
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

 

in your original example you are missing a statement for the cursor and the execution

.fetchall() will return all rows from the select and .fetchone() will return one row at a time

It seems like that will just return whether or not there are any tables that exist, as opposed to whether or not the tables have any data inside them. Am I wrong?

[DXMember]

32 minutes ago, Lunar Evolution said:

It seems like that will just return whether or not there are any tables that exist, as opposed to whether or not the tables have any data inside them. Am I wrong?

You are not wrong, I assumed that the "if database is empty" would mean to look if there are any tables present.

If you do know the table of interest then you would be able to substitute the "SELECT" query to a more appropriate like "SELECT COUNT(*) FROM MY_TABLE;" to see how many rows it has or use the "SELECT * FROM MY_TABLE" and then a subsequent loop of .fetchone() to iterate trough all the rows of the table

[Lunar Evolution]

11 minutes ago, DXMember said:

You are not wrong, I assumed that the "if database is empty" would mean to look if there are any tables present.

If you do know the table of interest then you would be able to substitute the "SELECT" query to a more appropriate like "SELECT COUNT(*) FROM MY_TABLE;" to see how many rows it has or use the "SELECT * FROM MY_TABLE" and then a subsequent loop of .fetchone() to iterate trough all the rows of the table

Right, I meant to say if a table is empty, not the entire database. I already have CREATE TABLE IF NOT EXISTS statements at the very beginning so there will always be tables. I'll see what I can do with your second response though. I'm very new to SQLite but I'm figuring it out slowly

[DXMember]

2 minutes ago, Lunar Evolution said:

Right, I meant to say if a table is empty, not the entire database. I already have CREATE TABLE IF NOT EXISTS statements at the very beginning so there will always be tables. I'll see what I can do with your second response though. I'm very new to SQLite but I'm figuring it out slowly

http://www.w3schools.com/sql/

is a great place to sharpen up your SQL skills

 

you're mostly interested in general SQL queries as well as the principle of working with cursors in phyton