[SQL] How to merge multiple tables with NULL vlues into one using SQLite and Python?

External software regularly upload dumps into a SQLite database, creating a standardized table. To analyze this data, I need to combine all the generated tables into one. Since SQLite does not have dynamic SQL functionality, I decided to do a table merge using Python.

First I find all the dump tables using...

tables_cur.execute("SELECT name FROM sqlite_master WHERE name LIKE 'dump_table%';")


...then I go through the cursor in Python, extract data from the each table...

source_cur.execute("SELECT * FROM " + tables_row[0] + ";")


...and write them down in a common table...

target_cur.execute("INSERT INTO 'common_dump'" + source_row + ";")


This approach works as long as there are no NULL values in the dump fields. As soon as they appear, Python's SELECT turns NULL into None, and then INSERT tries to insert the None value into the INSERT. Which naturally causes an error in SQLite.

It is possible to convert row to srting and use the string function to change all None to NULL. But if the dump contains text fields containing "None" or "None" as a substring, then the data will be damaged during conversion.

It is possible to explicitly split the SELECT result into fields, and then explicitly assign them to INSERT. But then, with any change in the number of fields in the dump tables, you will also have to make corrections to the conversion function. And in several places at once.

Is there any elegant solution to the problem described?

Continue reading...