15.11: Storing the friend relationship
- Page ID
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
Here is a sample execution of this program:
Enter a Twitter account, or quit: No unretrieved Twitter accounts found Enter a Twitter account, or quit: drchuck Retrieving http://api.twitter.com/1.1/friends ... New accounts= 20 revisited= 0 Enter a Twitter account, or quit: Retrieving http://api.twitter.com/1.1/friends ... New accounts= 17 revisited= 3 Enter a Twitter account, or quit: Retrieving http://api.twitter.com/1.1/friends ... New accounts= 17 revisited= 3 Enter 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
Follows tables after this run is completed:
People: (1, 'drchuck', 1) (2, 'opencontent', 1) (3, 'lhawthorn', 1) (4, 'steve_coppin', 0) (5, 'davidkocher', 0) 55 rows. Follows: (1, 2) (1, 3) (1, 4) (1, 5) (1, 6) 60 rows.
You can see the
visited fields in the
People table and you see the numbers of both ends of the relationship in the
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 users 2 and 3 as well.