image_pdf

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

  • Подключиться к различным системам управления базами данных с помощью SQL‑библиотек Python;
  • Взаимодействовать с базами данных SQLite, MySQL и PostgreSQL;
  • Выполнять общие запросы к базе данных с помощью приложений Python;
  • Разрабатывать приложения для разных баз данных с использованием скриптов Python.

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

Содержание

Что такое схема базы данных

В этом руководстве мы разработаем очень маленькое приложения базы данных для социальных сетей. База данных будет состоять из четырех таблиц: 1) users, 2) posts, 3) comments и 4) likes.

Схема базы данных показана ниже:

Схема базы данных нашего приложения
Схема базы данных нашего приложения

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

Использование SQL‑библиотек Python для связи с базами данных

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

Примечание:

Вам понадобятся серверы баз данных MySQL и PostgreSQL. Перед выполнением своих сценариев они должны быть установлены и запущены. Для быстрого ознакомления с тем, как запустить сервер MySQL, ознакомьтесь с разделом MySQL Запуск проекта Django. Чтобы узнать, как создать базу данных в PostgreSQL, ознакомьтесь с разделом «Настройка базы данных» в Предотвращение атак SQL‑инъекций с помощью Python.

Рекомендую создать три разных файла Python для каждой из трех баз данных. Мы выполним все три скрипта для каждой базы данных из соответствующего файла.

SQLite

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

Более того, базы данных SQLite являются безсерверными и автономными, поскольку они читают и записывают данные в файл. Это означает, что, в отличие от MySQL и PostgreSQL, вам даже не нужно устанавливать и запускать сервер для выполнения операций с базой данных SQLite!

Вот как мы используете sqlite3 для подключения к базе данных SQLite в Python:

import sqlite3
from sqlite3 import Error
def create_connection(path):
 connection = None
 try:
 connection = sqlite3.connect(path)
 print("Connection to SQLite DB successful")
 except Error as e:
 print(f"The error '{e}' occurred")
 return connection

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

  • Строки 1 и 2 импортируют модуль sqlite3 и класс Error.
  • Строка 4 определяет функцию .create_connection(), которая принимает путь к базе данных SQLite.
  • Строка 7 использует .connect() из модуля sqlite3 и принимает путь к базе данных SQLite в качестве параметра. Если база данных существует в указанном месте, то соединение с базой данных установлено. В противном случае создается новая база данных в указанном месте и устанавливается соединение.
  • Строка 8 печатает статус успешного подключения к базе данных.
  • Строка 9 ловит любое исключение, которое может быть выдано, если .connect() не удается установить соединение.
  • Строка 10 отображает сообщение об ошибке в консоли.

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

connection = create_connection("E:\\db_python_app.sqlite")

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

MySQL

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

$ pip install mysql-connector-python

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

  1. Установите соединение с сервером MySQL.
  2. Выполните отдельный запрос, чтобы создать базу данных.

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

import mysql.connector
from mysql.connector import Error
def create_connection(host_name, user_name, user_password):
 connection = None
 try:
 connection = mysql.connector.connect(
  host=host_name,
  user=user_name,
  passwd=user_password
 )
 print("Connection to MySQL DB successful")
 except Error as e:
 print(f"The error '{e}' occurred")
 return connection
connection = create_connection("localhost", "root", "")

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

  1. host_name
  2. user_name
  3. user_password

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

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

  1. connection является объектом connection с сервером базы данных, с которым мы хотите взаимодействовать
  2. query  — запрос, который создает базу данных.

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

def create_database(connection, query):
 cursor = connection.cursor()
 try:
 cursor.execute(query)
 print("Database created successfully")
 except Error as e:
 print(f"The error '{e}' occurred")

Для выполнения запросов мы используем объект cursor. Запрос query, который должен быть выполнен, передается в cursor.execute() в формате string
Создайте базу данных с именем db_python_app для своего приложения социальных сетей на сервере базы данных MySQL:

create_database_query = "CREATE DATABASE db_python_app"
create_database(connection, create_database_query)

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

def create_connection(host_name, user_name, user_password, db_name):
 connection = None
 try:
 connection = mysql.connector.connect(
  host=host_name,
  user=user_name,
  passwd=user_password,
  database=db_name
 )
 print("Connection to MySQL DB successful")
 except Error as e:
 print(f"The error '{e}' occurred")
 return connection

В строке 8 видно, что create_connection() теперь принимает дополнительный параметр с именем db_name. Этот параметр указывает имя базы данных, к которой мы хотим подключиться. Мы можем передать имя базы данных, к которой хотим подключиться при вызове этой функции:

connection = create_connection("localhost", "root", "", "db_python_app")

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

PostgreSQL

В библиотеке SQL Python, как и для MySQL нет функций, которые можно использовать для работы с базой данных PostgreSQL. Вместо этого вам нужно установить сторонний драйвер SQL для взаимодействия с PostgreSQL. Одним из таких драйверов SQL для PostgreSQL является psycopg2. Выполните следующую команду на своем терминале, чтобы установить SQL‑модуль Python psycopg2 :

$ pip install psycopg2

Как и в случае SQLite и MySQL, мы определим функцию create_connection() для подключения к нашей базе данных PostgreSQL:

import psycopg2
from psycopg2 import OperationalError
def create_connection(db_name, db_user, db_password, db_host, db_port):
 connection = None
 try:
 connection = psycopg2.connect(
  database=db_name,
  user=db_user,
  password=db_password,
  host=db_host,
  port=db_port,
 )
 print("Connection to PostgreSQL DB successful")
 except OperationalError as e:
 print(f"The error '{e}' occurred")
 return connection

Мы используем psycopg2.connect() для подключения к серверу PostgreSQL из своего приложения на Python.

Кроме того, create_connection() создаёт соединение с базой данных PostgreSQLю. Для начала мы создаём подключение к базе данных по‑умолчанию postgres, используя следующую строку:

connection = create_connection(
 "postgres", "postgres", "abc123", "127.0.0.1", "5432"
)

Затем мы должны создать базу данных db_python_app внутри базы данных по‑умолчанию postgres. Вы можете определить функцию для выполнения любого запроса SQL в PostgreSQL. Ниже мы определяете create_database() для создания новой базы данных на сервере базы данных PostgreSQL:

def create_database(connection, query):
 connection.autocommit = True
 cursor = connection.cursor()
 try:
 cursor.execute(query)
 print("Query executed successfully")
 except OperationalError as e:
 print(f"The error '{e}' occurred")
create_database_query = "CREATE DATABASE db_python_app"
create_database(connection, create_database_query)

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

connection = create_connection(
 "db_python_app", "postgres", "abc123", "127.0.0.1", "5432"
)

После выполнения мышеописанного сценария будет установлено соединение с базой данных db_python_app, расположенной на сервере базы данных postgres. Здесь 127.0.0.1 есть IP-адрес хоста сервера базы данных, а 5432  — номер порта сервера баз данных.

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

В предыдущем разделе мы увидели, как подключаться к серверам баз данных SQLite, MySQL и PostgreSQL, используя разные SQL‑библиотеки Python. Вы создали базу данных db_python_app на всех трех серверах баз данных. В этом разделе мы узнаем, как создавать таблицы в этих трех базах данных.
Как уже говорилось, мы создадим четыре таблицы:

  1. users
  2. posts
  3. comments
  4. likes

Начнем с SQLite.

SQLite

Для выполнения запросов в SQLite используйте cursor.execute(). Определите функцию execute_query(), которая использует этот метод. Наша функция примет объект connection и строку запроса, которую мы передадим в cursor.execute().

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

Примечание:

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

Вот наша функция:

def execute_query(connection, query):
 cursor = connection.cursor()
 try:
 cursor.execute(query)
 connection.commit()
 print("Query executed successfully")
 except Error as e:
 print(f"The error '{e}' occurred")

Этот код пытается выполнить запрос query и при необходимости печатает сообщение об ошибке.

Далее, запишите свой запрос query:

create_users_table = """
CREATE TABLE IF NOT EXISTS users(
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 age INTEGER,
 gender TEXT,
 nationality TEXT
);
"""

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

  1. id
  2. name
  3. age
  4. gender
  5. nationality

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

execute_query(connection, create_users_table)

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

create_post_table = """CREATE TABLE IF NOT EXISTS posts(
 id INTEGER PRIMARY KEY AUTOINCREMENT, 
 title TEXT NOT NULL, 
 description TEXT NOT NULL, 
 user_id INTEGER NOT NULL, 
 FOREIGN KEY(user_id) REFERENCES users(id)
);
"""

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

execute_query(connection, create_posts_table)

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

create_comments_table = """
CREATE TABLE IF NOT EXISTS comments(
 id INTEGER PRIMARY KEY AUTOINCREMENT, 
 text TEXT NOT NULL, 
 user_id INTEGER NOT NULL, 
 post_id INTEGER NOT NULL, 
 FOREIGN KEY(user_id) REFERENCES users(id) FOREIGN KEY(post_id) REFERENCES posts(id)
);
"""
create_likes_table = """
CREATE TABLE IF NOT EXISTS likes(
 id INTEGER PRIMARY KEY AUTOINCREMENT, 
 user_id INTEGER NOT NULL, 
 post_id integer NOT NULL, 
 FOREIGN KEY(user_id) REFERENCES users(id) FOREIGN KEY(post_id) REFERENCES posts(id)
);
"""
execute_query(connection, create_comments_table) 
execute_query(connection, create_likes_table)

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

MySQL

Мы будем использовать SQL‑модуль Python mysql‑connector‑python для создания таблиц в MySQL. Как и в SQLite, нам нужно передать запрос в cursor.execute(), который возвращается путем вызова .cursor() в объект connection. Мы можем создать другую функцию execute_query(), которая принимает строку connection и query :

def execute_query(connection, query):
 cursor = connection.cursor()
 try:
 cursor.execute(query)
 connection.commit()
 print("Query executed successfully")
 except Error as e:
 print(f"The error '{e}' occurred")

В строке 4 мы передаём запрос query в cursor.execute().
Для создания таблицы users используем следующую функцию:

create_users_table = """
CREATE TABLE IF NOT EXISTS users(
 id INT AUTO_INCREMENT, 
 name TEXT NOT NULL, 
 age INT, 
 gender TEXT, 
 nationality TEXT, 
 PRIMARY KEY(id)
) ENGINE = InnoDB
"""
execute_query(connection, create_users_table)

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

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

create_posts_table = """
CREATE TABLE IF NOT EXISTS posts(
 id INT AUTO_INCREMENT, 
 title TEXT NOT NULL, 
 description TEXT NOT NULL, 
 user_id INTEGER NOT NULL, 
 FOREIGN KEY fk_user_id(user_id) REFERENCES users(id), 
 PRIMARY KEY(id)
) ENGINE = InnoDB
"""

execute_query(connection, create_posts_table)

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

PostgreSQL

Как и в случае базами данных SQLite и MySQL, объект connection, возвращаемый функцией psycopg2.connect(), содержит объект cursor. Можно использовать cursor.execute() для выполнения запросов к вашей базе данных PostgreSQL.

Запишем функцию execute_query():

def execute_query(connection, query):
 connection.autocommit = True
 cursor = connection.cursor()
 try:
 cursor.execute(query)
 print("Query executed successfully")
 except OperationalError as e:
 print(f"The error '{e}' occurred")

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

Теперь создадим таблицу users внутри базы данных db_python_app:

create_users_table = """
CREATE TABLE IF NOT EXISTS users(
 id SERIAL PRIMARY KEY,
 name TEXT NOT NULL, 
 age INTEGER,
 gender TEXT,
 nationality TEXT
)
"""

execute_query(connection, create_users_table)

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

Кроме того, ссылки на внешние ключи также указываются по-разному так, как показано в следующем сценарии для создания таблицы posts:

create_posts_table = """
CREATE TABLE IF NOT EXISTS posts(
 id SERIAL PRIMARY KEY, 
 title TEXT NOT NULL, 
 description TEXT NOT NULL, 
 user_id INTEGER REFERENCES users(id)
)
"""

execute_query(connection, create_posts_table)
 

Чтобы создать таблицу comments, вам нужно написать запрос CREATE для таблицы comments и передать его в execute_query(). Процесс создания таблицы likes такой же. Вам нужно только изменить запрос CREATE, чтобы создать таблицу likes вместо таблицы comments.

Вставка записей

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

SQLite

Для вставки записи в базу данных SQLite можно использовать ту же функцию execute_query(), которую мы уже использовали для создания таблиц. Во-первых, мы должны сохранить запрос INSERT INTO в строке, затем передать объект connection и строку query в execute_query().

Давайте вставим пять записей в таблицу users :

create_users = """
INSERT INTO
 users(name, age, gender, nationality)
VALUES
('James', 25, 'male', 'USA'),
('Leila', 32, 'female', 'France'),
('Brigitte', 35, 'female', 'England'),
('Mike', 40, 'male', 'Denmark'),
('Elizabeth', 21, 'female', 'Canada');
"""

execute_query(connection, create_users) 

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

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

create_posts = """
INSERT INTO
 posts(title, description, user_id)
VALUES
("Happy", "I am feeling very happy today", 1),
("Hot Weather", "The weather is very hot today", 2),
("Help", "I need some help with my work", 2),
("Great News", "I am getting married", 1),
("Interesting Game", "It was a fantastic game of tennis", 5),
("Party", "Anyone up for a late-night party today?", 3);
"""

execute_query(connection, create_posts)

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

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

create_comments = """
INSERT INTO
 comments(text, user_id, post_id)
VALUES
('Count me in', 1, 6),
('What sort of help?', 5, 3),
('Congrats buddy', 2, 4),
('I was rooting for Nadal though', 4, 5),
('Help with your thesis?', 2, 3),
('Many congratulations', 5, 4);
"""

create_likes = """
INSERT INTO
 likes(user_id, post_id)
VALUES
(1, 6),
(2, 3),
(1, 5),
(5, 4),
(2, 4),
(4, 2),
(3, 6);
"""

execute_query(connection, create_comments)
execute_query(connection, create_likes)

В обоих случаях запрос INSERT INTO сохраняется в строке и выполняется с помощью execute_query().

MySQL

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

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

create_users = """
INSERT INTO
 `users`(`name`, `age`, `gender`, `nationality`)
VALUES
('James', 25, 'male', 'USA'),
('Leila', 32, 'female', 'France'),
('Brigitte', 35, 'female', 'England'),
('Mike', 40, 'male', 'Denmark'),
('Elizabeth', 21, 'female', 'Canada');
"""

execute_query(connection, create_users)

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

  1. Строка запроса с заполнителями для вставляемой новой строки;
  2. Список записей, которые мы хотим вставить.

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

sql = "INSERT INTO likes( user_id, post_id) VALUES( %s, %s)"
val = [(4, 5),(3, 4)]

cursor = connection.cursor()
cursor.executemany(sql, val)
connection.commit()

От нас зависит, какой подход выбрать для вставки записей в таблицу MySQL. Эксперт SQL может смело использовать .execute(). А вот если слово с SQL повергает в лёгкое недоумение, то будет проще использовать .executemany(). При любом из этих двух подходов можно успешно добиться своего и вставить нужные записи в таблицы posts, comments и likes.

PostgreSQL

В предыдущем разделе мы показали два подхода для вставки записей в таблицы базы данных SQLite. Первый использует запрос строки SQL, а второй использует .executemany(). psycopg2 следует этому второму подходу, хотя .execute() используется для выполнения запроса на основе заполнителя.

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

users = [
 ("James", 25, "male", "USA"),
 ("Leila", 32, "female", "France"),
 ("Brigitte", 35, "female", "England"),
 ("Mike", 40, "male", "Denmark"),
 ("Elizabeth", 21, "female", "Canada"),
]

user_records = ", ".join(["%s"] * len(users))

insert_query =(
  f"INSERT INTO users(name, age, gender, nationality) VALUES {user_records}"
)

connection.autocommit = True
cursor = connection.cursor()
cursor.execute(insert_query, users)

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

Взгляните на другой пример вставки записей в таблицу PostgreSQL. Следующий скрипт вставляет записи в таблицу posts :

posts = [
 ("Happy", "I am feeling very happy today", 1),
 ("Hot Weather", "The weather is very hot today", 2),
 ("Help", "I need some help with my work", 2),
 ("Great News", "I am getting married", 1),
 ("Interesting Game", "It was a fantastic game of tennis", 5),
 ("Party", "Anyone up for a late-night party today?", 3),
]

post_records = ", ".join(["%s"] * len(posts))

insert_query =(
  f"INSERT INTO posts(title, description, user_id) VALUES {post_records}"
)

connection.autocommit = True
cursor = connection.cursor()
cursor.execute(insert_query, posts)

You can insert records into the comments and likes tables with the same approach.

Отбор записей

In this section, you’ll see how to select records from database tables using the different Python SQL modules. In particular, you’ll see how to perform SELECT queries on your SQLite, MySQL, and PostgreSQL databases.

SQLite

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

Чтобы упростить процесс, вы можете создать функцию execute_read_query() :

def execute_read_query(connection, query):
  cursor = connection.cursor()
  result = None
  try:
    cursor.execute(query)
    result = cursor.fetchall()
    return result
  except Error as e:
    print(f"The error '{e}' occurred")

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

SELECT

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

select_users = "SELECT * from users"
users = execute_read_query(connection, select_users)

for user in users:
  print(user)

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

Примечание:

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

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

(1, 'James', 25, 'male', 'USA')
(2, 'Leila', 32, 'female', 'France')
(3, 'Brigitte', 35, 'female', 'England')
(4, 'Mike', 40, 'male', 'Denmark')
(5, 'Elizabeth', 21, 'female', 'Canada')

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

select_posts = "SELECT * FROM posts"
posts = execute_read_query(connection, select_posts)

for post in posts:
  print(post)

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

(1, 'Happy', 'I am feeling very happy today', 1)
(2, 'Hot Weather', 'The weather is very hot today', 2)
(3, 'Help', 'I need some help with my work', 2)
(4, 'Great News', 'I am getting married', 1)
(5, 'Interesting Game', 'It was a fantastic game of tennis', 5)
(6, 'Party', 'Anyone up for a late-night party today?', 3)

В результате отображаются все записи в таблице posts.

JOIN

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

select_users_posts = """
SELECT
 users.id,
 users.name,
 posts.description
FROM
 posts
 INNER JOIN users ON users.id = posts.user_id
"""

users_posts = execute_read_query(connection, select_users_posts)

for users_post in users_posts:
  print(users_post)

Вот результат:

(1, 'James', 'I am feeling very happy today')
(2, 'Leila', 'The weather is very hot today')
(2, 'Leila', 'I need some help with my work')
(1, 'James', 'I am getting married')
(5, 'Elizabeth', 'It was a fantastic game of tennis')
(3, 'Brigitte', 'Anyone up for a late night party today?')

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

select_posts_comments_users = """
SELECT
 posts.description as post,
 text as comment,
 name
FROM
 posts
 INNER JOIN comments ON posts.id = comments.post_id
 INNER JOIN users ON users.id = comments.user_id
"""

posts_comments_users = execute_read_query(
  connection, select_posts_comments_users
)

for posts_comments_user in posts_comments_users:
  print(posts_comments_user)

Смотрим результат:

('Anyone up for a late night party today?', 'Count me in', 'James')
('I need some help with my work', 'What sort of help?', 'Elizabeth')
('I am getting married', 'Congrats buddy', 'Leila')
('It was a fantastic game of tennis', 'I was rooting for Nadal though', 'Mike')
('I need some help with my work', 'Help with your thesis?', 'Leila')
('I am getting married', 'Many congratulations', 'Elizabeth')

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

cursor = connection.cursor()
cursor.execute(select_posts_comments_users)
cursor.fetchall()

column_names = [description[0] for description in cursor.description]
print(column_names)

Вот эти имена:

['post', 'comment', 'name'] 

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

WHERE

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

select_post_likes = """
SELECT
 description as Post,
 COUNT(likes.id) as Likes
FROM
 likes,
 posts
WHERE
 posts.id = likes.post_id
GROUP BY
 likes.post_id
"""

post_likes = execute_read_query(connection, select_post_likes)

for post_like in post_likes:
  print(post_like)

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

('The weather is very hot today', 1)
('I need some help with my work', 1)
('I am getting married', 2)
('It was a fantastic game of tennis', 1)
('Anyone up for a late night party today?', 2)

Используя предложение WHERE можно получать более детальнык результаты.

MySQL

Процесс выбора записей в MySQL абсолютно идентичен процессу выбора записей в SQLite. Вы можете использовать cursor.execute(), а затем .fetchall(). Следующий скрипт описывает функцию-обёртку execute_read_query(), которую можно использовать для отбора записей:

def execute_read_query(connection, query):
  cursor = connection.cursor()
  result = None
  try:
    cursor.execute(query)
    result = cursor.fetchall()
    return result
  except Error as e:
    print(f"The error '{e}' occurred")

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

select_users = "SELECT * FROM users"
users = execute_read_query(connection, select_users)

for user in users:
  print(user)

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

PostgreSQL

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

def execute_read_query(connection, query):
  cursor = connection.cursor()
  result = None
  try:
    cursor.execute(query)
    result = cursor.fetchall()
    return result
  except OperationalError as e:
    print(f"The error '{e}' occurred")

select_users = "SELECT * FROM users"
users = execute_read_query(connection, select_users)

for user in users:
  print(user)

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

Обновление записей в таблицах

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

SQLite

Обновление записей в SQLite довольно просто. Мы снова можем использовать execute_query(), например, обновим пост с id, равным 2. Сначала используем SELECT и прочитаем это пост:

select_post_description = "SELECT description FROM posts WHERE id = 2"

post_description = execute_read_query(connection, select_post_description)

for description in post_description:
  print(description)

Мы должны увидеть следующий вывод:

('The weather is very hot today',) 

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

update_post_description = """
UPDATE
 posts
SET
 description = "The weather has become pleasant now"
WHERE
 id = 2
"""

execute_query(connection, update_post_description)

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

('The weather has become pleasant now',) 

Запись была обновлена.

MySQL

Процесс обновления записей в MySQL с помощью mysql‑connector‑python является полной копией SQL-модуля sqlite3. Нам необходимо передать строковый запрос в cursor.execute(). Например, следующий скрипт обновляет описание поста с id равным 2:

update_post_description = """
UPDATE
 posts
SET
 description = "The weather has become pleasant now"
WHERE
 id = 2
"""

execute_query(connection, update_post_description)

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

PostgreSQL

Запрос на обновление PostgreSQL подобен тому, что мы видели в SQLite и MySQL. Можно использовать вышеупомянутые сценарии для обновления записей в таблице PostgreSQL.

Удаление записей в таблицах

В этом разделе вы увидите, как удалять записи таблиц в базах данных SQLite, MySQL и PostgreSQL. Процесс удаления записей одинаков для всех трех СУБД и запрос DELETE для трех баз данных одинаков.

SQLite

Опять же воспользуемся execute_query() для удаления записи из нашей базы данных SQLite. Все, что нужно сделать, это передать объект connection и строку запроса для записи, которую хотим удалить, в execute_query(). Затем execute_query() создаст объект cursor и, используя connection, передаст строку запроса в cursor.execute(), который удалит записи.

Например, попробуйте удалить комментарий с id, равным 5 :

delete_comment = "DELETE FROM comments WHERE id = 5"
execute_query(connection, delete_comment)

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

MySQL

Процесс удаления в MySQL также похож на SQLite и показан в следующем примере:

delete_comment = "DELETE FROM comments WHERE id = 2"
execute_query(connection, delete_comment)

Здесь мы удаляем второй комментарий из таблицы comments базы данных db_python_app на своем сервере MySQL.

PostgreSQL

Запрос на удаление для PostgreSQL также похож на SQLite и MySQL. Вы можете написать строку запроса на удаление, используя ключевое слово DELETE, а затем передав запрос и объект connection в execute_query(). Это удалит указанные записи из вашей базы данных PostgreSQL.

Заключение

Из этого руководства мы узнали, как использовать три распространенные SQL‑библиотеки Python. sqlite3, mysql-connector-python и psycopg2 позволяют подключать приложение Python к базам данных SQLite, MySQL и PostgreSQL соответственно.
Теперь мы можем:

  • Взаимодействовать с базами данных SQLite, MySQL или PostgreSQL
  • Использовать три разных модуля Python SQL
  • Выполннять SQL‑запросов к различным базам данных из приложения Python

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

По мотивам: Introduction to Python SQL Libraries

Опубликовано Вадим В. Костерин

Ст.преп. кафедры ИТЭ. Автор более 130 научных и учебно-методических работ. Лауреат ВДНХ (серебряная медаль).

Оставьте комментарий

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