Streaming Tweets from Twitter to Database Part 1

So let's say we want to insert actual tweets from Twitter into our database, how might we do that? Before actually using twitter, let's first try doing it with our own data, but this time as variables.

So let's say we have the following data:

username='python'

tweet='man im so cool'
	  

Now let's revisit the code from our previous tutorial, only making some changes this time:

import MySQLdb
import time

#        replace mysql.server with "localhost" if you are running via your own server!
#                        server       MySQL username	MySQL pass  Database name.
conn = MySQLdb.connect("mysql.server","beginneraccount","cookies","beginneraccount$tutorial")

c = conn.cursor()

username='python'

tweet='man im so cool'

c.execute("INSERT INTO taula (time, username, tweet) VALUES (%s,%s,%s)",
            (time.time(), username, tweet))

conn.commit()

c.execute("SELECT * FROM taula")

rows = c.fetchall()

for eachRow in rows:
    print eachRow

	  

Aside from the addition of the username and tweet variables, which are self-explanatory, our main change is:

c.execute("INSERT INTO taula (time, username, tweet) VALUES (%s,%s,%s)",
            (time.time(), username, tweet))

conn.commit()

So here, the MySQL query looks a bit different. This is how we insert variables. So we're telling MySQL that we're going to be inserting into the taula table, and we notify MySQL the column names, and order, that the variables will be in. In this case, time, username, then tweet. Then we add a VALUES parameter, then we finally have (%s, %s, %s), which is variable code for strings. After that, we close off the quotes, and then enter the tuple containing our variable data in the same order that we notified MySQL that the data would come in (time, username, tweet.), hence we use (time.time(), username, tweet))

Now comes this "conn.commit()," which we're seeing for the first time here. The previous tutorials didn't require this, they were committed based on us just hitting the enter key, basically. You must "commit" any changes to the database that you make, otherwise they simply will not stick. Think of "commit" as a sort of "save file," like in a word document.

After that, we run a simple SELECT again, and now we should see that our data indeed has been added. We should have a new row with a new timestamp, a username of python, and the tweet of "man im so cool" in our taula table.

If you run the script, you should see something like:

Great, now how far away are we from streaming actual tweets from Twitter into a database?!

About 15 minutes. Ready to learn how to stream live tweets from twitter?

Next tutorial: Streaming Tweets from Twitter