Explore

w24 March 13 Class Plan: Building the SQLite Database with Python

This Lab is a continuation of our work to build the PYTHONIC 3 Tier Model View Controller Application.

Learning Outcomes for Today’s Class:

⁠

To connect to a SQLite database in Python, you can use the sqlite3 module. Here is a step-by-step guide based on the provided search results:

Import the sqlite3 module: Begin by importing the sqlite3 module in your Python program.

Establish a connection: Use the connect() method of the sqlite3 module to connect to the SQLite database. Pass the database name as a parameter. If the specified database file does not exist, SQLite will create a new database for you.

Create a cursor: Use the cursor() method of the connection class to create a cursor object that will execute SQL queries.

Execute SQL queries: Write your SQL queries as strings and execute them using the execute() method on the cursor object.

Fetch results: After executing a query, you can fetch the results using methods like fetchall(), fetchone(), or fetchmany().

Close cursor and connection: Remember to close both the cursor and connection objects after completing your work to release resources.

Handle exceptions: It's important to catch any potential database exceptions that may occur during the connection process.

import sqlite3

try:

# Connect to the SQLite database

sqliteConnection = sqlite3.connect('your_database_name.db')

cursor = sqliteConnection.cursor()

# Execute a query

query = 'SELECT sqlite_version();'

cursor.execute(query)

# Fetch and output result

result = cursor.fetchall()

print('SQLite Version is {}'.format(result))

# Close cursor

cursor.close()

except sqlite3.Error as error:

print('Error occurred - ', error)

finally:

if sqliteConnection:

sqliteConnection.close()

print('SQLite Connection closed')

Class Plan: Today we will build a SQLite Database and interact with it using PYTHON. We like SQLite as it does not require any configuration.

In practice, at work, you will probably request the Database Administration team to set up and operate the Database to work with your Application Code. (Unless you are working for a startup or small company, if which case you will be expected to deliver the full spectrum of skills from business analysis to DEVOPS.).

Scenario: The first day of Spring is coming up in a few days, so let’s go with a seasonal Theme and make a Database for a Flower Shoppe.

Work Flow to deliver our PYTHONIC Information System:

Step 1:

Do the Business Analysis: Identify what Business Objects we need to Model in SQL.

How do we model a Business Domain in SQL?

⁠

Step 2:

⁠

A. Download and Install DBBrowser for SQLite

B. Make a directory to contain your Project assets. C:\sqlflowershoppe>

Let’s make a Database to be the Target of our PYTHON Code.

While we COULD use DDL in PYTHON to create the Database structure, in practice we would not invest the time to write Code for a one-of activity like initializing the database.

**— Let’s use DB Browser for SQLite as our Front End tool to make the database.

⁠

https://sqlitebrowser.org/dl/⁠

As you work, be attentive to how you build SQL Statements. In a few minutes, we will be embedding SQL in our PYTHON code.

The workflow is that you send a SQL statement to the Database connection.

The conn Connection object mediates that connection the Database Application running on your Operating System. Your PYTHON code runs the SQL query and receives back the RESULT Set which is a PYTHON collection Object.

Step 3:

Now let’s write a PYTHON program to interact with our Flower Shoppe Database. For today’s lab, we will focus on the Persistence Layer.

Next week, we will return to our study of PYFLASK to put an HTTP Server as the user interaction portal with the Database.

⁠

https://github.com/ProfessorBrownBear/TheSQLFlowerShoppe/⁠

⁠

Let’s make a SQLite DB using DBBrowserForSQLite on the FlowerShoppe Business Domain:

⁠

Introduction to PYTHON and SQLite

Today, we will be exploring the world of PYTHON and SQLite. During this lecture, we will cover the basics of using SQLite to create a database as our target for our PYTHON code. Although we could utilize DDL (data definition language) statements in PYTHON to define the database structure, in practice, we would probably refrain from investing the time to write code solely for the initialization of a database. Instead, we will utilize DB Browser for SQLite as our front-end tool to make the database.

To download DB Browser for SQLite, you can visit the following link: https://sqlitebrowser.org/dl/.

During your work, pay attention to how you build SQL statements. In a few minutes, we will be embedding SQL in our PYTHON code. The workflow is as follows: you send a SQL statement to the database connection, and the conn Connection object mediates that connection to the database application running on your Operating System. Your PYTHON code then runs the SQL query and receives the RESULT Set as a PYTHON collection Object.

Let's now take a look at an example of how you can interact with the database using SQL and PYTHON:

import sqlite3

# Connect to the database

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

# Execute a query

cursor = conn.cursor()

cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')

conn.commit()

# Insert a record into the database

cursor.execute('INSERT INTO users (id, name) VALUES(1, "John")')

conn.commit()

# Display all records in the database

cursor.execute('SELECT * FROM users')

rows = cursor.fetchall()

for row in rows:

print(row)

In this code, we first establish a connection to the database using the sqlite3.connect() function.

Next, we use the cursor() method to execute a query to create a table called users with two columns - id and name. The id column is of type INTEGER and serves as the primary key for the table, while the name column is of type TEXT. We commit the changes made to the database using the commit() method.

To insert a record into the users table, we use the execute() method again, but this time with INSERT INTO users (id, name) VALUES(1, "John"). This adds a new record to the table with the specified id and name values. Finally, we display all records in the database using the SELECT * FROM users query and fetchall() method.

You have successfully created and interacted with a database using SQLite and PYTHON. Next, we will explore more advanced SQLite features and incorporate them into our PYTHON code.

Background on SQL: (PowerPOINT)

⁠

Loading…⁠

⁠

Loading…⁠

⁠

Resources:

Google Doc to build our SQL statements:

⁠

https://docs.google.com/document/d/1DYxMwoanI6q7PHl9XGz99egKNvdZL8tjg7Zil7ZEgcM/edit?usp=sharing⁠

⁠

Getting started with SQLite and reviewing SQL:

done: Class Activity A:

Get started with SQLite and DBBrowser for SQLite:

Note: We do not need to download SQLite separately - it is already available in DB Browser for SQLite.

You are going to be making a Database (file on the File System) with DB Browser → Remember which directory you put it in!

⁠

Now setup the College Enrollment System so we have a database to manipulate in PYTHON

⁠

Loading…⁠

⁠

When working with SQLite in Python, students do not need to install SQLite separately. This is because Python’s standard library includes the `sqlite3` module, which provides a comprehensive interface for SQLite database interaction. Here are key points to consider:

1. **Integrated SQLite Support:** The `sqlite3` module that comes with Python acts as a wrapper around the SQLite database engine, offering full functionality to create, query, and manage SQLite databases directly within Python applications [citation:1].

2. **No Separate Installation Needed:** Since SQLite is a serverless database engine and the `sqlite3` module is part of Python's standard library, there’s no need for a separate installation process for SQLite when you already have Python installed on your system. This simplicity is particularly beneficial for educational settings and simplifies the setup for labs and projects [citation:1].

3. **Cross-Platform Compatibility:** Both Python and the integrated SQLite support are cross-platform, which means students can develop and test SQLite-based applications on any operating system (Windows, macOS, Linux) where Python is supported, without worrying about compatibility issues [citation:1].

4. **Comprehensive Functionality:** The `sqlite3` module supports a wide range of SQLite functionalities including transaction control, error handling, and connecting to databases using the SQLite DSN (Data Source Name). It offers all the capabilities needed for introductory database management and advanced operations alike [citation:1].

5. **Educational Benefits:** Integrating SQLite with Python without requiring additional installations streamlines the learning process. It allows students to focus on understanding database management concepts and SQL syntax without the overhead of managing database server software [citation:1].

In summary, the `sqlite3` module within Python provides a seamless and complete functionality for SQLite database management, making it an ideal choice for educational labs and projects focused on database interaction without the need for separate SQLite installation.

The Python sqlite3 module provides an interface for SQLite databases.

Here are some key points and resources to guide you through using this module:

- **Tutorial**: The tutorial section in the official Python documentation teaches how to use the sqlite3 module, covering topics like creating a database, opening a connection, creating tables, inserting data, and querying data[1].

- **Connection Object**: Each open SQLite database is represented by a Connection object created using `sqlite3.connect()`. This object is used for executing SQL statements and fetching results from queries[1]. - **Cursor Object**: To interact with the database, a Cursor object is created using the connection object. The cursor is used to execute SQL statements and manage data retrieval[1]. - **Creating Tables**: Tables in SQLite are created using SQL statements like `CREATE TABLE`. Columns can be defined with specific data types[3]. - **Inserting Data**: Data can be inserted into tables using `INSERT INTO` statements. This allows adding rows of data to the database[3]. - **Reading Data**: To retrieve data from tables, SQL `SELECT` statements are used. This enables fetching specific information from the database[3]. - **SQLite Features**: SQLite is a self-contained, file-based SQL database that comes bundled with Python. It can be used for various applications without requiring a separate server process[3].

These resources provide detailed instructions on how to work with SQLite databases in Python using the sqlite3 module.

Citations: [1] https://docs.python.org/3/library/sqlite3.html [2] https://www.geeksforgeeks.org/python-sqlite/ [3] https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3 [4] https://www.tutorialspoint.com/sqlite/sqlite_python.htm [5] https://docs.python.org/3.9/library/sqlite3.html

Lesson Plan: Using SQLite in Python to Create an Inventory Database

Objectives

By the end of this lesson, learners will be able to: 1. Understand the basics of SQL and its role in database management. 2. Use Python’s sqlite3 module to connect to an SQLite database. 3. Perform CRUD (Create, Read, Update, Delete) operations on a database. 4. Develop a simple inventory management system using Python and SQLite.

#### Required Materials - Computer with Python installed (version 3.x) - SQLite browser (Optional, for viewing the database) - Text editor or an IDE (Integrated Development Environment) like PyCharm or Visual Studio Code - Internet access for accessing the [Python documentation](https://docs.python.org/3/library/sqlite3.html)

#### Lesson Duration 90 minutes

#### Pre-Lesson Preparation - Ensure Python is installed on all systems. - Download and install an SQLite browser if visual database inspection is preferred.

---

### Introduction (15 minutes)

#### Hook: Discuss the importance of databases in modern applications, focusing on how they allow for efficient data retrieval, storage, and manipulation. Highlight SQLite's simplicity and how it integrates directly into applications, making it an excellent choice for lightweight projects.

#### Overview of SQL: Quickly outline what SQL is and its importance ([citation:2]). Emphasize that SQL (Structured Query Language) is a standardized language used to manipulate and manage databases. SQL commands such as `SELECT`, `INSERT`, `UPDATE`, and `DELETE` will be used in today’s project.

#### Introduction to SQLite and `sqlite3` Python Module: Explain SQLite's role as a lightweight, disk-based database that doesn’t require a separate server process ([citation:2]). Introduce Python’s `sqlite3` module as a way to interact with SQLite databases directly from Python ([citation:1]).

---

### Main Activity (60 minutes)

#### Part 1: Getting Started with SQLite in Python (15 minutes) - **Step 1:** Demonstrate how to import the sqlite3 module and establish a connection with an SQLite database ([citation:1]). - **Step 2:** Show how to create a new SQLite database and a table to hold inventory items. Explain the concept of SQL commands and execute a `CREATE TABLE` statement ([citation:1][citation:2]).

#### Part 2: Implementing CRUD Operations (30 minutes) - **Create (Add Item):** Walk through adding new items to the inventory database using `INSERT INTO` SQL command ([citation:1]). - **Read (List and Search Items):** Show how to retrieve items with the `SELECT` statement. Include listing all items and searching for specific items based on criteria like price or quantity ([citation:2]). - **Update (Modify Item Details):** Explain updating item details using the `UPDATE` command and underscore the importance of using placeholders (`?`) to prevent SQL injection ([citation:1]). - **Delete (Remove Items):** Cover deleting items from the inventory using the `DELETE` command ([citation:2]).

#### Part 3: Building a Simple Inventory System (15 minutes) - Combine the CRUD operations into a simple menu-driven inventory management system that allows users to add, list, update, search for, and delete items ([citation:1]).

---

### Conclusion (15 minutes)

#### Recap and Reflect: - Recap the key points covered in the lesson: SQL basics, SQLite integration with Python, and CRUD operations. - Discuss how the skills learned can be applied to broader projects involving data storage and manipulation.

#### Extend Learning: - Encourage learners to add more features to the inventory system, such as categorization, improved search functionality, or a graphical user interface (GUI). - Suggest exploring other Python modules for database interaction, like SQLAlchemy for larger applications.

#### Closing: - Stress the importance of practice in mastering database management. - Open the floor for any questions and provide guidance on further resources to explore.

---

This lesson plan provides a structured approach to teaching SQLite with Python, emphasizing practical application through the creation of an inventory database. It blends theoretical knowledge with hands-on activities to ensure a comprehensive learning experience.

Python Code Lab: Accessing and Displaying Table Fields from an SQLite Database

In this code lab, we will learn how to connect to a specified SQLite database in Python and print out all the fields (column names) in the tables, along with the data contained within those tables.

Prerequisites

Ensure Python is installed on your system. This lab uses Python 3.x and does not require additional installation of SQLite, as it comes integrated with Python via the `sqlite3` module.

Objectives

1. Establish a connection to a specified SQLite database. 2. Retrieve and print all table names in the database. 3. For each table, print out all field (column) names. 4. Display all data contained within each table.

Step 1: Connecting to the Database

First, let's create a connection to our SQLite database. If the specified database file does not exist, SQLite will automatically create it.

```python import sqlite3

# Function to create a connection to the specified database def create_connection(db_file): conn = None try: conn = sqlite3.connect(db_file) print("Connected to SQLite Database") except sqlite3.Error as e: print(e) return conn ```

Step 2: Fetching All Table Names

We'll define a function to list all tables present in our connected SQLite database.

```python def list_tables(conn): cursor = conn.cursor() query = "SELECT name FROM sqlite_master WHERE type='table';" try: cursor.execute(query) tables = cursor.fetchall() print("List of tables:") for table in tables: print(table[0]) return tables except sqlite3.Error as e: print(e) ```

Step 3: Retrieving and Printing All Fields from Each Table

Now, for each table in the database, we'll retrieve and print all field names using the description of the executed `SELECT *` query. Then, let's print all the data within each table.

```python def print_table_details(conn, tables): cursor = conn.cursor() for table in tables: print(f"\nColumns in {table[0]} table:") cursor.execute(f"SELECT * FROM {table[0]}") # Retrieving column names columns = [column[0] for column in cursor.description] for column in columns: print(column) # Displaying the data in the table print("\nData in table:") for row in cursor.fetchall(): print(row) ```

Step 4: Main Function to Execute Our Code

Next →You will assemble all this code together in a main function that connects to a database, lists all tables, and prints their details.

```python def main(): database = "path_to_your_database.db" # Specify your SQLite database file here # Create a database connection conn = create_connection(database) with conn: tables = list_tables(conn) if tables: print_table_details(conn, tables)

if __name__ == '__main__': main() ```

Replace `"path_to_your_database.db"` with the path to your SQLite database file. This script connects to the database, lists all tables, and for each table, prints the column names and all rows of data contained within.

Conclusion

By following these steps, you have learned how to interact with an SQLite database using Python to retrieve and display structure and content information about its tables. This skill is foundational for any tasks involving data analysis, migration, or management in Python using SQLite databases.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.

w24 March 13 Class Plan: Building the SQLite Database with Python

This Lab is a continuation of our work to build the PYTHONIC 3 Tier Model View Controller Application.

Learning Outcomes for Today’s Class:

To connect to a SQLite database in Python, you can use the sqlite3 module. Here is a step-by-step guide based on the provided search results:

import sqlite3

Class Plan: Today we will build a SQLite Database and interact with it using PYTHON. We like SQLite as it does not require any configuration.

Work Flow to deliver our PYTHONIC Information System:

Step 1:

Step 2:

Step 3:

Now let’s write a PYTHON program to interact with our Flower Shoppe Database. For today’s lab, we will focus on the Persistence Layer.

Introduction to PYTHON and SQLite

Background on SQL: (PowerPOINT)

Resources:

Getting started with SQLite and reviewing SQL:

done: Class Activity A:

Get started with SQLite and DBBrowser for SQLite:

When working with SQLite in Python, students **do not** need to install SQLite separately. This is because Python’s standard library includes the `sqlite3` module, which provides a comprehensive interface for SQLite database interaction. Here are key points to consider:

The Python sqlite3 module provides an interface for SQLite databases.

Here are some key points and resources to guide you through using this module:

Lesson Plan: Using SQLite in Python to Create an Inventory Database

Objectives

Python Code Lab: Accessing and Displaying Table Fields from an SQLite Database

Prerequisites

Step 2: Fetching All Table Names

Step 3: Retrieving and Printing All Fields from Each Table

Step 4: Main Function to Execute Our Code

Conclusion

By following these steps, you have learned how to interact with an SQLite database using Python to retrieve and display structure and content information about its tables. This skill is foundational for any tasks involving data analysis, migration, or management in Python using SQLite databases.

When working with SQLite in Python, students do not need to install SQLite separately. This is because Python’s standard library includes the `sqlite3` module, which provides a comprehensive interface for SQLite database interaction. Here are key points to consider: