Основы работы c SQLite
Работа в Google Colab с базой данных SQLite, расположенной на диске Google Drive
Google Colab позволяет выполнять программы, написанные на языке Python прямо в браузере без необходимости установки на компьютер дополнительного программного обеспечения.
Для использования Google Colab необходимо иметь учетную запись Google. Если вы используете смартфон на платформе Android, то такая учетная запись у вас уже есть, однако для безопасности вашей основной учетной записи рекомендуется создать новую учетную запись на время выполнения лабораторных работ.
Адрес Google Colab: https://colab.research.google.com.
Доступ к Google Drive для хранения базы данных
База данных может храниться локально, на том же компьютере, на котором запускается прикладное программное обеспечение для работы с базой данных или на удаленном компьютере.
Google Colab (точнее Python, запускаемый в среде Google Colab) позволяет работать и с условно «локальной» базой данных и с базой данных на удаленном сервере. В первом случае может использоваться СУБД SQLite, которая будет хранится на облачном диске Google Drive. Для среды Google Colab это будет локальная БД.
Для доступа к диску Google Drive сервису Google Colab необходимо дать разрешение на подключение к диску. К сожалению Google Colab через каждые 12 часов «забывает» полученные ранее разрешения, поэтому через каждые 12 часов необходимо заново разрешать доступ Google Colab к вашему облачному диску. Данные (файл базы данных SQLite) в Google Colab конечно сохраняются.
Для подключения и разрешения доступа Google Colab к диску Google Drive можно использовать следующий код:
from google.colab import drive
drive.mount('/content/drive')
Google Colab предложит перейти по предложенной ссылке для разрешения доступа к диску
После того, как вы разрешите доступ к диску Google Drive, появится окно с кодом, который нужно вставить в показанное выше поле “Enter your authorization code”:
После ввода кода и нажатия Enter появится сообщение о том, что диск подключен “Mounted at /content/drive/”.
Подключение к базе данных
Подключаем модуль для работы с базой SQLite
import sqlite3
Создаем на диске Google каталог databases. Указываем путь к будущей (или уже существующей) базе данных.
path = "./drive/My Drive/databases/mydatabase.db"
Подключаемся к базе mydatabase.db. Если этого файла нет в каталоге, то он будет создан.
conn = sqlite3.connect(path)
Создание таблицы
Создадим таблицу музыкальных альбомов (albums) с пятью столбцами:
title | artist | release_date | publisher | media_type |
---|---|---|---|---|
Наименование альбома | Исполнитель | Дата выхода | Издатель | Тип носителя |
# Создаем объект типа cursor для доступа к данным
cursor = conn.cursor()
# Создание простейшей таблицы, все поля (столбцы) которой имеют тип text
cursor.execute("CREATE TABLE albums (title text, artist text, release_date text, publisher text, media_type text)")
# Подтверждаем изменения (обязательно)
conn.commit()
# Закрываем курсор
cursor.close()
# Закрываем соединение (рекомендуется)
conn.close()
Добавление записей в таблицу
Добавление в таблицу двух строк:
conn = sqlite3.connect(path)
cursor = conn.cursor()
sql = "INSERT INTO albums VALUES (?, ?, ?, ?, ?)"
val1 = ("The Serpen't Egg", "Dead Can Dance", "1988", "4AD", "CD")
val2 = ("Everyday Is Christmas", "SIA", "2017", "Atlantic", "CD")
cursor.execute(sql, val1)
cursor.execute(sql, val2)
conn.commit()
cursor.close()
conn.close()
Запрос данных из таблицы
conn = sqlite3.connect(path)
cursor = conn.cursor()
sql = "SELECT artist, title, release_date from albums"
cursor.execute(sql)
for i in cursor:
print(i)
cursor.close()
conn.close()
Для наглядного представления табличных данных можно использовать библиотеку pandas:
import pandas as pd
conn = sqlite3.connect(path)
cursor = conn.cursor()
sql = "SELECT artist, title, release_date from albums"
cursor.execute(sql)
# Загружаем все результаты в список списков rows
rows = cursor.fetchall()
cursor.close()
conn.close()
Создаем объект DataFrame на основе списка rows, указывая наименования столбцов (columns=…):
pd.DataFrame( rows, columns=('Исполнитель', 'Альбом', 'Год') )
Результат будет выглядеть так:
Особенности хранения в SQLite значений дат
При работе с базой данных SQLite необходимо учитывать, что даты в таблицах SQLite хранятся в виде целого числа, которое представляет собой количество секунд прошедших с начала (по Гринвичу) первого января 1970 года – Unix-время (этот момент называют Unix-эпохой). Эту особенность представления даты и времени в SQLite следует учитывать при выводе (форматировании) и сравнении дат.
Например, следующий сценарий выводит таблицу имен покупателей с датами совершенных ими покупок до 20 января 2019 года.
select
customers.customer_name, strftime("%d.%m.%Y", purchase_date, 'unixepoch') as purchase_date_string
from
purchases
left join
customers on customers.customer_id = purchases.customer_id
where
purchase_date < CAST(strftime('%s', '2019-01-20') AS INTEGER)
order by
purchase_date;
Результат
customer_name |purchase_date_string|
-----------------------------|--------------------|
Гусев Пров Дмитриевич |06.01.2019 |
Борисова Евдокcия Григорьевна|06.01.2019 |
Дорофеев Митофан Демидович |10.01.2019 |
В этом сценарии для преобразования из внутреннего Unix-времени в “читаемый” текстовый формат даты ГГГГ.ММ.ДД используется функция strftime, которая преобразует числовое значение даны в строку в соответствии с заданным форматом, который передается первым аргументом функции. Второй аргумент функции strftime – столбец таблицы, который содержит дату, “закодированную” целым числом. Третий аргумент – формат хранения даты ‘unixepoch’.
Для того, чтобы сравнить заданную текстовым представлением дату (в примере это ‘2019-01-20’) со значением в таблице, например, в условии WHERE, необходимо преобразовать строку в ГГГГ-ММ-ДД в строку, представляющую целое число секунд с Unix-эпохи, а затем преобразовать эту строку в целое число, которое и будет сравниваться со значениями в столбце purchase_date таблицы. Для преобразования из строки в целое число используется функция CAST(СТРОКА AS INTEGER), например результатом работы функции CAST(‘123’ AS INTEGER) будет целое число 123.
При работа с базой данных из среды Python, преобразование даты в формат Unix-времени и обратно можно выполнять в коде, используя метод объекта datetime – datetime.timestamp(), например:
import sqlite3
import datetime
from tabulate import tabulate
path = "./drive/My Drive/databases/goods.db"
conn = sqlite3.connect(path)
cursor = conn.cursor()
sql = """
SELECT
customers.customer_name, purchases.purchase_id, COUNT(purchase_items.product_id)
FROM
purchase_items
LEFT JOIN
purchases on purchase_items.purchase_id = purchases.purchase_id
LEFT JOIN
customers on customers.customer_id = purchases.customer_id
WHERE
purchases.purchase_date BETWEEN ? AND ?
GROUP BY
purchases.purchase_id
ORDER BY
customer_name
"""
cursor.execute(sql,(datetime.datetime(2019,8,1).timestamp(),
datetime.datetime(2020,1,1).timestamp() ))
rows = cursor.fetchall()
print(tabulate(rows))
cursor.close()
conn.close()