Руководство по SQLite: настраиваем и учимся работать
Давно хотели познакомиться с SQLite? Мы сделали руководство по настройке и работе с инструментом, на основе статьи топового программиста.
SQLite — это автономная база данных без сервера SQL. Ричард Хипп, создатель SQLite, впервые выпустил программное обеспечение 17 августа 2000 года. С тех пор оно стало вторым по популярности ПО в мире. Его используют даже в таких важных системах, как Airbus A350. Кстати, программа вместе со всеми библиотеками весит всего несколько мегабайт.
Установка SQLite 3 клиента
Для запуска SQLite 3, в командной строке нужно прописать следующее:
$ sudo apt install sqlite3
Настройка клиента
Вы можете изменить заданные по умолчанию настройки CLI SQLite 3, отредактировав файлы ~/.sqliterc в директории. Это удобно для сохранения настроек, которые вы часто используете (рецептов). Вот пример:
$ vi ~/.sqliterc
.headers on .mode column .nullvalue .prompt ">" .timer on
Импорт CSV файлов
Вы можете импортировать CSV-данные в SQLite 3 с помощью двух команд. Первая переводит клиент в CSV, а вторая импортирует данные из CSV-файла. Предполагаемый разделитель можно менять с помощью команды .separator.
Если таблицы назначения еще не существует, первая строка CSV-файлов будет использоваться для именования каждого из столбцов. Если таблица существует, то все строки данных будут добавлены в существующую таблицу.
В качестве примера я собрал несколько аэропортов Уэльса в CSV-файл с разными кодировками.
$ vi airports.csv
都市,IATA,ICAO,空港 Aberporth,,EGFA,Aberporth 空港 Anglesey,,EGOQ,RAF Mona Anglesey,,EGOV,RAF Valley カナーボン,,EGCK,カナーボン空港 カーディフ,CWL,EGFF,カーディフ国際空港 カーディフ,,EGFC,Tremorfa ヘリポート チェスター,CEG,EGNR,Hawarden 空港 Haverfordwest,HAW,EGFE,Haverfordwest 小型飛行場 Llanbedr,,EGOD,Llanbedr 空港 Pembrey,,EGFP,Pembrey 空港 St Athan,DGX,EGDX,RAF Saint Athan スウォンジ,SWS,EGFH,スウォンジ空港 ウェルシュプール,,EGCW,ウェルシュプール空港
Я запустил в клиенте SQLite 3 новую базу данных под названием airport.db. Этого файла базы данных еще не существовало, поэтому SQLite 3 автоматически создал его для меня.
$ sqlite3 airports.db
Я переключил клиент в режим CSV, установил запятую разделителем, а затем импортировал файл airport.csv.
.mode csv .separator "," .import airports.csv airports
Теперь появляется возможность запустить команду schema в таблице новых аэропортов, видим два столбца с названиями на японском языке и ещё два — с использованием ASCII-символов.
.schema airports
CREATE TABLE airports( "都市" TEXT, "IATA" TEXT, "ICAO" TEXT, "空港" TEXT );
Без проблем можно давать команды, смешивая кодировки.
$ echo "SELECT ICAO, 空港 FROM airports;" \
| sqlite3 airports.db
EGFA|Aberporth 空港 EGOQ|RAF Mona EGOV|RAF Valley EGCK|カナーボン空港 EGFF|カーディフ国際空港 EGFC|Tremorfa ヘリポート EGNR|Hawarden 空港 EGFE|Haverfordwest 小型飛行場 EGOD|Llanbedr 空港 EGFP|Pembrey 空港 EGDX|RAF Saint Athan EGFH|スウォンジ空港 EGCW|ウェルシュプール空港
Кроме того, можно сбросить базу данных на SQL с помощью лишь одной команды.
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE airports(
"都市" TEXT,
"IATA" TEXT,
"ICAO" TEXT,
"空港" TEXT
);
INSERT INTO "airports" VALUES('Aberporth','','EGFA','Aberporth 空港');
INSERT INTO "airports" VALUES('Anglesey','','EGOQ','RAF Mona');
INSERT INTO "airports" VALUES('Anglesey','','EGOV','RAF Valley');
INSERT INTO "airports" VALUES('カナーボン','','EGCK','カナーボン空港');
INSERT INTO "airports" VALUES('カーディフ','CWL','EGFF','カーディフ国際空港');
INSERT INTO "airports" VALUES('カーディフ','','EGFC','Tremorfa ヘリポート');
INSERT INTO "airports" VALUES('チェスター','CEG','EGNR','Hawarden 空港');
INSERT INTO "airports" VALUES('Haverfordwest','HAW','EGFE','Haverfordwest 小型飛行場');
INSERT INTO "airports" VALUES('Llanbedr','','EGOD','Llanbedr 空港');
INSERT INTO "airports" VALUES('Pembrey','','EGFP','Pembrey 空港');
INSERT INTO "airports" VALUES('St Athan','DGX','EGDX','RAF Saint Athan');
INSERT INTO "airports" VALUES('スウォンジ','SWS','EGFH','スウォンジ空港');
INSERT INTO "airports" VALUES('ウェルシュプール','','EGCW','ウェルシュプール空港');
COMMIT;
Имейте в виду, что созданные файлы .db могут быть слишком большими. Во время написания этой статьи у меня получился CSV-файл с миллионом рядов и 12 столбцами, состоящий в основном из чисел и одного текстового поля. Сжатый CSV-файл с GZIP весил 41 МБ, распакованный CSV — 142 МБ, а при импорте в SQLite 3 — .db-файл — 165 МБ. Я смог с GZIP сжать файл .db до 48 МБ, но, к сожалению, SQLite 3 не может открывать базы данных, сжатые GZIP.
Создание базы данных в памяти
Локальность данных может быть значительно улучшена за счет хранения базы данных SQLite 3 в памяти, а не на диске. Ниже приведен пример, где я вычисляю 10 значений Фибоначчи и сохраняю их в базе данных SQLite 3, находящейся в памяти, с использованием Python 3.
$ sudo apt install python3 $ python3
import sqlite3
def fib(n):
a, b = 0, 1
for _ in range(n):
yield a
a, b = b, a + b
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()
with connection:
cursor.execute('''CREATE TABLE IF NOT EXISTS fib (
calculated_value INTEGER)''')
cursor.executemany('INSERT INTO fib VALUES (?)',
[(str(x),) for x in fib(10)])
cursor.execute('SELECT * FROM fib')
print(cursor.fetchall())
connection.close()
Пользовательские функции
Вы можете создавать пользовательские функции в Python, которые будут выполняться с использованием данных, находящихся внутри БД SQLite 3. Ниже приведена небольшая база данных SQLite 3:
$ sqlite3 urls.db
CREATE TABLE urls (url STRING);
INSERT INTO urls VALUES
('https://packages.debian.org/stretch/sqlite3'),
('https://docs.python.org/3/library/sqlite3.html'),
('https://sqlite.org/about.html');
Затем я создал функцию на Python, которая извлекает имя хоста из URL-адреса и выполняет действия, ориентируясь на таблицу.
$ python3
import sqlite3
from urllib.parse import urlsplit
def hostname(url):
return urlsplit(url).netloc
connection = sqlite3.connect('urls.db')
connection.create_function('hostname', 1, hostname)
cursor = connection.cursor()
cursor.execute('SELECT hostname(url) FROM urls')
print(cursor.fetchall())
Вот что выводится при вызове функции fetchall:
[(u'packages.debian.org',), (u'docs.python.org',), (u'sqlite.org',)]
Работа с несколькими базами данных
Клиент SQLite 3 способен работать с несколькими базами данных за один сеанс. Ниже я запустил клиент и подключил две базы данных.
$ sqlite3
ATTACH 'airports.db' AS airport; ATTACH 'urls.db' AS urls;
Затем я запустил команду .databases для вывода имен и мест баз данных.
.databases
seq name file --- --------------- ----------------------- 0 main 2 airport /home/mark/airports.db 3 urls /home/mark/urls.db
В качестве префикса я использую имена таблиц в моих запросах с именем, которое я назначил базе данных.
SELECT COUNT(*) FROM urls.urls;
3
Визуализация с помощью Jupyter Notebooks
Jupyter Notebooks — популярная программа для визуализации данных. Ниже можно посмотреть процесс настройки и несколько примеров визуализаций.
Для начала я установил ряд системных зависимостей.
$ sudo apt update
$ sudo apt install \
libgeos-dev \
python3-dev \
python3-pip \
python3-tk \
python3-venv
Затем я создал виртуальную среду Python, чтобы можно было отделить зависимость Python от других проектов и назвал её .taxis.
$ pyvenv .taxis $ source .taxis/bin/activate
Я обновил менеджер пакетов «pip» Python до версии 9.0.1 в этой виртуальной среде.
$ pip install --upgrade pip
Затем я установил несколько популярных Python-библиотек.
$ pip install \
https://github.com/matplotlib/basemap/archive/v1.1.0.tar.gz \
'bokeh<0.12.4' \
gmaps \
'holoviews[extras]' \
jupyter \
pandas \
Pillow
Jupyter Notebooks откроет рабочую папку на Linux-машине через HTTP, поэтому мне нужно создать отдельную рабочую папку.
$ mkdir -p ~/jupyter-working $ cd ~/jupyter-working
Затем я включил расширение gmaps и разрешил Jupyter использовать виджеты.
$ jupyter nbextension enable --py --sys-prefix gmaps $ jupyter nbextension enable --py widgetsnbextension
После этого я запустил сервер Notebook. Вы увидите URL-адрес, содержащий параметр токена. Чтобы запустить Notebook (не ПК, конечно же), откройте ссылку в веб-браузере.
$ jupyter notebook \
--ip=0.0.0.0 \
--NotebookApp.iopub_data_rate_limit=100000000
...
Copy/paste this URL into your browser when you connect for the first time,
to login with a token:
http://0.0.0.0:8888/?token=123...
Перед открытием URL-адреса я создал базу данных SQLite 3 из CSV-файла. Здесь содержится около миллиона случайных записей о поездках на такси. Чтобы экспортировать эти записи из Hive, я сделал следующее:
$ hive -e 'SET hive.cli.print.header=true;
SELECT trip_id,
cab_type,
passenger_count,
trip_distance,
fare_amount,
tip_amount,
pickup_datetime,
dropoff_datetime,
pickup_longitude,
pickup_latitude,
dropoff_longitude,
dropoff_latitude
FROM trips
WHERE RAND() <= 0.001
DISTRIBUTE BY RAND()
SORT BY RAND()
LIMIT 1000000' \
| sed 's/[\t]/,/g' \
| gzip \
> trips.csv.gz
В моём блоге есть краткие инструкции по импорту набора данных в Hive. Если использовать инструкции не на ОС Raspbian, а на других, то имена пакетов, например, для JDK, вероятно, будут отличаться.
Вот первые три строки этого CSV-файла. Обратите внимание: первая строка содержит имена столбцов.
$ gunzip -c trips.csv.gz | head -n3
trip_id, cab_type, passenger_count, trip_distance, fare_amount, tip_amount, pickup_datetime, dropoff_datetime, pickup_longitude,pickup_latitude, dropoff_longitude, dropoff_latitude 745713518, yellow, 1, 5.600, 20.50, 1.00, 2013-04-30 13:43:58, 2013-04-30 14:04:49, -73.94273100000000, 40.79017800000000, -74.00244499999999, 40.76083900000000 788379509, yellow, 1, 1.200, 6.00, 0.00, 2013-07-07 12:24:33, 2013-07-07 12:28:52, -73.95807200000000, 40.76124600000000, -73.94632400000000, 40.77708900000000
Я распаковал GZIP-файл, запустил SQLite 3, добавил trip.db в качестве параметра.
$ gunzip trips.csv.gz $ sqlite3 trips.db
Затем переключился в режим CSV, убедился в том, что разделителем является запятая, и что импортирует CSV-файл в таблицу маршрутов.
.mode csv .separator "," .import trips.csv trips
Настроили, что дальше?
С импортированными данными я открыл Notebook URL-адрес и создал Python 3 Notebook в интерфейсе Jupyter’а. Теперь необходимо вставить следующее в первую ячейку, одновременно зажать shift и кнопку выполнения.
import sqlite3
import pandas as pd
import holoviews as hv
hv.extension('bokeh')
connection = sqlite3.connect('trips.db')
Код выше будет импортировать Pandas, библиотеку Python для SQLite 3, Holoviews — библиотеку обработки данных, библиотеку визуализации, а затем инициализировать расширение Bokeh для Holoviews. Наконец, будет установлено соединение с базой данных SQLite 3 с информацией о поездках на такси.
В следующем примере я привел код, который создаст heatmap для разбивки поездок по дням и часам.
%%opts Points [tools=['hover']] (size=5) HeatMap [tools=['hover']] Histogram [tools=['hover']] Layout [shared_axes=False]
sql = """SELECT strftime('%w', pickup_datetime) as weekday,
strftime('%H', pickup_datetime) as hour,
COUNT(*) as cnt
FROM trips
GROUP BY 1, 2;"""
df = pd.read_sql_query(sql, connection)
hv.HeatMap(df)
Ниже приводится линейная диаграмма, показывающая количество поездок такси.
%matplotlib inline
sql = """SELECT date(pickup_datetime) as date,
COUNT(*) as cnt
FROM trips
GROUP BY 1
ORDER BY 1;"""
df = pd.read_sql_query(sql, connection)
df['date'] = df.date.astype('datetime64[ns]')
df.plot(x='date', y='cnt')
Чтобы построилась гистограмма, сравнивающая данные по разным цветам автомобилей, необходимо ввести информацию в новую ячейку.
%%opts Bars [stack_index=1 xrotation=90 legend_cols=7 show_legend=False show_frame=False tools=['hover']]
hv.extension('bokeh', 'matplotlib')
sql = """SELECT strftime('%m', pickup_datetime) as month,
cab_type,
COUNT(*) as cnt
FROM trips
GROUP BY 1, 2;"""
df = pd.read_sql_query(sql, connection)
table = hv.Table(df, kdims=[('month', 'month'), ('cab_type', 'cab_type')], vdims=[('cnt', 'cnt')])
table.to.bars(['month', 'cab_type'], 'cnt', [])
Ниже приводится круговая диаграмма, показывающая зависимость поездок от времени суток.
%matplotlib inline
sql = """SELECT round(strftime('%H', pickup_datetime)) as hour,
count(*) as cnt
FROM trips
GROUP BY 1;"""
df = pd.read_sql_query(sql, connection)
df.plot(kind='pie', y='cnt', legend=False)
Чтобы создать диаграмму матрицы рассеивания, выполните действия как в коде ниже. Заметьте, что это может занять несколько минут. Сначала будет показан массив данных, а потом и сам график.
%matplotlib inline
sql = """SELECT round(strftime('%H', pickup_datetime)) as hour,
round(trip_distance),
round(fare_amount),
round(tip_amount)
FROM trips;"""
df = pd.read_sql_query(sql, connection)
pd.plotting.scatter_matrix(df, figsize=(15, 15))
Я натолкнулся на два способа отображения географических точек на картах. Первый — с Matplotlib и Basemap, которые будут работать в автономном режиме, без необходимости использовать API-ключи. Ниже будут указаны точки сбора для маршрутов такси в наборе данных.
%matplotlib inline
import matplotlib.pyplot as plt
from mpl_toolkits.basemap import Basemap
sql = """SELECT ROUND(pickup_longitude, 3) as long,
ROUND(pickup_latitude, 3) as lat,
COUNT(*) as cnt
FROM trips
GROUP BY long, lat"""
df = pd.read_sql_query(sql, connection)
df = df[pd.to_numeric(df['long'], errors='coerce').notnull()]
df = df[pd.to_numeric(df['lat'], errors='coerce').notnull()]
df = df.dropna(thresh=1)
df.long = df.long.astype(float, errors='ignore').fillna(0.0)
df.lat = df.lat.astype(float, errors='ignore').fillna(0.0)
plt.figure(figsize=(20, 20))
map = Basemap(projection='merc',
llcrnrlat=40,
urcrnrlat=42,
llcrnrlon=-75,
urcrnrlon=-72,
resolution='i',
area_thresh=50,
lat_0=40.78,
lon_0=-73.96)
map.drawcountries()
map.drawcoastlines(linewidth=0.5)
map.drawstates()
map.bluemarble()
lons = df['long'].values
lats = df['lat'].values
x, y = map(lons, lats)
map.plot(x, y, 'ro', markersize=4)
plt.show()
Да, это выглядит несколько примитивно.
Следующий код построит heatmap поверх Google Maps виджета. Недостатком является то, что вам нужно будет создать связанный с Google API-ключ и подключаться к Интернету, когда вы его используете.
Другая проблема заключается в том, что если географические данные о широте/долготе недействительны, вы получите сообщение об ошибке, а не просто пропустите их. Зачастую набор данных находится в неидеальном состоянии, а потому, возможно, придется потратить некоторое время на фильтрацию неверных значений.
import gmaps
gmaps.configure(api_key="...")
locations = [(float(row['lat']), float(row['long']))
for index, row in df.iterrows()
if -80 < float(row['long']) < -70
and 35 < float(row['lat']) < 45]
fig = gmaps.Map()
fig.add_layer(gmaps.heatmap_layer(locations))
fig
Дампинг Pandas DataFrames для SQLite
Pandas DataFrames отлично подходят для создания производных наборов данных с минимальным количеством кода. Кроме того, сброс Pandas DataFrames обратно в SQLite 3 очень прост. В этом примере я заполнил DataFrame некоторыми CSV-данными, создал новую базу данных SQLite 3 и выгрузил DataFrame в этот файл.
import sqlite3
import pandas
connection = sqlite3.connect('trips.db')
df = pandas.read_csv('trips.csv', sep=',')
df.to_sql('trips', connection, if_exists='append', index=False)
Вывод
SQLite 3 — не игрушка, а мощное SQL-расширение. Поскольку скорость хранения и производительность одного ядра в процессорах увеличивают объем данных, SQLite 3 продолжает развиваться.
Я определенно считаю SQLite 3 одной из наиболее удобных баз данных, и я решаю значительное количество задач с его помощью.