Can't find my table in sqlite after creating it and streaming data to the db file

Question

this is my first time using sqlite, I've only worked on MySQL before. I have a program where I stream live twitter tweets and store them in a db. The program creates a database, and then starts running tweepy to fetch the data from twitter. I'm having trouble trying to print out my data for data exploration from my db file, twitter.db. I do however see the tweets stream real-time on my console, I just cannot seem to call the data from the db.

Below is my database.

conn = sqlite3.connect('twitter.db')

c = conn.cursor()

def create_table():
    try:
        c.execute("CREATE TABLE IF NOT EXISTS sentiment(unix REAL, tweet TEXT, sentiment REAL)")  
        c.execute("CREATE INDEX fast_unix ON sentiment(unix)")
        c.execute("CREATE INDEX fast_tweet ON sentiment(tweet)")
        c.execute("CREATE INDEX fast_sentiment ON sentiment(sentiment)")
        conn.commit()
    except Exception as e:
        print(str(e))
create_table()

After I run the program once, I hashtag the def create_table() function out to allow the flow of data to stream without having the program run another create_table(). Below is how I stream the data to my db.

    def on_data(self, data):
        try:
            data = json.loads(data)
            tweet = unidecode(data['text'])
            time_ms = data['timestamp_ms']

            analysis = TextBlob(tweet)

            sentiment = analysis.sentiment.polarity
            print(time_ms, tweet, sentiment)
            c.execute("INSERT INTO sentiment (unix, tweet, sentiment) VALUES (?, ?, ?)",
                  (time_ms, tweet, sentiment))
            conn.commit()

        except KeyError as e:
            print(str(e))
        return(True)

The streaming from twitter API seems to work well, however when I want to print out my rows for data exploration and check if the data is being stored, I receive this error: OperationalError: no such table: sentiment. The code below produces said error:

import sqlite3

conn = sqlite3.connect('twitter.db')

c = conn.cursor()

c.execute("SELECT * FROM sentiment")

print(c.fetchall())

When I run c.execute("SELECT * FROM sqlite_master") ...I get a [] printed on screen. Which I assume and know that something is very wrong. What is wrong with the code above?

Thanks.

Answer 1

are you executing the scripts from the same directory?

If not sure I suggest to write in both scripts

import os
print("I am in following directory: ", os.getcwd())
conn = sqlite3.connect('twitter.db')

instead of

conn = sqlite3.connect('twitter.db')

and check if both really look in the same directory for twitter.db

If they do, then go to the command line change into this directory and type

sqlite3 twitter.db

and type then

.tables

and look what tables will be listed. you can then even type queries (if the table exists) to check in more detail

SELECT * FROM sentiment;