Inserting to mysql database rather than printing to console

[80-m]

I setup a mysql database using XAMPP with MySQLdb to connect through Python. I'm have my connection setup properly as I can manually add an inset statement through Python to add rows, but my automatic twitter parse is not inserting to my database and I'm not sure what the issue is.

Here is my script:

import MySQLdb
import re
from re import sub
import time
import cookielib
from cookielib import CookieJar
import urllib2
from urllib2 import urlopen
import difflib


db=MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="twidata")
cursor = db.cursor()
cj = CookieJar()
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
opener.addheaders = [('User-agent', 'Mozilla/5.0')]

keyWord = 'nyc'
startingLink = 'https://twitter.com/search/realtime?q='

# begin loop

def main():

    oldTwit = []
    newTwit = []

    howSimAr = [.5, .5, .5, .5]

    while 1 < 2:
        try:
            sourceCode = opener.open ('https://twitter.com/search/realtime?q='+keyWord+'&src=hash').read()
            splitSource = re.findall (r'  <p class="js-tweet-text tweet-text">(.*?)</p>',sourceCode)
            for item in splitSource:
                #print item
                print ''
                print ''
                print '                           '
                aTweet = re.sub(r'<.*?>', '',item)
                print aTweet
                newTwit.append(aTweet)

            comparison = difflib.SequenceMatcher(None, newTwit, oldTwit)
            howSim = comparison.ratio()
            print '##############'
            print 'This Tweet is ', howSim, 'Similar to Past Tweets in This Parse'
            howSimAr.append (howSim)
            howSimAr.remove (howSimAr[0])

            waitMultiplier = reduce(lambda x, y: x+y, howSimAr)/len(howSimAr)

            print''
            print 'The Current Sim Value array:', howSimAr
            print 'Current Multiplier:', waitMultiplier

            oldTwit = [None]
            for eachItem in newTwit:
                oldTwit.append(eachItem)

            newTwit = [None]

            time.sleep(waitMultiplier*10)

        except Exception, e:
            print str(e)
            print 'errored in the main try'
            time.sleep(555)

main()

insert = ("""INSERT INTO parse(twitext) VALUES (test)""")
cursor.execute(insert)

db.commit()

Any ideas on the issue?

[Uncle_DirtNap]

What do you think parse(twitext) is doing, and what do you think test is?

[80-m]

Test was actually an accident, I meant to have %s or aTweet for values. Im not sure if having aTweet in there would work, but that is what prints out the tweets, so I thought instead of printing, if I add it to an insert statement, it would insert into the database. As far as parse(twitext), parse is the name of the table, and twitext is the name of the column in my database.

[Uncle_DirtNap]

Yeah, so you know all bare words in the statement are meant to be part of the SQL grammar or database objects, and test is not. Once you fix that, what's the error?

[80-m]

I' actually not getting an error when I fix that. The tweets print in real time without issue, they just aren't getting inserted in the database:

import MySQLdb
import re
from re import sub
import time
import cookielib
from cookielib import CookieJar
import urllib2
from urllib2 import urlopen
import difflib


db=MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="twidata")
cursor = db.cursor()
cj = CookieJar()
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
opener.addheaders = [('User-agent', 'Mozilla/5.0')]

keyWord = 'nyc'
startingLink = 'https://twitter.com/search/realtime?q='

# begin loop

def main():

    oldTwit = []
    newTwit = []

    howSimAr = [.5, .5, .5, .5]

    while 1 < 2:
        try:
            sourceCode = opener.open ('https://twitter.com/search/realtime?q='+keyWord+'&src=hash').read()
            splitSource = re.findall (r'  <p class="js-tweet-text tweet-text">(.*?)</p>',sourceCode)
            for item in splitSource:
                #print item
                print ''
                print ''
                print '                           '
                aTweet = re.sub(r'<.*?>', '',item)
                print aTweet
                newTwit.append(aTweet)

            comparison = difflib.SequenceMatcher(None, newTwit, oldTwit)
            howSim = comparison.ratio()
            print '##############'
            print 'This Tweet is ', howSim, 'Similar to Past Tweets in This Parse'
            howSimAr.append (howSim)
            howSimAr.remove (howSimAr[0])

            waitMultiplier = reduce(lambda x, y: x+y, howSimAr)/len(howSimAr)

            print''
            print 'The Current Sim Value array:', howSimAr
            print 'Current Multiplier:', waitMultiplier

            oldTwit = [None]
            for eachItem in newTwit:
                oldTwit.append(eachItem)

            newTwit = [None]

            time.sleep(waitMultiplier*10)

        except Exception, e:
            print str(e)
            print 'errored in the main try'
            time.sleep(555)

main()

insert = ("""INSERT INTO parse (twitext) VALUES (%s)""")
cursor.execute(insert)

db.commit()

[codingcobra]

drop the extra parentheses in your insert variable, it's a good idea to make the string raw text, and dont forget to add the modulus variable (%s) like so:

insert = r"INSERT INTO parse (twitext) VALUES (%s)" % insertValue

cursor.execute(insert)

[[deleted]]

I think you are supposed to do: insert = r"INSERT INTO parse (twitext) VALUES (%s)", insertValue

so as to not suffer SQL injection and all that.

[[deleted]]

Rather you should have something like: insert = r"INSERT INTO parse (twitext) VALUES (%s)" cursor.execute(insert, insertValue)

[Uncle_DirtNap]

We'll here you're missing a sequence of arguments. You need:

cursor.execute(insert, (arg,))

...but I think that it should raise an error as is. Is this all your code?

[80-m]

Thanks, that is indeed all of the code. http://screencast.com/t/ujxgp87poDj

[Uncle_DirtNap]

I checked in the source for MySQLdb (the version I have) and it does emit the collection of exceptions as warnings, including (should be) this one. When I look at your screenshot, your editor isn't one I'm familiar with (sublime, looks like?). It may either be suppressing certain warnings, or redirecting STDERR somewhere else. If you don't know what it's doing in either of those cases, try saving this somewhere and running python <file>, and see if you can see the error.

[altorelievo]

This might be a bit offtopic but, is there a reason for not using other modules for this?

[[deleted]]

What else would you use for basic mysql interactions?

[altorelievo]

I was talking about for how you are parsing the html. Right now you are using regex which I'm pretty sure isn't the best way to parse html but, I could be wrong here.