Running a Sqlite3 Script from Command Line

Question

I am writing a shell script to scp a project and part of it involves transferring some important tables in my database.

I have the following stored in the file SQLTableTransfer:

.o MyTable1.sql;
.dump MyTable1;

.o MyTable2.sql;
.dump MyTable2;

.o MyTable3.sql;
.dump MyTable3;

And took a chance on

$ sqlite3 SQLTableTransfer

But this just opened the Sqlite3 shell. What is the correct way to run a script like this from the command line?

http://stackoverflow.com/questions/10045035/how-to-execute-an-sql-script-file-against-an-sqlite-3-database-file — Zach Thacker, Feb 13 '14 at 15:52
I saw something like this, but that is executing SQL language from the command line, I need it to execute Sqlite language. — rwolst, Feb 13 '14 at 15:55
If those are commands that are entered at the sqlite3 command line, then this approach should still work. The general idea is that you're sending the file's contents to sqlite3's stdin. So instead of using keyboard input, it uses the input from this file. Did you give this a try already? — Zach Thacker, Feb 13 '14 at 15:58

score 56 · Accepted Answer · answered Feb 13 '14 at 16:01

56

The parameter you give to the sqlite3 program is the database file name.

To execute commands from a file, you must redirect the input to that file:

$ sqlite3 mydatabase.db < SQLTableTransfer

or tell it to read from that file:

$ sqlite3 mydatabase.db ".read SQLTableTransfer"

answered Feb 13 '14 at 16:01

CL.

173,858
17
217
259

Ok, thanks. However for some reason when I am using `.dump` when doing this, it is only dumping the table `PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT;` – rwolst Feb 13 '14 at 16:32
1

Recently I have been trying to import a utf8 encoded sql script that contains national characters like the German _äöüß_ and it turned out that the second method is the only way that works. Every other solution imports just question marks (I needed it for a PowerShell script). – t3chb0t Apr 07 '16 at 06:32
@t3chb0t This question is not about Windows. – CL. Apr 07 '16 at 06:33
2

It doesn't matter :) it has over 2k views so I guess generally people interested in importing sql scripts in sqlite3 are coming by (like me) and even though not directly related to windows this solution worked for me. I think it's important to mention the utf8 connection because it wouldn't work otherwise on some systems (eg Windows). I've googled for an hour before I found this. – t3chb0t Apr 07 '16 at 06:38
What if the name "SQLTableTransfer" is changing and is stored in a variable f? As in `for f in *.csv` called from bash? – Guilherme Salomé Jun 23 '17 at 18:42
1

@GuilhermeSalomé How to use variables in the shell would be a different question. – CL. Jun 23 '17 at 21:18

score 4 · Answer 2 · answered Nov 29 '20 at 18:20

4

For Windows CLI, assuming your database is loaded:

sqlite> .read C:\\somesubdir\\some.sql

answered Nov 29 '20 at 18:20

Andrew Cowenhoven

2,778
22
27

score 3 · Answer 3 · answered May 04 '18 at 10:12

3

You can get a list of the spatial tables as follows:

echo "SELECT f_table_name FROM geometry_columns;" | spatialite -noheader -silent your_db.sqlite

answered May 04 '18 at 10:12

Micha

403
2
13

score 3 · Answer 4 · answered Nov 30 '19 at 18:27

For the lazy who want to just dump this into their .bashrc:

### Execute an sqlite3 file on a given db
sql3-exec () {
  # TODO: write a  --help flag that doubles as error handling
  # TODO: Ensure that $1 is a db; Else display --help
  # TODO: Ensure that $2 is a .sql file; Else display --help
  # TODO: Probably store a backup (or at least a flag)...
  sqlite3 $1 ".read $2"
  true
}

Running a Sqlite3 Script from Command Line

4 Answers4

Linked