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.
2
Answers
are you executing the scripts from the same directory?
If not sure I suggest to write in both scripts
instead of
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
and type then
and look what tables will be listed.
you can then even type queries (if the table exists) to check in more detail
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:
running it prints: