Основы работы 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 предложит перейти по предложенной ссылке для разрешения доступа к диску

database_folder.png

После того, как вы разрешите доступ к диску Google Drive, появится окно с кодом, который нужно вставить в показанное выше поле “Enter your authorization code”:

database_folder.png

После ввода кода и нажатия Enter появится сообщение о том, что диск подключен “Mounted at /content/drive/”.

database_folder.png

Подключение к базе данных

Подключаем модуль для работы с базой SQLite

import sqlite3

Создаем на диске Google каталог databases. Указываем путь к будущей (или уже существующей) базе данных.

database_folder.png

path = "./drive/My Drive/databases/mydatabase.db"

Подключаемся к базе mydatabase.db. Если этого файла нет в каталоге, то он будет создан.

conn = sqlite3.connect(path)

Создание таблицы

Создадим таблицу музыкальных альбомов (albums) с пятью столбцами:

titleartistrelease_datepublishermedia_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=('Исполнитель', 'Альбом', 'Год') )

Результат будет выглядеть так:

database_folder.png

Особенности хранения в 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-времени и обратно можно выполнять в коде, используя метод объекта datetimedatetime.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()


© 2023. All rights reserved.

Powered by Hydejack v9.1.6