Linux下Bash脚本中如何获取SQLite3的返回值？

Hey there! Let's break down your questions about working with SQLite3 in Bash scripts clearly and practically.

Checking the Return Value of .open #

First off, in SQLite's interactive shell (sqlite3> prompt), commands like .open don't output a return code directly—they just silently execute. But when running SQLite in non-interactive mode (which is what you'll use in Bash scripts), you can rely on the exit code of the sqlite3 process to determine success or failure.

A key note: By default, if you run .open contacts.db (or use sqlite3 contacts.db directly), SQLite will automatically create the database file if it doesn't exist. That means the exit code will always be 0 (success) even if the file was just created. If you want to check if the database already exists (and avoid creating it), use the -readonly flag:

# Try to open the database in read-only mode
sqlite3 -readonly contacts.db ".exit"
# Check the exit code
if [ $? -eq 0 ]; then
    echo "Database exists and is accessible"
else
    echo "Database does NOT exist, or we can't open it"
fi

The -readonly flag tells SQLite to fail (exit code non-0) if the database file doesn't exist. The .exit command just makes SQLite quit immediately after opening.

Understanding Return Values for SELECT Queries #

When you run a query like SELECT count(_id) FROM contacts;, here's what you need to know:

1. SQLite's exit code: This is what Bash cares about. If the query is syntactically valid and runs without errors (e.g., the contacts table exists), the sqlite3 process will exit with code 0 (success). If there's an error (like a typo in the table name, or missing permissions), the exit code will be non-0.
2. Query result set: The actual output of the SELECT command is the data it returns (like the count number). You can capture this output in a Bash variable to use later.

Here's a script example that combines both checking for success and capturing the result:

# Run the query and capture the result
contact_count=$(sqlite3 contacts.db "SELECT count(_id) FROM contacts;")

# Check if the query executed successfully
if [ $? -eq 0 ]; then
    echo "Query ran successfully! Total contacts: $contact_count"
else
    echo "Error: Failed to run the count query."
fi

Even if the count is 0 (no contacts in the table), the exit code will still be 0—because the query itself didn't fail. The exit code only reflects whether SQLite could execute the command without errors, not the content of the result.

If you want to handle cases where the table might not exist, you can add error handling directly in SQL too:

contact_count=$(sqlite3 contacts.db "
    SELECT count(_id) FROM contacts 
    UNION ALL 
    SELECT -1 WHERE NOT EXISTS (SELECT 1 FROM sqlite_master WHERE type='table' AND name='contacts');
")

if [ $? -eq 0 ]; then
    if [ "$contact_count" -eq -1 ]; then
        echo "Error: The 'contacts' table doesn't exist!"
    else
        echo "Total contacts: $contact_count"
    fi
else
    echo "Error: Failed to execute query."
fi

内容的提问来源于stack exchange，提问作者Ameer Mutlaq