//The code to create a database file and a table named Tracks with two columns in the database is as follows:

1 #!/usr/bin/python2 import sqlite33 conn = sqlite3.connect('music.sqlite3')4 cur  = conn.cursor()5 cur.execute('DROP TABLE IF EXISTS Tracks ')6 cur.execute('CREATE TABLE Tracks (title TEXT,plays INTEGER)')7 conn.close()

The connect operation makes a “connection” to the database stored in the file music.sqlite3 in the current directory. If the file does not exist, it will be created. The reason this is called a “connection” is that sometimes the database is  stored on a separate “database server” from the server on which we are running our application. In our simple examples the database will just be a local file in the same directory as the Python code we are running.

A cursor is like a file handle that we can use to perform operations on the data stored in the database. Calling cursor() is very similar conceptually to calling open() when dealing with text files.

Once we have the cursor, we can begin to execute commands on the contents of the database using the execute() method.

The database language is called Structured Query Language or SQL  for short.

http://en.wikipedia.org/wiki/SQL

In our example, we are executing two SQL commands in our database. As a convention, we will show the SQL keywords in uppercase and the parts of the command that we are adding (such as the table and column names) will be shown in lowercase.

The first SQL command removes the Tracks table from the database if it exists. This pattern is simply to allow us to run the same program to create the Tracks table over and over again without causing an error. Note that the DROP TABLE command deletes the table and all of its contents from the database (i.e., there is no “undo”).

cur.execute('DROP TABLE IF EXISTS Tracks ')

The second command creates a table named Tracks with a text column named title and an integer column named plays.

cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')

Now that we have created a table named Tracks, we can put some data into that table using the SQL INSERT operation. Again, we begin by making a connection to the database and obtaining the cursor. We can then execute SQL commands using the cursor.

The SQL INSERT command indicates which table we are using and then defines a new row by listing the fields we want to include (title, plays) followed by the VALUES we want placed in the new row. We specify the values as question marks (?, ?) to indicate that the actual values are passed in as a tuple ( ’My Way’,15 ) as the second parameter to the execute() call.

1 #!/usr/bin/python 2 import sqlite3 3 conn = sqlite3.connect('music.sqlite3') 4 cur  = conn.cursor() 5 cur.execute('INSERT INTO Tracks (title,plays) VALUES (?,?)', 6             ('Thunderstruck',20)) 7 cur.execute('INSERT INTO Tracks (title,plays) VALUES (?,?)', 8             ('My Way',15)) 9 conn.commit()10 print  'Tracks:'11 cur.execute('SELECT title, plays FROM Tracks')12 for row in cur:13     print row14 cur.execute('DELETE FROM Tracks WHERE plays < 100')15 conn.commit()16 cur.close()

OUTPUT:

Tracks:(u'Thunderstruck', 20)(u'My Way', 15)

First we INSERT two rows into our table and use commit() to force the data to be written to the database file.

Then we use the SELECT command to retrieve the rows we just inserted from the table. On the SELECT command, we indicate which columns we would like (title, plays) and indicate which table we want to retrieve the data from. After we execute the SELECT statement, the cursor is something we can loop through in a for statement. For efficiency, the cursor does not read all of the data from the database when we execute the SELECT statement. Instead, the data is read on demand as we loop through the rows in the for statement.

Our for loop finds two rows, and each row is a Python tuple with the first value as the title and the second value as the number of plays. Do not be concerned that the title strings are shown starting with u’. This is an indication that the strings are Unicode strings that are capable of storing non-Latin character sets.

At the very end of the program, we execute an SQL command to DELETE the rows we have just created so we can run the program over and over. The DELETE command shows the use of a WHERE clause that allows us to express a selection criterion so that we can ask the database to apply the command to only the rows that match the criterion. In this example the criterion happens to apply to all the rows so we empty the table out so we can run the program repeatedly. After theDELETE is performed, we also call commit() to force the data to be removed from the database.

Structured Query Language summary

So far, we have been using the Structured Query Language in our Python examples and have covered many of the basics of the SQL commands. In this section, we look at the SQL language in particular and give an overview of SQL syntax.Since there are so many different database vendors, the Structured Query Language (SQL) was standardized so we could communicate in a portable manner to database systems from multiple vendors.

A relational database is made up of tables, rows, and columns. The columns generally have a type such as text, numeric, or date data. When we create a table, we indicate the names and types of the columns:

CREATE TABLE Tracks (title TEXT, plays INTEGER)

To insert a row into a table, we use the SQL INSERT command:

INSERT INTO Tracks (title, plays) VALUES ('My Way', 15)

The INSERT statement specifies the table name, then a list of the fields/columns that you would like to set in the new row, and then the keyword VALUES and a list of corresponding values for each of the fields.

The SQL SELECT command is used to retrieve rows and columns from a database.The SELECT statement lets you specify which columns you would like to retrieve as well as a WHERE clause to select which rows you would like to see. It also allows an optional ORDER BY clause to control the sorting of the returned rows.

SELECT * FROM Tracks WHERE title = 'My Way'

Using * indicates that you want the database to return all of the columns for each row that matches the WHERE clause.

Note, unlike in Python, in a SQL WHERE clause we use a single equal sign to indicate a test for equality rather than a double equal sign. Other logical operations allowed in a WHERE clause include <, >, <=, >=, !=, as well as AND and OR and parentheses to build your logical expressions.

You can request that the returned rows be sorted by one of the fields as follows:

SELECT title,plays FROM Tracks ORDER BY title

To remove a row, you need a WHERE clause on an SQL DELETE statement. The WHERE clause determines which rows are to be deleted:

DELETE FROM Tracks WHERE title = 'My Way'

It is possible to UPDATE a column or columns within one or more rows in a table using the SQL UPDATE statement as follows:

UPDATE Tracks SET plays = 16 WHERE title = 'My Way'

The UPDATE statement specifies a table and then a list of fields and values to change after the SET keyword and then an optional WHERE clause to select the rows that are to be updated. A single UPDATE statement will change all of the rows that match

the WHERE clause. If a WHERE clause is not specified, it performs the UPDATE on all of the rows in the table.

These four basic SQL commands (INSERT, SELECT, UPDATE, and DELETE)

allow the four basic operations needed to create and maintain data.

Spidering Twitter using a database

One of the problems of any kind of spidering program is that it needs to be able to be stopped and restarted many times and you do not want to lose the data that you have retrieved so far. You don’t want to always restart your data retrieval at the very beginning so we want to store data as we retrieve it so our program can start back up and pick up where it left off.
We will start by retrieving one person’s Twitter friends and their statuses, looping through the list of friends, and adding each of the friends to a database to be retrieved in the future. After we process one person’s Twitter friends, we check in our database and retrieve one of the friends of the friend. We do this over and over, picking an “unvisited” person, retrieving their friend list, and adding friends we have not seen to our list for a future visit.

We also track how many times we have seen a particular friend in the database to get some sense of their “popularity”.

1 import urllib 2 import twurl 3 import json 4 import sqlite3 5 TWITTER_URL = 'https://api.twitter.com/1.1/friends/list.json' 6 conn = sqlite3.connect('spider.sqlite3') 7 cur = conn.cursor() 8 cur.execute(''' 9 CREATE TABLE IF NOT EXISTS Twitter10 (name TEXT, retrieved INTEGER, friends INTEGER)''')11 while True:12     acct = raw_input('Enter a Twitter account, or quit: ')13     if ( acct == 'quit' ) : break14     if ( len(acct) < 1 ) :15         cur.execute('SELECT name FROM Twitter WHERE retrieved = 0 LIMIT 1')16         try:    17             acct = cur.fetchone()[0]18         except:19             print 'No unretrieved Twitter accounts found'20             continue    21 　　url = twurl.augment(TWITTER_URL,{
   'screen_name': acct, 'count': '20'} )22　　 print 'Retrieving', url23 　　connection = urllib.urlopen(url)24 　　data = connection.read()25 　　headers = connection.info().dict26 　　# print 'Remaining', headers['x-rate-limit-remaining']27 　　js = json.loads(data)28　　 # print json.dumps(js, indent=4)29 　　cur.execute('UPDATE Twitter SET retrieved=1 WHERE name = ?', (acct, ) )30 　　countnew = 031 　　countold = 032 　　for u in js['users'] :33     　　friend = u['screen_name']34    　　 print friend35     　　cur.execute('SELECT friends FROM Twitter WHERE name = ? LIMIT 1',(friend, ) ) 3637 　　　　try:38     　　　　count = cur.fetchone()[0]39     　　　　cur.execute('UPDATE Twitter SET friends = ? WHERE name = ?',40     　　　　(count+1, friend) )41     　　　　countold = countold + 142 　　　　except:43     　　　　cur.execute('''INSERT INTO Twitter (name, retrieved, friends) VALUES ( ?, 0, 1 )''', ( friend, ) )44       　　 countnew = countnew + 145     46 　　print 'New accounts=',countnew,' revisited=',countold47　　 conn.commit()48 49 cur.close()

Our database is stored in the file spider.sqlite3 and it has one table named Twitter. Each row in the Twitter table has a column for the account name, whether we have retrieved the friends of this account, and how many times this account has been “friended”.

In the main loop of the program, we prompt the user for a Twitter account name or “quit” to exit the program. If the user enters a Twitter account, we retrieve the list of friends and statuses for that user and add each friend to the database if not already in the database. If the friend is already in the list, we add 1 to the friends field in the row in the database. If the user presses enter, we look in the database for the next Twitter account that we have not yet retrieved, retrieve the friends and statuses for that account, add them to the database or update them, and increase their friends count.

Once we retrieve the list of friends and statuses, we loop through all of the user items in the returned JSON and retrieve the screen_name for each user. Then we use the SELECT statement to see if we already have stored this particular screen_name in the database and retrieve the friend count (friends) if the record exists.

Once the cursor executes the SELECT statement, we must retrieve the rows. We could do this with a for statement, but since we are only retrieving one row (LIMIT1), we can use the fetchone() method to fetch the first (and only) row that is the result of the SELECT operation. Since fetchone() returns the row as a tuple (even though there is only one field), we take the first value from the tuple using [0] to get the current friend count into the variable count.

If this retrieval is successful, we use the SQL UPDATE statement with a WHERE clause to add 1 to the friends column for the row that matches the friend’s account. Notice that there are two placeholders (i.e., question marks) in the SQL, and the second parameter to the execute() is a two-element tuple that holds the values to be substituted into the SQL in place of the question marks.
 If the code in the try block fails, it is probably because no record matched the WHERE name = ? clause on the SELECT statement. So in the except block, we use the SQL INSERT statement to add the friend’s screen_name to the table with an indication that we have not yet retrieved the screen_name and set the friend count to zero.
So the first time the program runs and we enter a Twitter account, the program runs as follows:

Enter a Twitter account, or quit: drchuckRetrieving http://api.twitter.com/1.1/friends ...New accounts= 20 revisited= 0Enter a Twitter account, or quit: quit

Since this is the first time we have run the program, the database is empty and we create the database in the file spider.sqlite3 and add a table named Twitter to the database. Then we retrieve some friends and add them all to the database since the database is empty.

At this point, we might want to write a simple database dumper to take a look at what is in our spider.sqlite3 file:

1 import sqlite3 2 conn = sqlite3.connect('spider.sqlite3') 3 cur = conn.cursor() 4 cur.execute('SELECT * FROM Twitter') 5 count = 0 6 for row in cur : 7     print row 8     count = count + 1 9 print count, 'rows.'10 cur.close()

This program simply opens the database and selects all of the columns of all of the rows in the table Twitter, then loops through the rows and prints out each row. If we run this program after the first execution of our Twitter spider above, its output will be as follows:

1 (u'opencontent', 0, 1)2 (u'lhawthorn', 0, 1)3 (u'steve_coppin', 0, 1)4 (u'davidkocher', 0, 1)5 (u'hrheingold', 0, 1)6 ...7 20 rows.

We see one row for each screen_name, that we have not retrieved the data for that screen_name, and everyone in the database has one friend.

Basic data modeling

The real power of a relational database is when we create multiple tables and make links between those tables. The act of deciding how to break up your application data into multiple tables and establishing the relationships between the tables is called data modeling. The design document that shows the tables and their relationships is called a data model.

　　Let’s say for our Twitter spider application, instead of just counting a person’s friends, we wanted to keep a list of all of the incoming relationships so we could find a list of everyone who is following a particular account.
Since everyone will potentially have many accounts that follow them, we cannot simply add a single column to our Twitter table. So we create a new table that keeps track of pairs of friends. The following is a simple way of making such a table:

CREATE TABLE Pals (from_friend TEXT, to_friend TEXT)

Each time we encounter a person who drchuck is following, we would insert a row of the form:

INSERT INTO Pals (from_friend,to_friend) VALUES ('drchuck', 'lhawthorn')

As we are processing the 20 friends from the drchuck Twitter feed, we will insert 20 records with “drchuck” as the first parameter so we will end up duplicating the string many times in the database.
This duplication of string data violates one of the best practices for database normalization which basically states that we should never put the same string data in the database more than once. If we need the data more than once, we create a numeric key for the data and reference the actual data using this key.

In practical terms, a string takes up a lot more space than an integer on the disk and in the memory of our computer, and takes more processor time to compare and sort. If we only have a few hundred entries, the storage and processor time hardly matters. But if we have a million people in our database and a possibility of 100 million friend links, it is important to be able to scan data as quickly as possible.

We will store our Twitter accounts in a table named People instead of the Twitter table used in the previous example. The People table has an additional column to store the numeric key associated with the row for this Twitter user. SQLite has a feature that automatically adds the key value for any row we insert into a table using a special type of data column (INTEGER PRIMARY KEY).

We can create the People table with this additional id column as follows:

CREATE TABLE People

(id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)

Notice that we are no longer maintaining a friend count in each row of the People table. When we select INTEGER PRIMARY KEY as the type of our id column, we are indicating that we would like SQLite to manage this column and assign a unique numeric key to each row we insert automatically. We also add the keyword UNIQUE to indicate that we will not allow SQLite to insert two rows with the same value for name.

Now instead of creating the table Pals above, we create a table called Follows with two integer columns from_id and to_id and a constraint on the table that the combination of from_id and to_id must be unique in this table (i.e., we cannot insert duplicate rows) in our database.

When we add UNIQUE clauses to our tables, we are communicating a set of rules that we are asking the database to enforce when we attempt to insert records. We are creating these rules as a convenience in our programs, as we will see in a moment. The rules both keep us from making mistakes and make it simpler to write some of our code.

In essence, in creating this Follows table, we are modelling a “relationship” where one person “follows” someone else and representing it with a pair of numbers indicating that (a) the people are connected and (b) the direction of the relationship.

Programming with multiple tables

We will now redo the Twitter spider program using two tables, the primary keys, and the key references as described above. Here is the code for the new version of the program:

1 import urllib 2 import twurl 3 import json 4 import sqlite3 5 TWITTER_URL = 'https://api.twitter.com/1.1/friends/list.json' 6 conn = sqlite3.connect('friends.sqlitesqlite3') 7 cur = conn.cursor() 8 cur.execute('''CREATE TABLE IF NOT EXISTS People 9 (id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)''')10 cur.execute('''CREATE TABLE IF NOT EXISTS Follows11 (from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id))''')12 while True:13     acct = raw_input('Enter a Twitter account, or quit: ')14     if ( acct == 'quit' ) : break15     if ( len(acct) < 1 ) :16         cur.execute('''SELECT id, name FROM People  WHERE retrieved = 0 LIMIT 1''')   17         try:18             (id, acct) = cur.fetchone()19         except:20             print 'No unretrieved Twitter accounts found'21             continue  22       else:23             cur.execute('SELECT id FROM People WHERE name = ? LIMIT 1', (acct, ) )  24            try:25                 id = cur.fetchone()[0]26            except:27                 cur.execute('''INSERT OR IGNORE INTO People (name, retrieved)  VALUES ( ?, 0)''', ( acct, ) )28                 conn.commit()29                 if cur.rowcount != 1 :30                      print 'Error inserting account:',acct31                      continue32                 id = cur.lastrowid33       url = twurl.augment(TWITTER_URL,{
   'screen_name': acct, 'count': '20'} )34       print 'Retrieving account', acct 35       connection = urllib.urlopen(url)36       data = connection.read()37       headers = connection.info().dict38       print 'Remaining', headers['x-rate-limit-remaining']39       js = json.loads(data)40 # print json.dumps(js, indent=4)41       cur.execute('UPDATE People SET retrieved=1 WHERE name = ?', (acct, ) )42       countnew = 043       countold = 044       for u in js['users'] :45         friend = u['screen_name']46         print friend47         cur.execute('SELECT id FROM People WHERE name = ? LIMIT 1',(friend, ) )48         try:49             friend_id = cur.fetchone()[0]50             countold = countold + 151         except:52             cur.execute('''INSERT OR IGNORE INTO People (name,retrieved)  VALUES ( ?, 0)''', ( friend, ) )53             conn.commit()54             if cur.rowcount != 1 :   55                 print 'Error inserting account:',friend56                 continue     57             friend_id = cur.lastrowid58             countnew = countnew + 159         cur.execute('''INSERT OR IGNORE INTO Follows (from_id, to_id) VALUES (?, ?)''', (id, friend_id) )60      print 'New accounts=',countnew,' revisited=',countold61      conn.commit()62 cur.close()

 Constraints in database tables

1 cur.execute('''CREATE TABLE IF NOT EXISTS People (id INTEGER PRIMARY KEY, name TEXT UNIQUE, retrieved INTEGER)''')2 cur.execute('''CREATE TABLE IF NOT EXISTS Follows (from_id INTEGER, to_id INTEGER, UNIQUE(from_id, to_id))''')

We indicate that the name column in the People table must be UNIQUE. We also indicate that the combination of the two numbers in each row of the Follows table must be unique. These constraints keep us from making mistakes such as adding the same relationship more than once.

We can take advantage of these constraints in the following code:

1 cur.execute('''INSERT OR IGNORE INTO People (name, retrieved) VALUES ( ?, 0)''', ( friend, ) )

We add the OR IGNORE clause to our INSERT statement to indicate that if this particular INSERT would cause a violation of the “name must be unique” rule, the database system is allowed to ignore the INSERT. We are using the database constraint as a safety net to make sure we don’t inadvertently do something incorrect.

Similarly, the following code ensures that we don’t add the exact same Follows relationship twice.

1 cur.execute('''INSERT OR IGNORE INTO Follows (from_id, to_id) VALUES (?, ?)''', (id, friend_id) )

Again, we simply tell the database to ignore our attempted INSERT if it would violate the uniqueness constraint that we specified for the Follows rows.

1 cur.execute('''INSERT OR IGNORE INTO People (name, retrieved) VALUES ( ?, 0)''', ( friend, ) )2 conn.commit()3 if cur.rowcount != 1 :4     print 'Error inserting account:',friend5     continue6 friend_id = cur.lastrowid7 countnew = countnew + 1

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.

If the INSERT is successful, we can look at cur.lastrowid to find out what valuethe database assigned to the id column in our newly created row.

Storing the friend relationship

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:

1 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:

1 Enter a Twitter account, or quit: 2 No unretrieved Twitter accounts found 3 Enter a Twitter account, or quit: drchuck 4 Retrieving http://api.twitter.com/1.1/friends ... 5 New accounts= 20 revisited= 0 6 Enter a Twitter account, or quit: 7 Retrieving http://api.twitter.com/1.1/friends ... 8 New accounts= 17 revisited= 3 9 Enter a Twitter account, or quit:10 Retrieving http://api.twitter.com/1.1/friends ...11 New accounts= 17 revisited= 312 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:

1 People: 2 (1, u'drchuck', 1) 3 (2, u'opencontent', 1) 4 (3, u'lhawthorn', 1) 5 (4, u'steve_coppin', 0) 6 (5, u'davidkocher', 0) 7 55 rows. 8 Follows: 9 (1, 2)10 (1, 3)11 (1, 4)12 (1, 5)13 (1, 6)14 60 rows.

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.

Three kinds of keys

Now that we have started building a data model putting our data into multiple linked tables and linking the rows in those tables using keys, we need to look at some terminology around keys. There are generally three kinds of keys used in a database model.

　　 A logical key is a key that the “real world” might use to look up a row. In our example data model, the name field is a logical key. It is the screen name for the user and we indeed look up a user’s row several times in the program using the name field. You will often find that it makes sense to add a UNIQUE constraint to a logical key. Since the logical key is how we look up a row from the outside world, it makes little sense to allow multiple rows with the same value in the table.
     A primary key is usually a number that is assigned automatically by the database. It generally has no meaning outside the program and is only used to link rows from different tables together. When we want to look up a row in a table, usually searching for the row using the primary key is the fastest way to find the row. Since primary keys are integer numbers, they take up very little storage and can be compared or sorted very quickly. In our data model, the id field is an example of a primary key.

    A foreign key is usually a number that points to the primary key of an associated row in a different table. An example of a foreign key in our data model is the from_id.

Using JOIN to retrieve data

　　Now that we have followed the rules of database normalization and have data separated into two tables, linked together using primary and foreign keys, we need to be able to build a SELECT that reassembles the data across the tables.

　　SQL uses the JOIN clause to reconnect these tables. In the JOIN clause you specify the fields that are used to reconnect the rows between the tables.

　　The following is an example of a SELECT with a JOIN clause:

1 SELECT * FROM Follows JOIN People 2    ON Follows.from_id = People.id WHERE People.id = 1

The JOIN clause indicates that the fields we are selecting cross both the Follows and People tables. The ON clause indicates how the two tables are to be joined: Take the rows from Follows and append the row from People where the field from_id in Follows is the same the id value in the People table.

　　The result of the JOIN is to create extra-long “metarows” which have both the fields from People and the matching fields from Follows. Where there is more than one match between the id field from People and the from_id from People, then JOIN creates a metarow for each of the matching pairs of rows, duplicating data as needed.

　　The following code demonstrates the data that we will have in the database after the multi-table Twitter spider program (above) has been run several times.

1 import sqlite3 2 conn = sqlite3.connect('spider.sqlite3') 3 cur = conn.cursor() 4 cur.execute('SELECT * FROM People') 5 count = 0 6 print 'People:' 7 for row in cur : 8     if count < 5: print row 9     count = count + 110 print count, 'rows.'11 12 cur.execute('SELECT * FROM Follows')13 count = 014 print 'Follows:'15 for row in cur :16     if count < 5: print row17     count = count + 118 print count, 'rows.'19 20 cur.execute('''SELECT * FROM Follows JOIN People21     ON Follows.to_id = People.id WHERE People.id = 2''')22 count = 023 print 'Connections for id=2:'24 for row in cur :25     if count < 5: print row26     count = count + 127 print count, 'rows.'28 cur.close()

In this program, we first dump out the People and Follows and then dump out a subset of the data in the tables joined together.

Here is the output of the program:

python twjoin.pyPeople:(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.Connections for id=2:(2, 1, 1, u'drchuck', 1)(2, 28, 28, u'cnxorg', 0)(2, 30, 30, u'kthanos', 0)(2, 102, 102, u'SomethingGirl', 0)(2, 103, 103, u'ja_Pac', 0)20 rows.

You see the columns from the People and Follows tables and the last set of rows is the result of the SELECT with the JOIN clause. In the last select, we are looking for accounts that are friends of “opencontent” (i.e., People.id=2).

In each of the “metarows” in the last select, the first two columns are from the Follows table followed by columns three through five from the People table. You can also see that the second column (Follows.to_id) matches the third column (People.id) in each of the joined-up “metarows”.

 

One common pattern when you are developing a Python program to connect to an SQLite database will be to run a Python program and check the results using the SQLite Database Browser. The browser allows you to quickly check to see if your program is working properly.

 You must be careful because SQLite takes care to keep two programs from changing the same data at the same time. For example, if you open a database in the browser and make a change to the database and have not yet pressed the “save” button in the browser, the browser “locks” the database file and keeps any other program from accessing the file. In particular, your Python program will not be able to access the file if it is locked.
So a solution is to make sure to either close the database browser or use the File menu to close the database in the browser before you attempt to access the database from Python to avoid the problem of your Python code failing because the database is locked.