Bash query sqlite

Question

sorry for the noob question. I'm trying to write a simple bash script, based on newsbeuter. Basically I'm trying to get the first 5 articles I haven't read yet, once I got them, I send them to my phone with pushover and I need so set them as read on newsbeuter.

#!/bin/bash --
urls=$( sqlite3 /home/pi/.newsbeuter/cache.db <<END
  select url from rss_item where unread = 1 limit 5;
END
)

This is the first query. I send the message variable through the pushover api. Now I need to get how to update the table and set the articles as read. Any ideas? (I'm totally new to bash syntax). I tried both to recreate a query like

UPDATE rss_item set unread = 0 where url = '$url'

I looped it but it didn't work, then I tried to make

`UPDATE rss_item set unread = 0 where url in ($urls)`

but I keep getting errors I can't even understand! I really need a syntax lecture!

what does message contain (echo $message). If you protect $message by simple quotes in bash, it won't be interpreted: you'll get $message literally in the query. — Jean-François Fabre
– Jean-François Fabre ♦, Commented Sep 2, 2016 at 19:21
I've tried both with "$message" and $message. $message contains 5 urls separated by a space. — Dani
– Dani, Commented Sep 2, 2016 at 19:55
if you were to hardcode your UPDATE rss_item set unread = 0 where url = '$message' query, what would you type? (edit your question it will be better) — Jean-François Fabre
– Jean-François Fabre ♦, Commented Sep 2, 2016 at 19:58
You're right, I'm just doing some tests not caring about it cause I feel like I've no idea of what I'm doing. As for the update, what should I do? — Dani
– Dani, Commented Sep 2, 2016 at 20:07
did you wrap your $urls in quotes? if $urls is equal to bob, alice, sally that won't work, you'd need it to be something like 'bob', 'alice', 'sally' — dudeofea
– dudeofea, Commented Sep 2, 2016 at 20:14

Jesin · Accepted Answer · 2016-09-02 20:24:30Z

1

Try this:

#!/bin/bash --
urls="$(
  sqlite3 /home/pi/.newsbeuter/cache.db \
    'select url from rss_item where unread = 1 limit 5' \
)"
for url in $urls; do
  sqlite3 /home/pi/.newsbeuter/cache.db \
    "UPDATE rss_item set unread = 0 where url = '$url'"
done

edited Sep 2, 2016 at 20:24

answered Sep 2, 2016 at 20:21

Jesin

1,0399 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Bash query sqlite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related