natkaida 15 июня 2023
Python Базы данных
0
27409

🐍 Самоучитель по Python для начинающих. Часть 22: Основы работы с SQLite

Разберем основные запросы к базе данных SQLite и обсудим альтернативу реляционным СУБД – модули dbm, pickle и shelves. В конце статьи – решения 10 практических задач, связанных с выборкой данных по различным критериям, редактированием записей и удалением дубликатов.

Python может работать со всеми распространенными СУБД (системами управления базами данных):

  • Реляционными (SQL)
  • Нереляционными (NoSQL)
  • Объектно-ориентированными
  • Объектно-реляционными
  • Резидентными
  • Столбцовыми
  • Ключ-значение

В этой статье мы будем изучать приемы работы с реляционной СУБД SQLite, которая поставляется с Python. Еще мы рассмотрим базы типа «ключ-значение», которые отлично подходят для хранения данных в простых приложениях.

Что такое SQL, СУБД, SQLite и ORM

SQL (Structured Query Language) – это специальный язык запросов, который используется для создания, изменения и выборки данных в реляционных базах данных, управляемых определенной СУБД.

Система управления базами данных (СУБД) – это программное обеспечение, которое позволяет сохранять, организовывать, изменять, получать и анализировать данные. Обычно СУБД выполняют следующие функции:

  • Создание и управление структурой данных (таблицы, индексы, ограничения и т.д.)
  • Хранение данных на диске или в памяти.
  • Поиск и выборка нужных данных с помощью запросов.
  • Манипулирование данными (добавление, изменение, удаление).
  • Обеспечение сохранности и целостности данных (транзакции, резервное копирование, восстановление).
  • Обеспечение доступности и безопасности данных (права доступа, шифрование и т.д.)

Реляционные СУБД используют язык SQL для выполнения запросов к данным и управления ими. Данные в реляционных базах хранятся в виде таблиц: каждая строка представляет собой отдельную запись, а каждый столбец – отдельное поле данных.

Надо заметить, что язык SQL – не единственный способ создавать запросы к базе данных: при создании веб-приложений на базе фреймворков Django и Flask разработчики обычно используют ORM.

ORM (объектно-реляционное отображение) – это своеобразная прослойка, которая позволяет программистам работать с данными в реляционных базах данных как с объектами Python. ORM использует язык SQL под капотом, но разработчикам не нужно его знать – таблицы в базах данных создаются автоматически, а запросы имеют очень простой и понятный синтаксис. Самые популярные прослойки – Django ORM и SQLAlchemy (для Flask).

SQLite – компактная, быстрая, универсальная СУБД. Хранит данные в локальном файле, не требует отдельного сервера для выполнения запросов или управления данными: вместо этого она использует библиотеку, которая работает внутри приложения. SQLite можно использовать для мобильных, настольных и веб-приложений.

🐍 Библиотека питониста
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека питониста»
🐍🎓 Библиотека собеса по Python
Подтянуть свои знания по Python вы можете на нашем телеграм-канале «Библиотека собеса по Python»
🐍🧩 Библиотека задач по Python
Интересные задачи по Python для практики можно найти на нашем телеграм-канале «Библиотека задач по Python»

Создание базы данных в Python

Новую SQLite базу можно создать за несколько простых шагов.

1. Импортировать DB-API 2.0 интерфейс (библиотеку sqlite3):

import sqlite3

2. Создать подключение к базе данных SQLite:

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

3. Создать объект курсора:

cursor = conn.cursor()

4. Создать таблицу в базе данных:

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

5. Закрыть соединение с базой данных:

conn.close()

Полностью код выглядит так:

import sqlite3
 
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE users
            	(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.close()

Этот код создает базу данных с названием example. База включает в себя таблицу users, которая содержит поля id, name и age:

  • INTEGER означает, что в поле хранятся целочисленные значения.
  • PRIMARY KEY указывает на то, что это поле будет использоваться в качестве первичного ключа таблицы. Первичный ключ – это уникальный идентификатор, который используется для создания связи между таблицами в базе данных.
  • TEXT – класс данных, который используется для хранения строковых значений. Например, имя пользователя, почтовый адрес или наименование товара могут храниться в поле типа TEXT.

Помимо TEXT и INTEGER SQLite имеет следующие классы для хранения данных:

  • NULL – представляет нулевое значение.
  • REAL – используется для хранения чисел с плавающей точкой.
  • BLOB – применяется для хранения бинарных объектов (изображений, аудио или видео). На практике мультимедийные файлы очень редко хранят в БД – целесообразнее хранить там только ссылки на объекты.

В SQLite не предусмотрены отдельные типы данных для хранения даты и времени, но можно использовать тип данных TEXT для хранения даты и времени в виде строки в формате ISO-8601.

Некоторые другие типы данных при необходимости можно преобразовать в классы данных SQLite. К примеру, для хранения BOOLEAN значений можно использовать INTEGER, присваивая записи значения 0 или 1.

Добавление столбцов в таблицу

В предыдущем примере мы создали таблицу users, в которой хранятся имена и возраст пользователей. Добавим два новых поля – для хранения фамилии и названия факультета:

import sqlite3
 
# Открываем соединение с базой данных example.db
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
 
# Выполняем запрос на добавление новых столбцов в таблицу users
cursor.execute('''ALTER TABLE users ADD COLUMN surname TEXT''')
cursor.execute('''ALTER TABLE users ADD COLUMN faculty TEXT''')
 
# Сохраняем изменения и закрываем соединение с базой
conn.commit()
conn.close()

Как узнать, что находится в базе данных

Есть два способа посмотреть, что записано в БД:

  • Выполнить специальный запрос к системной таблице sqlite_master.
  • Воспользоваться визуальным браузером/редактором.

Начнем с первого способа – напишем и выполним запрос, чтобы узнать, какие поля (столбцы) есть в таблице:

import sqlite3
 
# создаем соединение с нашей базой данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
 
# получаем метаданные для таблицы
cursor.execute("PRAGMA table_info(users)")
 
# выводим названия полей таблицы
fields = cursor.fetchall()
for field in fields:
	print(field[1])
 
# закрываем соединение с базой данных
conn.close()

Результат:

id
name
age
surname
faculty

Второй способ проще и удобнее – можно сразу увидеть и структуру, и содержимое БД. Нам понадобится любой визуальный редактор, поддерживающий SQLite. Самый минималистичный вариант – sqlite-gui:

Более продвинутый браузер/редактор – DB Browser:

Из многофункциональных инструментов для работы с SQLite отлично подходит базовая версия Dbeaver.

Добавление записей в БД

Внесем в базу первую запись – информацию о пользователе по имени Инна Егорова, 20 лет, с факультета прикладной математики:

import sqlite3
 
# устанавливаем соединение с базой данных
conn = sqlite3.connect('example.db')
 
# создаем курсор для выполнения операций с базой данных
cursor = conn.cursor()
 
# задаем значения для новой записи
name = 'Инна'
surname = 'Егорова'
age = 20
faculty = 'Прикладная математика'
 
# добавляем новую запись в таблицу users
cursor.execute('INSERT INTO users (name, surname, age, faculty) VALUES (?, ?, ?, ?)', (name, surname, age, faculty))
 
# сохраняем изменения в базе данных
conn.commit()
 
# закрываем соединение с базой данных
conn.close()

Результат:

Редактирование записей

Изменим возраст для пользователя с именем Инна и фамилией Егорова:

import sqlite3
 
# создаем соединение с нашей базой данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
 
# обновляем возраст пользователя
cursor.execute("UPDATE users SET age = ? WHERE name = ? AND surname = ?", (19, 'Инна', 'Егорова'))
conn.commit()
 
# закрываем соединение с базой данных
conn.close()

Результат:

Удаление записей

Напишем запрос на удаление из БД всех записей, которые содержат «Прикладная математика» в поле faculty:

import sqlite3
 
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
 
# удаляем записи, содержащие "прикладная математика" в поле faculty
cursor.execute("DELETE FROM users WHERE faculty LIKE '%Прикладная математика%'")
conn.commit()
 
# закрываем соединение с базой данных
conn.close()

Поскольку в БД была всего одна запись, и она соответствовала критерию, после выполнения запроса база опустела:

Запросы на извлечение данных из нескольких таблиц

В реальных приложениях очень часто необходимо извлекать данные из нескольких таблиц сразу. Проиллюстрируем на примере базы данных university, в которой содержатся две таблицы – students и fees. Размер оплаты за обучение (fee) снижается на 3% каждый год. Кроме того, студенты, у который средний балл успеваемости соответствует установленному критерию, могут рассчитывать на дополнительную скидку.

Создадим базу и таблицы:

import sqlite3
 
# устанавливаем соединение с базой данных university
conn = sqlite3.connect('university.db')
 
# создаем курсор для выполнения операций с базой данных
cursor = conn.cursor()
 
# создаем таблицу students
cursor.execute('''CREATE TABLE students
              	(id INTEGER PRIMARY KEY,
               	name TEXT,
               	lastname TEXT,
               	physics INTEGER,
               	math INTEGER,
               	CS INTEGER)''')
 
# создаем таблицу fees
cursor.execute('''CREATE TABLE fees
              	(year INTEGER PRIMARY KEY,
               	fee REAL,
               	average REAL,
               	discount REAL,
               	with_discount REAL)''')
 
# сохраняем изменения в базе данных
conn.commit()
 
# закрываем соединение с базой данных
conn.close()

Заполним таблицы данными:

import sqlite3
 
# устанавливаем соединение с базой данных university
conn = sqlite3.connect('university.db')
 
# создаем курсор для выполнения операций с базой данных
cursor = conn.cursor()
 
# добавляем информацию о студентах в таблицу students
students_info = [('Иван', 'Иванов', 5, 4, 4),
                 ('Анна', 'Андреева', 5, 4, 3),                 
                 ('Петр', 'Петров', 4, 4, 4),
                 ('Марина', 'Маринина', 4, 4, 4),
                 ('Сергей', 'Сергеев', 3, 3, 5),
                 ('Андрей', 'Андреев', 5, 4, 3),
                 ('Елена', 'Максимова', 5, 5, 5),
                 ('Светлана', 'Сергеева', 4, 3, 5),
                 ('Николай', 'Николаев', 3, 5, 4),
                 ('Валерия', 'Владимирова', 4, 5, 5),
                 ('Дмитрий', 'Дмитриев', 4, 4, 5),
                 ('Алексей', 'Алексеев', 4, 4, 4),
                 ('Инна', 'Ильина', 3, 3, 5),
                 ('Максим', 'Максимов', 5, 5, 5),
                 ('Егор', 'Егоров', 3, 3, 3),
                 ('Ольга', 'Артемова', 5, 5, 5),
                 ('Илья', 'Ильин', 3, 3, 5),
                 ('Олег', 'Олегов', 4, 3, 8),
                 ('Кирилл', 'Кириллов', 5, 5, 5),
                 ('Владимир', 'Владимиров', 3, 5, 5),
                 ('Артем', 'Артемов', 5, 5, 5),
                 ('Михаил', 'Михайлов', 4, 4, 4)]
cursor.executemany('INSERT INTO students (name, lastname, physics, math, CS) VALUES (?, ?, ?, ?, ?)', students_info)
 
# добавляем записи о стоимости обучения в таблицу fees
fee = 205000
average = 4.0
discount = 0.01
with_discount = fee * (100 - discount)
for year in range(1, 6):
    cursor.execute('INSERT INTO fees (year, fee, average, discount, with_discount) VALUES (?, ?, ?, ?, ?)', (year, fee, average, discount, with_discount))
    fee = round(fee * 0.97, 2)
    average += 0.1
    discount += 0.01
    with_discount = round(fee * 0.97 + discount, 2)
 
# сохраняем изменения в базе данных
conn.commit()
 
# закрываем соединение с базой данных
conn.close()

Чтобы выполнить запрос одновременно к таблицам students и fees, нам потребуется оператор JOIN:

import sqlite3
 
# создаем соединение с нашей базой данных
conn = sqlite3.connect('university.db')
 
# создаем курсор
cursor = conn.cursor()
 
# получаем имя, фамилию и курс
name, lastname, year = input("Введите имя, фамилию и курс (через пробел): ").split()
 
# выполняем запрос к таблицам students и fees
cursor.execute("""SELECT students.name, students.lastname, fees.with_discount, fees.fee,
                            students.physics, students.math, students.CS, fees.average
                  FROM students JOIN fees
                  WHERE students.name=:name AND students.lastname=:lastname AND fees.year=:year""",
                  {'name': name, 'lastname': lastname, 'year': year})
 
# получаем результат запроса
result = cursor.fetchone()
 
# проверяем результат запроса
if not result:
    print("Студент не найден")
else:
    name, lastname, with_discount, fee, physics, math, CS, average = result
    if (physics + math + CS) / 3 >= average:
        print(f'{name} {lastname} оплачивает за обучение на {year}-м курсе: {with_discount} руб.')
    else:
        print(f'{name} {lastname} оплачивает за обучение на {year}-м курсе: {fee} руб.')
 
# закрываем соединение с базой данных
conn.close()

Пример ввода:

Введите имя, фамилию и курс (через пробел): Кирилл Кириллов 5

Результат:

Кирилл Кириллов оплачивает за обучение на 5-м курсе: 176040.52 руб.

Альтернатива реляционной базе данных

SQL-запросы выглядят сложно (и это мы еще не рассматривали создание таблиц, связанных отношениями one to many, many to many и т.д., и написание запросов к ним!) Фреймворки Django и Flask со своими ORM, как уже упоминалось, максимально упрощают создание таблиц и выполнение запросов. А в обычном настольном Python-приложении часто можно обойтись простейшими базами типа «ключ-значение»: с ними можно работать, как с обычными словарями. Самые популярные модули, которые предоставляют интерфейс доступа к таким базам – dbm, pickle и shelves.

dbm входит в стандартную библиотеку Python. Представляет собой обертку для баз данных, которые функционируют как словари (со строковыми ключами и значениями). Такие базы можно использовать для хранения данных в простых приложениях:

import dbm
 
# создание базы данных
with dbm.open('mydb', 'c') as db:
    # добавление данных в базу данных
    db['apple'] = 'фрукт'.encode('utf-8')
    db['carrot'] = 'овощ'.encode('utf-8')
 
# чтение данных из базы данных
with dbm.open('mydb', 'r') as db:
    print(f"яблоко - это {db['apple'].decode()}")
    print(f"морковь - это {db['carrot'].decode()}")

pickle – модуль для сериализации и десериализации данных. С его помощью тоже можно создать и использовать в приложении базу данных типа «ключ-значение»:

import pickle

# создание базы данных
desserts = {'торт': 'сладкая выпечка с кремом', 'мороженое': 'замороженный сливочный десерт', 'пирог': 'выпечка со сладкой или пикантной начинкой'}
with open('mydb.pickle', 'wb') as db:
    # добавление данных в базу данных
    pickle.dump(desserts, db)

# чтение данных из базы данных
with open('mydb.pickle', 'rb') as db:
    desserts = pickle.load(db)
    print(desserts['торт'])
    print(desserts['мороженое'])
    print(desserts['пирог'])

shelve – обертка для pickle, позволяет создавать и извлекать словари Python из файлов на диске с помощью ключей:

import shelve

# создание базы данных
books = shelve.open('mydb')
books['Тургенев'] = 'Отцы и дети'
books['Достоевский'] = 'Преступление и наказание'
books['Толстой'] = 'Война и мир'
books.close()

# чтение данных из базы данных
books = shelve.open('mydb')
print(books['Тургенев'])
print(books['Достоевский'])
print(books['Толстой'])
books.close()

Практика

Задание 1

Напишите программу, которая:

Ожидаемый результат:

Решение:

import sqlite3

# создание подключения к базе данных
conn = sqlite3.connect('mydb.db')

# создание таблицы movies
conn.execute('''CREATE TABLE movies
                (название TEXT,
                 рейтинг REAL,
                 год INTEGER,
                 режиссер TEXT,
                 жанр TEXT);''')
# подключение к базе данных
conn = sqlite3.connect('mydb.db')

# данные о фильмах
movies_data = [
    ('Побег из Шоушенка', 9.3, 1994, 'Фрэнк Дарабонт', 'драма'),
    ('Крестный отец', 9.2, 1972, 'Фрэнсис Форд Коппола', 'драма'),
    ('Крестный отец 2', 9.0, 1974, 'Фрэнсис Форд Коппола', 'драма'),
    ('Тёмный рыцарь', 9.0, 2008, 'Кристофер Нолан', 'боевик'),
    ('12 разгневанных мужчин', 9, 1957, 'Сидни Люмет', 'драма'),
    ('Список Шиндлера', 9, 1993, 'Стивен Спилберг', 'драма'),
    ('Властелин колец: Возвращение короля ', 9, 2003, 'Питер Джексон', 'фэнтези'),
    ('Криминальное чтиво', 8.9, 1994, 'Квентин Тарантино', 'криминал'),
    ('Хороший, плохой, злой', 8.8, 1966, 'Серджио Леоне', 'вестерн'),
    ('Бойцовский клуб', 8.8, 1999, 'Дэвид Финчер', 'драма'),
]

# добавление данных в таблицу movies
for movie in movies_data:
    conn.execute(f"INSERT INTO movies VALUES ('{movie[0]}', {movie[1]}, {movie[2]}, '{movie[3]}', '{movie[4]}')")

# сохранение изменений и закрытие базы данных
conn.commit()
# закрытие подключения к базе данных
conn.close()

Задание 2

Напишите программу, которая:

  • Создает базу данных mybooks и заполняет таблицу books данными о топ-25 лучших книг по версии ВВС.
  • Выводит данные о книгах, изданных не в США и не в Великобритании.

Ожидаемый результат:

20 - Лев Толстой, "Война и мир", Россия

Решение:

import sqlite3

# подключение к базе данных
conn = sqlite3.connect('mybooks.db')

# создание таблицы books
conn.execute('''CREATE TABLE books
                (id INTEGER PRIMARY KEY,
                 имя_автора TEXT,
                 фамилия_автора TEXT,
                 название_романа TEXT,
                 страна TEXT);''')

books = [
    ('Джон', 'Р. Р. Толкин', 'Властелин колец', 'Великобритания'),
    ('Джейн', 'Остин', 'Гордость и предубеждение', 'Великобритания'),
    ('Филип', 'Пулман', 'Тёмные начала', 'Великобритания'),
    ('Дуглас', 'Адамс', 'Автостопом по галактике', 'Великобритания'),
    ('Джоан', 'Роулинг', 'Гарри Поттер и Кубок огня', 'Великобритания'),
    ('Харпер', 'Ли', 'Убить пересмешника', 'США'),
    ('Алан Александр', 'Милн', 'Винни Пух', 'Великобритания'),
    ('Джордж', 'Оруэлл', '1984', 'Великобритания'),
    ('Клайв Стэйплз', 'Льюис', 'Лев, колдунья и платяной шкаф', 'Великобритания'),
    ('Шарлотта', 'Бронте', 'Джейн Эйр', 'Великобритания'),
    ('Джозеф', 'Хеллер', 'Уловка-22', 'США'),
    ('Эмили', 'Бронте', 'Грозовой перевал', 'Великобритания'),
    ('Себастьян', 'Фолкс', 'Пение птиц', 'Великобритания'),
    ('Дафна', 'Дюморье', 'Ребекка', 'Великобритания'),
    ('Джером', 'Сэлинджер', 'Над пропастью во ржи', 'США'),
    ('Кеннет', 'Грэм', 'Ветер в ивах', 'Великобритания'),
    ('Чарльз', 'Диккенс', 'Большие надежды', 'Великобритания'),
    ('Луиза Мэй', 'Олкотт', 'Маленькие женщины', 'США'),
    ('Луи', 'де Берньер', 'Мандолина капитана Корелли', 'Великобритания'),
    ('Лев', 'Толстой', 'Война и мир', 'Россия'),
    ('Маргарет', 'Митчелл', 'Унесённые ветром', 'США'),
    ('Джоан', 'Роулинг', 'Гарри Поттер и философский камень', 'Великобритания'),
    ('Джоан', 'Роулинг', 'Гарри Поттер и Тайная комната', 'Великобритания'),
    ('Джоан', 'Роулинг', 'Гарри Поттер и узник Азкабана', 'Великобритания'),
    ('Джон', 'Р. Р. Толкин', 'Хоббит, или Туда и обратно', 'Великобритания')
]

# внесение записей в таблицу
conn.executemany("INSERT INTO books (имя_автора, фамилия_автора, название_романа, страна) VALUES (?, ?, ?, ?)", books)
conn.commit()

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

# выборка данных из таблицы books по условию
cursor = conn.execute("SELECT * FROM books WHERE страна NOT IN (?, ?)", ('США', 'Великобритания'))

# вывод результатов выборки
for row in cursor:
    print(f"{row[0]} - {row[1]} {row[2]}, \"{row[3]}\", {row[4]}")
conn.close()

Задание 3

Напишите две программы:

  • Первая создает базу данных pharmacy, в которой хранятся данные о лекарственных препаратах.
  • Вторая выполняет поиск по БД и выводит все лекарственные формы запрошенного препарата.

Решение:

Создадим и заполним базу с помощью скрипта. Код для поиска всех препаратов, названия которых совпадают с запросом, выглядит так:

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('pharmacy.db')
cursor = conn.cursor()

# Запрос на поиск лекарства по названию
medicine_name = input('Введите название препарата: ')
cursor.execute('''SELECT * FROM medicines
                WHERE name=?''', (medicine_name,))

# Вывод всех имеющихся записей с найденным лекарством
rows = cursor.fetchall()
if len(rows) == 0:
    print(f'Препарат {medicine_name} не найден')
else:
    print(f'Найдены записи о препарате {medicine_name}:')
    for row in rows:
        print(row)

# Закрытие базы данных
conn.close()

Задание 4

Напишите две программы:

  1. Первая создает базу данных clothing и таблицу t-shirts в ней. В таблице должны быть поля: бренд, размер, цена, цвет. Скрипт заполняет таблицу данными о 20 футболках.
  2. Вторая выбирает из таблицы футболки размера L, стоимостью до $100, не красного цвета.

Пример вывода:

3 - Adidas, L, $19.99, синий
6 - Zara, L, $29.99, белый
9 - Calvin Klein, L, $59.99, синий
12 - Uniqlo, L, $24.99, белый

Решение:

Для создания и заполнения БД напишем скрипт. Выбор футболок по нужным параметрам выполняет этот код:

import sqlite3

# подключение к базе данных
conn = sqlite3.connect('clothing.db')

# выборка данных из таблицы t_shirts по условию
cursor = conn.execute("SELECT * FROM t_shirts WHERE размер=? AND цена < ? AND цвет != ?", ('L', 100, 'красный'))

# вывод результатов выборки
for row in cursor:
    print(f"{row[0]} - {row[1]}, {row[2]}, ${row[3]}, {row[4]}")

# закрытие базы данных
conn.close()

Задание 5

Имеется файл movies.csv. Напишите программy, которая:

  • Создает базу данных и таблицу с полями, идентичными названиям столбцов в файле movies.csv.
  • Сохраняет в базе записи обо всех фильмах.
  • Выводит все фильмы с рейтингом более 8.5, выпущенные после 1999 года.

Ожидаемый результат:

Темный рыцарь 9.0 2008 Кристофер Нолан боевик
Властелин колец: Братство кольца 8.8 2001 Питер Джексон фэнтези
Властелин колец: Две крепости 8.7 2002 Питер Джексон фэнтези
Властелин колец: Возвращение короля 8.9 2003 Питер Джексон фэнтези

Решение:

Для создания БД из csv можно воспользоваться библиотекой pandas:

import pandas as pd
import sqlite3

# Открываем файл и загружаем данные в DataFrame
df = pd.read_csv('movies.csv')

# Создаем базу данных и таблицу
conn = sqlite3.connect('mymovies.db')
c = conn.cursor()

# Создаем таблицу, используя названия столбцов в DataFrame
c.execute('''CREATE TABLE movies
             (Название фильма TEXT, Рейтинг REAL, Год INTEGER, Режиссер TEXT, Жанр TEXT)''')

# Сохраняем данные из DataFrame в таблицу
for index, row in df.iterrows():
    c.execute("INSERT INTO movies VALUES (?, ?, ?, ?, ?)", row)

# Сохраняем изменения и закрываем соединение
conn.commit()

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

# SQL-запрос на вывод всех фильмов с рейтингом более 8.0,
# выпущенных после 1999 года
query = '''SELECT *
           FROM movies
           WHERE Рейтинг > 8.5 AND Год > 1999'''

# Выполнение запроса и вывод результатов
for row in conn.execute(query):
    print(*row)
conn.close()

Задание 6

Напишите программу, которая:

  • Создает базу company и таблицу employees. В таблице есть поля для хранения имени, фамилии, возраста и даты приема на работу.
  • Заполняет таблицу данными о сотрудниках.
  • Выводит список сотрудников, отсортированный по убыванию опыта работы.

Пример вывода:

Сергей Морозов - 73 мес. опыта работы
Дмитрий Терентьев - 69 мес. опыта работы
Игорь Соколов - 67 мес. опыта работы
Артем Буров - 67 мес. опыта работы
Павел Куликов - 63 мес. опыта работы
Денис Медведев - 61 мес. опыта работы
Ольга Попова - 60 мес. опыта работы
Елена Новикова - 45 мес. опыта работы
Александр Иванов - 43 мес. опыта работы
Олег Кузьмин - 42 мес. опыта работы
Анна Смирнова - 40 мес. опыта работы
Алексей Смирнов - 38 мес. опыта работы
Дмитрий Кузнецов - 34 мес. опыта работы
Иван Петров - 29 мес. опыта работы
Екатерина Крылова - 28 мес. опыта работы
Мария Ковалева - 27 мес. опыта работы
Анастасия Григорьева - 25 мес. опыта работы
Егор Сергеев - 17 мес. опыта работы
Андрей Игнатьев - 12 мес. опыта работы
Марина Егорова - 3 мес. опыта работы
Елена Иванова - 1 мес. опыта работы

Решение:

import sqlite3
import datetime


def calculate_experience(date_hired):
    today = datetime.date.today()
    hire_date = datetime.datetime.strptime(date_hired, '%Y-%m-%d').date()
    experience = (today.year - hire_date.year) * 12 + today.month - hire_date.month
    return experience


# Добавление данных в таблицу employees
employees_data = [('Иван', 'Петров', 'менеджер', 28, '2021-01-01'),
                  ('Анна', 'Смирнова', 'разработчик', 35, '2020-02-01'),
                  ('Егор', 'Сергеев', 'технический писатель', 30, '2022-01-01'),
                  ('Александр', 'Иванов', 'дизайнер', 42, '2019-11-01'),
                  ('Ольга', 'Попова', 'финансист', 37, '2018-06-01'),
                  ('Дмитрий', 'Кузнецов', 'менеджер', 26, '2020-08-01'),
                  ('Мария', 'Ковалева', 'разработчик', 31, '2021-03-01'),
                  ('Сергей', 'Морозов', 'технический писатель', 48, '2017-05-01'),
                  ('Елена', 'Новикова', 'дизайнер', 34, '2019-09-01'),
                  ('Игорь', 'Соколов', 'финансист', 30, '2017-11-01'),
                  ('Алексей', 'Смирнов', 'менеджер', 33, '2020-04-01'),
                  ('Екатерина', 'Крылова', 'разработчик', 29, '2021-02-01'),
                  ('Денис', 'Медведев', 'технический писатель', 45, '2018-05-01'),
                  ('Елена', 'Иванова', 'дизайнер', 31, '2023-05-01'),
                  ('Дмитрий', 'Терентьев', 'финансист', 29, '2017-09-01'),
                  ('Олег', 'Кузьмин', 'менеджер', 39, '2019-12-01'),
                  ('Анастасия', 'Григорьева', 'разработчик', 33, '2021-05-01'),
                  ('Павел', 'Куликов', 'технический писатель', 47, '2018-03-01'),
                  ('Марина', 'Егорова', 'дизайнер', 28, '2023-03-01'),
                  ('Артем', 'Буров', 'финансист', 26, '2017-11-01'),
                  ('Андрей', 'Игнатьев', 'менеджер', 32, '2022-06-01')]

conn = sqlite3.connect('company.db')
cursor = conn.cursor()

# Создание таблицы employees
cursor.execute('''CREATE TABLE employees
                (id INTEGER PRIMARY KEY,
                first_name TEXT,
                last_name TEXT,
                position TEXT,
                age INTEGER,
                hire_date TEXT)''')

# Добавление данных в таблицу employees
for employee in employees_data:
    cursor.execute('INSERT INTO employees (first_name, last_name, position, age, hire_date) VALUES (?, ?, ?, ?, ?)',
                   (employee[0], employee[1], employee[2], employee[3], employee[4]))

# Извлечение данных из таблицы employees и сортировка по столбцу hire_date
cursor.execute('SELECT * FROM employees')
employees = cursor.fetchall()
employees_sorted = sorted(employees, key=lambda x: calculate_experience(x[5]), reverse=True)

# Вывод отсортированного списка сотрудников
for employee in employees_sorted:
    experience = calculate_experience(employee[5])
    print(f'{employee[1]} {employee[2]} - {experience} мес. опыта работы')

conn.commit()
conn.close()

Задание 7

Напишите программу, которая:

  • Создает базу данных с таблицей calories, содержащей данные о калорийности основных продуктов питания.
  • Получает от пользователя строку с перечислением продуктов и их веса.
  • Выводит калорийность готового блюда.

Пример ввода:

Введите названия продуктов и их вес через запятую: овсянка:30, молоко:250, сахар:10

Вывод:

Калорийность готового блюда: 306.00 ккал

Решение:

Для создания БД напишем скрипт. Подсчет калорийности и вывод результатов выполняет эта программа:

import sqlite3

con = sqlite3.connect('calories.db')
cursor = con.cursor()

input_str = input('Введите названия продуктов и их вес через запятую: ')

items = input_str.split(',')
total_calories = 0
for item in items:
    # Разбиваем строку на название продукта и его вес
    name, weight = map(str.strip, item.split(':'))
    name = name.capitalize()
    
    # Получаем калорийность продукта из базы данных
    result = cursor.execute("SELECT calories FROM calories WHERE name=?", (name,))
    row = result.fetchone()
    
    if row is not None:
        # Считаем калорийность текущего продукта
        product_calories = row[0] * float(weight) / 100
        
        # Добавляем калории этого продукта к общей калорийности
        total_calories += product_calories
    else:
        print(f"Продукт {name} отсутствует в базе данных.")

# Выводим общую калорийность готового блюда
print(f"Калорийность готового блюда: {total_calories:.2f} ккал")
con.close()

Задание 8

Студенты группы b32 прогуливали занятия и плохо сдали экзамен по математике – почти все получили оценки 2 и 3. Один из студентов решил взломать университетскую базу данных и добавить по 1 баллу себе и одногруппникам. Напишите программу, которая вернет настоящие оценки. База с оценками выглядит так, она создана при помощи этого скрипта.

Ожидаемый результат:

Решение:

import sqlite3

con = sqlite3.connect('exams.db')
cursor = con.cursor()

# Выполняем запрос для обновления оценок
cursor.execute('''
    UPDATE math SET grade = grade - 1 WHERE group_name = 'b23';
''')

# Сохраняем изменения в базе данных
con.commit()
con.close()

Задание 9

Имеется база данных retailcompany, созданная при помощи этого скрипта. Каждый день, кроме выходных, сотрудники используют CRM. Время входа и выхода за июнь 2023 регистрируется в таблице workload_june.

Напишите программу, которая определяет, сколько времени суммарно каждый сотрудник провел в CRM в июне. Нужно вывести список сотрудников, которые провели в CRM более 95 часов.

Решение:

import sqlite3
import datetime

conn = sqlite3.connect('retailcompany.db')
cursor = conn.cursor()

# Создание словаря для хранения времени пребывания каждого сотрудника
employee_time = {}

# Запрос на вычисление времени пребывания каждого сотрудника в CRM
cursor.execute('''SELECT date, first_name, last_name, time_in, time_out
                FROM workload_june''')

# Суммирование времени пребывания каждого сотрудника за каждый день
for row in cursor.fetchall():
    date_str = row[0]
    first_name = row[1]
    last_name = row[2]
    time_in_str = row[3]
    time_out_str = row[4]

    # Преобразование даты и времени в нужный формат
    date = datetime.datetime.strptime(date_str, '%d.%m.%Y').date()
    time_in = datetime.datetime.combine(date, datetime.datetime.strptime(time_in_str, '%H:%M').time())
    time_out = datetime.datetime.combine(date, datetime.datetime.strptime(time_out_str, '%H:%M').time())

    employee = (first_name, last_name)

    if employee not in employee_time:
        employee_time[employee] = {}

    if date not in employee_time[employee]:
        employee_time[employee][date] = 0

    # Вычисление времени пребывания сотрудника в системе
    time_spent = time_out - time_in
    minutes_spent = time_spent.seconds // 60
    employee_time[employee][date] += minutes_spent

# Суммирование времени пребывания каждого сотрудника за весь месяц
for employee, days in employee_time.items():
    total_minutes_spent = sum(days.values())
    total_hours_spent = total_minutes_spent // 60
    total_minutes_spent = total_minutes_spent % 60

    if total_hours_spent >= 100:
        print(f'{employee[0]} {employee[1]}: {total_hours_spent} ч. {str(total_minutes_spent).ljust(2, "0")} мин.')

# Закрытие базы данных
conn.close()

Задание 10

Имеется база данных sales, созданная с помощью этого скрипта. В базе содержится таблица cars. Дилер по ошибке внес в таблицу дублирующие записи: они полностью повторяют существующие данные, за исключением неверного регистра – названия марок и/или моделей в них всегда начинаются со строчных букв:

Напишите программу, которая:

  • Находит и выводит список записей, у которых есть дубликаты.
  • Предлагает удалить дубликаты, и в случае ответа yes – удаляет их.

Решение:

import sqlite3

con = sqlite3.connect('sales.db')
cursor = con.cursor()

# Находим дубликаты
cursor.execute('''
    SELECT brand, model, engine_volume, max_speed, price, COUNT(*) FROM items
    GROUP BY LOWER(brand), LOWER(model), engine_volume, max_speed, price
    HAVING COUNT(*) > 1
''')

# Выводим найденные дубликаты
duplicates = cursor.fetchall()
if duplicates:
    print('У этих записей есть дубликаты:')
    for row in duplicates:
        print(row[:-1])
    answer = input('Хотите удалить дубликаты? Введите yes/no ' )
    if answer.lower() == 'yes':
        # Создаем подключение к базе данных
        con = sqlite3.connect('sales.db')

        # Создаем курсор для взаимодействия с базой данных
        cursor = con.cursor()

        # Удаляем дубликаты со значением не начинающимся на заглавную букву
        cursor.execute('''
            DELETE FROM items WHERE id NOT IN (
                SELECT id FROM (
                    SELECT id, ROW_NUMBER() OVER (
                        PARTITION BY LOWER(brand), LOWER(model), color, engine_volume, max_speed, price
                        ORDER BY id
                    ) row_number
                    FROM items
                ) WHERE row_number = 1 or (row_number = 2 and brand = LOWER(brand) and model = LOWER(model))
            )
        ''')
        # Сохраняем изменения в базе данных
        con.commit()
        print('Дубликаты удалены')
    else:
        print('Не забудьте удалить дубликаты')
else:
    print('Дубликаты не найдены')

# Закрываем соединение с базой данных
con.close()

Подведем итоги

Мы научились выполнять основные операции с базой данных SQLite:

  • создавать, редактировать и удалять записи;
  • заполнять базу данными с помощью pandas;
  • делать выборки данных по различным критериям;
  • извлекать данные для последующих расчетов;
  • удалять дубликаты записей.

Работа с базами данных – обширная тема, и ее невозможно подробно рассмотреть в одной статье. За кадром осталось многое, в том числе работа с таблицами, связанными отношениями один к одному, один ко многим, многие ко многим и многие к одному. Эту тему мы подробно разберем в новом курсе по Django.

***

Содержание самоучителя

  1. Особенности, сферы применения, установка, онлайн IDE
  2. Все, что нужно для изучения Python с нуля – книги, сайты, каналы и курсы
  3. Типы данных: преобразование и базовые операции
  4. Методы работы со строками
  5. Методы работы со списками и списковыми включениями
  6. Методы работы со словарями и генераторами словарей
  7. Методы работы с кортежами
  8. Методы работы со множествами
  9. Особенности цикла for
  10. Условный цикл while
  11. Функции с позиционными и именованными аргументами
  12. Анонимные функции
  13. Рекурсивные функции
  14. Функции высшего порядка, замыкания и декораторы
  15. Методы работы с файлами и файловой системой
  16. Регулярные выражения
  17. Основы скрапинга и парсинга
  18. Основы ООП: инкапсуляция и наследование
  19. Основы ООП: абстракция и полиморфизм
  20. Графический интерфейс на Tkinter
  21. Основы разработки игр на Pygame
  22. Основы работы с SQLite
  23. Основы веб-разработки на Flask
  24. Основы работы с NumPy
  25. Основы анализа данных с Pandas
***

Материалы по теме

ВАКАНСИИ

Добавить вакансию
Разработчик C++
Москва, по итогам собеседования

ЛУЧШИЕ СТАТЬИ ПО ТЕМЕ

admin
11 декабря 2018

ООП на Python: концепции, принципы и примеры реализации

Программирование на Python допускает различные методологии, но в его основе...
admin
23 февраля 2017

SQL за 20 минут

Предлагаем вашему вниманию статью с кричащим названием "SQL за 20 минут". К...
admin
13 февраля 2017

Программирование на Python: от новичка до профессионала

Пошаговая инструкция для всех, кто хочет изучить программирование на Python...