Редактор базы данных python

Python: Работа с базой данных, часть 1/2: Используем DB-API

Python DB-API – это не конкретная библиотека, а набор правил, которым подчиняются отдельные модули, реализующие работу с конкретными базами данных. Отдельные нюансы реализации для разных баз могут отличаться, но общие принципы позволяют использовать один и тот же подход при работе с разными базами данных.

В статье рассмотрены основные методы DB-API, позволяющие полноценно работать с базой данных. Полный список можете найти по ссылкам в конец статьи.

Требуемый уровень подготовки: базовое понимание синтаксиса SQL и Python.

Готовим инвентарь для дальнейшей комфортной работы

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

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

Вы можете использовать (последние два варианта кросс-платформенные и бесплатные):

Python DB-API модули в зависимости от базы данных

Соединение с базой, получение курсора

Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:

# Импортируем библиотеку, соответствующую типу нашей базы данных import sqlite3 # Создаем соединение с нашей базой данных # В нашем примере у нас это просто файл базы conn = sqlite3.connect('Chinook_Sqlite.sqlite') # Создаем курсор - это специальный объект который делает запросы и получает их результаты cursor = conn.cursor() # ТУТ БУДЕТ НАШ КОД РАБОТЫ С БАЗОЙ ДАННЫХ # КОД ДАЛЬНЕЙШИХ ПРИМЕРОВ ВСТАВЛЯТЬ В ЭТО МЕСТО # Не забываем закрыть соединение с базой данных conn.close()

При работе с другими базами данных, используются дополнительные параметры соединения, например для PostrgeSQL:

conn = psycopg2.connect( host=hostname, user=username, password=password, dbname=database)

Чтение из базы

# Делаем SELECT запрос к базе данных, используя обычный SQL-синтаксис cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") # Получаем результат сделанного запроса results = cursor.fetchall() results2 = cursor.fetchall() print(results) # [('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',), ('Aaron Goldberg',)] print(results2) # []

Обратите внимание: После получения результата из курсора, второй раз без повторения самого запроса его получить нельзя — вернется пустой результат!

Запись в базу

# Делаем INSERT запрос к базе данных, используя обычный SQL-синтаксис cursor.execute("insert into Artist values (Null, 'A Aagrh!') ") # Если мы не просто читаем, но и вносим изменения в базу данных - необходимо сохранить транзакцию conn.commit() # Проверяем результат cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") results = cursor.fetchall() print(results) # [('A Aagrh!',), ('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',)]

Примечание: Если к базе установлено несколько соединений и одно из них осуществляет модификацю базы, то база SQLite залочивается до завершения (метод соединения .commit()) или отмены (метод соединения .rollback()) транзакции.

Разбиваем запрос на несколько строк в тройных кавычках

Длинные запросы можно разбивать на несколько строк в произвольном порядке, если они заключены в тройные кавычки — одинарные (»’…»’) или двойные («»». «»»)

cursor.execute(""" SELECT name FROM Artist ORDER BY Name LIMIT 3 """)

Конечно в таком простом примере разбивка не имеет смысла, но на сложных длинных запросах она может кардинально повышать читаемость кода.

Объединяем запросы к базе данных в один вызов метода

Метод курсора .execute() позволяет делать только один запрос за раз, при попытке сделать несколько через точку с запятой будет ошибка.

cursor.execute(""" insert into Artist values (Null, 'A Aagrh!'); insert into Artist values (Null, 'A Aagrh-2!'); """) # sqlite3.Warning: You can only execute one statement at a time. 

Для решения такой задачи можно либо несколько раз вызывать метод курсора .execute()

cursor.execute("""insert into Artist values (Null, 'A Aagrh!');""") cursor.execute("""insert into Artist values (Null, 'A Aagrh-2!');""")

Либо использовать метод курсора .executescript()

cursor.executescript(""" insert into Artist values (Null, 'A Aagrh!'); insert into Artist values (Null, 'A Aagrh-2!'); """)

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

Делаем подстановку значения в запрос

Важно! Никогда, ни при каких условиях, не используйте конкатенацию строк (+) или интерполяцию параметра в строке (%) для передачи переменных в SQL запрос. Такое формирование запроса, при возможности попадания в него пользовательских данных – это ворота для SQL-инъекций!

Правильный способ – использование второго аргумента метода .execute()

# C подставновкой по порядку на места знаков вопросов: cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT ?", ('2')) # И с использованием именнованных замен: cursor.execute("SELECT Name from Artist ORDER BY Name LIMIT :limit", )

Примечание 1: В PostgreSQL (UPD: и в MySQL) вместо знака ‘?’ для подстановки используется: %s

Примечание 2: Таким способом не получится заменять имена таблиц, одно из возможных решений в таком случае рассматривается тут: stackoverflow.com/questions/3247183/variable-table-name-in-sqlite/3247553#3247553

UPD: Примечание 3: Благодарю Igelko за упоминание параметра paramstyle — он определяет какой именно стиль используется для подстановки переменных в данном модуле.
Вот ссылка с полезным приемом для работы с разными стилями подстановок.

Делаем множественную вставку строк проходя по коллекции с помощью метода курсора .executemany()

# Обратите внимание, даже передавая одно значение - его нужно передавать кортежем! # Именно по этому тут используется запятая в скобках! new_artists = [ ('A Aagrh!',), ('A Aagrh!-2',), ('A Aagrh!-3',), ] cursor.executemany("insert into Artist values (Null, ?);", new_artists)

Получаем результаты по одному, используя метод курсора .fetchone()

Он всегда возвращает кортеж или None. если запрос пустой.

cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") print(cursor.fetchone()) # ('A Cor Do Som',) print(cursor.fetchone()) # ('Aaron Copland & London Symphony Orchestra',) print(cursor.fetchone()) # ('Aaron Goldberg',) print(cursor.fetchone()) # None

Важно! Стандартный курсор забирает все данные с сервера сразу, не зависимо от того, используем мы .fetchall() или .fetchone()

Курсор как итератор

# Использование курсора как итератора for row in cursor.execute('SELECT Name from Artist ORDER BY Name LIMIT 3'): print(row) # ('A Cor Do Som',) # ('Aaron Copland & London Symphony Orchestra',) # ('Aaron Goldberg',)

UPD: Повышаем устойчивость кода

Благодарю paratagas за ценное дополнение:
Для большей устойчивости программы (особенно при операциях записи) можно оборачивать инструкции обращения к БД в блоки «try-except-else» и использовать встроенный в sqlite3 «родной» объект ошибок, например, так:

try: cursor.execute(sql_statement) result = cursor.fetchall() except sqlite3.DatabaseError as err: print("Error: ", err) else: conn.commit()

UPD: Использование with в psycopg2

 with psycopg2.connect("dbname='habr'") as conn: with conn.cursor() as cur:

Некоторые объекты в Python имеют __enter__ и __exit__ методы, что позволяет «чисто» взаимодействовать с ними, как в примере выше.

UPD: Ипользование row_factory

Благодарю remzalp за ценное дополнение:
Использование row_factory позволяет брать метаданные из запроса и обращаться в итоге к результату, например по имени столбца.
По сути — callback для обработки данных при возврате строки. Да еще и полезнейший cursor.description, где есть всё необходимое.

import sqlite3 def dict_factory(cursor, row): d = <> for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d con = sqlite3.connect(":memory:") con.row_factory = dict_factory cur = con.cursor() cur.execute("select 1 as a") print(cur.fetchone()["a"])

Дополнительные материалы (на английском)

Краткий бесплатный он-лайн курс — Udacity — Intro to Relational Databases — Рассматриваются синтаксис и принципы работы SQL, Python DB-API – и теория и практика в одном флаконе. Очень рекомендую для начинающих!

Приглашаю к обсуждению:

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

Источник

Редактор базы данных python

2. Создать подключение к базе данных SQLite:

Более продвинутый браузер/редактор – DB Browser :

Из многофункциональных инструментов для работы с SQLite отлично подходит базовая версия Dbeaver .

Добавление записей в БД

Внесем в базу первую запись – информацию о пользователе по имени Инна Егорова, 20 лет, с факультета прикладной математики:

Редактирование записей

Изменим возраст для пользователя с именем Инна и фамилией Егорова:

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

Напишем запрос на удаление из БД всех записей, которые содержат «Прикладная математика» в поле faculty :

Запросы на извлечение данных из нескольких таблиц

В реальных приложениях очень часто необходимо извлекать данные из нескольких таблиц сразу. Проиллюстрируем на примере базы данных university, в которой содержатся две таблицы – students и fees. Размер оплаты за обучение (fee) снижается на 3% каждый год. Кроме того, студенты, у который средний балл успеваемости соответствует установленному критерию, могут рассчитывать на дополнительную скидку.

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

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

Напишите программу, которая:

• Находит и выводит список записей, у которых есть дубликаты.
• Предлагает удалить дубликаты, и в случае ответа yes – удаляет их.

import sqlite3 con = sqlite3.connect('sales.db') cursor = con.cursor() # Находим дубликаты cursor.execute(''' SELECT brand, model, engine_volume, max_speed, price, COUNT(*) FROM items GROUP BY LOWER(brand), LOWER(model), engine_volume, max_speed, price HAVING COUNT(*) > 1 ''') # Выводим найденные дубликаты duplicates = cursor.fetchall() if duplicates: print('У этих записей есть дубликаты:') for row in duplicates: print(row[:-1]) answer = input('Хотите удалить дубликаты? Введите yes/no ' ) if answer.lower() == 'yes': # Создаем подключение к базе данных con = sqlite3.connect('sales.db') # Создаем курсор для взаимодействия с базой данных cursor = con.cursor() # Удаляем дубликаты со значением не начинающимся на заглавную букву cursor.execute(''' DELETE FROM items WHERE id NOT IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY LOWER(brand), LOWER(model), color, engine_volume, max_speed, price ORDER BY id ) row_number FROM items ) WHERE row_number = 1 or (row_number = 2 and brand = LOWER(brand) and model = LOWER(model)) ) ''') # Сохраняем изменения в базе данных con.commit() print('Дубликаты удалены') else: print('Не забудьте удалить дубликаты') else: print('Дубликаты не найдены') # Закрываем соединение с базой данных con.close() 

Подведем итоги

Мы научились выполнять основные операции с базой данных SQLite :

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

Работа с базами данных – обширная тема, и ее невозможно подробно рассмотреть в одной статье. За кадром осталось многое, в том числе работа с таблицами, связанными отношениями один к одному, один ко многим, многие ко многим и многие к одному. Эту тему мы подробно разберем в новом курсе по Django.

1. Особенности, сферы применения, установка, онлайн IDE
2. Все, что нужно для изучения Python с нуля – книги, сайты, каналы и курсы
3. Типы данных: преобразование и базовые операции
4. Методы работы со строками
5. Методы работы со списками и списковыми включениями
6. Методы работы со словарями и генераторами словарей
7. Методы работы с кортежами
8. Методы работы со множествами
9. Особенности цикла for
10. Условный цикл while
11. Функции с позиционными и именованными аргументами
12. Анонимные функции
13. Рекурсивные функции
14. Функции высшего порядка, замыкания и декораторы
15. Методы работы с файлами и файловой системой
16. Регулярные выражения
17. Основы скрапинга и парсинга
18. Основы ООП: инкапсуляция и наследование
19. Основы ООП: абстракция и полиморфизм
20. Графический интерфейс на Tkinter
21. Основы разработки игр на Pygame
22. Основы работы с SQLite
23. Основы веб-разработки на Flask
24. Основы работы с NumPy

Материалы по теме

Источник