Таблицы SQLite + Python: SELECT, INSERT, CREATE TABLE

Часто мы делаем маленькие проекты, которые требуют хранения данных. Мы используем для этого json, txt и другие файлы. В таких ситуациях нам может помочь база данных SQLite3, специально созданная для небольших задач. 

В этой БД не используется модель клиент-сервер, как в MySQL или PostgreSQL. База данных SQLite3 находится локально, она сама выступает в роли и клиента, и сервера. Это утверждение немного грубо, но будем придерживается его. 

Для этой базы не нужно администрирование и настройка — в этом ее плюс. SQLite3 используется в мобильных приложениях, телевизорах, дронах, в общем везде, где не требуется постоянный обмен с сервером, но нужно хранить какую-то информацию. 

В этой гайде мы разберем как можно создать базу данных, заполнять ее и получать из неё информацию. Наша программа будет выводить всю информацию в консоль.

Что потребуется:

  1. Компьютер или ноутбук 
  2. Редактор кода (У меня PyCharm)
  3. 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.

Ну и последний запрос к БД – добавление записи в таблицу: 

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

Sqlite «Создать таблицу, если она не существует» с использованием Python

Автор Пиюш Бхуджбал / 27 февраля 2022 г. 16 февраля 2023 г.

Привет, любители Python (особенно программисты, а не любители змей), вот мы и добрались до новой темы для обсуждения и реализации: — «Sqlite — создать таблицу, если она не существует, используя Python».

Теперь мы все знаем об очень известном плагине SQLite sqlite3, который взаимодействует со средой Python по умолчанию. Это помогает нам создавать приложения в реальном времени, а затем подключать их к базе данных без использования локального хоста или онлайн-сервера.

Мы можем назвать SQLite3 типом приложения, которое запускается на локальном компьютере без каких-либо настроек конфигурации. Итак, чтобы упростить задачу, мы напишем скрипт, который будет проверять, существует ли таблица. Если он не существует, он автоматически создаст его для нас. Это похоже на создание умного инструмента. Итак, давайте попробуем!

Читайте также: Проверка существования таблицы — Python SQLite3

Создание таблицы, если она не существует, с помощью Python SQLite3

Создайте папку с именем Table Creation и добавьте следующий код в файл в той же папке.

Код: 

 импорт sqlite3
connection = sqlite3.connect('database/school.db') # путь к файлу
# создать объект курсора из класса курсора
курс = соединение.курсор()
cur.execute('''
   СОЗДАТЬ ТАБЛИЦУ stud_data(
       roll_no целое,
       текст имени,
       целое число класса,
       текст разделения
   )''')
print("\nБаза данных успешно создана!!!")
# фиксируем наше соединение
соединение. коммит()
# закрываем наше соединение
соединение.закрыть()

Вывод: 

 База данных успешно создана!!!

Мы создали школьную базу данных, содержащую таблицу данных учащихся «stud_data» . В таблице четыре столбца: roll_no, имя, класс и подразделение. Когда мы визуализируем это в студии SQLite, это выглядит так:

Визуализация таблицы в студии

Удаление таблицы

Целенаправленно мы удалим таблицу, а затем создадим наш смарт-скрипт.

Код для удаления таблицы: 

 импорт sqlite3
соединение = sqlite3.connect('база данных/school.db')
connection.execute("УДАЛИТЬ ТАБЛИЦУ stud_data")
print("Ваша таблица удалена!!!")
соединение.закрыть()

Запрос DROP TABLE «table_name» отбрасывает/удаляет таблицу. Обязательно добавьте соответствующее имя таблицы.

Вывод: 

 Ваша таблица удалена!!!

Полный код для создания и удаления таблицы

Этот раздел содержит основной сценарий, который проверяет, существует ли таблица в базе данных или нет. В этом случае создается новая таблица с тем же именем и параметрами.

Код: 

 импорт sqlite3
соединение = sqlite3.connect('база данных/school.db')
курс = соединение.курсор()
пытаться:
    cur.execute("ВЫБЕРИТЕ * ИЗ stud_data")
    
    # сохранение данных в списке
    data_list = cur.fetchall()
    print('Roll_Number' + '\t Name')
    print('--------' + '\t\t-------------')
    для пункта в пунктах:
        печать (элемент [0] + ' | ' + элемент [1] + '\ t' + элемент [2])
        
кроме sqlite3.OperationalError:
    print("Такой таблицы нет: stud_data")
    if(sqlite3.OperationalError): # если возникает эта ошибка
        пытаться:
            print("Создание новой таблицы:")
            cur.execute('''
            
                СОЗДАТЬ ТАБЛИЦУ stud_data(
                roll_no целое,
                текст имени,
                целое число класса,
                текст разделения
            
            )''')
            print("Новая таблица успешно создана!!!")
            print("Вот содержимое таблицы: \n1: roll_no.  \n2: имя \n3: класс \n4:дивизия.")
  
        кроме sqlite3.Error() как e:
            print(e, "произошло")
соединение.коммит()
соединение.закрыть()

Вывод: 

 Нет такой таблицы: stud_data
Создание новой таблицы:
Новая таблица успешно создана!!!
Вот содержание таблицы:
1: рулон_номер.
2: имя
3: класс
4: деление.

Объяснение:

  1. Мы определяем два блока try. Первый проверяет, существует ли таблица или нет. Если нет, то условие if переходит к новому блоку try и создает для нас новую таблицу.
  2. В блоке первой попытки: Использование запроса SQLite:  «SELECT * FROM table_name»  пытается получить все строки и столбцы из таблицы.
  3. Если таблица отсутствует, блок try выдает sqlite.OperationalError . Блок exclude обрабатывает это. Оператор if() под ним открывает второй блок try-except.
  4. Затем второй оператор try выполняет задачу создания новой таблицы с теми же параметрами.
  5. Блок exclude проверяет любую распространенную ошибку, используя метод sqlite.Error() , и обрабатывает ее.
  6. Вторая часть кода просто выполняет запрос на создание новой таблицы stud_data и ее вставку в нашу базу данных.

Подведение итогов…

Таким образом мы можем проверить, существует ли таблица в нашей базе данных SQLite или нет. Перед внедрением решения рекомендуется понять, как работает код. Спасибо за прочтение.

SQLite: оператор CREATE TABLE AS

В этом руководстве по SQLite объясняется, как использовать SQLite Оператор CREATE TABLE AS с синтаксисом и примерами.

Описание

Оператор SQLite CREATE TABLE AS используется для создания таблицы из существующей таблицы путем копирования столбцов существующей таблицы.

Важно отметить, что при создании таблицы таким образом новая таблица будет заполнена записями из существующей таблицы (на основе инструкции SELECT).

Синтаксис

Синтаксис оператора CREATE TABLE AS в SQLite:

 СОЗДАТЬ ТАБЛИЦУ new_table КАК
  ВЫБЕРИТЕ выражения
  ИЗ существующих_таблиц
  [ГДЕ условия];

Параметры или аргументы

new_table
Имя таблицы, которую вы хотите создать.
выражения
Столбцы из существующих_таблиц , которые вы хотели бы создать в новой_таблице . Определения столбцов из перечисленных столбцов будут перенесены в созданную вами новую_таблицу .
существующие_таблицы
Существующие таблицы, из которых следует скопировать определения столбцов и связанные записи (в соответствии с предложением WHERE).
ГДЕ условия
Дополнительно. Условия, которые должны быть выполнены для копирования записей в new_table .

Примечание

  • Определения столбцов из существующих_таблиц будут скопированы в новых_таблиц .
  • new_table будет заполнена записями на основе условий в предложении WHERE.

Пример

Давайте рассмотрим пример SQLite CREATE TABLE AS, который показывает, как создать таблицу путем копирования всех столбцов из другой таблицы.

 СОЗДАТЬ ТАБЛИЦУ active_employees КАК
  ВЫБИРАТЬ *
  ОТ сотрудников
  ГДЕ найм_дата НЕ НУЛЕВАЯ;

В этом примере будет создана новая таблица с именем active_employees , которая включает все столбцы из сотрудников табл.

Если в таблице сотрудников есть записи, то новая таблица active_employees будет заполнена записями, возвращенными оператором SELECT.

Далее рассмотрим пример CREATE TABLE AS, в котором показано, как создать таблицу путем копирования выбранных столбцов из нескольких таблиц.

Например:

 СОЗДАТЬ ТАБЛИЦУ active_employees AS
 ВЫБЕРИТЕ сотрудников.employee_id КАК "active_employee_id",
 служащие.фамилия, служащие.
No related posts.