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?

I saw something like this, but that is executing SQL language from the command line, I need it to execute Sqlite language. — rwolst
– rwolst, Commented Feb 13, 2014 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
– Zach Thacker, Commented Feb 13, 2014 at 15:58

CL. · Accepted Answer · 2014-02-13 16:01:46Z

66

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, 2014 at 16:01

CL.

182k18 gold badges241 silver badges282 bronze badges

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

6 Comments

rwolst Over a year ago

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;

t3chb0t Over a year ago

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).

CL. Over a year ago

@t3chb0t This question is not about Windows.

t3chb0t Over a year ago

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.

CL. Over a year ago

@GuilhermeSalomé How to use variables in the shell would be a different question.

|

Andrew Cowenhoven · Accepted Answer · 2020-11-29 18:20:08Z

5

For Windows CLI, assuming your database is loaded:

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

answered Nov 29, 2020 at 18:20

Andrew Cowenhoven

2,82623 silver badges28 bronze badges

Comments

T.Woody · Accepted Answer · 2019-11-30 18:27:13Z

4

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
}

answered Nov 30, 2019 at 18:27

T.Woody

1,2382 gold badges13 silver badges26 bronze badges

Comments

Micha · Accepted Answer · 2018-05-04 10:12:55Z

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 4, 2018 at 10:12

Micha

5332 silver badges13 bronze badges

Collectives™ on Stack Overflow

Running a Sqlite3 Script from Command Line

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related