Database Access - Quiz

Complete the code so that a connection to an SQLite database is created and assigned to db_conn

Complete the code so that a row is inserted using a safe, parameterized query instead of string formatting

Complete the code so that the SQLAlchemy engine logs all generated SQL statements

In a relational database, what does a primary key uniquely identify?

Which statement best describes a relational database?

Which Python module from the standard library is used to work with SQLite databases?

What does connection.commit() do in the context of sqlite3?

When connecting to an SQLite database with sqlite3.connect('data.db'), what happens if 'data.db' does not exist?

Which method on a sqlite3 cursor object is used to run a single SQL command?

Why are parameterized queries preferred over building SQL strings with string concatenation?

In SQLAlchemy ORM, what does a model class typically represent?

What is the role of a SQLAlchemy Session?

Which connection parameter is LEAST likely to be needed when using sqlite3 with a local .db file?

In an ORM, what is a one-to-many relationship?

Order the steps to insert a new row into an SQLite database using sqlite3.

Drag and drop to reorder, or use the arrows.

Order the steps to define a SQLAlchemy ORM model and create its table in the database.

Drag and drop to reorder, or use the arrows.

What is the output of this sqlite3 code after inserting two users?

1import sqlite3
2conn = sqlite3.connect(':memory:')
3cur = conn.cursor()
4cur.execute('CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT)')
5cur.execute('INSERT INTO users(name) VALUES(?)', ('Alice',))
6cur.execute('INSERT INTO users(name) VALUES(?)', ('Bob',))
7conn.commit()
8cur.execute('SELECT COUNT(*) FROM users')
9count = cur.fetchone()[0]
10print(count)

What is the output of this code that uses a parameterized SELECT?

1import sqlite3
2conn = sqlite3.connect(':memory:')
3cur = conn.cursor()
4cur.execute('CREATE TABLE products(id INTEGER PRIMARY KEY, name TEXT, price INTEGER)')
5cur.execute('INSERT INTO products(name, price) VALUES(?, ?)', ('Book', 10))
6cur.execute('INSERT INTO products(name, price) VALUES(?, ?)', ('Pen', 2))
7conn.commit()
8cur.execute('SELECT name FROM products WHERE price > ?', (5,))
9row = cur.fetchone()
10print(row[0])

What is the output of this code querying with SQLAlchemy ORM?

1from sqlalchemy import create_engine, Column, Integer, String
2from sqlalchemy.orm import declarative_base, sessionmaker
3engine = create_engine('sqlite:///:memory:')
4Base = declarative_base()
5class User(Base):
6    __tablename__ = 'users'
7    id = Column(Integer, primary_key=True)
8    name = Column(String)
9Base.metadata.create_all(engine)
10Session = sessionmaker(bind=engine)
11session = Session()
12user = User(name='Alice')
13session.add(user)
14session.commit()
15result = session.query(User).filter_by(name='Alice').first()
16print(result.name)

What is the output when selecting a missing row with fetchone()?

1import sqlite3
2conn = sqlite3.connect(':memory:')
3cur = conn.cursor()
4cur.execute('CREATE TABLE items(id INTEGER PRIMARY KEY, label TEXT)')
5cur.execute('INSERT INTO items(label) VALUES(?)', ('Box',))
6conn.commit()
7cur.execute('SELECT label FROM items WHERE id = ?', (2,))
8row = cur.fetchone()
9print(row is None)

What is the output of this code that updates a row and reads it back?

1import sqlite3
2conn = sqlite3.connect(':memory:')
3cur = conn.cursor()
4cur.execute('CREATE TABLE counters(id INTEGER PRIMARY KEY, value INTEGER)')
5cur.execute('INSERT INTO counters(value) VALUES(?)', (1,))
6conn.commit()
7cur.execute('UPDATE counters SET value = value + 4 WHERE id = 1')
8conn.commit()
9cur.execute('SELECT value FROM counters WHERE id = 1')
10val = cur.fetchone()[0]
11print(val)

Find the bug in this sqlite3 code that should insert and fetch a user by email.

Click on the line(s) that contain the bug.

import sqlite3

conn = sqlite3.connect(':memory:')

cur = conn.cursor()

cur.execute('CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT)')

cur.execute("INSERT INTO users(email) VALUES('%s')" % ('user@example.com',))

conn.commit()

cur.execute('SELECT email FROM users WHERE email = ?', 'user@example.com')

row = cur.fetchone()

print(row[0])

Find the bug in this SQLAlchemy ORM code that should create and query a Product.

Click on the line(s) that contain the bug.

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.orm import declarative_base, sessionmaker

engine = create_engine('sqlite:///:memory:')

Base = declarative_base()

class Product(Base):

    __tablename__ = 'products'

    id = Column(Integer, primary_key=True)

    name = Column(String)

Session = sessionmaker()

session = Session(bind=engine)

item = Product(name='Table')

session.add(item)

result = session.query(Product).filter_by(name='Table').first()

print(result.name)

Match each SQL command with its primary purpose.

Click an item on the left, then click its match on the right. Click a matched item to unmatch.

Match each SQLAlchemy concept with its description.

Click an item on the left, then click its match on the right. Click a matched item to unmatch.

Complete the code to safely insert a new customer into an SQLite database using a parameterized query.

Complete the SQLAlchemy code to query all users older than a given age.

Complete the code so that a safe SELECT is executed to get a user by name.

Complete the ORM code to add a new BlogPost and persist it.

Click the line that attempts to build an SQL query using unsafe string concatenation.

Click on the line to select.

import sqlite3

conn = sqlite3.connect(':memory:')

cur = conn.cursor()

username = input('Username: ')

query = "SELECT * FROM users WHERE name = '" + username + "'"

cur.execute(query)

row = cur.fetchone()

print(row)

Click the line responsible for actually committing changes to the SQLite database.

Click on the line to select.

import sqlite3

conn = sqlite3.connect('inventory.db')

cur = conn.cursor()

cur.execute('CREATE TABLE IF NOT EXISTS items(id INTEGER PRIMARY KEY, name TEXT)')

cur.execute('INSERT INTO items(name) VALUES(?)', ('Box',))

conn.commit()

conn.close()