Как работать с sqlite3 через shell

sqlite или sqlite3 (последняя версия) — это легковесная реляционная база (ACID-совместимая) данных работающая из файла. Не работает по сети.
Совместима с стандартом SQL-92.
Она намного быстрее (в своём классе задач) mysql & postgresql.

Я бы рекомендовал её использовать во многих случаях вместо mysql & postgresql, например на личном блоге ;)

    Сама библиотека SQLite написана на C; существует большое количество привязок к другим языкам программирования, в том числе C++, Java, C#, VB.NET, Python, Perl, PHP, Tcl (средства для работы с Tcl включены в комплект поставки SQLite), Ruby, Haskell, Scheme, Smalltalk, Lua и Parser, а также ко многим другим. Полный список существующих средств размещён на странице проекта

Обычно надо откуда-то получить данные, их записать и обработать.

*Желательно что бы запись и изменение данных не конфликтовала между собой.
*Не хочется городить километры кода для простых вещей. Например не хочется писать свои или искать чужой код для чтения и записи в файл.

Вообще — применений просто масса.
bash

Как же встроить sqlite в скрипт на bash-интерпретаторе?

#!/bin/bash
#
# test application by http://wel.org.ua
#
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
 
sqlite3=`which sqlite3`
DB_FILE=~/test.db
SQLITE_OPTIONS=" -column -header  "
 
$sqlite3 $DB_FILE  "
        create table IF NOT EXISTS  test (
                test TEXT UNIQUE,
                test2 TEXT);"
 
 
if [ "$#" -lt 2 ];then
        echo "Введите пожалуйста 2-а аргумента, для того что бы внести их в базу данных $DB_FILE"
        exit 1
else
        $sqlite3 $DB_FILE  " insert into test (test,test2) values  ('"$1"', '"$2"')"
        #echo $error
fi
 
echo "Уже было внесено:"
$sqlite3 $SQLITE_OPTIONS $DB_FILE <<EOF
        SELECT * FROM test;
EOF
 
exit 0

Выполнения кода:

Как видно скрипт не заработал с 1-м аргументом

    # /test.sh 1
    Введите пожалуйста 2-а аргумента, для того что бы внести их в базу данных /root/test.db

Скрипт заработал

    #/test.sh 1 2
    Уже было внесено:
    1|2

SQLite сообщил, что Мы внесли нейникальное значение для индекса

    # /test.sh 1 2
    Error: column test is not unique
    Уже было внесено:
    1|2

Всё отлично )))

    # /test.sh 2 3
    Уже было внесено:
    1|2
    2|3

А теперь небольшое пособие по работе в SQLite

Команда	Краткое описание
.databases	Вывод списка имен всех подключенных в текущем сеансе баз данных и соответствующих им файлов.
.dump ?TABLES?:	Дамп всех SQL инструкций использованных в создании БД или отдельной таблицы в текстовом формате
.echo ON|OFF	ВКЛ | ВЫКЛ эхо введенных команд
.exit	Выход из программы
.explain ON|OFF	Управляет режимом вывода виртуальных машинных команд. Используется при выполнении SQL запроса EXPLAIN.
.header(s) ON|OFF	ВКЛ | ВЫКЛ показ заголовков столбцов
.import FILE TABLE	Импорт данных из файла FILE в таблицу TABLE
.indices TABLE	Показывает имена всех индексов таблицы
.mode MODE	Установка режима вывода: line(s), column(s), insert, list, html и других
.nullvalue STRING	Напечатает строку STRING вместо NULL данных при выводе SQL запроса SELECT
.output FILENAME	Послать весь вывод в файл FILENAME
.output stdout	Послать весь вывод на экран
.promt MAIN COTINUE	Изменить стандартную строку подсказки
.quit	Выход их программы
.read FILENAME	Выполнение SQL инструкций из файла FILENAME
.schema ?TABLE?	Покажет текст SQL инструкции CREATE для всех таблиц или указанной таблицы
.separator STRING	Изменить строку разделитель колонок, используется при выводе SQL запроса SELECT и команды .import
.show	Показать значения установленных переменных
.tables ?PATTERN?	Вывод списка имен таблиц БД (возможно по шаблону)
.timeout MS	Блокирование открытия таблиц на число миллисекунд MS
.width NUM NUM :	Установка ширины столбцов при выводе в режиме column

Подробно рассмотрим некоторые из этих команд. Предварительно создадим базу данных, на которой мы и рассмотрим специальные команды. Из командной строки запустите консольную программу "sqlite3.exe" введя следующее:

sqlite3 exam.db
SQLite version 3.1.3
Enter ".help" for instructions

Где exam.db имя создаваемой базы данных.
Создадим таблицу tbl1, с помощью SQL инструкции CREATE TABLE.

sqlite> CREATE TABLE tbl1 (one INTEGER PRIMARY KEY AUTOINCREMENT,
two VARCHAR(50), three REAL);

Вставим несколько записей в данную таблицу. Отмечу, что в инструкции создания таблицы для каждого поля мы задали тип данных для его значений, этот тип данных игнорируется программой "SQLite". Система управления базой данных "SQLite" довольно свободно относится к определению типов данных, что не характерно для стандарта языка SQL. Типизация данных в "SQLite" основывается на так называемом принципе "typeless", означающий игнорирование информации о типе данных в определении столбцов таблицы при ее создании с помощью инструкции CREATE TABLE. И все же лучше включать имена типов столбцов при объявлении таблиц, это повысит переносимость вашей базы данных. Имеется одно исключение из принципа "typeless" - это столбец типа INTEGER PRYMARY KEY (INTEGER не INT; INT PRIMARY KEY - удовлетворяет принципу "typeless"). Столбец с типом INTEGER PRIMARY KEY должен содержать 32-х битное целое число, попытка записать данные любого другого типа в столбец этого типа приведет к ошибке. Этот тип обычно используется перед спецификатором столбца AUTOINCREMENT, столбец с типом INTEGER PRIMARY KEY AUTOINCREMENT является ключевым столбцом таблицы, его значения генерируются автоматически.

sqlite> INSERT INTO tbl1 VALUES(NULL, 'hello', 3.10);
sqlite> INSERT INTO tbl1 VALUES(NULL, 'is', 5.34);
sqlite> INSERT INTO tbl1 VALUES(NULL, 'from', NULL);

Создадим, пока пустую таблицу tbl2 и индекс по ее первому полю "ikey".

sqlite> CREATE TABLE tbl2 (ikey INTEGER PRIMARY KEY, nm VARCHAR(50));
sqlite> CREATE UNIQUE INDEX itbl2 ON tbl2(ikey);
sqlite>

Команда .dump
Позволит вам получить полный дамп (листинг) всех SQL инструкций использованных при создании базы данных или некоторой конкретной таблицы.
Синтаксис:

.dump ?TABLE?  

где TABLE - необязательное имя таблицы, для которой вы хотите получить дамп SQL инструкций.
Пример:

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE tbl1 (one integer primary key autoincrement, two varchar(50),
three real);
INSERT INTO "tbl1" VALUES(1, 'hello', 3.1);
INSERT INTO "tbl1" VALUES(2, 'is', 5.34);
INSERT INTO "tbl1" VALUES(3, 'from', NULL);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('tbl1', 3);
CREATE TABLE tbl2 (ikey integer integer primary key, nm VARCHAR(20));
CREATE UNIQUE INDEX itbl2 on tbl2(ikey);
COMMIT; 
sqlite>

Эту команду можно использовать для экспорта базы данных созданную в SQLite в другую программу работы с базами данных. Для этого необходимо сохранить полный дамп SQL инструкций в файле.

sqlite> .output exam.sql --перенаправить вывод в файл
sqlite> .dump

Команда .header(s)
Включает / выключает вывод названий заголовков столбцов.
Синтаксис:

.header(s) ON | OFF

где,

• ON - включить вывод имен заголовков столбцов
• OFF - отключить вывод имен заголовков столбцов

Пример:

sqlite> .header ON
sqlite> SELECT * FROM tbl1;
one|two|three
1|hello|3.1
2|is|5.34
3|from|
sqlite> .header OFF
sqlite> SELECT * FROM tbl1;
1|hello|3.1
2|is|5.34
3|from|
sqlite>

Команда .import
С помощью данной команды имеется возможность импортировать данные из текстового файла. Данные в текстовом файле должны быть разделены строкой-разделителем, эта строка разделитель определяется командой .separator. По умолчанию символом-разделителем является символ вертикальной черты "|". Данная команда введена в версии 3.
Синтаксис:

.import FILE TABLE

где,

• FILE - имя входного тестового файла с данными
• TABLE - имя таблицы куда вставляются данные

Пример:
Подготовим следующие данные в файле exam.txt

1;Roy 
2;Ted
3;Sam
4;Bob

Вставим эти данные в таблицу tbl2, для этого выполним следующие команды:

sqlite> .separator ';'  -- установим символ-разделитель в точку с запятой  
sqlite> .import exam.txt tbl2  -- импорт данных 
sqlite> SELECT * FROM tbl2;   
1; Roy
2; Ted
3; Sam
4; Bob

Команда .mode
Данная команда изменяет формат вывода таблицы SQL запросом SELECT. "SQLite" может вывести результат запроса в следующих форматах: csv, column, html, insert, line, list, tabs, tcl. Для версии 2.8 поддерживается только следующие форматы: line(s), column(s), insert, list, html. Рассмотрим их по порядку.
- Режим csv. В данном формате столбцы записи таблицы разделяются запятыми.
Пример:

sqlite> .mode csv
sqlite> SELECT * FROM tbl1;
1,"hello",3.1
2,"is",5.34
3,"from",
sqlite>  

Режим удобен для экспорта таблицы в сторонние программы, например данный формат, понимает Excel.
Режим column. В этом режиме вывод выровнен по левой границе столбцов записей таблицы.
Пример:

sqlite> .mode columns
sqlite> SELECT * FROM tbl1;
1           hello       3.1
2           is          5.34
3           from
sqlite> 

Смотрите также команду .width для задания ширины столбцов.
Режим html. Вывод в этом режиме выполнен в стиле определения таблицы языка html. Удобен для включения таблицы в страничку сайта.
Пример:

sqlite> .mode html
sqlite> SELECT * FROM tbl2;

<TR><TD>1</TD>
</TD>Roy
</TR>
<TR><TD>2</TD>
</TD>Ted
</TR>
<TR><TD>3</TD>
</TD>Sam
</TR>
<TR><TD>4</TD>
</TD>Bob
</TR>
sqlite>

Режим insert. В данном режиме генерируются SQL запросы INSERT для таблицы, имя которой указано третьим параметром в команде.
Синтаксис:

.mode insert TABLE

где TABLE - имя таблицы, для которой генерируются инструкции INSERT
Пример:

sqlite> .mode insert tbl3
sqlite> SELECT * FROM tbl1;
INSERT INTO tbl3 VALUES(1,'hello',3.1);
INSERT INTO tbl3 VALUES(2,'is',5.34);
INSERT INTO tbl3 VALUES(3,'from',NULL);
sqlite>

Режим line. Здесь каждый столбец выводится в отдельной строке в формате имя_столбца = значение. Записи разделяются пустой строкой.
Пример:

sqlite> .mode line
sqlite> SELECT * FROM tbl1;
  one = 1
  two = hello
three = 3.1

  one = 2
  two = is
three = 5.34

  one = 3
  two = from
three =
sqlite> 

Режим list. Является режимом, установленным по умолчанию. Каждая запись таблицы выводится в отдельной строке с разделением столбцов символом-разделителем. Символ-разделитель определяется командой .separator, и по умолчанию равен прямой черте '|'.
Пример:

sqlite> .mode list
sqlite> SELECT * FROM tbl1;
1|hello|3.1
2|is|5.34
3|from|
sqlite>  

Режим tabs. В данном режиме столбцы записей таблицы отделяются друг от друга символом табуляции.
Пример:

sqlite> .mode tabs
sqlite> SELECT * FROM tbl1;
1       hello   3.1
2       is      5.34
3       from
sqlite> 

Режим tcl. Вывод выполнен в формате элементов списка языка TCL.
Пример:

sqlite> .mode tcl
sqlite> SELECT * FROM tbl2;
"1"     " Roy \r"
"2"     " Ted\r"
"3"     " Sam\r"
"4"     " Bob\r"
sqlite>

Команда .output
Данная команда перенаправляет вывод в файл или обратно на экран.
Синтаксис:

.output FILENAME
 .output stdout

где,

• FILENAME - имя файла, куда направлен вывод
• stdout - направит вывод на экран

Команда .read
Читает и выполняет SQL инструкции из файла.
Синтаксис:

.read FILENAME

где FILENAME - имя файла содержащего SQL инструкции
Пример:
Создайте текстовый файл, содержащий следующие SQL запросы:

BEGIN TRANSACTION;
CREATE TABLE tbl3 (name VARCHAR(30), lastname VARCHAR(50), num INT);
INSERT INTO tbl3 VALUES ('Jim', 'Green', 1653);    
INSERT INTO tbl3 VALUES ('Ann', 'Folkin', 1891);    
INSERT INTO tbl3 VALUES ('Fredy', 'Donovan', 2345);    
COMMIT;

Назовем созданный файл exam.sql, и выполним следующие команды:

sqlite> .read exam.sql
sqlite> SELECT * FROM tbl3;
Jim|Green|1653
Ann|Folkin|1891
Fredy|Donovan|2345
sqlite>

Команда .schema
Данная команда выдаст все SQL инструкции CREATE TABLE и CREATE INDEX использованные для создания таблиц и индексов текущей базы данных. Возможно указание имени конкретной таблицы, для которой необходимо вывести SQL инструкцию ее создания.
Синтаксис:

.schema ?TABLE?

где, ?TABLE ? - необязательное имя таблицы
Пример:

sqlite> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE tbl1 (one integer primary key autoincrement, two varchar(50), three real);
CREATE TABLE tbl2 (ikey integer integer primary key, nm VARCHAR(20));
CREATE TABLE tbl3 (name VARCHAR(30), lastname VARCHAR(50), num INT);
CREATE UNIQUE INDEX itbl2 on tbl2(ikey);
sqlite> 

Команда .tables
Команда выдаст список названий таблиц созданных в текущей базе данных. Возможно, использовать определение шаблона, для выдачи имен таблиц удовлетворяющему шаблону. Синтаксис шаблона аналогичен определению шаблона в предложении LIKE.
Синтаксис:

.tables ?PATTERN?

где ?PATERN? - необязательное определение шаблона поиска
Пример:

sqlite> .tables
sqlite_sequence  tbl1             tbl2             tbl3
sqlite> .tables %tb% 'с шаблоном
tbl1  tbl2  tbl3
sqlite>

Команда .width
Используется для установки ширины столбцов, при формате вывода установленном в column.
Синтаксис:

.width NUM NUM :

где NUM - ширина соответствующего столбца в символах.
Пример:

sqlite> .mode column
sqlite> .width 3 10 7
sqlite> SELECT * FROM tbl1;
1    hello       3.1
2    is          5.34
3    from
sqlite>