Пожаловаться

Руководство по SQLite: настраиваем и учимся работать

42891
Пожаловаться

Давно хотели познакомиться с 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 одной из наиболее удобных баз данных, и я решаю значительное количество задач с его помощью.

Возможно вас заинтересует следующая статья

Перевод статьи Mark Litwintschik

42891

Комментарии

Рекомендуем

BUG!