CODEWITHSUNDEEP
pythonsqlite
David
David

Reputation: 29

How to add partial row data in sqlite3, one column at a time in python

My python code produces a condition (such as Up, Down, Left, Right), the start time when that condition occurs, and at some point in the future an end time for that condition ending.

Presently, the code produces the following output in the database.

Current State

condition | start_time | end_time

Up | NULL | NULL

NULL | 44:03.1 | NULL

NULL | NULL | 44:05.2

Down | NULL | NULL

NULL | 44:05.2 | NULL

NULL | NULL | 44:20.4

Right | NULL | NULL

NULL | 44:20.4 | NULL

NULL | NULL |44:21.6

This is my current code for logging the first value, the condition direction:

c.execute('INSERT INTO conditionsAndTimesToPlot (condition_direction) VALUES (?)', (condition,))
conn.commit()

After some other stuff has to happen, the start_time is then recorded with:

c.execute('INSERT INTO conditionsAndTimesToPlot (start_time) VALUES (?)', (start_time_full,))
        conn.commit()

Then after some arbitrary time that condition ends and the end time is recorded with:

c.execute('INSERT INTO conditionsAndTimesToPlot (end_time) VALUES (?)', (end_time_full,))
                    conn.commit()

What I Want To Achieve

condition | start_time | end_time

Up | 44:03.1 | 44:05.2

Down | 44:05.2 | 44:20.4

Right | 44:20.4 | 44:21.6

What I Tried

I tried using UPDATE for the time arguments. I thought that INSERT for the condition direction would insert a new line and UPDATE for the start and end times would then naturally backfill the 2nd and 3rd columns but now I get this error in the terminal:

sqlite3.OperationalError: near "INTO": syntax error

Questions

Thanks for your help on my first ever stackoverflow question,

David

Upvotes: 1

Views: 973

Answers (1)

David
David

Reputation: 29

After fixing the UPDATE INTO error (no INTO after UPDATE) and researching the documentation further I found the following resources: https://www.sqlite.org/lang_update.html and https://www.tutorialspoint.com/sqlite/sqlite_update_query.htm.

My code now produces the table appearance I wanted to achieve by doing the following. The first part is unchanged, this creates each new row:

c.execute('INSERT INTO conditionsAndTimesToPlot (condition_direction) VALUES (?)', (condition,))
conn.commit()

This produces say Left | NULL | NULL. Then instead of INSERT for the next column what you can do is UPDATE and look for the next column where there is a NULL value:

c.execute("UPDATE conditionsAndTimesToPlot SET start_time = (?) WHERE start_time IS NULL", (start_time_full,))
conn.commit()

This produces say Left | 44:03.1 | NULL. Then the same for the end time, look for a NULL value:

c.execute("UPDATE conditionsAndTimesToPlot SET end_time = (?) WHERE end_time IS NULL", (end_time_full,))
conn.commit()

This produces say Left | 44:03.1 | 44:05.2. The problem is now fully resolved.

Upvotes: 1

Related Questions