15.11: Storing the friend relationship
- Page ID
- 3264
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 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 People
and 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 id
, name
, and 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.