How to check if the data is empty in a database?

[chris0147]

Hi,
I'm fetching the data from the database and I would like to check the data above of the data in the same rows as the per channel to see if the cell is empty.

Here is for example table:

---------------------------
| channel   | program_id
---------------------------
| ITV       |
| ITV       | 3021
| ITV       | 3022
| ITV       | 3023


Here is the code:

def update_in_database(self):
     profilePath = xbmc.translatePath(os.path.join('special://userdata/addon_data/script.tvguide', 'source.db'))
     conn = database.connect(profilePath)
     cur = conn.cursor()
     program_id = ''.join(str(x) for x in self.program_id)
     cur.execute('SELECT channel, program_id FROM programs WHERE program_id=?;', (program_id,))
     data = cur.fetchone()


     if data:
       #check if the data above the 3021 in a database is empty

Here is the output for the data:

(u'103 ITV', u'3021')

I have got a string of program_id which it is 3021, so I want to check the string in a database to see if the data above of the 3021 is empty so I could do something.

How I can check in a database to see if the data above of the string is empty or not?

[Larz60+]

you can find all empty rows with '

SELECT channel FROM programs WHERE program_id is NULL;'

[chris0147]

you can find all empty rows with '

SELECT channel FROM programs WHERE program_id is NULL;'

Thank you but there is a problem.

When I try this:

cur.execute('SELECT channel FROM programs WHERE program_id is NULL;', (program_id,))
data = cur.fetchone()
print "data for update_in_database..................."
print data

It will give me an error: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

Do you know why I am getting an error?

Are you sure this is the correct code I could use to check if the row above of the program_id is empty not on below?

[ichabod801]

The error is in the first line. Since you changed program_id = ? to program_id is NULL, you don't need program_id in the second argument. That is, you took out where you were placing the thing (the ?), but you didn't take out what you were placing (program_id).

[chris0147]

The error is in the first line. Since you changed program_id = ? to program_id is NULL, you don't need program_id in the second argument. That is, you took out where you were placing the thing (the ?), but you didn't take out what you were placing (program_id).

You have got it wrong there, I need to use program_id as a second argument because I am getting the value from the button object in each time when I get the value so I need to use it.

By the way, I try to use this:

cur.execute('SELECT program_id FROM programs WHERE program_id is NULL and channel=?;', (channel,))

if data is None:
   print "Now you are working on it........................."
else:
   print "You won't do anything........................"

It did not look up for the NULL data above of the value in a database in the same row as the channel when I am trying to use the channel variable to find the channel name in the same rows as the value.

If you are not sure what I am trying to do, please see the screenshots:

http://i.imgur.com/nwJGog8.png

http://i.imgur.com/hoe62gt.png

I want to find the value 3001, then look up of the null data in the same rows as the channel BBC ONE before I would do something. If there is no null data above of the value 3021 in the same rows as the channel ITV then I won't do something. I hope you get my point?

[nilamo]

If there is no null data above of the value 3021 in the same rows as the channel ITV then I won't do something.

"above" doesn't make sense in this context.  The table doesn't have a primary key (...apparently), so the rows are unordered.  The fact that they happen to look like they're ordered in whatever order they were inserted is an implementation detail, and isn't guaranteed to stay that way (ie: if you rebuild an index, the ordering could change).

It sort of looks like you want to do

select @row_id=row_id from {tablename} where program_id is null and channel = ? order by row_id desc limit 1;

update set program_id = ? where row_id = @row_id

But without having a sequential id, any solution would look pretty hackish.

Maybe the best solution would be to alter the table and add a unique, auto-generated, primary key?

[Fran_3]

OT: What database is this? Is it a db written in Python and if so what?

Thanks.

[chris0147]

If there is no null data above of the value 3021 in the same rows as the channel ITV then I won't do something.

"above" doesn't make sense in this context.  The table doesn't have a primary key (...apparently), so the rows are unordered.  The fact that they happen to look like they're ordered in whatever order they were inserted is an implementation detail, and isn't guaranteed to stay that way (ie: if you rebuild an index, the ordering could change).

It sort of looks like you want to do

select @row_id=row_id from {tablename} where program_id is null and channel = ? order by row_id desc limit 1;

update set program_id = ? where row_id = @row_id

But without having a sequential id, any solution would look pretty hackish.

Maybe the best solution would be to alter the table and add a unique, auto-generated, primary key?    

I'm sorry but I'm trying my best to explain what I am trying to achieved. I wanted to check if the row before the 3001 is empty or not so I could do something.

However, I have found the solution. Here is what I use to check if the row before the 3001 is empty or not:

     
    conn = database.connect(profilePath)
    cur = conn.cursor()
    cur.execute("SELECT channel, start_date FROM programs WHERE program_id=?;",(program_id,))
    data = cur.fetchone()
    cur.execute("SELECT channel FROM programs WHERE channel=? and stop_date=? ",(data[0],data[1]))
    data = cur.fetchone()
    cur.execute('SELECT start_date, stop_date, title FROM programs where program_id=?', [program_id])
    data = cur.fetchone()

    #if data:
    if data is not None:
       print "you are working on this........."
       print data
    else:
       print "there is no empty row before that data so you don't need to do anything..."

[Fran_3]

Chris,
It has been some time since I've done database work and time has not permitted me to read this entire thread... but if you could answer a few questions...
1 - What kind of database are you trying to query? Is it written in Python ( or an off the shelf product?
2 - Is it a flat-file (single table) or a relational db (set of tables) linked together by primary keys or what?
3 - Are you querying a single table in the db or are attempting to query multiple tables?
4 - If a single table roughly how many records (rows)? 100, 1,000, 10,000 or more?
5 - Ditto for the record set you would expect to get back from a multi-table query
6 - Considering the resultant record set a virtual table are you attempting to...
.. search for the first row that contains the "target text" in column N...
.. then examine Column N in the previous row to see if it is either null or blank?
7 - If the rows are un-ordered how can you write a query to look for the "previous" row ? (Maybe that is your question)
8 - Have you considered...
.. importing one row at at time into a simple python list variable...
.. use a Python counter keep up with what row is the current row...
.. then examining the n'th element of that list for your target...
.. and if found...
.. read in the previous row and examine the same element to see if it is null or blank.
.. if not found import the next row of the table into the list variable and inc the counter and repeat... or whatever

# Basically ( I think) you would
.. use SQL to return the ROW and Python to put the results into a "list"
.. use Python to keep up with which ROW is the current row under investigation
.. use Python to examine the n'th element of the list
.. etc

I'm probably way off here as haven't done any db work in a long time and am very new to Python... but maybe answering my questions and reading my comments will spark an idea.

Actually I'm asking this as I am interested in using Python with a database I have in mind... also just Googled and see SQLite3... is that what you are working with?