CODEWITHSUNDEEP
pythonpython-3.xflasksqliteapscheduler
candronikos
candronikos

Reputation: 169

SQL INSERT in SQLITE with APSCHEDULER

Having an issue inserting values in sqlite3 from a job running in apscheduler.

What I'm looking for is a way to insert values from the job. I suppose this means running the jobs from the host thread? Or filling a buffer and funnelling that to a single thread that manages the sql transactions?

What's the best way to handle this? I plan to have this running within a flask app later as well.

Here's the code:

"""
Demonstrates how to use the background scheduler to schedule a job that executes on 3 second
intervals.
"""

from datetime import datetime
import time
import os

from apscheduler.schedulers.background import BackgroundScheduler


def tick():
    print('Tick! The time is: %s' % datetime.now())

def tick1():
    print('Tick1! The time is: %s' % datetime.now())
    id = "testId"
    global sql_cursor
    sql_cursor.execute("INSERT INTO histories VALUES(?,?,?)", (datetime.now(),id,0.0))


if __name__ == '__main__':
    import sqlite3
    sql_db = sqlite3.connect('histories.db')
    sql_cursor = sql_db.cursor()
    sql_cursor.execute(
    '''CREATE TABLE IF NOT EXISTS histories(
       timestamp DATE, id TEXT, value REAL)''')
    sql_db.commit()

    scheduler = BackgroundScheduler()
    scheduler.add_job(tick, 'interval', seconds=3)
    scheduler.start()
    scheduler.add_job(tick1, 'interval', seconds=1)
    print('Press Ctrl+{0} to exit'.format('Break' if os.name == 'nt' else 'C'))

    try:
        # This is here to simulate application activity (which keeps the main thread alive).
        while True:
            time.sleep(2)
    except (KeyboardInterrupt, SystemExit):
        # Not strictly necessary if daemonic mode is enabled but should be done if possible
        scheduler.shutdown()

And the errors:

    Tick1! The time is: 2017-12-11 22:22:59.232296
    Job "tick1 (trigger: interval[0:00:01], next run at: 2017-12-11 22:22:59 UTC)" raised an exception
    Traceback (most recent call last):
      File "/usr/local/lib/python3.5/dist-packages/apscheduler/executors/base.py", line 125, in run_job
        retval = job.func(*job.args, **job.kwargs)
      File "background.py", line 20, in tick1
        sql_cursor.execute("INSERT INTO histories VALUES(?,?,?)", (datetime.now(),id,0.0))
    sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id -1225585456 and this is thread id -1260391312

Upvotes: 1

Views: 2082

Answers (1)

Augmented Jacob
Augmented Jacob

Reputation: 1577

Connections and Cursors cannot be used across threads that easily. And to answer your question in the comment, there shouldn't be any issues in creating connections in quick succession as long as you do a connection.close() at the end of its use. The below example should give you a good idea of how it would look like. You can tidy it up by creating your own classes to connect and execute.

import os
from apscheduler.schedulers.background import BackgroundScheduler
def tick():
    print('Tick! The time is: %s' % datetime.now())

def tick1():
    print('Tick1! The time is: %s' % datetime.now())
    id = "testId"
    sql_db = sqlite3.connect('histories.db')
    sql_cursor = sql_db.cursor()
    sql_cursor.execute("INSERT INTO histories VALUES(?,?,?)", (datetime.now(),id,0.0))
    sql_cursor.close()
    sql_db.close()


if __name__ == '__main__':
    import sqlite3
    sql_db = sqlite3.connect('histories.db')
    sql_cursor = sql_db.cursor()
    sql_cursor.execute(
    '''CREATE TABLE IF NOT EXISTS histories(
       timestamp DATE, id TEXT, value REAL)''')
    sql_db.commit()
    sql_cursor.close()
    sql_db.close()
    scheduler = BackgroundScheduler()
    scheduler.add_job(tick, 'interval', seconds=3)
    scheduler.start()
    scheduler.add_job(tick1, 'interval', seconds=1)
    print('Press Ctrl+{0} to exit'.format('Break' if os.name == 'nt' else 'C'))

    try:
        # This is here to simulate application activity (which keeps the main thread alive).
        while True:
            time.sleep(2)
    except (KeyboardInterrupt, SystemExit):
        # Not strictly necessary if daemonic mode is enabled but should be done if possible
        scheduler.shutdown()

Upvotes: 1

Related Questions