Creating an empty database in SQLite from a template SQLite file

Question

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?

probably you can vacuum to get the filesize back to a normal/expected amount: sqlite.org/lang_vacuum.html — mechanical_meat
– mechanical_meat, Commented Jan 22, 2022 at 23:51
that can be done from Python as shown here: stackoverflow.com/a/4713249/42346 — mechanical_meat
– mechanical_meat, Commented Jan 22, 2022 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
– Swistack, Commented Jan 23, 2022 at 14:10

Jonas Tuemand Møller · Accepted Answer · 2022-01-23 15:25:38Z

4

You can use Command Line Shell For SQLite

sqlite3 old.db .schema | sqlite3 new.db

edited Jan 23, 2022 at 15:25

answered Jan 23, 2022 at 0:08

Jonas Tuemand Møller

6453 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating an empty database in SQLite from a template SQLite file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related