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

Question

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

Is there something obvious that I am missing here to make this happen?
Should I create 3 tables (one for each value [condition, start_time, end_time] and then write code that combines the three tables by row into a single table for every cycle through my loop?

Thanks for your help on my first ever stackoverflow question,

David

There is no INTO keyword in Update. Please see the document for Update first. — biozid bostami
– biozid bostami, Commented Jan 23, 2018 at 0:19

David · Accepted Answer · 2018-01-23 00:51:52Z

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.

Collectives™ on Stack Overflow

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

Current State

condition | start_time | end_time

What I Want To Achieve

condition | start_time | end_time

What I Tried

Questions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Current State

condition | start_time | end_time

What I Want To Achieve

condition | start_time | end_time

What I Tried

Questions

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related