CODEWITHSUNDEEP
pythonsqlitetweepy
Danielscottt
Danielscottt

Reputation: 81

Commiting data from Tweepy to a sqlite3 database, no matter what I do, db remains empty

Code:

import time
import tweepy
import sqlite3

class Listener(tweepy.StreamListener):

    conn = sqlite3.connect('/home/daniel/Desktop/activeSites/djeep/djeep.db')

    def on_status(self, status):
        try:
            c = self.conn.cursor()
            c.execute("""insert into feed_post values (%r,'%s','%s',%d)""") % (status.id, status.text, status.author.screen_name, status.created_at)
            self.conn.commit()
        except:
            pass


    def on_error(self, status_code):
        print 'An error has occured! Status code = %s' % status_code
        return True  # keep stream alive

    def on_timeout(self):
        print 'timeout...'

def main():
    auth = tweepy.OAuthHandler('C_KEY', 'C_SECRET') 
    auth.set_access_token('ACCESS_TOKEN', 'ACCESS_SECRET') 
    stream = tweepy.Stream(auth=auth, listener=Listener())     
    stream.filter(track=('baseball',)) 

if __name__=="__main__":
    try:
        main()
    except KeyboardInterrupt:
        print "See ya!"

I've gone back and added one line of the database related code at a time to try and find out what breaks it, and it seems to be the addition of the c.execute() line. I just can't figure out what I'm missing!

Upvotes: 3

Views: 705

Answers (2)

John Machin
John Machin

Reputation: 82934

The path to the database should be an argument to your script, not hardcoded. It should be supplied to your class each time the class is instantiated, NOT when your class is created. However it's not apparent that that is the cause of your problem, not yet exactly what the problem is:

Your title indicates that you can't get anything written to your database, but the question body implies that something "breaks" when you add in c.execute -- which is correct? What are the symptoms when it "breaks"?

Your try\yadda\except\pass is silently ignoring all possible exceptions -- don't do that! Remove the try\except\pass leaving only the yadda, answer the above questions, and let us know the outcome.

UPDATE: Your c.execute() statement is a shocker. Making it legible without scrolling, it's equivalent to this:

(
    c.execute("""insert into feed_post values (%r,'%s','%s',%d)""")
    % 
    (status.id, status.text, status.author.screen_name, status.created_at)
)

In other words, you have a right parenthesis grossly misplaced. The result is syntactically valid, but is sure to cause an exception at run time.

Worse: you are setting yourself up for an SQL injection attack. Use parameters instead of string formatting:

sql = "insert into feed_post values (?,?,?,?)"
params = (status.id, status.text, status.author.screen_name, status.created_at)
c.execute(sql, params)

A bonus from this approach is that it should run much faster, as the engine will not need to parse (or have its cache swamped by) a typically-different SQL statement for each row written.

Upvotes: 2

Lance Collins
Lance Collins

Reputation: 3445

Try taking the self references out of the class, or use an __init__ function to initialize self.conn

def __init__(self):
    self.conn = sqlite3.connect('/home/daniel/Desktop/activeSites/djeep/djeep.db')

def on_status(self, status):
    try:
        c = self.conn.cursor()
        c.execute(SQL...)
        self.conn.commit()
    except:
        pass

I agree with machin though, pass your connection and cursor objects as parameters when you initialize the object.

Upvotes: 0

Related Questions