pandas.io.sql.DatabaseError: Execution failed on sql

Dash Python
1

Hi all, I seem to be having some issues with the Pie graph function:
I’m trying to create a pie chart that displays sentiment for a given word - sentiment data generate with TextBlob and data started with sqlite3 and is read by pandas.

I keep getting the following error:
pandas.io.sql.DatabaseError: Execution failed on sql ‘SELECT * FROM sentiment ORDER BY id DESC, unix DESC LIMIT 10000’: no such column: id

Here is the code for the main project:

import dash
from dash.dependencies import Output, Input
import dash_core_components as dcc
import dash_html_components as html
import plotly
import random
import plotly.graph_objs as go
from collections import deque
import sqlite3
import pandas as pd
import time
import plotly.express as px
import plotly.io as pio
from config import stop_words
import time
from collections import Counter
import regex as re
import string
import os
import sys

# set chdir to current dir
sys.path.insert(0, os.path.realpath(os.path.dirname(__file__)))
os.chdir(os.path.realpath(os.path.dirname(__file__)))

conn = sqlite3.connect('twitter.db', check_same_thread=False)

punctuation = [str(i) for i in string.punctuation]

app_colors = {
    'background': '#FFFFFF',
    'text': '#0C0F0A',
    'sentiment-plot': '#41EAD4',
    'volume-bar': '#FBFC74',
    'someothercolor': '#FF206E',
}


POS_NEG_NEUT = 0.1

MAX_DF_LENGTH = 100

app = dash.Dash(__name__)
app.layout = html.Div(
    [
        html.H2('[PIE-TESTING-NEW]Live Twitter Sentiment'),
        dcc.Input(id='sentiment_term', value='trump', type='text'),
        dcc.Graph(id='sentiment-pie', animate=False),
        dcc.Interval(
            id='graph-update',
            interval=1 * 1000,
            n_intervals=1
        ),
        dcc.Interval(
            id='sentiment-pie-update',
            interval=60 * 1000,
            n_intervals=0
        )
    ]
)


@app.callback(Output('sentiment-pie', 'figure'),
              [Input(component_id='sentiment_term', component_property='value'),
               Input('sentiment-pie-update', 'n_intervals')])
def update_pie_chart(sentiment_term, n):
    for i in range(100):
        conn = sqlite3.connect('twitter.db')
        c = conn.cursor()
        df = pd.read_sql("SELECT * FROM sentiment WHERE tweet LIKE ? ORDER BY unix DESC LIMIT 100000000", conn,
                         params=('%' + sentiment_term + '%',))
        df = pd.read_sql("SELECT * FROM sentiment ORDER BY id DESC, unix DESC LIMIT 10000", conn)
        df.sort_values('unix', inplace=True)
        df['date'] = pd.to_datetime(df['unix'], unit='ms')
        df.set_index('date', inplace=True)
        df['sentiment_shares'] = list(map(POS_NEG_NEUT, df['sentiment']))
        if df:
            break
    if not df:
        return None

    labels = ['Positive', 'Negative']

    try: pos = df[1]
    except:
        pos = 0

    try:
        neg = df[-1]
    except:
        neg = 0

    values = [pos, neg]
    colors = ['#007F25', '#800000']

    trace = go.Pie(labels=labels, values=values,
                   hoverinfo='label+percent', textinfo='value',
                   textfont=dict(size=20, color=app_colors['text']),
                   marker=dict(colors=colors,
                               line=dict(color=app_colors['background'], width=2)))

    return {"data": [trace], 'layout': go.Layout(
        title='Positive vs Negative sentiment for "{}" (longer-term)'.format(sentiment_term),
        font={'color': app_colors['text']},
        plot_bgcolor=app_colors['background'],
        paper_bgcolor=app_colors['background'],
        showlegend=True)}


if __name__ == '__main__':
    app.run_server(debug=True)

and here is the code of the python file generating the data:

from tweepy import Stream
from tweepy import OAuthHandler
from tweepy.streaming import StreamListener
import json
import sqlite3
from vaderSentiment.vaderSentiment import SentimentIntensityAnalyzer
from unidecode import unidecode
import textblob
from textblob import TextBlob
import time


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

ckey=""
csecret=""
atoken=""
asecret=""

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()



class listener(StreamListener):

    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)

    def on_error(self, status):
        print(status)


while True:

    try:
        auth = OAuthHandler(ckey, csecret)
        auth.set_access_token(atoken, asecret)
        twitterStream = Stream(auth, listener())
        twitterStream.filter(track=["a","e","i","o","u"])
    except Exception as e:
        print(str(e))
        time.sleep(5)

I’ve left the twitter key information blank.
I’m pretty sure the created database has an ID column.

This is from the sentiment analysis project by Sentdex,
Full project repo: GitHub - Sentdex/socialsentiment: Sentiment Analysis application created with Python and Dash, hosted at socialsentiment.net

Any insight would be greatly appreciated!