5.3sqlite3

import

• Import the sqlite3 library into the environment.
• Then, use the sqlite3.connect() function to connect to jobs.db, and assign the Connection instance it returns to conn.

import sqlite3
conn = sqlite3.connect(jobs.db)

---

cursor object & tuple

Before we can execute a query, we need to express our SQL query as a string.

we use the Connection class to represent the database we're working with, we use the Cursor class to:

• Run a query against the database
• Parse the results from the database
• Convert the results to native Python objects
• Store the results within the Cursor instance as a local variable.

After running a query and converting the results to a list of tuples, the Cursor instance stores the list as a local variable.

A tuple is a core data structure that Python uses to represent a sequence of values, similar to a list. Unlike lists, tuples are immutable, which means we can't modify existing ones. Python represents each row in the results set as a tuple.

Python indexes Tuples from 0 to n-1, just like it does with lists. We access the values in a tuple using bracket notation.

t = ('Apple', 'Banana')
apple = t[0] 
banana = t[1]

---

creat a cursor and run a query

cursor = conn.cursor()

In the following code block, we:

• Write a basic select query that will return all of the values from the recent_grads table, and store this query as a string named query
• Use the Cursor method execute() to run the query against our database
• Return the full results set and store it as results
• Print the first three tuples in the list results

# SQL Query as a string
query = "select * from recent_grads"
# convert the results to tuples,
# and store as a local variable
cursor.execute(query)
# Fetch the full results set as a list of tuples
results = cursor.fatchall()
#  Display the first three results
print(results[:3])

• Write a query that returns all of the values in the Major column from the recent_grads table.
• Store the full results set (a list of tuples) in majors.
• Then, print the first three tuples in majors.

import sqlite3
conn = sqlite3.connect("jobs.db")
cursor = conn.cursor()

query = "select Major from recent_grads"
cursor.execute(query)
majors = cursor.fetchall()
print(majors[:3])

---

execute as a shortcut for running a query

So far, we've been running queries by creating a Cursor instance, and then calling the execute method on the instance.
The SQLite library actually allows us to skip creating a Cursor altogether by using the execute method within the Connection object itself.

conn = sqlite3.connect("jobs.db")
query = "select * from recent_grads;"
conn.execute(query).fetchall()

---

fetching a specific number of results

To make it easier to work with large results sets, the Cursor class allows us to control the number of results we want to retrieve at any given time. To return a single result (as a tuple), we use the Cursor method fetchone(). To return n results, we use the Cursor method fetchmany().

Each Cursor instance contains an internal counter that updates every time we retrieve results. When we call the fetchone() method, the Cursor instance will return a single result, and then increment its internal counter by 1. This means that if we call fetchone() again, the Cursor instance will actually return the second tuple in the results set (and increment by 1 again).

The fetchmany() method takes in an integer (n) and returns the corresponding results, starting from the current position. It then increments the Cursor instance's counter by n. In the following code, we return the first two results using the fetchone() method, then the next five results using the fetchmany() method.

first_result = cursor.fetchone()
second_result = cursor.fetchone()
next_five_results = cursor.fetchmany(5)

• Write and run a query that returns the Major and Major_category columns from recent_grads.
• Then, fetch the first five results and store them as five_results.

import sqlite3
conn = sqlite3.connect("jobs.db")
query = "select Major ,Major_category from recent_grads"
five_results = conn.execute(query).fetchmany(5)

---

close the database connection

Because SQLite restricts access to the database file when we're connected to a database, we need to close the connection when we're done working with it. Closing the connection allows other processes to access the database, which is important when you're in a production environment and working with other team members.

To close a connection to a database, use the Connection instance method close(). When we're working with multiple databases and multiple Connection instances, we want to make sure we call the close() method on the correct instance. After closing the connection, attempting to query the database using any linked Cursor instances will return the following error:

ProgrammingError: Cannot operate on a closed database.

Close the connection to the database using the Connection instance method close().

conn = sqlite3.connect("jobs.db")
conn.close()

---

practice

Now let's practice the entire workflow we've learned so far, from start to finish.

• Connect to the database jobs2.db, which contains the same data as jobs.db.
• Write and execute a query that returns all of the majors (Major) in reverse alphabetical order (Z to A).
• Assign the full result set to reverse_alphabetical.
• Finally, close the connection to the database.

import sqlite3
conn= sqlite3.connect("jobs2.db")
query = '''select Major from recent_grads 

order by Major desc'''
reverse_alphabetical = conn.execute(query).fetchall()