Querying SQLite from Python
1. We use connect() in the library sqlite3 to connect the database we would like to query. Once it is connected, the target database is the only one database we are currently connecting.
import sqlite3
conn = sqlite3.connect("jobs.db") # connect() only have one parameter, which is the target database.
2.Tuples is a set of data like list. But it can not be modified after creating, and it is faster than list.
t = ('Apple', 'Banana') # t is a tuple
apple = t[0]
banana = t[1]
3. When we would like to query some result from the query, we have to
a. Connect the database with the local file by using connect() ,and store as a local variable.
b. Because the query we get is a string, we have to change them into a cursor class by using conn.cursor.
c. cursor.exercute() change the cursor class into a instance.
d. We fatch all the data from the cursor class and store it as a tuples format.
import sqlite3
conn = sqlite3.connect("jobs.db") # connect the database to local python environment
cursor = conn.cursor() #store the connected result into cursor class
query_1 = "select Major from recent_grads;" # get a query and store as a string
cursor.execute(query_1) # Execute the query, convert the results to tuples, and store as a local variable.
majors = cursor.fetchall() # fetch all the result from the cursor into a variable
print(majors[0:2])
4. Every tuples have to be executed before fetch. We can use fetchone() or fetchall() to fetch certain number of results.
query = "select Major,Major_category from recent_grads"
cursor.execute(query)
five_results = cursor.fetchmany(5)
5. close the connection by using close()
