Run command-line SQLite 3 query and exit

ShellCommand LineSqlite

Shell Problem Overview

We can use the -cmd option with sqlite3 to run a query, but then sqlite3 opens the database and waits in there for interactive input. How can we run a query on sqlite3 from the command line and exit?

Shell Solutions

Solution 1 - Shell

Just include the command in quotes after the database file argument.

For example, the following creates a table called abc:

sqlite3 test.db 'create table abc (col0 int)'

Solution 2 - Shell

You can use the .exit command (1), to exit gracefully:

sqlite3 test.db "select * from abc;" ".exit"

Documentation: Command Line Shell For SQLite.

Solution 3 - Shell

If you are stuck in a situation where you absolutely "have to" use the -cmd flag when you are running SQLite 3 from the command line, you can use successive blank command to exit.

For example:

sqlite3 test.db "select * from urls;" "" > test.txt

In this example, the "" will cause the SQLite 3 process to exit. (At least it does for me on OS X.)

Solution 4 - Shell

Also it might be useful to have multiline queries:

sqlite3 ./database.db <<EOF
    SELECT *
    FROM something
    LIMIT 5
EOF

Solution 5 - Shell

As it is mentioned in the question, we can also make use of the '-cmd' option and... apply some shell 'magic' at the end (to imitate user manual 'exit'). Let's evaluate a sql expression:

#!/bin/sh
sqlite3 -cmd "select 5.0/2;" < `echo ".exit"`

2.5

This approach is effective, when we have to submit '.xxx' sqlite meta-commands (i.e. .show or .stat) as well:

sqlite3 -cmd ".stat" < `echo ".exit"`

Memory Used:                         0 (max 56) bytes
Number of Outstanding Allocations:   0 (max 2)
Number of Pcache Overflow Bytes:     0 (max 0) bytes
Largest Allocation:                  40 bytes
Largest Pcache Allocation:           0 bytes
Bytes received by read():            10992
Bytes sent to write():               253
Read() system calls:                 19
Write() system calls:                6
Bytes read from storage:             0
Bytes written to storage:            0
Cancelled write bytes:               0

Note: Tested in dash & bash. Should also play in zsh, fish, etc.

Categories
Recommended Shell Solutions
Recommended Command Line Solutions
Recommended Sqlite Solutions

Previous Solution:

Different result with roc_auc_score() and auc()

PythonMachine LearningScikit Learn

Next Solution:

How can I change the NavigationView's item text size?

AndroidNavigation DrawerMaterial DesignAndroiddesignsupport

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionfarmirView Question on Stackoverflow
Solution 1 - ShellgbrenerView Answer on Stackoverflow
Solution 2 - ShellAnnoying TechnologyView Answer on Stackoverflow
Solution 3 - Shellf1lt3rView Answer on Stackoverflow
Solution 4 - ShellAlex KoshView Answer on Stackoverflow
Solution 5 - ShellnaiView Answer on Stackoverflow