I have a question about a problem that I'm sure is pretty often, but I still couldn't find any satisfying answers. Suppose I have a huge-size original SQLite database. Now, I want to create an empty database that is initialized with all the tables, columns, and primary/foreign keys but has no rows in the tables. Basically, I want to create an empty table using the original one as a template.
The "DELETE from {table_name}" query for every table in the initial database won't do it because the resulting empty database ends up having a very large size, just like the original one. As I understand it, this is because all the logs about the deleted rows are still being stored. Also, I don't know exactly what else is being stored in the original SQLite file, I believe it may store some other logs/garbage things that I don't need, so I would prefer just creating an empty one from scratch if it is possible. FWIW, I eventually need to do it inside a Python script with sqlite3 library.
Would anyone please provide me with an example of how this can be done? A set of SQLite queries or a python script that would do the job?
You can use Command Line Shell For SQLite
sqlite3 old.db .schema | sqlite3 new.db