When we prompt the user for a Twitter account, if the account exists, we must look up its
id value. If the account does not yet exist in the
People table, we must insert the record and get the
id value from the inserted row.
This is a very common pattern and is done twice in the program above. This code shows how we look up the
id for a friend's account when we have extracted a
screen_name from a
user node in the retrieved Twitter JSON.
Since over time it will be increasingly likely that the account will already be in the database, we first check to see if the
People record exists using a
If all goes well2 inside the
try section, we retrieve the record using
fetchone() and then retrieve the first (and only) element of the returned tuple and store it in
SELECT fails, the
fetchone() code will fail and control will transfer into the
friend = u['screen_name'] cur.execute('SELECT id FROM People WHERE name = ? LIMIT 1', (friend, ) ) try: friend_id = cur.fetchone() countold = countold + 1 except: cur.execute('''INSERT OR IGNORE INTO People (name, retrieved) VALUES ( ?, 0)''', ( friend, ) ) conn.commit() if cur.rowcount != 1 : print('Error inserting account:',friend) continue friend_id = cur.lastrowid countnew = countnew + 1
If we end up in the
except code, it simply means that the row was not found, so we must insert the row. We use
INSERT OR IGNORE just to avoid errors and then call
commit() to force the database to really be updated. After the write is done, we can check the
cur.rowcount to see how many rows were affected. Since we are attempting to insert a single row, if the number of affected rows is something other than 1, it is an error.
INSERT is successful, we can look at
cur.lastrowid to find out what value the database assigned to the
id column in our newly created row.