How to print on the screen data from SQLite db?

Question

I have tried to follow this: How do I dump the data of some SQLite3 tables? but was not helpfull too much.

sqlite3 Database.pich1.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;

Also with

sqlite3 database.pich1.db .dump | grep '^INSERT INTO "tablename"'

I got nothing. This works better,although writes to file:

echo '.dump' | sqlite3 pich1.db > pich1.txt

After that I can cat the text file:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE pcod (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    address TEXT,
    salary FLOAT
);
INSERT INTO "pcod" VALUES(1,'Silivio',37,'Futoshka',250.0);
INSERT INTO "pcod" VALUES(2,'Jean',17,'Bulevar',800.0);
INSERT INTO "pcod" VALUES(3,'Phil',22,'Dunavska',770.0);
INSERT INTO "pcod" VALUES(4,'Ed',24,'Lipov Gaj',1300.0);
INSERT INTO "pcod" VALUES(5,'Oskar',57,'Partizanska',99.0);
INSERT INTO "pcod" VALUES(6,'John',37,'Veternik',250.0);
INSERT INTO "pcod" VALUES(7,'JIm',17,'Suboticks',800.0);
INSERT INTO "pcod" VALUES(8,'Doug',22,'Vrsacka',770.0);
INSERT INTO "pcod" VALUES(9,'Lev',24,'Beogradska',1300.0);
INSERT INTO "pcod" VALUES(10,'Chris',57,'Bihacka',99.0);
COMMIT;

Is there any other way to dump the contents on the screen?I am on Ubuntu 14.04.

Your question is a bit broad. What "screen" are you trying to "print" to? A command line shell? What do you want to display? Do you actually want a dump, or are you just trying to query a table? What languages and tools do you have at your disposal? — Bacon Bits, Jun 10 '15 at 12:52
Yes,actually I want a dump.I am on Ubuntu 14.04,can work with shell scripting andPython. — Richard Rublev, Jun 10 '15 at 13:00

score 1 · Accepted Answer · answered Jun 07 '17 at 06:05

1

Use this code

SELECT * FROM <FileName>

* is a special character which prints all the columns. you can also refer here : https://dba.stackexchange.com/questions/40656/how-to-properly-format-sqlite-shell-output

answered Jun 07 '17 at 06:05

Kundan

590
9
21

score 0 · Answer 2 · answered Oct 26 '20 at 08:07

I faced a similar problem some time ago. I think the point is, that older versions of the sqlite3 binary dont accept the .dump command as argument, and only read it when it comes on stdin.

I think should yust upgrade your sqlite and sqlite3 Database.pich1.db .dump should work like expected, and you should get all Create and Insert commands für all your tables.

p.s. dont forget, you ONLY get the data of the table. Meta informations like pragma_user_version ect. will NOT be shown in the dump.

How to print on the screen data from SQLite db?

2 Answers2