0

I'm trying to follow How do I dump the data of some SQLite3 tables? to dump a table's column in a database. The table I'm interested in is called certificates, and the column I am interested in is called certificate:

$ sqlite3 lbb.db 
SQLite version 3.8.3 2013-12-17 16:32:56
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from sqlite_master where type='table';
table|hardware|hardware|2|CREATE TABLE "hardware" (id INTEGER PRIMARY KEY, vendor TEXT, model TEXT, revision TEXT, description TEXT)
table|certificates|certificates|3|CREATE TABLE certificates(id INTEGER PRIMARY KEY, fingerprint TEXT, certificate TEXT, key TEXT, description TEXT)
table|firmware|firmware|4|CREATE TABLE firmware(id INTEGER PRIMARY KEY, device_id INTEGER, certificate_id INTEGER, vendor TEXT, description TEXT)

When I dump the table, I get the complete dump. When I try to dump the table's column, I get nearly nothing:

$ sqlite3 lbb.db ".dump 'certificates.certificate'"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
$

How do I dump the column data in a Sqlite database?

Community
  • 1
  • 1
jww
  • 97,681
  • 90
  • 411
  • 885

1 Answers1

1
.mode csv 
.header on 
.out file.dmp 
select certificate from certificates;
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Thanks bernie. I found that elsewhere, but I did not like the `"` surrounding the entry (csv mode) or `certificate =` (line mode) prepended to each entry because the output has to be run through `sed`. But I think its the best I can do. – jww Jan 18 '14 at 09:39
  • If I understand you correctly you could use string operations to get rid of the prepended text in the query itself. I just tested this and got a nicely formatted text file with column header and then the values below the header. – mechanical_meat Jan 18 '14 at 09:41