Sqlite связать таблицы

Обновлено: 02.05.2024

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

Общий синтаксис установки внешнего ключа на уровне таблицы:

Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE , которые задают действие при удалении и обновлении строки из главной таблицы соответственно.

Например, определим две таблицы и свяжем их посредством внешнего ключа:

В данном случае определены таблицы companies и users. companies является главной и представляет компании, где может работать пользователь. users является зависимой и представляет пользователей. Таблица users через столбец company_id связана с таблицей companies и ее столбцом id. То есть столбец company_id является внешним ключом , который указывает на столбец id из таблицы companies.

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:

ON DELETE и ON UPDATE

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

CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.

SET NULL : при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL . (В этом случае столбец внешнего ключа должен поддерживать установку NULL)

RESTRICT : отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.

NO ACTION : то же самое, что и RESTRICT .

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

Каскадное удаление

Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE :

Подобным образом работает и выражение ON UPDATE CASCADE . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

Часть 3.2: Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты

Виды связей между таблицами в базе данных. Связи в реляционных базах данных. Отношения, кортежи, атрибуты.

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

Термины кортеж, атрибут и отношение в реляционных базах данных

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

Таблица с данными из базы данных World

У нас есть простая таблица City из базы данных World, в которой есть строки и столбцы. Но термины: таблица, строка, столбец – это термины стандарта SQL.
Кстати: ни одна из существующих в мире СУБД не имеет полной поддержки того или иного стандарта SQL, но и ни один стандарт SQL полностью не реализует математику реляционных баз данных.
В терминологии реляционных баз данных: таблица – это отношение (принимается такое допущение), строка – это кортеж, а столбец – атрибут. Иногда вы можете услышать, как некоторые разработчики называют строки записями. Чтобы не было путаницы в дальнейшем предлагаю использовать термины SQL.
Если рассматривать таблицу, как объект (например книга), то столбец – это характеристики объекта, а строки содержат информацию об объекте.

Виды и типы связей между таблицами в реляционных базах данных

Давайте теперь рассмотрим то, как могут быть связаны таблицы в реляционных базах данных. Сразу скажу, что всего существует три вида связей между таблицами баз данных:
• связь один к одному;
• связь один ко многим;
• связь многие ко многим.
Рассмотрим, как такие связи между таблицами могут быть реализованы в реляционных базах данных.

Реализация связи один ко многим в теории баз данных

Связь один ко многим в реляционных базах данных реализуется тогда, когда объекту А может принадлежать или же соответствовать несколько объектов Б, но объекту Б может соответствовать только один объект А. Не совсем понятно, поэтому смотрим пример ниже.

Реализация связи один ко многим в реляционных базах данных

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

Связь многие ко многим

Связь многие ко многим реализуется в том случае, когда нескольким объектам из таблицы А может соответствовать несколько объектов из таблицы Б, и в тоже время нескольким объектам из таблицы Б соответствует несколько объектов из таблицы А. Рассмотрим простой пример.

Пример связи многие ко многим

У нас есть таблица с книгами и есть таблица с авторами. Приведу два верных утверждения. Первое: одну книгу может написать несколько авторов. Второе: автор может написать несколько книг. Здесь мы наблюдаем типичную ситуацию, когда связь между таблицами многие ко многим. Такая связь (связь многие ко многим) реализуется путем добавления третьей таблицы.

Связь один к одному

Связь один к одному – самая редко встречаемая связь между таблицами. В 97 случаях из 100, если вы видите такую связь, вам необходимо объединить две таблицы в одну.

Пример связи один к одному

Таблицы будут связаны один к одному тогда, когда одному объекту таблицы А соответствует один объект таблицы Б, и одному объекту таблицы Б соответствует один объект таблицы А. Как я уже говорил: если вы видите, что связь один к одному – смело объединяйте таблицы в одну, за исключением тех случаев, когда происходит модернизация базы данных.
Например, у нас была таблица, в которой хранились данные о сотрудниках компании. Но произошли какие-то изменения в бизнес-процессе и появилась необходимость создать таблицы с теми же самыми сотрудниками, но не для всей компании, а разбив их по отделам. Таблицы отделов будут дочерними по отношению к таблице, в которой хранятся данные обо всех сотрудниках компании, и связаны такие таблицы будут связью один к одному.

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

Еще записи о создании сайтов и их продвижении, базах данных, IT-технология и сетевых протоколах

Возможно, эти записи вам покажутся интересными

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

This article has 3 comments

> В 97 случаях из 100

Как производилось измерение частоты случаев? Каковы были условия измерения?

у меня нет слов от ваших комментариев про связь Один ко одному. Создаётся впечатление, что вы никогда не занимались профессиональной разработкой программ и слышали о ней, только из книг. Бедные ваши студенты если вы преподаватель.

Из-за вашего комментария ко мне домой приехало два наряда пожарных, тушили весь вечер. Не нужно так, я очень впечатлительный. А теперь по факту:

1. Может вы все-таки подумаете, почему я так ответил, с учетом общей направленности всего цикла публикаций по SQLite. Может человеку сперва нужно понять как вещь работает в общем, прежде чем начинать к ней приделывать костыли?

2. Если хотите высказаться конструктивно, напишите свою публикацию с примерами и пояснениями, готов опубликовать со ссылками на вас и ваши ресурсы, если они не противоречат законам РФ.

Часть 12.14: Объединение таблиц в SQL и базах данных SQLite: JOIN и SELECT

Объединение таблиц в SQL и базах данных SQLite: JOIN и SELECT

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

Объединение таблиц в SQL запросах SELECT: LEFT JOIN, LEFT OUTER JOIN, INNER JOIN, CROSS JOIN. Разница между запросами JOIN.

На наш взгляд запросы JOIN – это самое интересное, что может делать команда SELECT. Мы рассмотрели объединение запросов в базах данных, которые реализуются при помощи UNION, а далее еще рассмотрим сравнение результатов выборки, но всё это не так интересно, как объединение таблиц в базах данных SQLite. Объединение таблиц реализуется при помощи ключевого слова JOIN.

Стандарт SQL делит объединение таблиц на три вида: внутреннее объединение таблиц (INNER JOIN), внешнее объединение таблиц (LEFT OUTER JOIN, RIGHT JOIN, FULL JOIN) и перекрестное объединение таблиц (CROSS JOIN). Принцип работы любого объединения схож, но результаты будут всегда или почти всегда отличаться.

Принцип работы запросов на объединения таблиц в SQL и реляционных базах данных заключается в том, что внутри одного SQL запроса SELECT выполняется два или более подзапроса (в зависимости от того, сколько мы хотим объединить таблиц), подзапросы разделяются между собой ключевым словом JOIN. У этого JOIN есть ограничение ON (во всяком случае официальная документация SQLite называет ON ограничением), которое называют предикатом объединения. Предикат объединения – это всегда какое-то условие, с помощью которого РСУБД определяет какие строки из двух таблиц ей нужно объединять. А вот с тем, как объединять строки, SQLite разбирается специальным модификаторам: INNER, LEFT OUTER или просто LEFT и CROSS.

Данное объяснение использует не совсем уместный в данном случае термин подзапрос, это сделано намеренно для того, чтобы объяснить принцип работы JOIN читателю, который еще не знаком с SQL. Термин подзапрос не совсем уместен, так как подзапрос SELECT всегда возвращает какую-то результирующую таблицу, а когда мы объединяем таблицы при помощи JOIN, чаще всего мы обращаемся к физическим таблицам базы данных (хотя никто не запрещает вам объединить существующую таблицу, с таблицей, которую вернет подзапрос SELECT).

Вообще, стандарт SQL выделяет гораздо больше модификаторов JOIN:

INNER JOIN – внутреннее объединение таблиц.
LEFT JOIN или LEFT OUTER JOIN – левое внешнее объединение таблиц.
RIGHT JOIN или RIGHT OUTER JOIN – правое внешнее объединение таблиц.
FULL JOIN – полное объединение таблиц.
CROSS JOIN – перекрестное объединение таблиц.

Но в базах данных SQLite есть только три вида объединения таблиц, о которых мы говорили ранее, их вполне достаточно для любых целей. Помните, мы рассматривали связи между таблицами и пытались нормализовать отношения? Когда наша база данных находится в первой нормальной форме мы и думать не думаем о том, как объединить таблицы в запросе SELECT, а вот когда отношение находится во второй нормальной форме или в третьей нормальной форме, у нас может появиться вопрос: как в одном запросе получить данные из двух или трех таблиц?

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

Ниже вы найдете небольшую шпаргалку по SQL запросу SELECT с использованием JOIN, в этой шпаргалке есть маленькие примеры и диаграммы, которые позволяют понять, как работает JOIN и как происходит объединение таблиц в базах данных.

Примеры работы запросов SELECT с JOIN и диаграммы, демонстрирующие работу различных способов объединения таблиц

Те, кто разобрались с тем, как работает JOIN в SQL и базах данных SQLite глядя на изображение выше – честь вам и хвала, это действительно здорово, а вот тем, кто не разобрался, мы предлагаем продолжить чтение и разобраться вместе с объединением таблиц и использованием JOIN в SQLite.

Готовим таблицы для реализации примеров SQL запросов JOIN в базе данных SQLite

Давайте подготовим таблицы, чтобы в дальнейшем реализовать примеры объединения таблицы в базе данных при помощи SQL запросов JOIN. И понять в чем разница между: LEFT JOIN, LEFT OUTER JOIN, INNER JOIN и CROSS JOIN. Напомним, что в SQLite нет возможности объединить таблицы при помощи: RIGHT JOIN и FULL JOIN.

Часть 11.4: Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Из этой записи вы узнаете как работает и для чего нужны внешние ключи в базах данных SQLite3. Познакомитесь с синтаксисом FROREIGN KEY в SQLite. А так же узнаете, как реализовать связи один ко многим и многие ко многим между таблицами базы данных под управлением SQLite при помощи внешних ключей. Но, стоит заметить, что вы сможете реализовать данные связи и без использования FOREIGN KEY, но в этом случае вы никак не обеспечите целостность данных в ваших таблицах.

Работа внешних ключей в базах данных SQLite: FOREIGEN KEY и REFERENCE в SQLite3

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

В SQLite внешний ключ объявляется при помощи конструкции FOREIGN KEY, а таблица, на которую ссылается внешний ключ указывается после ключевого слово REFERENCE. Обратите внимание: указывается не только таблица, но и столбец, на который идет ссылка. Ниже вы можете увидеть изображение, на котором показан синтаксис использования внешнего ключа в базах данных SQLite, вернее синтаксис конструкции REFERENCE.

Синтаксис ограничения внешнего ключа в базах данных SQLite3

Правила использования внешнего ключа не очень сложные, но давайте разберемся с тем, как реализован внешний ключ в SQLite3 и его конструкции: FOREIGEN KEY и REFEERENCE. Обратите внимание: когда вы связываете таблицы при помощи внешнего ключа одна таблица является родительской, а вторая таблица является дочерней. Внешний ключ всегда ссылается на родительскую таблиц, другими словами конструкция FOREIGN KEY и REFERENCE указывается в дочерней таблице.

Подведем итог: внешний ключ в базах данных SQLite необходим для реализации связей между таблицами. FOREIGEN KEY позволяет реализовывать связи между таблицами в базах данных. Конструкция REFERENCE используется для указания ссылки на родительскую таблицу. Внешний ключ обеспечивает целостность данных между двумя таблицами и необходим для нормализации базы данных. Вторая нормальная форма и третья нормальная форма не могут быть реализованы без внешнего ключа. Вернее будет сказать: мы можем организовать связи между таблицами без внешнего ключа, но проверка правил при этом выполняться не будет.

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

Реализация связи один ко многим в базах данных SQLite. Пример связи один ко многим и FOREIGEN KEY в SQLite

Давайте реализуем связь один ко многим при помощи внешнего ключа, для этого воспользуемся конструкциями FORIGEN KEY и REFERENCE. Мы будем связывать при помощи внешнего ключа две таблицы: таблицу авторов и таблицу книг, поэтому давайте договоримся о допущение, что один автор может написать много книг, но у книги может быть только один автор.

Чтобы реализовать связь один ко многим, нам нужно воспользоваться конструкцией FOREIGEN KEY и REFERENCE при создании таблицы при помощи команды CREATE.

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

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

1. Подключение к базе данных

Вызывая функцию connect мы достигаем 2 целей:

Модуль подключается к базе данных test.db . Если ее не существует, он создает одноименную БД в текущей директории.
Вызов данной функции создает объект Connect , представляющий БД. С этого момента все связанные с ней операции выполняются с использованием объекта Connection .

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

В рамках данного руководства мы будем оперировать объектом con , связанным с test.db .

2. Создание новой таблицы и внесение записей

Начнем с заполнения БД данными. Допустим, у нас должно быть 2 поля данных (имя и балл) и 4 записи, как показано ниже:

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

3. Запрос записей

Нельзя не заметить, что вместо ожидаемых всех записей вызов execute возвращает тот же самый объект cursor . После выполнения инструкции SELECT с курсором можно работать как с итератором. Поэтому включаем его в конструктор списка для отображения всех записей.

Как вариант, у объекта cursor есть встроенные методы fetchall и feathone . Первый отображает все записи, а второй — только одну определенную из них. Отметим, что для извлечения возвращаемого значения из вызова execute используются нижние подчеркивания.

4. Изменение записей

Для обновления записей нужен следующий синтаксис инструкции SQL: update table_name set field_name=new_value, another_field=new_value where condition . Задействуем его для изменения балла Джона:

Как видно, данная операция прошла успешно, изменив порядок имеющихся баллов.

5. Удаление записей

Для удаления записей предназначен вот такой синтаксис инструкции SQL: delete from table_name where condition . Важно не пропустить условие condition , иначе вы удалите все строки в таблице, что в большинстве случаев весьма не желательно. Рассмотрим пример:

Как видно, были удалены записи, связанные с John .

6. Чтение данных SQLite с помощью Pandas

С pandas управление базой данных SQLite превращается в увлекательный процесс. Она предоставляет функцию read_sql , позволяющую напрямую выполнять инструкции SQL, не заботясь о внутренней инфраструктуре.

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

Кто-то, возможно, сталкивался с такими функциями pandas, как read_sql_table и read_sql_query . Фактически функция read_sql — это обертка вокруг них. Она анализирует входные данные и вызывает соответствующую функцию. Таким образом, на повседневной основе можно просто задействовать read_sql , доверив pandas всю тяжелую работу.

7. Обратная запись DataFrame в SQLite

После обработки данных с помощью pandas самое время осуществить обратную запись DataFrame в БД SQLite для долгосрочного хранения. На этот случай в pandas есть метод to_sql . Обратимся к соответствующему примеру:

В отличие от read_sql , функции из библиотеки pandas, to_sql является методом класса DataFrame , вследствие чего он непосредственно вызывается объектом DataFrame .
В методе to_sql указывается таблица, в которую сохраняется DataFrame .
Отметим важность параметра if_exists , так как по умолчанию ему задается значение “fail” . Это значит, что если таблица уже существует, вы не сможете записать в нее текущий DataFrame , поскольку будет вызвана ошибка ValueError . В рассматриваемом примере требуется заменить существующую таблицу из-за изменения средних баллов оценок, поэтому параметру if_exisits устанавливается значение “replace” .
В результате установки index=False индекс объекта DataFrame просто игнорируется при сохранении в таблицу. По своему принципу данное действие аналогично методу to_csv , с которым вы наверняка знакомы лучше.

Заключение

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

Не обошли мы своим вниманием и принцип взаимодействия pandas с БД SQLite. Запомнить его легко: read_sql предназначен для извлечения данных из нее, а to_sql выгружает их туда обратно. Иначе говоря, если предположить, что вашим основным инструментом обработки данных является pandas, то относительно базы данных SQLite read_sql служит для вывода данных, а to_sql — для ввода.

Читайте также: