Содержание:
- Логическая структура БД
- Подключение к базам данных SQL из Python
- Создание таблиц
- Добавление записей
- Выборка записей
- Обновление табличных записей
- Удаление записей
Многие программы взаимодействуют с данными с помощью систем управления базами данных (СУБД). В одних языках программирования предусмотрены встроенные модули для работы с СУБД, другие же требуют использования библиотек, предоставляемых сторонними пакетами.
В этой статье рассмотрены различные SQL-библиотеки Python, а также процесс создания простого приложения для работы с базами данных SQLite, MySQL и PostgreSQL.
Благодаря этому руководству можно научиться:
- Подключаться к различным СУБД с помощью SQL-библиотек Python.
- Работать с базами SQLite, MySQL и PostgreSQL.
- Выполнять из приложения Python типичные запросы к базам данных.
- Разрабатывать приложения для различных баз данных, используя скрипты Python.
Для получения максимальной пользы от руководства нужно знать основы Python, SQL и работы с СУБД. Вы должны уметь скачивать и импортировать пакеты в Python. Знать, как устанавливать и запускать разные серверы баз данных, локально или удалённо.
Логическая структура базы данных
В этом руководстве мы создадим небольшую базу данных для приложения социальной сети. База будет состоять из четырёх таблиц:
- users (пользователи).
- posts (публикации).
- comments (комментарии).
- likes (лайки).
Логическая структура нашей базы данных показана ниже:
У таблиц «users» и «posts» будут связи типа «один ко многим», поскольку один пользователь может поставить лайк нескольким постам. Точно так же один пользователь может оставить много комментариев или сделано много комментариев к одной и той же публикации.
Поэтому таблицы «users» и «posts» имеют связь типа «один ко многим» с таблицей «comments». То же самое с таблицей «likes» — таблицы «users» и «posts» будут иметь связь типа «один ко многим» и с ней.
Подключение к базам данных с помощью SQL-библиотек Python
Прежде чем работать с любой базой при помощи SQL-библиотек Python, к ней необходимо подключиться. В этом разделе вы увидите, как подключиться к PostgreSQL, SQLite и MySQL из приложения Python.
Примечание. Для выполнения скриптов из подразделов MySQL и PostgreSQL вам понадобятся их запущенные серверы.
Рекомендуется создать для каждой из трёх СУБД по отдельному файлу Python. Так вы сможете запускать скрипт для каждой базы из нужного файла.
SQLite
SQLite — это, пожалуй, самая простая база данных SQL для Python, поскольку не требует установки внешних SQL модулей. По умолчанию в установленной системе Python уже есть SQL библиотека «sqlite3», которая позволяет подключаться к базе SQLite.
Более того, базы SQLite не нуждаются в сервере и самодостаточны, поскольку просто считывают и записывают данные в файл. В отличие от MySQL и PostgreSQL, для выполнения операций с базами данных даже не нужно устанавливать и запускать серверное приложение.
Подключение к базе SQLite в Python с помощью «sqlite3» происходит следующим образом:
1 import sqlite3
2 from sqlite3 import Error
3
4 def create_connection(path):
5 connection = None
6 try:
7 connection = sqlite3.connect(path)
8 print("Подключение к базе данных SQLite прошло успешно")
9 except Error as e:
10 print(f"Произошла ошибка '{e}'")
11
12 return connection
Как работает этот код:
- Строки 1 и 2 импортируют библиотеку «sqlite3» и класс «Error» этого модуля.
- Строка 4 определяет функцию «.create_connection()», которая принимает в качестве входного параметра путь к базе данных SQLite (path).
- В строке 7 используется функция «.connect()» из модуля «sqlite3», которой передаётся этот путь. Если база данных находится в указанном месте, с ней устанавливается соединение. В противном случае там создаётся новая база и подключение осуществляется уже к ней.
- Строка 8 выводит статус успешного подключения к базе.
- Строка 9 перехватывает исключение, которое может возникнуть, если по методу «.connect()» подключиться к базе SQL не удалось.
- Строка 10 выводит на терминал сообщение об ошибке.
Метод «sqlite3.connect(path)» возвращает объект «(connection)». Его же, в свою очередь, возвращает и наша функция «create_connection()».
Объект «connection» можно использовать для выполнения запросов к базе SQLite. Следующий скрипт устанавливает подключение к SQLite:
connection = create_connection("E:\\sm_app.sqlite")
Когда вы запустите скрипт базы данных SQL, то увидите, что в корневом каталоге создан файл базы данных «sm_app.sqlite». Путь к файлу можно изменить.
MySQL
В отличие от SQLite, в Python нет встроенного модуля для подключения к базам MySQL. Чтобы подключиться к базе MySQL из Python, нужно установить подходящий SQL-драйвер. Один из таких — «mysql-connector-python».
Скачать этот SQL модуль можно с помощью менеджера пакетов «pip»:
$ pip install mysql-connector-python
Учтите, что MySQL — серверная СУБД. Поэтому на одном сервере может быть много баз. В отличие от SQLite, где подключение к базе равносильно её созданию, в MySQL для создания базы нужны два шага:
- Подключение к серверу MySQL.
- Выполнение запроса на создание базы данных SQL.
Подключение к серверу MySQL
Определим функцию, которая подключается к серверу MySQL и возвращает объект «connection»:
1 import mysql.connector
2 from mysql.connector import Error
3
4 def create_connection(host_name, user_name, user_password):
5 connection = None
6 try:
7 connection = mysql.connector.connect(
8 host=host_name,
9 user=user_name,
10 passwd=user_password
11 )
12 print("Подключение к базе данных MySQL прошло успешно")
13 except Error as e:
14 print(f"Произошла ошибка '{e}'")
15
16 return connection
17
18 connection = create_connection("localhost", "root", "")
В приведенном выше скрипте определяется функция «create_connection()». Она принимает три параметра:
- host_name (имя сервера)
- user_name (имя пользователя)
- user_password (пароль пользователя)
В строке 7 для подключения к серверу MySQL используется метод «.connect()» из модуля «mysql.connector». После установки соединения объект «connection» возвращается вызывающей функции.
Наконец, в строке 18 вызывается функция «create_connection()». Её аргументами служат имя сервера, пользователь и пароль.
Выполнение запроса на создание базы данных
Пока мы только установили подключение MySQL к Python, но база данных ещё не создана. Чтобы это сделать, мы определим ещё функцию «create_database()». Она будет принимать два параметра:
- connection — объект подключения к серверу баз данных.
- query — запрос, который создаёт новую базу.
Функция выглядит так:
def create_database(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
print("База данных создана успешно")
except Error as e:
print(f"Произошла ошибка '{e}'")
Для выполнения SQL запросов используется объект «cursor». Запрос «query» передаётся методу «cursor.execute()» в формате строки.
Создадим на сервере MySQL базу данных под названием «sm_app» для нашего приложения соцсети:
create_database_query = "CREATE DATABASE sm_app" create_database(connection, create_database_query)
Теперь на сервере баз данных создана база «sm_app». Однако, объект «connection», возвращённый функцией «create_connection()», всё ещё указывает на сам сервер баз данных MySQL. А нам нужно подключиться к базе «sm_app». Чтобы сделать это, изменим функцию «create_connection()» так:
1 def create_connection(host_name, user_name, user_password, db_name):
2 connection = None
3 try:
4 connection = mysql.connector.connect(
5 host=host_name,
6 user=user_name,
7 passwd=user_password,
8 database=db_name
9 )
10 print("Подключение к базе данных MySQL прошло успешно")
11 except Error as e:
12 print(f"Произошла ошибка '{e}'")
13
14 return connection
Как видно из приведенного кода, на строке 8 теперь функция «create_connection()» принимает дополнительный параметр «db_name». Он указывает имя базы данных для подключения. Теперь имя базы, к которой вы хотите подключиться, можно передать при вызове функции:
connection = create_connection("localhost", "root", "", "sm_app")
Этот скрипт успешно вызывает функцию «create_connection()» и подключается к базе «sm_app».
PostgreSQL
Как и в случае с MySQL, в PostgreSQL нет встроенной в Python SQL библиотеки. Для подключения к PostgreSQL из Python можно использовать сторонний драйвер баз данных. Примером может послужить модуль «psycopg2».
Для установки в Python SQL-модуля «psycopg2» выполним в терминале следующую команду:
$ pip install psycopg2
Как и в случае с MySQL и SQLite, для подключения к базе PostgreSQL определим функцию «create_connection()»:
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("Подключение к базе данных PostgreSQL прошло успешно")
except OperationalError as e:
print(f"Произошла ошибка '{e}'")
return connection
Для подключения из нашего приложения Python к серверу PostgreSQL используется метод «psycopg2.connect()».
После этого для подключения к самой базе можно использовать функцию «create_connection()». Сперва подключимся к «postgres», базе по умолчанию:
connection = create_connection( "postgres", "postgres", "abc123", "127.0.0.1", "5432" )
Затем нужно создать внутри базы «postgres» уже нашу базу «sm_app». Можно определить функции для выполнения в PostgreSQL любых SQL-запросов. Ниже определим функцию «create_database()», которая создаст новую базу данных на сервере PostgreSQL.
def create_database(connection, query):
connection.autocommit = True
cursor = connection.cursor()
try:
cursor.execute(query)
print("Запрос выполнен успешно")
except OperationalError as e:
print(f"Произошла ошибка '{e}'")
create_database_query = "CREATE DATABASE sm_app"
create_database(connection, create_database_query)
После запуска скрипта мы увидим, что на сервере PostgreSQL создана база «sm_app».
Прежде чем выполнять SQL запросы к базе «sm_app», к ней нужно подключиться:
connection = create_connection( "sm_app", "postgres", "abc123", "127.0.0.1", "5432" )
При выполнении скрипта установится соединение с базой «sm_app» на сервере баз данных «postgres». Параметр «127.0.0.1» задаёт IP-адрес сервера баз данных, а «5432» — это номер порта сервера баз данных.
Создание таблиц
В предыдущем разделе вы узнали, как подключиться к базам данных SQLite, MySQL и PostgreSQL, используя SQL-библиотеки Python. На всех трёх СУБД мы создали базу «sm_app». В этом разделе расскажем, как создать таблицы баз данных SQL внутри этих трёх баз.
Как уже говорилось, мы будем создавать четыре таблицы:
- users;
- posts;
- comments;
- likes.
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("Запрос выполнен успешно")
except Error as e:
print(f"Произошла ошибка '{e}'")
Этот код пытается выполнить запрос «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» с пятью столбцами:
- id (идентификатор);
- name (имя);
- age (возраст);
- gender (пол)
- nationality (гражданство).
Наконец, для создания этой таблицы выполняем «execute_query()». Мы передаём созданный в предыдущем сеансе объект «connection» вместе со строкой «create_users_table», которая содержит запрос на создание таблицы.
execute_query(connection, create_users_table)
Следующий запрос создаст таблицу «posts»:
create_posts_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» связаны по типу «один ко многим». Поэтому в таблице «posts» есть внешний ключ «user_id», отсылающий к столбцу «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)
Как видно из примера, создание таблиц в SQLite очень похоже на использование языка SQL напрямую. Нужно лишь поместить запрос в строковую переменную и передать её методу «cursor.execute()».
MySQL
Для создания таблиц в MySQL воспользуемся драйвером «mysql-connector-python». Точно так же, как и с SQLite, нам нужно передать методу «cursor.execute()» запрос, который возвращается функцией «.cursor()» по объекту «connection».
Мы можем создать свою функцию «execute_query()». Её аргументами также будут объект подключения «connection» и строка запроса «query»:
1 def execute_query(connection, query):
2 cursor = connection.cursor()
3 try:
4 cursor.execute(query)
5 connection.commit()
6 print("Запрос выполнен успешно")
7 except Error as e:
8 print(f"Произошла ошибка '{e}'")
На строке 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)
По сравнению с SQLite, запрос на создание отношения по внешнему ключу для MySQL немного отличается. Кроме того, для создания столбцов, значение которых при добавлении новых записей автоматически возрастает на единицу, 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», передав методу «execute_query()» запросы «CREATE».
PostgreSQL
Как и в случае с MySQL и SQLite, возвращаемый функцией «psycopg2.connect()» объект «connection» содержит в себе объект «cursor». Для выполнения SQL-запросов к базам данных PostgreSQL в Python воспользуемся методом «cursor.execute()».
Определим функцию «execute_query()»:
def execute_query(connection, query):
connection.autocommit = True
cursor = connection.cursor()
try:
cursor.execute(query)
print("Запрос выполнен успешно")
except OperationalError as e:
print(f"Произошла ошибка '{e}'")
С помощью этой функции в базе PostgreSQL можно создавать таблицы, а также добавлять, изменять и удалять записи.
Теперь создадим внутри базы «sm_app» таблицу «users»:
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)
Как видно, в PostgreSQL запрос на создание таблицы «users» немного отличается от аналогичных для 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» и передать функции «execute_query()». Процесс создания для таблицы «likes» тот же. Нужно лишь изменить запрос «CREATE» так, чтобы вместо таблицы «comments» создалась «likes».
Добавление записей
В предыдущем разделе вы узнали, как создавать таблицы в базах SQLite, MySQL и PostgreSQL с помощью разных модулей Python. В этом разделе вы узнаете, как добавлять данные в ваши таблицы.
SQLite
Чтобы добавить записи в нашу базу SQLite, можно использовать ту же функцию «execute_query()», что мы использовали для создания таблиц. Сперва нужно задать строку с запросом «INSERT INTO». Затем можно передать объект «connection» и строковой запрос «query» на вход функции «execute_query()».
Внесём в таблицу «users» пять записей:
create_users = """
INSERT INTO
users (name, age, gender, nationality)
VALUES
('Джеймс', 25, 'мужской', 'США'),
('Лейла', 32, 'женский', 'Франция'),
('Бриджит', 35, 'женский', 'Англия'),
('Майк', 40, 'мужской', 'Дания'),
('Элизабет', 21, 'женский', 'Канада');
"""
execute_query(connection, create_users)
Так как мы задали для столбца «id» автоинкремент, указывать его значение для таблицы «users» незачем. Она сама заполнит значения «id» для этих пяти записей числами от 1 до 5.
Теперь внесём шесть записей в таблицу «posts»:
create_posts = """
INSERT INTO
posts (title, description, user_id)
VALUES
("Счастлив", "Сегодня я очень счастлив", 1),
("Жара", "Погода сегодня очень жаркая", 2),
("Помогите", "Мне надо немного помочь с работой", 2),
("Отличная новость", "Я женюсь", 1),
("Интересная игра", "Это был потрясающий теннисный матч", 5),
("Вечеринка", "Кто готов сегодня ночью тусить?", 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
('Я с вами', 1, 6),
('А с чем помочь?', 5, 3),
('Поздравляю, чувак', 2, 4),
('А я за Надаля болел', 4, 5),
('Помочь тебе с дипломом?', 2, 3),
('Мои поздравления', 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», а затем для добавления записей в таблицу SQL вызвать функцию «cursor.execute()».
Ранее мы уже определили функцию-обёртку «execute_query()», с помощью которой добавляли записи. Ту же самую функцию можно использовать для внесения записей в таблицу MySQL. Следующий скрипт вносит записи в таблицу «users» с помощью функции «execute_query()»:
create_users = """
INSERT INTO
`users` (`name`, `age`, `gender`, `nationality`)
VALUES
('Джеймс', 25, 'мужской', 'США'),
('Лейла', 32, 'женский', 'Франция'),
('Бриджит', 35, 'женский', 'Англия'),
('Майк', 40, 'мужской', 'Дания'),
('Элизабет', 21, 'женский', 'Канада');
"""
execute_query(connection, create_users)
Второй подход использует метод «cursor.executemany()», принимающий два входных параметра:
- Строку запроса, которая содержит заполнители для вносимых записей.
- Список записей для добавления.
Посмотрим на случай, в котором в таблицу «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()».
Если вы мало с ним знакомы, проще будет обратиться к методу «.executemany()». Успешно добавить записи в таблицы «posts», «comments» и «likes» позволят оба подхода.
PostgreSQL
В предыдущем разделе мы рассмотрели два подхода, которые позволяют добавлять записи в таблицы базы MySQL. Первый использует строку с SQL-запросом, а второй — метод «.executemany()».
Модуль «psycopg2» следует второму подходу c использованием заполнителей «%s», хотя его метод и назван просто «.execute()». Поэтому мы передаём этому методу SQL запрос на добавление записей с заполнителями и список записей.
Каждая запись в списке представляет собой кортеж, значения которого соотносятся со столбцами в таблице базы. Добавить пользователей в таблицу «users» базы PostgreSQL можно так:
users = [
("Джеймс", 25, "мужской", "США"),
("Лейла", 32, "женский", "Франция"),
("Бриджит", 35, "женский", "Англия"),
("Майк", 40, "мужской", "Дания"),
("Элизабет", 21, "женский", "Канада"),
]
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 = [
("Счастлив", "Сегодня я очень счастлив", 1),
("Жара", "Погода сегодня очень жаркая", 2),
("Помогите", "Мне надо немного помочь с работой", 2),
("Отличная новость", "Я женюсь", 1),
("Интересная игра", "Это был потрясающий теннисный матч", 5),
("Вечеринка", "Кто готов сегодня ночью тусить?", 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)
Добавить записи в таблицы «comments» и «likes» можно точно так же.
Выборка записей
В этом разделе вы узнаете, как делать из таблиц выборки записей с помощью SQL модулей Python. В частности, выполнять запросы «SELECT» для наших баз SQLite, MySQL и PostgreSQL.
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"Произошла ошибка '{e}'")
Эта функция принимает объект «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, 'Джеймс, 25, 'мужской', 'США') (2, 'Лейла', 32, 'женский', 'Франция') (3, 'Бриджит', 35, 'женский', 'Англия') (4, 'Майк', 40, 'мужской', 'Дания') (5, 'Элизабет', 21, 'женский', 'Канада')
Точно так же можно извлечь все записи из таблицы «posts»:
select_posts = "SELECT * FROM posts" posts = execute_read_query(connection, select_posts) for post in posts: print(post)
Результат выглядит примерно так:
(1, 'Счастлив', 'Сегодня я очень счастлив', 1) (2, 'Жара', 'Погода сегодня очень жаркая', 2) (3, 'Помогите', 'Мне надо немного помочь с работой', 2) (4, 'Отличная новость', 'Я женюсь', 1) (5, 'Интересная игра', 'Это был потрясающий теннисный матч', 5) (6, 'Вечеринка', 'Кто готов сегодня ночью тусить?', 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, 'Джеймс', 'Сегодня я очень счастлив') (2, 'Лейла', 'Погода сегодня очень жаркая') (2, 'Лейла', 'Мне надо немного помочь с работой') (1, 'Джеймс', 'Я женюсь') (5, 'Элизабет', 'Это был потрясающий теннисный матч') (3, 'Бриджит', 'Кто готов сегодня ночью тусить?')
Используя несколько операторов «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)
Результат выглядит примерно так:
('Кто готов сегодня ночью тусить?', 'Я с вами, 'Джеймс')
('Мне надо немного помочь с работой', 'А с чем помочь?', 'Элизабет')
('Я женюсь', 'Поздравляю, приятель', 'Лейла')
('Это был потрясающий теннисный матч', 'А я за Надаля болел', 'Майк')
('Мне надо немного помочь с работой', 'Помочь тебе с дипломом?', ''Бриджит')
('Я женюсь', 'Мои поздравления', 'Элизабет')
Как видно, метод «.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)
Результат таков:
('Погода сегодня очень жаркая', 1)
('Мне надо немного помочь с работой', 1)
('Я женюсь', 2)
('Это был потрясающий теннисный матч', 1)
('Кто готов сегодня ночью тусить?', 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"Произошла ошибка '{e}'")
Сделаем выборку всех записей из таблицы «users»:
select_users = "SELECT * from users" users = execute_read_query(connection, select_users) for user in users: print(user)
Результат выполнения похож на то, что мы видели у SQLite.
PostgreSQL
Процесс выборки записей из таблиц PostgreSQL с помощью SQL-модуля «psycopg2» напоминает то, что мы делали с SQLite и MySQL.
Для получения записей из таблицы PostgreSQL мы снова используем сначала метод «cursor.execute()», а затем «.fetchall()». Этот скрипт получает все записи из таблицы «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"Произошла ошибка '{e}'")
select_users = "SELECT * from users"
users = execute_read_query(connection, select_users)
for user in users:
print(user)
Результат аналогичен тому, что мы уже видели.
Обновление табличных записей
В прошлом разделе вы узнали, как получать записи из баз SQLite, MySQL и PostgreSQL. В этом разделе мы рассмотрим, как обновлять записи, используя SQL-библиотеки Python: SQLite, PostgreSQL и MySQL.
SQLite
Обновление записей в SQLite происходит довольно просто. Можно вновь воспользоваться методом «execute_query()».
Для примера обновим текст публикации (поле «description») с идентификатором («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)
Мы должны получить такой результат:
('Погода сегодня очень жаркая',)
Этот скрипт изменит текст:
update_post_description = """ UPDATE posts SET description = "Установилась приятная погода" WHERE id = 2 """ execute_query(connection, update_post_description)
Если теперь снова выполнить запрос «SELECT», вывод команды будет иным:
('Установилась приятная погода',)
Как видно, результат изменился.
MySQL
При использовании драйвера «mysql-connector-python» процесс обновления записей в MySQL ничем не отличается от «sqlite3». Нужно лишь передать строку запроса методу «cursor.execute()».
К примеру такой скрипт обновит текст публикации с идентификатором («id») 2:
update_post_description = """ UPDATE posts SET description = "Установилась приятная погода" WHERE id = 2 """ execute_query(connection, update_post_description)
Здесь для обновления текста публикации мы вновь использовали нашу функцию-обёртку «execute_query()».
PostgreSQL
В PostgreSQL запрос на изменение записи похож на те, что мы видели в SQLite и MySQL. Для обновления записей в таблице PostgreSQL можно использовать те же скрипты.
Удаление табличных записей
В этом разделе вы узнаете, как удалить запись в таблице, используя модули Python для баз данных SQLite, MySQL и PostgreSQL.
Процесс удаления записей для всех трёх СУБД в Python одинаков, поскольку использование оператора «DELETE» в них идентично.
SQLite
Для удаления записей из нашей базы SQLite можно вновь прибегнуть к функции «execute_query()». Нужно лишь передать ей объект «connection» и строку запроса с указанием записи, которую мы хотим удалить.
Затем функция «execute_query()» создаст на основе объекта «connection» объект «cursor» и передаст строку запроса методу «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» в базе данных «sm_app» на сервере MySQL.
PostgreSQL
Запрос на удаление в PostgreSQL выполняется аналогично подобным запросам в SQLite и MySQL.
Можно написать строку с запросом на удаление, в которой будет использоваться оператор «DELETE». Затем передать эту строку вместе с объектом «connection» функции «execute_query()». Это удалит указанные записи из нашей базы PostgreSQL.
Заключение
Из этого руководства вы узнали, как использовать три основные SQL-библиотеки Python. Модули «sqlite3», «mysql-connector-python» и «psycopg2» позволяют подключиться из приложений Python к базам SQLite, MySQL и PostgreSQL соответственно.
Теперь вы умеете:
- Применять Python для работы с MySQL, SQLite и PostgreSQL.
- Использовать три различных SQL модуля Python.
- Выполнять из приложений Python SQL запросы для различных баз данных.
Но всё это только вершина айсберга. В Python есть и SQL-библиотеки для объектно-реляционного отображения (ORM). Например SQLAlchemy или Django ORM, которые автоматизируют работу с SQL базами данных из Python.
Нужна надёжная база для разработки программных продуктов? Выбирайте виртуальные серверы от Eternalhost с технической поддержкой 24/7 и бесплатной защитой от DDoS!
Автор оригинала: Usman Malik
