Twitter API Question posted in
The official Twitter API documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    I think gelonida is right on the money and comand-line sqlite3 is your friend.

    I slightly modified your code and it works fine when the db is in the same location:

    import os
import sqlite3

os.remove('twitter.db')
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()

def on_data():
    try:
        tweet = 'text'
        time_ms = 123.2
        sentiment = 1.234
        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)
on_data()

conn.close()

# Now check the result
conn = sqlite3.connect('twitter.db')
c = conn.cursor()
c.execute("SELECT * FROM sentiment")
print(c.fetchall())

    running it prints:

    123.2 text 1.234
[(123.2, 'text', 1.234)]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search