Python - Table not found error when using SQlite3 with Daemonize

Question

I have a slack bot that accesses an SQlite database. When I run the bot in the foreground, everything works but when I run it as a daemon, it seems to connect to my database but then returns "table not found" on every query that I run.

The thing is, this used to work until my latest version and I can't find what changed, so I made a stripped down version as a test but I still have the same problem. See below:

import syslog
import sqlite3
import os
from daemonize import Daemonize
from slackclient import SlackClient
from qatoken import token

#Slack vars
nickname = "NetBot-QA"
dump = "#net-dump-qa"
channel = "#net-alerts-qa"
avatar = ":loader:"

#Daemonize vars
pid = "/tmp/netbot-qa.pid"
app = "net-bot-qa"
syslog.openlog('netbot', 0, syslog.LOG_LOCAL4 )

###################################################### MAIN
def main():

    sc = SlackClient(token)
    sc.api_call("chat.postMessage", username=nickname, channel=dump, text="{0}/net-alerts-test.db".format(os.path.dirname(__file__)), icon_emoji=avatar)

    try:
        conn = sqlite3.connect("{0}/net-alerts-test.db".format(os.path.dirname(__file__)))
        c = conn.cursor()
        c.execute("SELECT * from maint_calendar")
        sc.api_call("chat.postMessage", username=nickname, channel=dump, text="made it!", icon_emoji=avatar)
    except (SystemExit, KeyboardInterrupt):
        raise
    except Exception, e:
        sc.api_call("chat.postMessage", username=nickname, channel=dump, text="The bot crashed", icon_emoji=avatar)
        syslog.syslog("error: {0}".format(e))

########################################################################################
#main()
########################################################################################
#main()
daemon = Daemonize(app=app, pid=pid, action=main)
daemon.start()

If I run main(), I get the slack message "Made it!", but if I comment it out and run daemon.start() instead, I get "The bot crashed".

When I cat /var/log/messages, this is what I see:

Nov 16 09:47:37 server1 netbot: error: no such table: maint_calendar

[root@server1 net-alerts]# pwd
/opt/bots/net-alerts

[root@server1 net-alerts]# sqlite3 net-alerts-test.db
SQLite version 3.7.17 2013-05-20 00:56:22
sqlite> .schema
CREATE TABLE maint_calendar (start time, finish time, summary text, dc_tag text, provider text, CONSTRAINT start_sum PRIMARY KEY (start, summary));

I've also tried running this with a static path to the db but os.path.dirname has worked very well with every other script I use. I found another post saying you have to make the DB connection inside the Daemon context (main()) but I've been doing this from the start. I'm running out of ideas.

Try to put ; at the end of your SQL statement first, please :) — Peter Majko
– Peter Majko, Commented Nov 16, 2017 at 15:19
Please print this into your log: os.path.dirname( __file__ ), so you can see if it's really the same path even if it's daemonized. — Peter Majko
– Peter Majko, Commented Nov 16, 2017 at 15:20

Peter Majko · Accepted Answer · 2017-11-16 15:23:49Z

1

It seems to me, that this line:

conn = sqlite3.connect("{0}/net-alerts-test.db".format(os.path.dirname(__file__)))

Is actually connecting to two different sources, depending if you daemonize or just run main().

Just save the file to db in a global variable, which you use later. E.g.:

DB_PATH = "{0}/net-alerts-test.db".format(os.path.dirname(__file__))
...
def main():

    sc = SlackClient(token)
    sc.api_call("chat.postMessage", username=nickname, channel=dump, text="{0}/net-alerts-test.db".format(os.path.dirname(__file__)), icon_emoji=avatar)

    try:
        conn = sqlite3.connect(DB_PATH)

answered Nov 16, 2017 at 15:23

Peter Majko

1,33113 silver badges22 bronze badges

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

5 Comments

Quake Over a year ago

Didn't work. I copied your code but I get the same error. That first slack message displays the path from the point of view of main() and it shows the correct path in both cases. I had also tried with a static path and it didn't solve the issue.

Peter Majko Over a year ago

Also when debugging you should leave the exception to raise, so you see the full stack error: except Exception, e: sc.api_call("chat.postMessage", username=nickname, channel=dump, text="The bot crashed", icon_emoji=avatar) syslog.syslog("error: {0}".format(e)) raise e

Quake Over a year ago

Would I see that error in Stderr? Since this is running as a daemon I have to send the output to syslog but even with raise e at the end, I'm not seeing any more information.

Peter Majko Over a year ago

Yes, you will not see it, unless you redirect stderr to a file to see the traceback for whole error.

Quake Over a year ago

Alright, I got rid of os.path.dirname entirely and now it's working. I'm not sure what to think because I even rolled back to my earlier version where I AM using os.path.dirname, and I brought my old DB from backups, and the bot was still crashing at the same place. So the lesson here is: Be mindful of os paths when using Daemonize. You were right, have my upvote.

shuberman · Accepted Answer · 2020-07-20 06:11:43Z

1

I had the same problem. I have hard-coded the full path of the DB file and its working nice!

Example:

dbconn = sqlite3.connect("/home/pi/db.db")
cur = dbconn.cursor()
cur.execute("INSERT INTO raw_data(id, packet, db_insert_time, sync) VALUES(NULL, ?, ?, ?)", ('Line', x, 'N'))

dbconn.commit()
print('DB Insert success!')

edited Jul 20, 2020 at 6:11

shuberman

1,4887 gold badges23 silver badges42 bronze badges

answered Jul 20, 2020 at 4:34

Muhammad Hasan-uz-zaman

111 silver badge1 bronze badge

Collectives™ on Stack Overflow

Python - Table not found error when using SQlite3 with Daemonize

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related