Sqlite как создать базу данных

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

С помощью sqlite3 создать или открыть существующую базу данных можно двумя способами. Во-первых, при вызове утилиты sqlite3 в качестве аргумента можно указать имя базы данных. Если БД существует, она будет открыта. Если ее нет, она будет создана и открыта.

./sqlite3 your.db

Во вторых, работая в самой программе, можно выполнить команду

.open your.db

Выяснить, какая база данных является текущей, можно с помощью команды .databases утилиты sqlite3. Если вы работаете с одной БД, а потом открываете другую, то текущей становится вторая БД.

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

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

sqlite> CREATE TABLE pages ( . > title TEXT, . > url TEXT, . > theme INTEGER, . > num INTEGER);

Имена как таблицы, так и столбцов принято писать строчными буквами. Если имя включает два слова, обычно их соединяют с помощью нижнего подчеркивания. Команды можно писать в одну строку, а не так, как показано выше.

Чтобы увидеть список таблиц базы данных используется команда .tables .

Для удаления целой таблицы из базы данных используется директива DROP TABLE, после которой идет имя удаляемой таблицы.

Первичный ключи и автоинкремент

Для реляционных баз данных важно, чтобы каждую запись-строку таблицы можно было однозначно идентифицировать. То есть в таблицах не должно быть полностью совпадающих строк. Записи должны отличаться хотя бы по одному полю.

С этой целью принято создавать дополнительное поле, которое часто называют ID или подобно. В базах данных под Android по соглашению столбец для уникального идентификатора записей называют _id.

sqlite> CREATE TABLE pages ( . > _id INTEGER, . > title TEXT, . > url TEXT, . > theme INTEGER, . > num INTEGER);

При таком создании таблицы следить за уникальностью поля _id каждой записи должен будет человек. Для SQLite столбец _id ничем не отличается от любого другого. Мы вполне можем сделать несколько записей с одинаковым ID.

Чтобы исключить возможность ввода одинаковых идентификаторов, столбец ID назначают первичным ключом. PRIMARY KEY – ограничитель, который заставляет СУБД проверять уникальность значения данного поля у каждой добавляемой записи.

sqlite> CREATE TABLE pages ( . > _id INTEGER PRIMARY KEY, . > title TEXT, . > url TEXT, . > theme INTEGER, . > num INTEGER);

Если нам не важно, какие конкретно идентификаторы будут записываться в поле _id, а важна только уникальность поля, следует назначить полю еще один ограничитель – автоинкремент – AUTOINCREMENT.

sqlite> CREATE TABLE pages ( . > _id INTEGER PRIMARY KEY AUTOINCREMENT, . > title TEXT, . > url TEXT, . > theme INTEGER, . > num INTEGER);

В этом случае SQLite будет сам записывать в поле уникальное целочисленное значение по нарастающей от записи к записи. Поскольку это поле заполняется автоматически, то при добавлении записи в таблицу его игнорируют.

NOT NULL и DEFAULT

Ограничитель NOT NULL используют, чтобы запретить оставление поля пустым. По умолчанию, если поле не является первичным ключом, в него можно не помещать данные. В этом случае полю будет присвоено значение NULL. В случае NOT NULL вы не сможете добавить запись, не указав значения соответствующего поля.

Однако, добавив ограничитель DEFAULT, вы сможете не указывать значение. DEFAULT задает значение по умолчанию. В результате, когда данные в поле не передаются при добавлении записи, поле заполняется тем, что было указано по умолчанию.

Допустим, в таблице поля url, theme и num не должны быть пустыми. При этом если значение для num не передается, то полю присваивается 0. В этом случае команда для создания таблицы будет такой:

sqlite> CREATE TABLE pages ( . > _id INTEGER PRIMARY KEY AUTOINCREMENT, . > title TEXT, . > url TEXT NOT NULL, . > theme INTEGER NOT NULL, . > num INTEGER NOT NULL DEFAULT 0);

С помощью команд .schema и PRAGMA TABLE_INFO() можно посмотреть схему таблицы.

Внешний ключ

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

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

sqlite> CREATE TABLE sections ( . > _id INTEGER PRIMARY KEY, . > name TEXT);

Тогда в первой таблице в столбце theme следует хранить номера тем – их ID, взятые из второй таблицы. Это будут внешние ключи, представляющие собой первичные в таблице с разделами. Внешние ключи уникальными не будут, так как разные страницы могут принадлежать к одной и той же теме.

sqlite> CREATE TABLE pages ( . > _id INTEGER PRIMARY KEY AUTOINCREMENT, . > title TEXT, . > url TEXT NOT NULL, . > theme INTEGER NOT NULL, . > num INTEGER NOT NULL DEFAULT 100, . > FOREIGN KEY (theme) REFERENCES sections(_id) . > );

FOREIGN KEY является ограничителем, так как не дает нам записать в поле столбца theme какое-либо иное значение, которое не встречается в качестве первичного ключа в таблице sections. Однако в SQLite поддержка внешнего ключа по умолчанию отключена. Поэтому, даже назначив столбец внешним ключом, вы сможете записывать в его поля любые значения.

Чтобы включить поддержку внешних ключей в sqlite3, надо выполнить команду PRAGMA foreign_keys = ON; . После этого добавить в таблицу запись, в которой внешний ключ не совпадает ни с одним первичным из другой таблицы, не получится.

X Скрыть Наверх

Введение в реляционные базы данных. SQLite

#5 – Создание базы данных SQLite

Начиная с этого урока мы приступаем к работе над базой данных. В качестве СУБД мы будем использовать SQLite. За урок мы создадим базу данных, добавим в неё таблицу и подключим БД к нашему WPF проекту.

Видеоурок

Полезная ссылка:

База данных SQLite

Работа с БД SQLite относительно проста в сравнении с другими СУБД по типу MySQL, PostgreSQL, Firebase и тому подобные. Простота обеспечивается за счет того, что SQLite встраивается внутрь вашего проекта. Это значит, что для нее не требуется отдельный сервер или хостинг и она всегда будет поставляться пользователю вместе с приложением.

Подключение SQLite

Для работы с SQLite вам требуется скачать и установить специальную программу к себе на компьютер, а также внутрь проекта добавить библиотеку «System.Data.SQLite». После добавления библиотеки необходимо описать изменения в файле «App.config».

В скачанной программе создайте новую БД, добавьте в неё таблицу (или таблицы) и далее файл базы данных закиньте в папку вашего WPF проекта.

SQLite – создание базы данных

В SQLite команда sqlite3 используется для создания новой базы данных SQLite. Вам не нужно иметь никаких особых привилегий для создания базы данных.

Синтаксис

Ниже приведен основной синтаксис команды sqlite3 для создания базы данных:

$sqlite3 DatabaseName.db

Всегда имя базы данных должно быть уникальным в СУБД.

пример

Если вы хотите создать новую базу данных , оператор SQLITE3 будет выглядеть следующим образом:

$sqlite3 testDB.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>

Приведенная выше команда создаст файл testDB.db в текущем каталоге. Этот файл будет использоваться в качестве базы данных движком SQLite. Если вы заметили это при создании базы данных, команда sqlite3 предоставит приглашение sqlite> после успешного создания файла базы данных.

После создания базы данных ее можно проверить в списке баз данных с помощью следующей команды SQLite .databases .

sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db

Вы будете использовать команду SQLite .quit, чтобы выйти из приглашения sqlite следующим образом:

sqlite>.quit $

Команда .dump

Вы можете использовать команду .dump dot для экспорта всей базы данных в текстовый файл, используя следующую команду SQLite в командной строке.

$sqlite3 testDB.db .dump > testDB.sql

Приведенная выше команда преобразует все содержимое базы данных testDB.db в операторы SQLite и помещает его в текстовый файл ASCII testDB.sql . Вы можете выполнить восстановление из сгенерированного testDB.sql простым способом:

$sqlite3 testDB.db  testDB.sql

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

Создаём и наполняем базу данных SQLite в Python

В прошлой статье мы рассказали про SQLite — простую базу данных, которая может работать почти на любой платформе. Теперь проверим теорию на практике: напишем простой код на Python, который сделает нам простую базу и наполнит её данными и связями.

Предыстория

Если это первая статья про базы данных, которую вы читаете, то лучше сделать так, а потом вернуться сюда:

1. Почитать про виды баз данных и посмотреть на схему связей в реляционной базе данных. Там простая схема про магазин — в ней связаны товары, клиенты и покупки.
2. Посмотреть, как работают SQL-запросы: что это такое, как база на них реагирует и что получается в итоге. В статье мы с помощью SQL-запросов сделали базу данных по магазинной схеме.

Что будем делать

Сегодня мы сделаем то же самое, что и в SQL-запросах, но на Python, используя стандартную библиотеку sqlite3:

• создадим базу и таблицы в ней;
• наполним их данными;
• создадим связи;
• проверим, как это работает.

После этого мы сможем использовать такой же подход в других проектах и хранить все данные не в текстовых файлах, а в полноценной базе данных.

Подключаем и создаём базу данных

За работу с SQLite в Python отвечает стандартная библиотека sqlite3:

# подключаем SQLite
import sqlite3 as sl

Теперь нам нужно указать файл базы данных, с которым мы будем дальше работать. Удобство библиотеки в том, что нам достаточно указать имя файла, а дальше будет такое:

• если этого файла нет, то программа создаст пустую базу данных с таким именем;
• если указанный файл есть, то программа подключится к нему и будет с ним работать.

Получается, нам неважно, есть файл с базой или нет — мы в любом случае после запуска получим то, что нам нужно. Для этого пишем команду:

# открываем файл с базой данных
con = sl.connect(‘thecode.db’)

Мы указали, что файл называется thecode.db, без указания папок и дисков. Это значит, что файл с базой появится в той же папке, что и наш скрипт — можно в этом убедиться после запуска программы.

Создаём таблицу с товарами

У нас есть база, в которой можно создавать таблицы для хранения данных. Создадим первую таблицу для товаров:

with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)

Если посмотреть внимательно на код, можно заметить, что текст внутри кавычек полностью повторяет обычный SQL-запрос, который мы уже использовали в прошлой статье. Единственное отличие — в SQLite используется INTEGER вместо INT:

CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INT, price INT );

Теперь соберём код вместе и запустим его ещё раз:

# подключаем SQLite import sqlite3 as sl # открываем файл с базой данных con = sl.connect('thecode.db') # создаём таблицу для товаров with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)

Но после второго запуска компьютер почему-то выдаёт ошибку:

❌ sqlite3.OperationalError: table goods already exists

Дело в том, что при повторном запуске программа пытается создать таблицу с товарами, которая уже есть в базе. Так как имена таблиц совпадают, а двух одинаковых имён быть не может, отсюда и возникает ошибка.

Чтобы не попадать в такую ситуацию, добавим проверку: посмотрим, есть ли в базе нужная нам таблица или нет. Если нет — создаём, если есть — двигаемся дальше:

# открываем базу with con: # получаем количество таблиц с нужным нам именем data = con.execute("select count(*) from sqlite_master where type='table' and name='goods'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для товаров with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)

Точно так же мы потом сделаем и с остальными таблицами — сразу встроим проверку, и если нужных таблиц не будет, то программа создаст их автоматически.

Теперь наполняем нашу таблицу товарами, используя стандартный SQL-запрос. Например, можно добавить два стола, которые стоят по 3000 ₽:

INSERT INTO goods SET
product = ‘стол’,
count = 2,
price = 3000;

Но добавлять записи по одному товару за раз — это долго и неэффективно. Проще сразу в одном запросе добавить все нужные товары: стол, стул и табурет:

# подготавливаем множественный запрос sql = 'INSERT INTO goods (product, count, price) values(?, ?, ?)' # указываем данные для запроса data = [ ('стол', 2, 3000), ('стул', 5, 1000), ('табурет', 1, 500) ] # добавляем с помощью множественного запроса все данные сразу with con: con.executemany(sql, data) # выводим содержимое таблицы на экран with con: data = con.execute("SELECT * FROM goods") for row in data: print(row)

В конце мы добавили вывод таблицы — так можно убедиться, что запрос сработал и данные отправились в базу в нужное место.

Создаём и заполняем таблицу с товарами

Заведём таблицу clients для клиентов и заполним её точно так же, как мы это сделали с клиентской таблицей. Для этого просто копируем предыдущий код, меняем название таблицы и указываем правильные названия полей.Ещё посмотрите на отличие от обычного SQL в последней строке объявления полей таблицы: вместо id INT AUTO_INCREMENT PRIMARY KEY надо указать id INTEGER PRIMARY KEY . Без этого не будет работать автоувеличение счётчика.

# --- создаём таблицу с клиентами --- # открываем базу with con: # получаем количество таблиц с нужным нам именем — clients data = con.execute("select count(*) from sqlite_master where type='table' and name='clients'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для клиентов with con: con.execute(""" CREATE TABLE clients ( name VARCHAR(40), phone VARCHAR(10) UNIQUE, id INTEGER PRIMARY KEY ); """) # подготавливаем множественный запрос sql = 'INSERT INTO clients (name, phone) values(?, ?)' # указываем данные для запроса data = [ ('Миша', 9208381096), ('Наташа', 9307265198), ('Саша', 9307281096) ] # добавляем с помощью множественного запроса все данные сразу with con: con.executemany(sql, data) # выводим содержимое таблицы с клиентами на экран with con: data = con.execute("SELECT * FROM clients") for row in data: print(row)

Cоздаём таблицу с покупками и связываем всё вместе

У нас всё готово для того, чтобы на основе первых двух таблиц создать третью — в ней будут данные сразу и о покупках, и о том, кто это купил. Если интересно, как это работает в деталях, — почитайте статью про связи в базе данных.

# --- создаём таблицу с покупками --- # открываем базу with con: # получаем количество таблиц с нужным нам именем — orders data = con.execute("select count(*) from sqlite_master where type='table' and name='orders'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для покупок with con: con.execute(""" CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, product VARCHAR, amount INTEGER, client_id INTEGER, FOREIGN KEY (product) REFERENCES goods(product), FOREIGN KEY (client_id) REFERENCES clients(id) ); """)

Проверим, что связь работает: добавим в таблицу с заказами запись о том, что Миша купил 2 табурета:

# подготавливаем запрос sql = 'INSERT INTO orders (product, amount, client_id) values(?, ?, ?)' # указываем данные для запроса data = [ ('табурет', 2, 1) ] # добавляем запись в таблицу with con: con.executemany(sql, data) # выводим содержимое таблицы с покупками на экран with con: data = con.execute("SELECT * FROM orders") for row in data: print(row)

Компьютер выдал строку (1, ‘табурет’, 2, 1), значит, таблицы связались правильно.

Что дальше

Теперь, когда мы знаем, как работать с SQLite в Python, можно использовать эту базу данных в более серьёзных проектах:

• хранить результаты парсинга;
• запоминать отсортированные датасеты;
• вести учёт пользователей и их действий в системе.

Подпишитесь, чтобы не пропустить продолжение про SQLite. А если вам интересна аналитика и работа с данными, приходите на курс «SQL для работы с данными и аналитики».

Данные — это новая нефть

А аналитики данных — новая элита ИТ. Эти люди помогают понять огромные массивы данных и принять правильные решения. Изучите профессию аналитика и начните карьеру в ИТ: старт — бесплатно, а после обучения — помощь с трудоустройством.

Получите ИТ-профессию

В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.