1

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?

Swistack
  • 195
  • 7
  • 1
    probably you can vacuum to get the filesize back to a normal/expected amount: https://www.sqlite.org/lang_vacuum.html – mechanical_meat Jan 22 '22 at 23:51
  • 3
    that can be done from Python as shown here: https://stackoverflow.com/a/4713249/42346 – mechanical_meat Jan 22 '22 at 23:52
  • @mechanical_meat Thank you! I tried to use it before but did that in the wrong way. Turns out that after DELETE-ing tables, you have to execute connection.commit() first before VACUUM-ing, otherwise it leads to OperationalError. After this change, that worked out for me perfectly. – Swistack Jan 23 '22 at 14:10

1 Answers1

4

You can use Command Line Shell For SQLite

sqlite3 old.db .schema | sqlite3 new.db
Jonas Tuemand Møller
  • 615
  • 3
  • 7