Once we know the key value for both the Twitter user and the friend in the JSON, it is a simple matter to insert the two numbers into the Follows table with the following code:
cur.execute('INSERT OR IGNORE INTO Follows (from_id, to_id) VALUES (?, ?)', (id, friend_id) )
Notice that we let the database take care of keeping us from “double-inserting” a relationship by creating the table with a uniqueness constraint and then adding OR IGNORE to our INSERT statement.
Here is a sample execution of this program:
Enter a Twitter account, or quit:No unretrieved Twitter accounts foundEnter a Twitter account, or quit: drchuckRetrieving http://api.twitter.com/1.1/friends ...New accounts= 20 revisited= 0Enter a Twitter account, or quit:Retrieving http://api.twitter.com/1.1/friends ...New accounts= 17 revisited= 3Enter a Twitter account, or quit:Retrieving http://api.twitter.com/1.1/friends ...New accounts= 17 revisited= 3Enter a Twitter account, or quit: quit
We started with the drchuck account and then let the program automatically pick the next two accounts to retrieve and add to our database.
The following is the first few rows in the People and Follows tables after this run is completed:
People:(1, u'drchuck', 1)(2, u'opencontent', 1)(3, u'lhawthorn', 1)(4, u'steve_coppin', 0)(5, u'davidkocher', 0)55 rows.Follows:(1, 2)(1, 3)(1, 4)(1, 5)(1, 6)60 rows.
You can see the id, name, and visited fields in the People table and you see the numbers of both ends of the relationship Follows table. In the People table, we can see that the first three people have been visited and their data has been retrieved. The data in the Follows table indicates that drchuck (user 1) is a friend to all of the people shown in the first five rows. This makes sense because the first data we retrieved and stored was the Twitter friends of drchuck. If you were to print more rows from the Follows table, you would see the friends of user two and three as well.