Цели:
- Понимание основ Python и баз данных.
- Узнайте, как подключить Python к SQLite.
- Узнайте, как взаимодействовать с базами данных с помощью команд SQL.
- Применяйте изученные концепции к реальным случаям использования, связанным с Apple Inc.
Материалы:
- Python 3.6 или новее
- SQLite
- Браузер SQLite (необязательно)
Модуль 1. Введение
Продолжительность: 10 минут
Цель: Предоставить обзор Python, баз данных и SQLite и объяснить, почему они необходимы для реальных случаев использования.
Python — широко используемый высокоуровневый и универсальный язык программирования. Его удобочитаемость и простота делают его отличным выбором для различных приложений, включая веб-разработку, анализ данных, искусственный интеллект и многое другое. Одной из сильных сторон Python является его способность легко взаимодействовать с различными базами данных, что позволяет разработчикам хранить, извлекать и анализировать данные.
База данных — это структурированный набор данных, и она жизненно важна для управления и организации информации масштабируемым и эффективным способом. Многие организации, в том числе Apple Inc., полагаются на базы данных для хранения и обработки огромных объемов данных, от инвентаризации продуктов до транзакций продаж.
SQLite — это программная библиотека, которая предоставляет систему управления реляционными базами данных (RDBMS). В отличие от других СУБД, SQLite является бессерверной, автономной и не требует настройки, что делает ее идеальным выбором для многих приложений, включая встроенные системы, мобильные устройства и настольные приложения. SQLite широко используется из-за простоты использования, переносимости и низких требований к ресурсам.
В этом уроке мы научимся подключать Python к SQLite, создавать таблицы, вставлять данные и выполнять различные операции с базой данных с помощью команд SQL. Мы применим эти концепции к реальным случаям использования, связанным с Apple Inc., таким как анализ продаж продуктов и определение самых продаваемых продуктов.
К концу этого модуля у вас должно быть базовое представление о Python, базах данных, SQLite и их применимости к реальным сценариям.
Модуль 2. Настройка Python и SQLite
Продолжительность: 10 минут
Цель: узнать, как настроить SQLite и установить необходимые библиотеки для взаимодействия с базами данных SQLite с помощью Python.
Содержание:
SQLite поставляется с предустановленным Python, начиная с версии 2.5.x. Для этого урока мы будем использовать Python 3.6 или более позднюю версию. Чтобы проверить, установлен ли SQLite и готов ли он к использованию, откройте оболочку Python и введите следующие команды:
import sqlite3 print(sqlite3.version)
Этот код импортирует модуль sqlite3 и печатает версию SQLite. Если он выводит номер версии, это означает, что SQLite установлен и готов к использованию.
Теперь давайте создадим новый файл Python с именем apple_database.py
для хранения нашего кода. Мы начнем с импорта необходимой библиотеки и установки соединения с базой данных SQLite.
# Importing SQLite3 module import sqlite3 # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Closing the connection conn.close()
В этом фрагменте кода мы импортируем модуль sqlite3 и используем функцию connect()
для установления соединения с базой данных SQLite с именем «apple.db». Если база данных не существует, она будет создана автоматически. Наконец, мы закрываем соединение, используя метод close()
.
В следующем модуле мы научимся создавать таблицы и вставлять в них данные.
Модуль 3. Создание базы данных и таблиц
Продолжительность: 15 минут
Цель: узнать, как создать базу данных SQLite и определить таблицы для продуктов Apple и данных о продажах.
Теперь, когда мы настроили Python и SQLite, давайте создадим таблицы для хранения данных о продуктах и продажах Apple. Мы создадим две таблицы: одну для информации о продукте, а другую для транзакций продаж.
В apple_database.py
добавьте следующий код для создания таблиц:
# Function to create tables def create_tables(connection): cursor = connection.cursor() # Creating the 'products' table cursor.execute(''' CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price REAL NOT NULL ); ''') # Creating the 'sales' table cursor.execute(''' CREATE TABLE IF NOT EXISTS sales ( id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, sale_date TEXT NOT NULL, FOREIGN KEY (product_id) REFERENCES products (id) ); ''') # Committing the changes and closing the cursor connection.commit() cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Creating tables create_tables(conn) # Closing the connection conn.close()
В этом коде мы определяем функцию с именем create_tables()
, которая принимает соединение с базой данных в качестве аргумента. Внутри функции мы создаем объект курсора с помощью метода cursor()
. Затем мы выполняем две команды SQL для создания таблиц «продукты» и «продажи» соответственно.
В таблице «Продукты» есть следующие столбцы:
- id: уникальный идентификатор для каждого продукта (целое число, первичный ключ)
- name: Название продукта (текст, не нуль)
- категория: категория продукта (текст, не нуль)
- цена: Цена товара (реальная, не нулевая)
В таблице «Продажи» есть следующие столбцы:
- id: уникальный идентификатор для каждой продажи (целое число, первичный ключ)
- product_id: идентификатор продаваемого продукта (целое число, внешний ключ)
- количество: количество проданных товаров (целое число, не ноль)
- sale_date: Дата продажи (текст, не нуль)
После определения таблиц фиксируем изменения методом commit()
и закрываем курсор. Наконец, мы подключаемся к базе данных «apple.db», создаем таблицы, вызывая create_tables()
, и закрываем соединение. Дважды щелкните файл apple.db, чтобы просмотреть содержимое файла и подтвердить создание таблиц.
В следующем модуле мы узнаем, как вставлять данные в эти таблицы.
Модуль 4. Вставка данных в таблицы
Продолжительность: 15 минут
Цель: узнать, как вставлять данные о продуктах и продажах Apple в таблицы.
Теперь, когда мы создали таблицы «продукты» и «продажи», давайте заполним их некоторыми примерами данных. Мы начнем с вставки данных о продуктах Apple в таблицу «products».
В apple_database.py
добавьте следующую функцию для вставки данных о продукте:
# Function to insert product data def insert_product_data(connection, products): cursor = connection.cursor() for product in products: cursor.execute(''' INSERT INTO products (name, category, price) VALUES (?, ?, ?) ''', product) # Committing the changes and closing the cursor connection.commit() cursor.close() # Sample Apple product data product_data = [ ("iPhone 13", "smartphone", 799), ("iPhone 13 Pro", "smartphone", 999), ("MacBook Air", "laptop", 999), ("MacBook Pro", "laptop", 1299), ("iPad Air", "tablet", 599), ("iPad Pro", "tablet", 799), ] # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Inserting product data insert_product_data(conn, product_data) # Closing the connection conn.close()
В этом коде мы определяем функцию insert_product_data()
, которая принимает соединение с базой данных и список кортежей продуктов в качестве аргументов. Для каждого кортежа продукта мы выполняем команду SQL, чтобы вставить данные в таблицу «продукты». После вставки всех данных о товаре фиксируем изменения и закрываем курсор.
Мы также определяем примерный список продуктов Apple под названием product_data
. Каждый кортеж в списке содержит название, категорию и цену продукта. После определения образцов данных мы подключаемся к базе данных «apple.db», вставляем данные о продукте, вызывая insert_product_data()
, и закрываем соединение.
Затем давайте вставим некоторые образцы данных о продажах в таблицу «продажи». Добавьте следующую функцию в apple_database.py
:
# Function to insert sales data def insert_sales_data(connection, sales): cursor = connection.cursor() for sale in sales: cursor.execute(''' INSERT INTO sales (product_id, quantity, sale_date) VALUES (?, ?, ?) ''', sale) # Committing the changes and closing the cursor connection.commit() cursor.close() # Sample sales data sales_data = [ (1, 3, "2023-01-15"), (2, 2, "2023-01-17"), (3, 1, "2023-01-19"), (4, 5, "2023-01-22"), (5, 3, "2023-01-24"), (6, 2, "2023-01-26"), ] # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Inserting sales data insert_sales_data(conn, sales_data) # Closing the connection conn.close()
В этом коде мы определяем функцию insert_sales_data()
, которая принимает соединение с базой данных и список кортежей продаж в качестве аргументов. Для каждого кортежа продаж мы выполняем команду SQL, чтобы вставить данные в таблицу «продажи». После вставки всех данных о продажах мы фиксируем изменения и закрываем курсор.
Мы также определяем примерный список продаж под названием sales_data
. Каждый кортеж в списке содержит идентификатор продукта, количество и дату продажи транзакции продажи. После определения данных образца мы подключаемся к базе данных «apple.db», вставляем данные о продажах, вызывая insert_sales_data()
, и закрываем соединение.
На данный момент мы успешно вставили образцы данных о продуктах и продажах Apple в таблицы «продукты» и «продажи». В следующем модуле мы узнаем, как запрашивать эти данные с помощью различных команд SQL.
Модуль 5. Запрос данных из таблиц
Продолжительность: 20 минут
Цель: научиться извлекать данные из таблиц с помощью таких команд SQL, как SELECT, WHERE и ORDER BY.
Теперь, когда мы вставили данные в таблицы «продукты» и «продажи», давайте научимся запрашивать данные с помощью команд SQL. Мы начнем с извлечения всех продуктов из таблицы «продукты».
В apple_database.py
добавьте следующую функцию для получения всех продуктов:
# Function to fetch all products def fetch_all_products(connection): cursor = connection.cursor() cursor.execute('SELECT * FROM products') # Fetching all rows and printing them rows = cursor.fetchall() for row in rows: print(row) # Closing the cursor cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Fetching and printing all products fetch_all_products(conn) # Closing the connection conn.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Fetching and printing all products fetch_all_products(conn) # Closing the connection conn.close()
В этом коде мы определяем функцию с именем fetch_all_products()
, которая принимает соединение с базой данных в качестве аргумента. Мы выполняем команду SQL «SELECT * FROM products», чтобы получить все строки из таблицы «products». Затем мы получаем все строки с помощью метода fetchall()
и печатаем их. Наконец, мы закрываем курсор.
Теперь давайте получим данные о продажах для конкретного продукта. Добавьте следующую функцию в apple_database.py
:
# Fetching all rows and printing them rows = cursor.fetchall() for row in rows: print(row) # Closing the cursor cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Fetching and printing sales data for a specific product (e.g., product_id = 1) fetch_sales_by_product(conn, 1) # Closing the connection conn.close()
В этом коде мы определяем функцию с именем fetch_sales_by_product()
, которая принимает соединение с базой данных и product_id в качестве аргументов. Выполняем команду SQL 'SELECT * FROM sales WHERE product_id = ?' для получения всех строк из таблицы «продажи» с указанным product_id. Затем мы получаем все строки с помощью метода fetchall()
и печатаем их. Наконец, мы закрываем курсор.
Наконец, давайте выберем все продукты, отсортированные по их цене. Добавьте следующую функцию в apple_database.py
:
# Function to fetch all products sorted by price def fetch_products_sorted_by_price(connection): cursor = connection.cursor() cursor.execute('SELECT * FROM products ORDER BY price') # Fetching all rows and printing them rows = cursor.fetchall() for row in rows: print(row) # Closing the cursor cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Fetching and printing all products sorted by price fetch_products_sorted_by_price(conn) # Closing the connection conn.close()
В этом коде мы определяем функцию с именем fetch_products_sorted_by_price()
, которая принимает соединение с базой данных в качестве аргумента. Мы выполняем команду SQL «SELECT * FROM products ORDER BY price», чтобы получить все строки из таблицы «products», отсортированные по их цене. Затем мы получаем все строки с помощью метода fetchall()
и печатаем их. Наконец, мы закрываем курсор.
К концу этого модуля вы должны быть знакомы с запросом данных из таблиц SQLite с использованием различных команд SQL, таких как SELECT, WHERE и ORDER BY. Вы также должны понимать, как получить все продукты, данные о продажах для определенного продукта и продукты, отсортированные по их цене. Эти навыки можно применить в реальных случаях использования, связанных с Apple Inc. или любой другой организацией, которая использует базы данных для хранения и анализа данных о продуктах и продажах.
Модуль 6. Объединение таблиц и агрегирование данных
Продолжительность: 20 минут
Цель: узнать, как объединять таблицы и агрегировать данные с помощью команд SQL, таких как INNER JOIN, GROUP BY, и функций агрегирования, таких как SUM и COUNT.
В этом модуле мы узнаем, как объединять таблицы и агрегировать данные с помощью команд SQL. Это поможет нам более эффективно анализировать данные о продуктах и продажах Apple.
Во-первых, давайте получим общие продажи для каждого продукта. В apple_database.py
добавьте следующую функцию, чтобы соединить таблицы «продукты» и «продажи» и рассчитать общий объем продаж:
# Function to fetch total sales for each product def fetch_total_sales_per_product(connection): cursor = connection.cursor() cursor.execute(''' SELECT products.name, SUM(sales.quantity) as total_sales FROM products INNER JOIN sales ON products.id = sales.product_id GROUP BY products.id ''') # Fetching all rows and printing them rows = cursor.fetchall() for row in rows: print(row) # Closing the cursor cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Fetching and printing total sales for each product fetch_total_sales_per_product(conn) # Closing the connection conn.close()
В этом коде мы определяем функцию с именем fetch_total_sales_per_product()
, которая принимает соединение с базой данных в качестве аргумента. Мы выполняем команду SQL, чтобы присоединиться к таблицам «продукты» и «продажи», используя ВНУТРЕННЕЕ СОЕДИНЕНИЕ в столбце product_id. Затем мы группируем результаты по идентификатору продукта и вычисляем общий объем продаж с помощью функции СУММ().
Далее, давайте выберем самые продаваемые продукты. Добавьте следующую функцию в apple_database.py
:
# Function to fetch the top-selling products def fetch_top_selling_products(connection, limit=3): cursor = connection.cursor() cursor.execute(''' SELECT products.name, SUM(sales.quantity) as total_sales FROM products INNER JOIN sales ON products.id = sales.product_id GROUP BY products.id ORDER BY total_sales DESC LIMIT ? ''', (limit,)) # Fetching all rows and printing them rows = cursor.fetchall() for row in rows: print(row) # Closing the cursor cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Fetching and printing top-selling products (e.g., top 3) fetch_top_selling_products(conn, 3) # Closing the connection conn.close()
В этом коде мы определяем функцию с именем fetch_top_selling_products()
, которая принимает в качестве входных данных соединение с базой данных и необязательный аргумент limit. Мы выполняем команду SQL, чтобы соединить таблицы «продукты» и «продажи» с помощью ВНУТРЕННЕГО СОЕДИНЕНИЯ в столбце product_id, сгруппировать результаты по идентификатору продукта и вычислить общий объем продаж с помощью функции SUM(). Затем мы упорядочиваем результаты по общему объему продаж в порядке убывания и ограничиваем результаты указанным количеством самых продаваемых продуктов.
К концу этого модуля вы должны быть знакомы с объединением таблиц, агрегированием данных и использованием команд SQL, таких как INNER JOIN, GROUP BY, и агрегатных функций, таких как SUM и COUNT. Вы также должны понимать, как получить общий объем продаж по продукту и самые продаваемые продукты. Эти навыки можно применить в реальных случаях использования, связанных с Apple Inc. или любой другой организацией, которая использует базы данных для хранения и анализа данных о продуктах и продажах.
Модуль 7. Обновление и удаление записей
Продолжительность: 15 минут
Цель: узнать, как обновлять и удалять записи в таблицах с помощью таких команд SQL, как UPDATE и DELETE.
В этом модуле мы узнаем, как обновлять и удалять записи в таблицах «продукты» и «продажи» с помощью команд SQL.
Во-первых, давайте обновим цену конкретного продукта. В apple_database.py
добавьте следующую функцию для обновления цены продукта:
# Function to update product price def update_product_price(connection, product_id, new_price): cursor = connection.cursor() cursor.execute(''' UPDATE products SET price = ? WHERE id = ? ''', (new_price, product_id)) # Committing the changes and closing the cursor connection.commit() cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Updating the price of a specific product (e.g., product_id = 1) update_product_price(conn, 1, 849) # Closing the connection conn.close()
В этом коде мы определяем функцию update_product_price()
, которая принимает в качестве аргументов соединение с базой данных, product_id и new_price. Выполняем команду SQL 'UPDATE products SET price = ? ГДЕ идентификатор = ?' обновить цену продукта с указанным product_id. После обновления цены товара фиксируем изменения и закрываем курсор.
Далее давайте удалим конкретную запись о продажах. Добавьте следующую функцию в apple_database.py
:
# Function to delete a sales record def delete_sales_record(connection, sale_id): cursor = connection.cursor() cursor.execute(''' DELETE FROM sales WHERE id = ? ''', (sale_id,)) # Committing the changes and closing the cursor connection.commit() cursor.close() # Establishing a connection to the database conn = sqlite3.connect('apple.db') # Deleting a specific sales record (e.g., sale_id = 1) delete_sales_record(conn, 1) # Closing the connection conn.close()
В этом коде мы определяем функцию с именем delete_sales_record()
, которая принимает соединение с базой данных и sale_id в качестве аргументов. Мы выполняем команду SQL «УДАЛИТЬ ИЗ продаж, ГДЕ id =?» для удаления записи о продажах с указанным sale_id. После удаления записи фиксируем изменения и закрываем курсор.
К концу этого модуля вы должны быть знакомы с обновлением и удалением записей в таблицах SQLite с помощью команд SQL, таких как UPDATE и DELETE. Эти навыки можно применить в реальных случаях использования, связанных с Apple Inc. или любой другой организацией, которая использует базы данных для хранения и анализа данных о продуктах и продажах.
Модуль 8: Введение в SQLAlchemy
Продолжительность: 20 минут
Цель: изучить основы SQLAlchemy, объектно-реляционного преобразователя (ORM) для Python, который упрощает операции с базой данных.
SQLAlchemy — это мощный и гибкий объектно-реляционный преобразователь (ORM) для Python. Он предоставляет набор высокоуровневых API для подключения к реляционным базам данных и выполнения общих операций с базами данных без непосредственного написания запросов SQL. В этом модуле мы рассмотрим основы SQLAlchemy и предоставим практические примеры.
Сначала установите SQLAlchemy с помощью pip:
!pip install sqlalchemy
Теперь давайте создадим файл Python с именем apple_sqlalchemy.py
и импортируем необходимые библиотеки:
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker
Затем создайте движок и базовый класс для ORM:
engine = create_engine("sqlite:///apple_sqlalchemy.db", echo=False) Base = declarative_base()
Теперь давайте определим простой класс Product, который сопоставляется с таблицей products:
class Product(Base): __tablename__ = "products" id = Column(Integer, primary_key=True) name = Column(String) category = Column(String) price = Column(Integer) def __repr__(self): return f"Product(id={self.id}, name={self.name}, category={self.category}, price={self.price})"
Определив класс Product, создайте таблицу «products» в базе данных:
Base.metadata.create_all(engine)
Далее создадим фабрику сессий и сессию для взаимодействия с базой данных:
Session = sessionmaker(bind=engine) session = Session()
Создав сеанс, мы теперь можем выполнять различные операции с таблицей «продукты». Начнем с добавления нового продукта:
new_product = Product(name="iPhone 13", category="Smartphone", price=799) session.add(new_product) session.commit()
Чтобы запросить таблицу «products», мы можем использовать объект сеанса следующим образом:
products = session.query(Product).all() for product in products: print(product)
Чтобы обновить запись, вы можете изменить атрибуты объекта и зафиксировать изменения:
product_to_update = session.query(Product).filter_by(name="iPhone 13").first() product_to_update.price = 849 session.commit()
Наконец, чтобы удалить запись, вы можете использовать метод delete()
:
product_to_delete = session.query(Product).filter_by(name="iPhone 13").first() session.delete(product_to_delete) session.commit()
Не забудьте закрыть сеанс, когда закончите операции с базой данных:
session.close()
В этом модуле мы рассмотрели основы SQLAlchemy, включая создание механизма, определение схемы таблицы с использованием классов Python и выполнение общих операций с базой данных, таких как вставка, запрос, обновление и удаление с использованием ORM. SQLAlchemy позволяет использовать более Pythonic способ обработки баз данных и может упростить сложные операции с базами данных, что делает его бесценным инструментом для управления и анализа данных в реальных сценариях.
Задание: Создание образца базы данных и управление ею
Цель: учащиеся создадут новую базу данных, добавят таблицу, вставят образцы записей и выполнят различные операции, включая использование циклов для добавления и удаления элементов из базы данных.
Образец решения задания
Для выполнения этой задачи мы будем использовать библиотеки pandas
, sqlite3
и requests
. Обязательно установите pandas
и requests
перед запуском кода, используя:
!pip install pandas requests
Вот код:
import pandas as pd import sqlite3 import requests # Download data from an internet source (using a CSV file as an example) url = "https://people.sc.fsu.edu/~jburkardt/data/csv/hw_200.csv" response = requests.get(url) open("hw_200.csv", "w").write(response.text) # Read the CSV file using pandas data = pd.read_csv("hw_200.csv") # Establish a connection to the SQLite database conn = sqlite3.connect("hw_200.db") # Load the data into a table called 'sample_data' in the SQLite database data.to_sql("sample_data", conn, if_exists="replace", index=False) # Define a function to query the database def query_database(connection, query): cursor = connection.cursor() cursor.execute(query) # Fetch all rows and print them rows = cursor.fetchall() for row in rows: print(row) # Close the cursor cursor.close() # Query the database query = "SELECT * FROM sample_data WHERE Index1 > 50" query_database(conn, query) # Close the database connection conn.close()
В этом коде мы сначала загружаем CSV-файл с заданного URL-адреса и сохраняем его локально как «hw_200.csv». Затем мы используем pandas для чтения CSV-файла и сохранения его в DataFrame с именем data
. После установления соединения с базой данных SQLite с именем «hw_200.db» мы загружаем данные из DataFrame в таблицу с именем «sample_data» в базе данных.
Мы определяем функцию с именем query_database()
, которая принимает соединение с базой данных и SQL-запрос в качестве аргументов. Внутри функции мы выполняем запрос, извлекаем все строки из результата и печатаем их. Наконец, мы закрываем курсор.
Затем мы вызываем функцию query_database()
с запросом, который выбирает все строки из таблицы «sample_data», где значение Index1
больше 50. После запроса к базе данных мы закрываем соединение.
Этот код демонстрирует, как загружать данные из интернет-источника, считывать их в кадр данных pandas, загружать данные в базу данных SQLite и запрашивать базу данных.
Заключение
В заключение, это подробное руководство по Python и базам данных, в частности SQLite, снабдило вас знаниями и навыками для эффективного управления данными и их анализа в реальных сценариях. Охватывая широкий круг тем, от создания баз данных и таблиц до обработки и агрегирования данных, мы предоставили вам прочную основу для работы с базами данных в Python. Практические примеры, сосредоточенные вокруг гипотетического варианта использования Apple Inc., продемонстрировали применимость этих методов в реальных ситуациях. Мы надеемся, что по мере того, как вы продвигаетесь в своем пути, основанном на данных, вы будете продолжать развивать эти навыки и раскрывать весь потенциал Python и баз данных для принятия обоснованных решений, оптимизации процессов и достижения успеха в личных и профессиональных начинаниях.