Базы данных в питоне кратко

Обновлено: 06.07.2024

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

Если вы разработчик программного обеспечения, то, скорее всего, вы знакомы с невероятно легкой базой данных SQLite или даже уже использовали ее. Она содержит практически все функции реляционной базы данных и представлена всего одним файлом. На официальном сайте можно найти несколько сценариев применения SQLite:

встроенные устройства и интернет вещей;
анализ данных;
передача данных;
архив файлов и/или контейнер данных;
внутренние или временные базы данных;
замена корпоративной базы данных в период демо-версий или тестирования;
обучение и тестирование;
экспериментальные расширения языка SQL.

Если вам нужна SQLite для каких-либо других целей, то обратитесь к документации.

Но самое главное — SQLite встроена в библиотеку Python. То есть вам не нужно устанавливать серверное или клиентское ПО и поддерживать работу какого-либо сервиса. Если вы импортировали библиотеку в Python и приступили к работе, значит вы уже используете систему управления реляционными базами данных!

import sqlite3 as sl

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

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

Теперь создадим таблицу:

with con: con.execute(""" CREATE TABLE USER ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER ); """)

Мы добавили три столбца в таблицу USER. Как видите, SQLite действительно легка и при этом поддерживает все основные функции обычной реляционной СУБД, такие как тип данных, обнуляемый тип, первичный ключ и автоинкремент.

После запуска этого кода создается таблица, но она ничего не выводит.

Вставим несколько записей в только что созданную таблицу USER, чтобы доказать, что она действительно создана.

Предположим, мы хотим вставить сразу несколько записей. Выполним:

sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)' data = [ (1, 'Alice', 21), (2, 'Bob', 22), (3, 'Chris', 23) ]

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

with con:
con.executemany(sql, data)

После запуска кода не появилось никаких предупреждений, значит все прошло успешно.

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

Как видите, все очень просто!

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

Чаще всего я использую инструмент DBeaver. Рассмотрим его на примере.

Поскольку я использую Google Colab, я буду загружать файл my-test.db на свой компьютер. При запуске Python на локальном компьютере можно использовать клиент SQL для прямого подключения к файлу баз данных.

Создаем новое соединение в DBeaver и выбираем SQLite в качестве типа БД:

Затем переходим к файлу БД:

Теперь к базе данных можно выполнить любой SQL-запрос, как и в любых других реляционных БД:

Но это еще не все. Дело в том, что, являясь встроенной функцией Python, SQLite может легко интегрироваться с фреймом данных Pandas.

Определяем фрейм данных:

Затем просто вызываем метод фрейма данных to_sql(), чтобы сохранить его в базе данных:

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

Допустим, мы хотим объединить таблицу USER и SKILL и прочитать результат во фрейме данных Pandas. Это тоже можно выполнить без проблем.

Результаты запишем в новую таблицу под названием USER_SKILL:

Теперь мы также можем использовать клиент SQL для получения таблицы:

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

Но самое главное, мы можем легко прочитать таблицу из базы данных SQLite во фрейме данных Pandas и наоборот. Такая возможность еще больше упрощает взаимодействие с этой легкой реляционной базой данных.

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

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

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

Зачем нужны базы данных?

На высоком уровне веб-приложения хранят данные и представляют их пользователям в удобном виде. Например, Google хранит данные о дорогах и предоставляет маршруты для проезда из одного места в другое при каждом использовании вами Google Maps. Строить такие маршруты движения возможно благодаря тому, что данные хранятся в структурированном формате.

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

Для управления созданием и использованием баз данных создано много реализаций СУБД — систем управления базами данных. Примеры СУБД — PostgreSQL, MySQL, SQLite. Также существуют нереляционные базы данных — NoSQL.

Английский для программистов

Наш телеграм канал с тестами по английскому языку для программистов. Английский это часть карьеры программиста. Поэтому полезно заняться им уже сейчас

Реляционные базы данных

В веб-разработке на Python чаще всего используются реляционные базы данных.

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

Реализации хранилищ баз данных различаются по сложности. SQLite — СУБД, встроенная в Python — создает один файл для всех данных каждой базы данных.

Другие СУБД, такие как PostgreSQL, MySQL, Oracle и Microsoft SQL Server, имеют более сложные схемы хранения. Кроме того, они предлагают дополнительные расширенные функции, полезные для хранения данных веб-приложений. Например:

Репликация данных между главной базой данных и одним или несколькими подчиненными экземплярами, доступными только для чтения.
Расширенные типы столбцов, которые могут эффективно хранить полуструктурированные данные, такие как JSON (JavaScript Object Notation).
Сегментирование, которое позволяет горизонтально масштабировать несколько баз данных, каждая из которых служит экземпляром для чтения и записи, за счет задержки в согласованности данных.
Мониторинг, статистика и другая полезная информация о выполнении для схем и таблиц базы данных.

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

Наиболее распространенные базы данных для веб-приложений на Python

PostgreSQL и MySQL – две наиболее распространенные базы данных с открытым исходным кодом для хранения данных веб-приложений на Python.

SQLite – это база данных, которая хранится в одном файле на диске. SQLite встроена в Python, но предназначена только для доступа по одному соединению за раз. Поэтому настоятельно рекомендуется не запускать производственное веб-приложение с SQLite. Эта база данных хороша для учебных проектов, когда вы только осваиваете, как всё работает.

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

База данных PostgreSQL

PostgreSQL – это рекомендуемая реляционная СУБД для работы с веб-приложениями на Python. Функционал PostgreSQL, его активное развитие и улучшение, а также стабильность послужили причиной использования этой СУБД в бэкенде миллионов приложений, существующих сегодня в сети.

База данных MySQL

MySQL – еще одна практичная реализация СУБД для приложений, написанных на Python. Имеет открытый исходный код.

MySQL проще в освоении, чем PostgreSQL, но не так богата функциями.

Подключение к базе данных с помощью Python

Для работы с реляционной базой данных с использованием Python нужны библиотеки. Наиболее распространенные библиотеки для реляционных баз данных:

psycopg2 (исходный код) для PostgreSQL.
MySQLdb (исходный код) для MySQL. Обратите внимание, что разработка этого драйвера в основном заморожена. Поэтому будет целесообразно обратить внимание на альтернативные варианты, если в бэкенде вашего приложения используется MySQL.
cx_Oracle (исходный код) для Oracle Database.

Поддержка SQLite встроена во все версии Python 2.7+, поэтому отдельная библиотека для подключения не требуется. Просто импортируйте sqlite3 ( import sqlite3 ), и можно начинать работатьтоirметоyey.

Объектно-реляционное отображение

Объектно-реляционное отображение (англ. object-relational mapping, ORM) позволяет разработчикам получать доступ к данным из бэкенда при помощи Python-кода, а не SQL-запросов. Все структуры веб-приложений по-разному обрабатывают интеграцию ORM. По объектно-реляционному отображению есть множество ресурсов, так что вы без проблем разберетесь в этой теме.

Размещение баз данных на стороннем сервере

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

предоставляет предварительно настроенные экземпляры MySQL и PostgreSQL. Экземпляры можно масштабировать до больших или меньших конфигураций в зависимости от требований к хранилищу и производительности. – это сервис для работы с серверами MySQL, PostgreSQL и SQL. Позволяет управлять базами данных, делать бэкапы, репликации и вносить автоматические исправления. Cloud SQL интегрируется с Google App Engine, но также может использоваться и независимо. предоставляет размещение баз данных MySQL и MongoDB с обширными услугами резервного копирования. – это SaaS-компания, которая размещает базы данных PostgreSQL и управляет конфигурацией сервера, резервным копированием и подключением к данным поверх экземпляров Amazon Web Services.

Полезные источники для изучения баз данных

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

Книги для изучения баз данных:

Видеокурсы:

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

Контрольный чеклист для изучения баз данных

Установите PostgreSQL на свой сервер или персональный компьютер. Если вы используете Ubuntu, запустите sudo apt-get install postgresql .
Убедитесь, что в зависимостях вашего приложения есть библиотека psycopg2.
Настройте свое веб-приложение для подключения к экземпляру PostgreSQL.
Создавайте модели в ORM с помощью встроенного ORM Django или SQLAlchemy с Flask.
Создайте таблицы своей базы данных или синхронизируйте модели ORM с экземпляром PostgreSQL, если вы используете ORM.
Начните создавать, читать, обновлять и удалять данные в базе данных из вашего веб-приложения.

Заключение

В этой статье мы кратко рассказали про базы данных в Python. Обсудили, какие они бывают и в чем особенности использования той или иной СУБД. Также мы дали вам подборки книг и видеоматериалов для дальнейшего изучения баз данных. Надеемся, что вам все это пригодится. Успехов в освоении баз данных и написании кода!

SQL и Python — обязательные инструменты для любого специалиста в сфере анализа данных. Это руководство — все, что вам нужно для первоначальной настройки и освоения основ работы с SQLite в Python. Оно включает следующие пункты:

Загрузка библиотеки
Создание и соединение с базой данных
Создание таблиц базы данных
Добавление данных
Запросы на получение данных
Удаление данных
И многое другое!

SQLite3 (часто говорят просто SQLite) — это часть стандартного пакета Python 3, поэтому ничего дополнительно устанавливать не придется.

Что будем создавать

В процессе этого руководства создадим базу данных в SQLite с помощью Python, несколько таблиц и настроим отношения:

Типы данных SQLite в Python

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

NULL — значение NULL
INTEGER — целое число
REAL — число с плавающей точкой
TEXT — текст
BLOB — бинарное представление крупных объектов, хранящееся в точности с тем, как его ввели

К сожалению, других привычных для SQL типов данных в SQLite нет.

Первые шаги с SQLite в Python

Начнем руководство с загрузки библиотеки. Для этого нужно использовать следующую команду:

Следующий шаг — создание базы данных.

Создание базы данных SQLite в Python

Есть несколько способов создания базы данных в Python с помощью SQLite. Для этого используется объект Connection , который и представляет собой базу. Он создается с помощью функции connect() .

Создадим файл .db , поскольку это стандартный способ управления базой SQLite. Файл будет называться orders.db . За соединение будет отвечать переменная conn .

Эта строка создает объект connection , а также новый файл orders.db в рабочей директории. Если нужно использовать другую, ее нужно обозначить явно:

Если файл уже существует, то функция connect осуществит подключение к нему.

Функция connect создает соединение с базой данных SQLite и возвращает объект, представляющий ее.

Резидентная база данных

Еще один способ создания баз данных с помощью SQLite в Python — создание их в памяти. Это отличный вариант для тестирования, ведь такие базы существуют только в оперативной памяти.

Однако в большинстве случаев (и в этом руководстве) будет использоваться описанный до этого способ.

Создание объекта cursor

После создания объекта соединения с базой данных нужно создать объект cursor . Он позволяет делать SQL-запросы к базе. Используем переменную cur для хранения объекта:

Теперь выполнять запросы можно следующим образом:

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

Создание таблиц в SQLite в Python

Пришло время создать первую таблицу в базе данных. С объектами соединения ( conn ) и cursor ( cur ) это можно сделать. Будем следовать этой схеме.

Начнем с таблицы users .

В коде выше выполняются следующие операции:

Функция execute отвечает за SQL-запрос
SQL генерирует таблицу users
IF NOT EXISTS поможет при попытке повторного подключения к базе данных. Запрос проверит, существует ли таблица. Если да — проверит, ничего ли не поменялось.
Создаем первые четыре колонки: userid , fname , lname и gender . Userid — это основной ключ.
Сохраняем изменения с помощью функции commit для объекта соединения.

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

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

Добавление данных с SQLite в Python

По аналогии с запросом для создания таблиц для добавления данных также нужно использовать объект cursor .

В Python часто приходится иметь дело с переменными, в которых хранятся значения. Например, это может быть кортеж с информацией о пользователе.

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

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

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

Но нужно использовать функцию executemany вместо обычной execute :

Если применить execute , то функция подумает, то пользователь хочет передать в таблицу два объекта (два кортежа), а не два кортежа, каждый из которых содержит по 4 значения для каждого пользователя. Хотя в первую очередь вообще должна была возникнуть ошибка.

SQLite и предотвращение SQL-инъекций

Использование способа с вопросительными знаками (?, ?, …) также помогает противостоять SQL-инъекциям. Поэтому рекомендуется использовать его, а не упомянутый до этого.

Скрипты для загрузки данных

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

Используйте следующие запросы:

Получение данных с SQLite в Python

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

Использование fetchone() в SQLite в Python

Начнем с использования функции fetchone() . Создадим переменную one_result для получения только одного результата:

Она вернет следующее:

Использование fetchmany() в SQLite в Python

Если же нужно получить много данных, то используется функция fetchmany() . Выполним другой скрипт для генерации 3 результатов:

Он вернет следующее:

Использование fetchall() в SQLite в Python

Функцию fetchall() можно использовать для получения всех результатов. Вот что будет, если запустить скрипт:

Удаление данных в SQLite в Python

Если затем сделать следующей запрос:

Будет выведен пустой список, подтверждающий, что запись удалена.

Объединение таблиц в SQLite в Python

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

Для этого напишем следующее:

Тот же подход работает с другими SQL-операциями.

Выводы

В этом материале вы узнали все, что требуется для работы с SQLite в Python: загрузка библиотеки, создание баз и таблиц, добавление, запрос и удаление данных.

Рассказываем, как связать Python и реляционные базы данных трёх типов: SQLite, MySQL и PostgreSQL. Зная одну библиотеку для работы с SQL, вы легко разберетесь в остальных.

Все приложения взаимодействуют с данными, чаще всего через систему управления базами данных (СУБД). Одни языки программирования поставляются с модулями для работы с СУБД, другие требуют использования сторонних пакетов. Из этого подробного руководства вы узнаете о различных библиотеках Python для работы с SQL-базами данных. Мы разработаем простое приложение для взаимодействия с БД SQLite, MySQL и PostgreSQL.

Примечание. Если вы не разбираетесь в базах данных, советуем обратить внимание на следующие публикации Библиотеки программиста: 11 типов современных баз данных, SQL за 20 минут, Подборка материалов для изучения баз данных и SQL.

Из этого пособия вы узнаете:

как подключиться к различным СУБД с помощью библиотек Python для работы с SQL базами данных;
как управлять базами данных SQLite, MySQL и PostgreSQL;
как выполнять запросы к базе данных внутри приложения Python;
как разрабатывать приложения для разных баз данных.

Чтобы получить максимальную отдачу от этого учебного пособия, необходимо знать основы Python, SQL и работы с СУБД. Вы также должны иметь возможность загружать и импортировать пакеты в Python и знать, как устанавливать и запускать серверы БД локально или удаленно.

Содержание статьи:

Схема базы данных
Подключение к базам данных
Создание таблиц
Вставка записей
Извлечение записей
Обновление содержания
Удаление записей таблицы

В каждом разделе по три подраздела: SQLite, MySQL и PostgreSQL.

В этом уроке мы разработаем очень маленькую базу данных приложения для социальных сетей. База данных будет состоять из четырех таблиц:

Схема базы данных показана на рисунке ниже.

Пользователи ( users ) и публикации ( posts ) будут находиться иметь тип связи один-ко-многим: одному читателю может понравиться несколько постов. Точно так же один и тот же юзер может оставлять много комментариев ( comments ), а один пост может иметь несколько комментариев. Таким образом, и users , и posts по отношению к comments имеют тот же тип связи. А лайки ( likes ) в этом плане идентичны комментариям.

Прежде чем взаимодействовать с любой базой данных через SQL-библиотеку, с ней необходимо связаться. В этом разделе мы рассмотрим, как подключиться из приложения Python к базам данных SQLite , MySQL и PostgreSQL. Рекомендуем сделать собственный .py файл для каждой из трёх баз данных.

Примечание. Для выполнения разделов о MySQL и PostgreSQL необходимо самостоятельно запустить соответствующие серверы. Для быстрого ознакомления с тем, как запустить сервер MySQL, ознакомьтесь с разделом MySQL в публикации Запуск проекта Django (англ.). Чтобы узнать, как создать базу данных в PostgreSQL, перейдите к разделу Setting Up a Database в публикации Предотвращение атак SQL-инъекций с помощью Python (англ.).

SQLite

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

Более того, SQLite база данных не требует сервера и самодостаточна, то есть просто читает и записывает данные в файл. Подключимся с помощью sqlite3 к базе данных:

Вот как работает этот код:

sqlite3.connect(path) возвращает объект connection . Этот объект может использоваться для выполнения запросов к базе данных SQLite. Следующий скрипт формирует соединение с базой данных SQLite:

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

MySQL

В отличие от SQLite, в Python по умолчанию нет модуля, который можно использовать для подключения к базе данных MySQL. Для этого вам нужно установить драйвер Python для MySQL. Одним из таких драйверов является mysql-connector-python . Вы можете скачать этот модуль Python SQL с помощью pip:

Обратите внимание, что MySQL – это серверная система управления базами данных. Один сервер MySQL может хранить несколько баз данных. В отличие от SQLite, где соединение равносильно порождению БД, формирование базы данных MySQL состоит из двух этапов:

Установка соединения с сервером MySQL.
Выполнение запроса для создания БД.

Определим функцию, которая будет подключаться к серверу MySQL и возвращать объект подключения:

В приведенном выше коде мы определили новую функцию create_connection() , которая принимает три параметра:

host_name
user_name
user_password

Модуль mysql.connector определяет метод connect() , используемый в седьмой строке для подключения к серверу MySQL. Как только соединение установлено, объект connection возвращается вызывающей функции. В последней строке функция create_connection() вызывается с именем хоста, именем пользователя и паролем.

Пока мы только установили соединение. Самой базы ещё нет. Для этого мы определим другую функцию – create_database() , которая принимает два параметра:

Объект connection ;
query – строковый запрос о создании базу данных.

Вот как выглядит эта функция:

Для выполнения запросов используется объект cursor .

Создадим базу данных sm_app для нашего приложения на сервере MySQL:

Теперь у нас есть база данных на сервере. Однако объект connection , возвращаемый функцией create_connection() подключен к серверу MySQL. А нам необходимо подключиться к базе данных sm_app . Для этого нужно изменить create_connection() следующим образом:

Функция create_connection() теперь принимает дополнительный параметр с именем db_name . Этот параметр указывает имя БД, к которой мы хотим подключиться. Имя теперь можно передать при вызове функции:

Скрипт успешно вызывает create_connection() и подключается к базе данных sm_app .

PostgreSQL

Как и в случае MySQL, для PostgreSQL в стандартной библиотеке Python нет модуля для взаимодействия с базой данных. Но и для этой задачи есть решение – модуль psycopg2 :

Определим функцию create_connection() для подключения к базе данных PostgreSQL:

Подключение осуществляется через интерфейс psycopg2.connect() . Далее используем написанную нами функцию:

Теперь внутри дефолтной БД postgres нужно создать базу данных sm_app . Ниже определена соответствующая функция create_database() :

Запустив вышеприведенный скрипт, мы увидим базу данных sm_app на своем сервере PostgreSQL. Подключимся к ней:

Здесь 127.0.0.1 и 5432 это соответственно IP-адресу и порт хоста сервера.

В предыдущем разделе мы увидели, как подключаться к серверам баз данных SQLite, MySQL и PostgreSQL, используя разные библиотеки Python. Мы создали базу данных sm_app на всех трех серверах БД. В данном разделе мы рассмотрим, как формировать таблицы внутри этих трех баз данных.

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

SQLite

Для выполнения запросов в SQLite используется метод cursor.execute() . В этом разделе мы определим функцию execute_query() , которая использует этот метод. Функция будет принимать объект connection и строку запроса. Далее строка запроса будет передаваться методу execute( ) . В этом разделе он будет использоваться для формирования таблиц, а в следующих – мы применим его для выполнения запросов на обновление и удаление.

Примечание. Описываемый далее скрипт – часть того же файла, в котором мы описали соединение с базой данных SQLite.

Итак, начнем с определения функции execute_query() :

Теперь напишем передаваемый запрос ( query ):

В запросе говорится, что нужно создать таблицу users со следующими пятью столбцами:

Наконец, чтобы появилась таблица, вызываем execute_query() . Передаём объект connection , который мы описали в предыдущем разделе, вместе с только что подготовленной строкой запроса create_users_table :

Следующий запрос используется для создания таблицы posts:

Поскольку между users и posts имеет место отношение один-ко-многим, в таблице появляется ключ user_id , который ссылается на столбец id в таблице users . Выполняем следующий скрипт для построения таблицы posts :

Наконец, формируем следующим скриптом таблицы comments и likes :

Вы могли заметить, что создание таблиц в SQLite очень похоже на использование чистого SQL. Все, что вам нужно сделать, это сохранить запрос в строковой переменной и затем передать эту переменную cursor.execute() .

MySQL

Так же, как с SQLite, чтобы создать таблицу в MySQL, нужно передать запрос в cursor.execute() . Создадим новый вариант функции execute_query() :

Описываем таблицу users :

Запрос для реализации отношения внешнего ключа в MySQL немного отличается от SQLite. Более того, MySQL использует ключевое слово AUTO_INCREMENT для указания столбцов, значения которых автоматически увеличиваются при вставке новых записей.

Следующий скрипт составит таблицу posts , содержащую внешний ключ user_id , который ссылается на id столбца таблицы users :

Аналогично для создания таблиц comments и likes , передаём соответствующие CREATE -запросы функции execute_query() .

PostgreSQL

Применение библиотеки psycopg2 в execute_query() также подразумевает работу с cursor :

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

Создадим внутри базы данных sm_app таблицу users :

Запрос на создание таблицы users в PostgreSQL немного отличается от SQLite и MySQL. Здесь для указания столбцов с автоматическим инкрементом используется ключевое слово SERIAL . Кроме того, отличается способ указания ссылок на внешние ключи:

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

SQLite

Чтобы вставить записи в базу данных SQLite, мы можем использовать ту же execute_query() функцию, что и для создания таблиц. Для этого сначала нужно сохранить в виде строки запрос INSERT INTO . Затем нужно передать объект connection и строковый запрос в execute_query() . Вставим для примера пять записей в таблицу users :

Поскольку мы установили автоинкремент для столбца id , нам не нужно указывать его дополнительно. Таблица users будет автоматически заполнена пятью записями со значениями id от 1 до 5.

Вставим в таблицу posts шесть записей:

Следующий скрипт вставляет записи в таблицы comments и likes :

MySQL

Есть два способа вставить записи в базы данных MySQL из приложения Python. Первый подход похож на SQLite. Можно сохранить запрос INSERT INTO в строке, а затем использовать для вставки записей cursor.execute() .

Ранее мы определили функцию-оболочку execute_query() , которую использовали для вставки записей. Мы можем использовать ту же функцию:

Второй подход использует метод cursor.executemany() , который принимает два параметра:

Строка query , содержащая заполнители для вставляемых записей.
Список записей, которые мы хотим вставить.

Посмотрите на следующий пример, который вставляет две записи в таблицу likes :

Какой подход выбрать – зависит от вас. Если вы не очень хорошо знакомы с SQL, проще использовать метод курсора executemany() .

PostgreSQL

В предыдущем подразделе мы познакомились с двумя подходами для вставки записей в таблицы баз данных MySQL. В psycopg2 используется второй подход: мы передаем SQL-запрос с заполнителями и списком записей методу execute() . Каждая запись в списке должна являться кортежем, значения которого соответствуют значениям столбца в таблице БД. Вот как мы можем вставить пользовательские записи в таблицу users :

Список users содержит пять пользовательских записей в виде кортежей. Затем мы создаём строку с пятью элементами-заполнителями ( %s ), соответствующими пяти пользовательским записям. Строка-заполнитель объединяется с запросом, который вставляет записи в таблицу users . Наконец, строка запроса и пользовательские записи передаются в метод execute() .

Следующий скрипт вставляет записи в таблицу posts :

По той же методике можно вставить записи в таблицы comments и likes .

SQLite

Чтобы выбрать записи в SQLite, можно снова использовать cursor.execute() . Однако после этого потребуется вызвать метод курсора fetchall() . Этот метод возвращает список кортежей, где каждый кортеж сопоставлен с соответствующей строкой в извлеченных записях. Чтобы упростить процесс, напишем функцию execute_read_query() :

Эта функция принимает объект connection и SELECT -запрос, а возвращает выбранную запись.

SELECT

Давайте выберем все записи из таблицы users :

В приведенном выше скрипте запрос SELECT забирает всех пользователей из таблицы users . Результат передается в написанную нами функцию execute_read_query() , возвращающую все записи из таблицы users .

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

Результат вышеприведенного запроса выглядит следующим образом:

Таким же образом вы можете извлечь все записи из таблицы posts :

Вывод выглядит так:

Из вывода понятно, что имена столбцов не были возвращены методом fetchall() . Чтобы вернуть имена столбцов, нужно забрать атрибут description объекта cursor . Например, следующий список возвращает все имена столбцов для вышеуказанного запроса:

Вывод выглядит так:

WHERE

Теперь мы выполним SELECT -запрос, который возвращает текст поста и общее количество лайков, им полученных:

То есть используя запрос WHERE , вы можете возвращать более конкретные результаты.

MySQL

Процесс выбора записей в MySQL абсолютно идентичен процессу выбора записей в SQLite:

Теперь выберем все записи из таблицы users :

Вывод будет похож на то, что мы видели с SQLite.

PostgreSQL

Процесс выбора записей из таблицы PostgreSQL с помощью модуля psycopg2 тоже похож на SQLite и MySQL. Снова используем cursor.execute() , затем метод fetchall() для выбора записей из таблицы. Следующий скрипт выбирает все записи из таблицы users :

Опять же, результат будет похож на то, что мы видели раньше.

SQLite

Обновление записей в SQLite выглядит довольно просто. Снова можно применить execute_query() . В качестве примера обновим текст поста с id равным 2. Сначала создадим описание для SELECT :

Увидим следующий вывод:

Следующий скрипт обновит описание:

Теперь, если мы выполним SELECT -запрос еще раз, увидим следующий результат:

То есть запись была обновлена.

MySQL

Процесс обновления записей в MySQL с помощью модуля mysql-connector-python является точной копией модуля sqlite3 :

PostgreSQL

Запрос на обновление PostgreSQL аналогичен SQLite и MySQL.

SQLite

В качестве примера удалим комментарий с id равным 5:

Теперь, если мы извлечем все записи из таблицы comments , то увидим, что пятый комментарий был удален. Процесс удаления в MySQL и PostgreSQL идентичен SQLite:

В этом руководстве мы разобрались, как применять три распространенные библиотеки Python для работы с реляционными базами данных. Научившись работать с одним из модулей sqlite3 , mysql-connector-python и psycopg2 , вы легко сможете перенести свои знания на другие модули и оперировать любой из баз данных SQLite, MySQL и PostgreSQL.

Однако это лишь вершина айсберга! Существуют также библиотеки для работы с SQL и объектно-реляционными отображениями, такие как SQLAlchemy и Django ORM, которые автоматизируют задачи взаимодействия Python с базами данных.

Если вам интересна тематика работы с базами данных с помощью Python, напишите об этом в комментариях – мы подготовим дополнительные материалы.

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