Python Sqlite, Insert into table and make all non specified values Null, then update Null values one by one

Question

I am currently creating dynamic sqlite tables in Python. The table in question is generated as follows:

year_list = range(start_year, end_year+1)

dbs_to_create = ["Revenue", "Sale", "Rev_Maintenance",...]

for db_name in dbs_to_create:
    concat_string = ""
    for year_used in year_list:
        concat_string += ", '{}' REAL".format(str(year_used))
    
    cursor.execute("CREATE TABLE IF NOT EXISTS {} ( Project_name TEXT PRIMARY KEY{});".format(db_name, concat_string))

Having done so I now Insert a project into the table "Revenue" via the following statement after which it creates a Row with the column Project_name having the name Project 1. However all the other 29 columns (a year range from 2022-2051) now have the value of their column header. The row will thus look like "Project 1, 2022, 2023, ..., 2051). However I want all the other columns except Project_name to hold the value Null until I update it again. This brings me to problem 2, the below code also holds the syntax for updating the column values to their respective revenues for each year, however despite the syntax looking like "UPDATE Revenue SET '2038' = 50000 WHERE Project_name='Project 1'" when passed to the cursor.execute, it still doesnt update the Revenue values.

    cursor.execute("SELECT * FROM Revenue WHERE Project_name = ?", (project_number,))
                        if cursor.fetchone() is None:
                            cursor.execute("INSERT INTO Revenue (Project_name) VALUES (?);", (project_number,))
                            conn.commit()
                        else:
                            cursor.execute("UPDATE Revenue SET '{}' = {} WHERE Project_name='{}'".format(curr_year, revenue_cell_val, project_number)) 
                            conn.commit()

score 0 · Answer 1 · answered Feb 28 '23 at 10:11

Modify your insert statement to:

cursor.execute("INSERT INTO Revenue (Project_name, {}) VALUES (?,{})".format(", ".join(year_list), ",?"*len(year_list)), ([project_number] + [None]*len(year_list)))

and for the UPDATE:

cursor.execute("UPDATE Revenue SET '2038' = ? WHERE Project_name=?", (50000, project_number))

let me know if that helps

score 0 · Accepted Answer · answered Feb 28 '23 at 14:28

The code provided the question is actually correct, it turns out that the SQLite Viewer extension for visual studio code for some reason doesnt update the values. However opening the database with a different SQL browser program such as HeidiSQL indeed does show the right values at the right columns.

Python Sqlite, Insert into table and make all non specified values Null, then update Null values one by one

2 Answers2