SQLite — это автономная база данных без сервера SQL. Ричард Хипп, создатель SQLite, впервые выпустил программное
обеспечение 17 августа 2000 года. С тех пор оно стало вторым по популярности ПО в мире. Его используют даже в таких
важных системах, как Airbus A350. Кстати, программа вместе со всеми библиотеками весит всего несколько мегабайт.
Содержание
Установка SQLite 3 клиента
Для запуска SQLite 3, в командной строке нужно прописать следующее:
|
1 |
$ sudo apt install sqlite3 |
Настройка клиента
Вы можете изменить заданные по умолчанию настройки CLI SQLite 3, отредактировав файлы ~/.sqliterc в директории. Это удобно для сохранения настроек, которые вы часто используете (рецептов). Вот пример:
|
1 2 3 4 5 6 |
$ vi ~/.sqliterc .headers on .mode column .nullvalue .prompt ">" .timer on |
Импорт CSV файлов
Вы можете импортировать CSV-данные в SQLite 3 с помощью двух команд. Первая переводит клиент в CSV, а вторая импортирует данные из CSV-файла. Предполагаемый разделитель можно менять с помощью команды .separator.
Если таблицы назначения еще не существует, первая строка CSV-файлов будет использоваться для именования каждого из столбцов. Если таблица существует, то все строки данных будут добавлены в существующую таблицу.
В качестве примера я собрал несколько аэропортов Уэльса в CSV-файл с разными кодировками.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ 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 автоматически создал его для меня.
|
1 |
$ sqlite3 airports.db |
Я переключил клиент в режим CSV, установил запятую разделителем, а затем импортировал файл airport.csv.
|
1 2 3 |
.mode csv .separator "," .import airports.csv airports |
Теперь появляется возможность запустить команду schema в таблице новых аэропортов, видим два столбца с названиями на японском языке и ещё два — с использованием ASCII-символов.
|
1 2 3 4 5 6 7 8 |
.schema airports CREATE TABLE airports( "都市" TEXT, "IATA" TEXT, "ICAO" TEXT, "空港" TEXT ); |
Без проблем можно давать команды, смешивая кодировки.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ 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 с помощью лишь одной команды.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
$ 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:
|
1 2 3 4 5 6 7 |
$ 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-адреса и выполняет действия, ориентируясь на таблицу.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$ 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:
|
1 |
[(u'packages.debian.org',), (u'docs.python.org',), (u'sqlite.org',)] |
Работа с несколькими базами данных
Клиент SQLite 3 способен работать с несколькими базами данных за один сеанс. Ниже я запустил клиент и подключил две базы данных.
|
1 2 3 4 |
$ sqlite3 ATTACH 'airports.db' AS airport; ATTACH 'urls.db' AS urls; |
Затем я запустил команду .databases для вывода имен и мест баз данных.
|
1 2 3 4 5 6 7 |
.databases seq name file --- --------------- ----------------------- 0 main 2 airport /home/mark/airports.db 3 urls /home/mark/urls.db |
В качестве префикса я использую имена таблиц в моих запросах с именем, которое я назначил базе данных.
|
1 |
SELECT COUNT(*) FROM urls.urls; |
Визуализация с помощью Jupyter Notebooks
Jupyter Notebooks — популярная программа для визуализации данных. Ниже можно посмотреть процесс настройки и несколько примеров визуализаций. Для начала я установил ряд системных зависимостей.
|
1 2 3 4 5 6 7 |
$ sudo apt update $ sudo apt install \ libgeos-dev \ python3-dev \ python3-pip \ python3-tk \ python3-venv |
Затем я создал виртуальную среду Python, чтобы можно было отделить зависимость Python от других проектов и назвал её .taxis.
|
1 2 |
$ pyvenv .taxis $ source .taxis/bin/activate |
Я обновил менеджер пакетов «pip» Python до версии 9.0.1 в этой виртуальной среде.
|
1 |
$ pip install --upgrade pip |
Затем я установил несколько популярных Python-библиотек.
|
1 2 3 |
$ pip install \ https://github.com/matplotlib/basemap/archive/v1.1.0.tar.gz \ 'bokeh |
Jupyter Notebooks откроет рабочую папку на Linux-машине через HTTP, поэтому мне нужно создать отдельную рабочую папку.
|
1 2 |
$ mkdir -p ~/jupyter-working $ cd ~/jupyter-working |
Затем я включил расширение gmaps и разрешил Jupyter использовать виджеты.
|
1 2 |
$ jupyter nbextension enable --py --sys-prefix gmaps $ jupyter nbextension enable --py widgetsnbextension |
После этого я запустил сервер Notebook. Вы увидите URL-адрес, содержащий параметр токена. Чтобы запустить Notebook (не ПК, конечно же), откройте ссылку в веб-браузере.
|
1 2 3 |
$ jupyter notebook \ --ip=0.0.0.0 \ --NotebookApp.iopub_data_rate_limit=100000000 |
|
1 2 3 4 |
... Copy/paste this URL into your browser when you connect for the first time, to login with a token: . |
Перед открытием URL-адреса я создал базу данных SQLite 3 из CSV-файла. Здесь содержится около миллиона случайных записей о поездках на такси. Чтобы экспортировать эти записи из Hive, я сделал следующее:
|
1 |
$ 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() trips.csv.gz |
Вот первые три строки этого CSV-файла. Обратите внимание: первая строка содержит имена столбцов.
|
1 2 3 4 5 |
$ 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 в качестве параметра.
|
1 2 |
$ gunzip trips.csv.gz $ sqlite3 trips.db |
Затем переключился в режим CSV, убедился в том, что разделителем является запятая, и что импортирует CSV-файл в таблицу маршрутов.
|
1 2 3 |
.mode csv .separator "," .import trips.csv trips |
Настроили, что дальше?
С импортированными данными я открыл Notebook URL-адрес и создал Python 3 Notebook в интерфейсе Jupyter’а. Теперь необходимо вставить следующее в первую ячейку, одновременно зажать shift и кнопку выполнения.
|
1 2 3 4 5 6 7 8 9 |
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 для разбивки поездок по дням и часам.
|
1 2 3 4 5 6 |
%%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) |
Ниже приводится линейная диаграмма, показывающая количество поездок такси.
|
1 2 3 4 5 6 7 |
%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') |
Чтобы построилась гистограмма, сравнивающая данные по разным цветам автомобилей, необходимо ввести информацию в новую ячейку.
|
1 2 3 4 5 6 7 8 |
%%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', []) |
Ниже приводится круговая диаграмма, показывающая зависимость поездок от времени суток.
|
1 2 3 4 5 6 |
%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) |
Чтобы создать диаграмму матрицы рассеивания, выполните действия как в коде ниже. Заметьте, что это может занять несколько минут. Сначала будет показан массив данных, а потом и сам график.
|
1 2 3 4 5 6 |
%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-ключи. Ниже будут указаны точки сбора для маршрутов такси в наборе данных.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
%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-ключ и подключаться к Интернету, когда вы его используете.
Другая проблема заключается в том, что если географические данные о широте/долготе недействительны, вы получите сообщение об ошибке, а не просто пропустите их. Зачастую набор данных находится в неидеальном состоянии, а потому, возможно, придется потратить некоторое время на фильтрацию неверных значений.
|
1 2 3 4 5 |
import gmaps gmaps.configure(api_key="...") locations = [(float(row['lat']), float(row['long'])) for index, row in df.iterrows() if -80 |
Дампинг Pandas DataFrames для SQLite
Pandas DataFrames отлично подходят для создания производных наборов данных с минимальным количеством кода. Кроме того, сброс Pandas DataFrames обратно в SQLite 3 очень прост. В этом примере я заполнил DataFrame некоторыми CSV-данными, создал новую базу данных SQLite 3 и выгрузил DataFrame в этот файл.
|
1 2 3 4 5 6 7 8 |
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 одной из наиболее удобных баз данных, и я решаю значительное количество задач с его помощью.
Ваш комментарий будет первым