Arch Linux

alphaniner

Member

From: Ancapistan

Registered: 2010-07-12

Posts: 2,810

[solved] Best way to test for record in sqlite3 db?

In a bash script, I need to know if a sqlite3 'SELECT ... WHERE' operation finds any matches.  I don't need the output; I just need to know if a match is found.  My thought was to do

if [[ "$(sqlite3 ... "SELECT ..." )" ]];then ...

Is there a better way to do it?

Bonus question: if searching a primary key or unique column, is a ' SELECT ... WHERE foo="bar" ' operation smart enough to stop when it finds a match, or will it continue through the rest of the table?

Thanks.

Last edited by alphaniner (2012-04-26 14:51:36)

---

But whether the Constitution really be one thing, or another, this much is certain - that it has either authorized such a government as we have had, or has been powerless to prevent it. In either case, it is unfit to exist.
-Lysander Spooner

lunar

Member

Registered: 2010-10-04

Posts: 95

Re: [solved] Best way to test for record in sqlite3 db?

@alphaniner: Primary key columns are usually indexed, hence a query for a primary key column will not traverse the table, but perform an index lookup. Since primary keys are unique this will end up at a single row.

To test for existence of a row, you'd usually use "select exists(select 1 from <table> where <condition>)".  This will output 1, if the row exists, and 0 otherwise. I don't know whether the exit state of the "sqlite" tool is reliable, but testing the output will be with this query.

And again: If you're doing this kind of stuff, you're better off with a real languages with data types and the like. Python provides SQLite support in the standard library.

alphaniner

Member

From: Ancapistan

Registered: 2010-07-12

Posts: 2,810

Re: [solved] Best way to test for record in sqlite3 db?

Always hatin' on bash... I'm sure you're right, but first I'd have to learn a real language.

The command still returns 0 regardless, so from the scripting point of view I haven't gained much.  But the sqlite command is probably more efficent.  Thanks again.

Edit: Now that I understand the command, I'm not sure it is more efficent.  What is the point of printing '1' for each record found (SELECT 1)?  I assumed that meant print one record.

Last edited by alphaniner (2012-04-26 15:15:35)

---

But whether the Constitution really be one thing, or another, this much is certain - that it has either authorized such a government as we have had, or has been powerless to prevent it. In either case, it is unfit to exist.
-Lysander Spooner