Часто мы делаем маленькие проекты, которые требуют хранения данных. Мы используем для этого json, txt и другие файлы. В таких ситуациях нам может помочь база данных SQLite3, специально созданная для небольших задач.
В этой БД не используется модель клиент-сервер, как в MySQL или PostgreSQL. База данных SQLite3 находится локально, она сама выступает в роли и клиента, и сервера. Это утверждение немного грубо, но будем придерживается его.
Для этой базы не нужно администрирование и настройка - в этом ее плюс. SQLite3 используется в мобильных приложениях, телевизорах, дронах, в общем везде, где не требуется постоянный обмен с сервером, но нужно хранить какую-то информацию.
В этой гайде мы разберем как можно создать базу данных, заполнять ее и получать из неё информацию. Наша программа будет выводить всю информацию в консоль.
Что потребуется:
- Компьютер или ноутбук
- Редактор кода (У меня PyCharm)
- Python версии 3.9 и выше
Установка sqlite3 prettytable и SQLite Studio
Установим необходимые библиотеки.
Для windows:
pip install sqlite3 prettytableДля macOS:
pip3 install sqlite3 prettytableЧтобы мы могли визуально работать с базой данных SQLite3, скачаем менеджер для БД. Я пользуюсь программой SQLiteSudio, которая достаточно проста и удобна. Переходите по ссылке тут и внизу страницы нажимайте скачать. После установки запускаете программу.
Чтобы добавить новую базу данных, открываем выпадающее меню «Базы данных» и ищем пункт «Добавить базу данных», потом добавляем путь до БД и нажимаем ОК:
Пишем код в SQLite.py
Проект будет помещаться в два файла, первый - это класс с методами взаимодействия с SQLite, второй - сама программа для пользователя.
В файле SQLite.py будем прописывать класс для работы с базой данных. Мы напишем программу для сохранения трат пользователя. Он будет самостоятельно ее заполнять, вводя день, сумму и наименование товара. Также, пользователь сможет получать сводку о потраченных деньгах за выбранный период.
import sqlite3
import datetimeИмпортируем библиотеку sqlite3 для подключения и работы с базой данных. Datetime нужен для создание timestamp (это время, которое прошло с 00:00 часов 1 января 1970 года). Создаем класс и даем ему любое имя:
class SQLite:Вызываем конструктор (подробнее о классах и конструкторах написано тут и тут) и создаем переменную класса db. Внутри неё будет объект библиотеки sqlite, туда мы передаем путь до БД. Если БД там не будет, библиотека самостоятельно создаст файл). Выносим cursor в отдельную переменную, так как к нему мы будем обращаться часто.
def __init__(self, db_path):
self.db = sqlite3.connect(db_path)
self.cursor = self.db.cursor()Метод создания таблицы не сильно нужен, так как можно создать таблицу и через менеджера БД. Но на нем мы разберем, как можно использовать **kwargs.
Метод create_table принимает в себя два аргумента: table_name – имя создаваемой таблицы и kwargs – это будут столбцы таблицы:
def create_table(self, table_name: str, **kwargs) -> None:
columns = ', '.join([' '.join(i) for i in kwargs.items()])
query = f"""CREATE TABLE IF NOT EXISTS {table_name} (id INTEGER PRIMARY KEY AUTOINCREMENT, {columns})"""
try:
self.cursor.execute(query)
self.db.commit()
except Exception as e:
print(e)На выходе мы получаем словарь:
{“date”: “INTEGER”, “amount”: “INTEGER”, “name”: “TEXT”}
Ключ — это имя колонки, а значение - её тип в SQLite. Дальше мы используем генератор, чтобы перебрать все значения key + value, выглядит он так:
[“date INTEGER”, “amount INTEGER”, “name TEXT”]
Собираем его еще раз в строку, на выходе она будет такой:
“date INTEGER, amount INTEGER, name TEXT”
После всех манипуляций собираем до конца запрос и передаем его в метод курсора execute. Все запросы к базе данных обернуты в try except, чтобы ошибки не останавливали полностью программу.
В методе get_all мы вытаскиваем все строки из базы данных и печатаем их:
def get_all(self) -> list:
query = f"""SELECT * FROM payments"""
try:
return self.cursor.execute(query).fetchall()
except Exception as e:
print(e)В методе get_by_data мы забираем траты за определенный период времени. Даты будут приходить в формате массива "год, месяц, день" (пример - [“2005”, “04”, “15”]). Потом мы прогоняем их через метод и получаем timestamp (этот метод рассмотрен ниже). Затем встраиваем в SQL-запрос нужные даты и получаем ответ:
def get_by_data(self, date_from: list, date_to: list) -> list:
date_from = self.get_timestamp(*date_from)
date_to = self.get_timestamp(*date_to)
query = f"""SELECT * FROM payments WHERE (date > {date_from}) AND (date < {date_to})"""
try:
return self.cursor.execute(query).fetchall()
except Exception as e:
print(e)Добавим немного декораторов:
@staticmethod
def get_timestamp(y, m, d):
return datetime.datetime.timestamp(datetime.datetime(int(y), int(m), int(d)))Давайте разберемся, что за декоратор staticmethod. Эта функция может находиться отдельно от класса, и мы можем вызывать её извне. Декоратор позволяет положить функцию внутрь и обращаться к ней не создавая объекта. Да, звучит запутанно, если хотите разобраться в деталях - их можно найти в этой статье.
Дальше обращаемся к библиотеке datetime и трансформируем дату в timestamp. По итогу возвращается timestamp.
Анонсы всех видео, статей и полезностей - в нашем Telegram🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
Ну и последний запрос к БД – добавление записи в таблицу:
def insert(self, date, amount, name):
date = self.get_timestamp(*date)
query = f"""INSERT INTO payments (date, amount, name) VALUES({date}, {amount}, '{name}')"""
try:
self.cursor.execute(query)
self.db.commit()
except Exception as e:
print(e)В методе get_date аналогично, как и get_timestamp, мы используем встроенную библиотеку datetime (подробнее о библиотеке можно узнать тут):
@staticmethod
def get_date(timestamp):
return datetime.datetime.fromtimestamp(timestamp).date()После удаления класса или завершения работы программы на Python вызываем деконструктор, чтобы завершить соединение с БД:
def __del__(self):
self.db.close()Взаимодействие базы данных с пользователем
Создадим таблицу для работы - передаем название таблицы и столбцы с типами данных:
db = SQLite('db.db')
db.create_table('payments', date='INTEGER', amount='INTEGER', name='TEXT')У нас уже есть класс для взаимодействия с БД, теперь напишем саму программу.
Импортируем наш класс из файла SQLite.py. Дальше идет prettytable – библиотека для опрятных и красивых таблиц в консоли:
from SQLite import SQLite
from prettytable import PrettyTableСоздаем объект класса SQLite и передаем путь к БД:
db = SQLite('db.db')Для работы с таблицей выписываем колонки, которые есть в таблице:
header = ['id', 'date', 'amount', 'name']Выводим в консоль пользовательские программы:
print("Взаимодействие с базой данных SQLite")
print("Команды: 1-Показать БД 2-Добавить новую запись 3-Поиск платеже за период 4-Еще раз вывести команды 0-Завершить программу\n")Создаем бесконечный цикл для постоянного приема команды.
Это нужно, чтобы не перезапускать программу постоянно:
while True:
command = int(input("Введите номер команды: "))
print('\n')
match command:
case 1:
print('Таблица `payments`:')
table = PrettyTable(header)
all_rows = db.get_all()
money = 0
for row in all_rows:
row = list(row)
row[1] = db.get_date(row[1])
table.add_row(row)
money += float(row[-2])
print(table)
print("Вы потратили всего: ", money)
case 2:
print('Таблица `payments`:')
print("Введите данные как в примере yyyy-mm-dd 000 name")
text = input("Новая запись: ").split(' ')
date = text[0].split('-')
db.insert(date, text[1], text[2])
print("Запись добавлена")
case 3:
print('Таблица `payments`:')
print("Введите даты периода в формате yyyy-mm-dd yyyy-mm-dd")
text = input("Новая запись: ").split(' ')
date_from = text[0].split('-')
date_to = text[1].split('-')
rows = db.get_by_data(date_from, date_to)
table = PrettyTable(header)
money = 0
for row in rows:
row = list(row)
row[1] = db.get_date(row[1])
table.add_row(row)
money += float(row[-2])
print(table)
print(f"Вы потратили: {money}\nЗа период с {'-'.join(date_from)} по {'-'.join(date_to)}")
case 4:
print("Команды: 1-Показать БД 2-Добавить новую запись 3-Поиск платеже за период 4-Еще раз вывести команды 0-Завершить программу")
case 0:
exit()
case _:
print("Такой команды не сушествует")
print('\n\n4-Еще раз вывести команды')Command – принимает от пользователя цифру ID команды и преобразовывает из строки в число.
Match – новая фишка Python (ее добавили с версии 3.10). Она прогоняет передаваемое значение по возможным вариантам и если есть совпадение, то запускается код внутри.
Case 1 – выводит все строки в таблице и составляет таблицу. Мы создаем в переменной table таблицу и передаем внутрь ее столбцы. Потом собираем в table строки, полученные из БД.
Case 2 – отображает пользователю пример как нужно добавлять новую запись в таблицу и делает запрос к БД.
Case 3 – запрашивает даты периода, по которому нужна сводка и потом совершает запрос и ответ выводит пользователю.
Case 4 – повторно выводит возможные команды.
Case 0 – завершает программу
Case _ - нижнее подчеркивание срабатывает, если команды не удовлетворяет ни одному варианту
Пример работы программы:
Заключение
SQLite - хорошая база данных для локальных задач. На ней нельзя построить большой проект, а также масштабировать его, что является ее минусом. SQLite не допускает несколько параллельных потоков получения или загрузки данных, что сильно ограничивает её использование.
Мы написали простую программу взаимодействия с SQLite. Вы можете увеличить функционал этой программы: сделать больше критериев подбора для пользователя, добавить интерфейс или красивый цветной вывод в консоль, и так далее.
Полезные ссылки
Документация SQLite: https://docs.python.org/3/library/sqlite3.html
Документация PrettyTable: https://ptable.readthedocs.io/en/latest/tutorial.html
Комментарии