SQLite dump: dump specific table into file, dump only table structure

📖 Theory

The exam cell wants proof on paper

The exam cell trusts ResultDesk now, with one condition: "before you touch the marks table, give us a copy we can read and restore."

A copy of college.db is binary soup to them. What they want is the table as readable SQL: the exact CREATE TABLE and INSERT statements that would rebuild it from nothing.

The sqlite3 command shell does this with one dot-command: .dump. Learn it and any table becomes a text file you can read, mail, or replay.

📖 Theory

The recipe, not the cooked dish

Copying college.db is like photographing a cooked thali: exact, but you cannot see what went into it.

.dump writes down the recipe: every step (CREATE TABLE) and every ingredient (each INSERT), in plain text. Anyone with the recipe can cook the identical dish later, on any stove (any SQLite version), and can even read or edit a line before cooking. Backups you can read are backups you can trust.

📖 Theory

Dot-commands: the shell's own language

In the sqlite3 shell (sqlite3 college.db from the terminal), two languages coexist:

• SQL ends with a semicolon and talks to the database: SELECT * FROM marks;
• Dot-commands start with a dot, take no semicolon, and command the shell itself: .dump, .schema, .output, .read.

Dot-commands are NOT SQL: they will not run inside Python or any program, only in the shell. Exams check exactly this distinction.

🪜 Follow along

Dump one table into a file

1. Open the shell on the database Terminal: sqlite3 college.db (the prompt changes to sqlite>)
2. Point output at a file: .output marks_backup.sql From now on, whatever the shell prints goes into marks_backup.sql instead of the screen.
3. Dump the table: .dump marks Writes the CREATE TABLE marks statement followed by one INSERT per row.
4. Point output back at the screen: .output stdout Forget this and every later query result silently disappears into the file.
5. Verify: open marks_backup.sql in any text editor You should read CREATE TABLE marks(...); INSERT INTO marks VALUES(101,'DBMS',78); and so on.

📖 Theory

Structure only: .schema

Sometimes you want the skeleton without the data: sharing the design with a teammate, or recreating empty tables on a fresh machine.

• .schema marks prints just the CREATE TABLE marks (...) statement.
• .schema alone prints the structure of every table, index and trigger.

Rule of thumb: .dump = structure + data, .schema = structure only. Both are text; both can be replayed later with .read.

🧩 Quiz

What exactly does the file produced by .dump marks contain?

1. SQL text: the CREATE TABLE statement plus one INSERT statement per row
2. A compressed binary copy of the marks table
3. A CSV file with a header row
4. Only the data values, without any structure

💭 Think first

Spot the stuck shell

A student runs .output backup.sql then .dump marks, then types SELECT * FROM students; and sees NOTHING on screen. The database is fine. Before tapping: what happened, and which single command fixes it?

⚠️ Watch out

Where marks leak

Semicolons on dot-commands: .dump; confuses the shell. Dot-commands take no semicolon; SQL does.

Running dot-commands outside the shell: they are shell features, not SQL: cursor.execute('.dump') in Python fails.

Answering "copy the file" when asked for a dump: a file copy is a valid binary backup, but the question asks for the SQL-text mechanism; name .dump and what its output contains.

📖 Theory

Why professionals love text dumps

Text dumps diff cleanly in git (yesterday's dump vs today's shows exactly which rows changed), survive SQLite version upgrades, and can be edited before restore (fix a bad row in the text file). The next lesson scales this from one table to the whole database, and adds the restore command that turns your recipe back into a dish.

✅ Summary

Key takeaways

• Dot-commands (.dump, .schema, .output) are sqlite3 SHELL commands: dot first, no semicolon, not SQL.
• .dump marks writes the SQL that rebuilds the table: CREATE TABLE + one INSERT per row.
• .schema marks gives structure only; .schema alone covers every object.
• Redirect with .output file.sql before dumping; ALWAYS return with .output stdout.
• A dump is a logical, readable, portable backup; copying the .db file is a binary snapshot.
• Memory hook: the recipe, not the cooked dish.