Connecting Python to a SQLite database

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which line opens a SQLite database file called school.db from Python?

A conn = sqlite3.connect('school.db')

B conn = sqlite3.open('school.db')

2. Which statement must appear at the top of the program before you can use sqlite3.connect()?

A import connect

B import sqlite3

3. Which line creates the cursor you use to run SQL?

A cur = conn.cursor()

B cur = sqlite3.cursor()

4. True or false: if school.db does not exist, sqlite3.connect('school.db') creates a new empty database file.

A True

B False

5. Which line correctly finishes with the database when your program is done?

A cur.close_database()

B conn.close()

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1conn = sqlite3.connect('school.db')		AThe file name is not in quotes, so Python treats school.db as a variable name instead of a string. It should be 'school.db'.
2cur = conn.cursor()		BOpens the school.db database file (creating it if missing) and stores the connection in conn
3Before Python can use SQLite, the sqlite3 module must be brought into the program		CThe cursor is made from the connection, not the module. It should be conn.cursor().
4When the program has finished with the database, the connection should be closed		Dimport sqlite3
5conn = sqlite3.connect(school.db)		Econn.close()
6cur = sqlite3.cursor()		FCreates a cursor from the connection so the program can run SQL statements

Definitions

sqlite3.connect()

Fill in the Blanks

Complete each line of code by filling in the missing parts.

import _______

conn = sqlite3._______("school.db")

conn = sqlite3.connect("school.db") cur = conn.______()

cur._______("CREATE TABLE pet(id INTEGER)")

cur = conn.cursor() conn._____()

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A import sqlite3

B conn = sqlite3.connect("school.db")

C cur = conn.cursor

D conn.close()

Question 2

A import sqlite3

B conn = sqlite3.connect("a.db")

C cur = sqlite3.cursor()

D conn.close()

Question 3

A import sqlite

B conn = sqlite3.connect("b.db")

C conn.close()

Question 4

A import sqlite3

B conn = sqlite3.connect("pets.db")

C cur = conn.cursor()

D sql = "CREATE TABLE p(id INT"

E cur.execute(sql)

Question 5

A import sqlite3

B conn = sqlite3.connect(pets.db)

C cur = conn.cursor()

D conn.close()

Answer Key

Connecting Python to a SQLite database

Section A: Quick Questions

A — conn = sqlite3.connect('school.db')

B — import sqlite3

A — cur = conn.cursor()

A — True

B — conn.close()

Section B: Match the Pairs

conn = sqlite3.connect('school.db') → Opens the school.db database file (creating it if missing) and stores the connection in conn

cur = conn.cursor() → Creates a cursor from the connection so the program can run SQL statements

Before Python can use SQLite, the sqlite3 module must be brought into the program → import sqlite3

When the program has finished with the database, the connection should be closed → conn.close()

conn = sqlite3.connect(school.db) → The file name is not in quotes, so Python treats school.db as a variable name instead of a string. It should be 'school.db'.

cur = sqlite3.cursor() → The cursor is made from the connection, not the module. It should be conn.cursor().

Section C: Definitions

sqlite3.connect() — Opens a SQLite database file from Python and returns a connection. Call conn.cursor() on it to get a cursor for running SQL, and conn.close() when finished.

Section D: Fill in the Blanks

import sqlite3

conn = sqlite3.connect("school.db")

conn = sqlite3.connect("school.db")
cur = conn.cursor()

cur.execute("CREATE TABLE pet(id INTEGER)")

cur = conn.cursor()
conn.close()

Section E: Spot the Error

C — conn.cursor is missing its brackets, so it only refers to the method and does not call it. It should be cur = conn.cursor().

C — The cursor is created from the connection object, not from the sqlite3 module. It should be cur = conn.cursor().

A — The module is called sqlite3, including the 3. Without it Python cannot find the module. It should be import sqlite3.

D — The SQL text is missing its closing bracket after INT, so the CREATE TABLE statement is invalid. It should end with INT).

B — The database file name must be a string in quotes. Without quotes Python treats pets.db as a variable. It should be connect("pets.db").