Как создать sqlite файл в pycharm
Даже среди тех, кто давно и активно пользуется IntelliJ IDEA, есть немало людей, ничего не знающих о плагине, позволяющем работать с базами данных прямо из IDE. Между тем, лично для меня это — одна из самых полезных возможностей IDEA, ибо писать код я могу и в vi. Важно, насколько удобно его потом отлаживать и улучшать. Спрашивается, при чем тут Database support? Собственно, при отладке приложений я его и использую.
Рассказ про плагин Database Support будет совершенно неофициальным, я сам его использовал только в веб-разработке и в разработке под Android, но хардкорным разработчикам жесткого enterprise тоже, надеюсь, будет интересно.
История веб-разработчика
Как-то раз мне пришла в голову мысль написать веб-приложение. Был шанс, что нагрузка на него будет довольно высокой и неравномерной, так что я решил в качестве СУБД использовать PostgreSQL в надежде, что она будет работать быстрее привычной MySQL. Сказано — сделано. Прототип написан, база создана, начинаем заполнять таблицы всякой тестовой гадостью.
Встает вопрос, есть ли какая-нибудь универсальная штука, которая может работать удобным интерфейсом к БД, причем интерфейсом, внешний вид которого не зависит от БД, с которой он работает?
В JetBrains такую штуку довольно давно придумали, это плагин Database Support, который работает с IntelliJ IDEA Ultimate, RubyMine, PyCharm Professional Edition и PhpStorm. Он довольно сложен в разработке и поддержке, так что в бесплатные IDE он не ставится.
Посмотрим, чем же он нам может помочь. Для начала, настроим его для работы с PostgreSQL. Если у нас на компьютере еще нет драйвера JDBC для PostgreSQL, IDE нам предложит его скачать и установить. В моем случае СУБД запущена на том же компьютере, что и IDE:
Видите вкладку Database по правому краю? Вот при нажатии на нее и открывается окно плагина Database Support (можно его открыть и через View | Tool Windows | Database, и через список окон Tool Windows):
Теперь посмотрим, что же мы можем сделать с его помощью. Во-первых, можно смотреть структуру БД в окне плагина, а если надо вывести UML-диаграмму, то и это можно (нажимаем <Ctrl+Alt+U>). Во-вторых, нам доступны просмотр и редактирование таблиц БД. Нажимаем F4 на требуемой таблице и вот она, перед нами:
Любую ячейку мы можем редактировать, нажав F2, отметка «Auto-commit» означает, что после передачи изменений в БД будет автоматически делаться commit. Если отметку снять, то можно будет делать commit и rollback вручную.
под спойлером - коллаж, одновременно увидеть подсказки Commit и Rollback невозможно, не пытайтесь!
При сортировке таблицы (кликайте в имя столбца) изменений в БД не происходит, ибо таблица — это просто результат SELECT *, и если пока вы ее просматриваете, БД изменяется кем-то еще, вы этого не увидите.
Сортировку можно делать по разным столбцам — вначале по одному, потом по другому, а кнопка сброса сортировок (Reset columns order and visibility) появится, если окно достаточно широкое, или вы наведете мышь на двойные угловые скобки слева от настройки Auto-commit.
В этом же окне выбора отображается, в каком порядке сортируются столбцы (A — ascending, по возрастанию, D — descending, по убыванию). Если шрифт вашего look and feel позволяет, то вместо A и D вы увидите красивые стрелочки вверх и вниз. Выбирать столбцы для просмотра можно пробелом.
Сортировку можно делать как в таблице на стороне IDE, так и заранее — на стороне базы. Последнее делается кнопкой ORDER BY, а с помощью View Query можно посмотреть, какой именно запрос IDEA отправляет в базу:
Из неочевидных возможностей стоит отметить недавно — в EAP 13-й версии — появившееся транспонирование строк (в IDEA 12 такого не было): если столбцов так много, что строка не умещается на экран, то двойным <Ctrl+Q> ее можно получить в виде столбца, и стрелочками еще бегать вверх-вниз по таблице с длинными строками. С содержимым базы, разумеется, при этом ничего не происходит, оно остается прежним.
Пока что мы работали с Table View базы. Однако поддерживается и режим консоли (нажимаем на значок консоли над tree view в окне Databases):
Теперь мы можем общаться с базой напрямую, отдавая ей SQL-команды, причем в SQL полностью поддерживается знакомый нам autocompletion по <Ctrl+пробел>.
В консольном режиме тоже есть настройка «Auto-commit». Как и раньше, она означает, что после передачи изменений в БД транзакция будет подтверждаться автоматически. Если отметку снять, то справа от нее активируются кнопки для ручного commit и rollback.
Результаты запросов, если таковые ожидаются (например, при SELECT'ах) будут показаны в отдельной таблице внизу экрана, и их можно просматривать также, как и табличное представление базы, вызванное по F4.
Естественно, что с помощью этого плагина можно менять не только значения полей в таблицах, но и структуру таблиц, для этого легко использовать контекстные меню в дереве БД справа.
История разработчика под Андроид
Если бы поддержкой традиционных БД дело и ограничивалось, то статья на Хабр могла бы и не попасть: в работе с БД с JDBC есть много удобств, но для хабрастатьи не хватало финального аккорда. Аккорд случился осенью 2013-го, когда я пришел к моему коллеге, пишущему всю поддержку Android в JetBrains.
Разработчик под Android легко узнает свои мучения в скриншоте командной строки ниже. Для того, чтобы поработать с базой SQLite на устройстве или эмуляторе напрямую, без приложения, приходится звать adb shell, потом вызывать sqlite3, и просить его открыть базу по адресу, в который входит полное имя пакета приложения. А имя может быть длинным. И autocompletion к adb shell не прилагается.
Поэтому мой визит был жестом отчаяния: даже для того, чтобы сказать DROP DATABASE, когда в таблицах набралось достаточно ненужных тестовых данных, приходилось тратить время на рутинный набор длинных строк.
Любимое дело разработчиков в JetBrains — борьба с рутиной, так что мои горячие просьбы что-нибудь с этим сделать хорошо легли в русло добрых намерений, уже проложенное пятьюдесятью голосами за эту фичу в нашем трекере. Несмотря на тучу других задач, поддержку SQLite в Android допилили за две недели. Решение получилось элегантным: база SQLite вытягивается на компьютер из устройства или эмулятора прозрачно для пользователя, затем с локальной копией можно работать как с обычной БД, а затем ее можно залить в устройство обратно, вызвав Upload из контекстного меню.
Для уверенности в том, что вы работаете с актуальной копией БД, можно синхронизироваться с нею на устройстве, нажав кнопку Syncronize или <Ctrl+Alt+Y>:
В остальном работа с БД при разработке под Android аналогична работе с обычным SQL-сервером.
В заключение остается сказать, что плагин Database Support поддерживает все СУБД, для которых существуют драйверы JDBC (а это практически все популярные СУБД на рынке), и понимает 12 диалектов SQL. Какой именно диалект вы хотите использовать (и иметь соответствующее автодополнение и прочие фишечки к нему), можно указать в настройках IDE: Settings | SQL Dialects.
В статье рассмотрены основные методы DB-API, позволяющие полноценно работать с базой данных. Полный список можете найти по ссылкам в конец статьи.
Требуемый уровень подготовки: базовое понимание синтаксиса SQL и Python.
Готовим инвентарь для дальнейшей комфортной работы
-
Python имеет встроенную поддержку SQLite базы данных, для этого вам не надо ничего дополнительно устанавливать, достаточно в скрипте указать импорт стандартной библиотеки
Примечание: внося изменения в базу не забудьте их применить, так как база с непримененными изменениями остается залоченной.
Вы можете использовать (последние два варианта кросс-платформенные и бесплатные):
- Привычную вам утилиту для работы с базой в составе вашей IDE;
Python DB-API модули в зависимости от базы данных
| База данных | DB-API модуль |
|---|---|
| SQLite | sqlite3 |
| PostgreSQL | psycopg2 |
| MySQL | mysql.connector |
| ODBC | pyodbc |
Соединение с базой, получение курсора
Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:
При работе с другими базами данных, используются дополнительные параметры соединения, например для PostrgeSQL:
Чтение из базы
Обратите внимание: После получения результата из курсора, второй раз без повторения самого запроса его получить нельзя — вернется пустой результат!
Запись в базу
Примечание: Если к базе установлено несколько соединений и одно из них осуществляет модификацю базы, то база SQLite залочивается до завершения (метод соединения .commit()) или отмены (метод соединения .rollback()) транзакции.
Разбиваем запрос на несколько строк в тройных кавычках
Длинные запросы можно разбивать на несколько строк в произвольном порядке, если они заключены в тройные кавычки — одинарные ('''…''') или двойные (""". """)
Конечно в таком простом примере разбивка не имеет смысла, но на сложных длинных запросах она может кардинально повышать читаемость кода.
Объединяем запросы к базе данных в один вызов метода
Метод курсора .execute() позволяет делать только один запрос за раз, при попытке сделать несколько через точку с запятой будет ошибка.
Для решения такой задачи можно либо несколько раз вызывать метод курсора .execute()
Либо использовать метод курсора .executescript()
Данный метод также удобен, когда у нас запросы сохранены в отдельной переменной или даже в файле и нам его надо применить такой запрос к базе.
Делаем подстановку значения в запрос
Важно! Никогда, ни при каких условиях, не используйте конкатенацию строк (+) или интерполяцию параметра в строке (%) для передачи переменных в SQL запрос. Такое формирование запроса, при возможности попадания в него пользовательских данных – это ворота для SQL-инъекций!
Правильный способ – использование второго аргумента метода .execute()
Возможны два варианта:
Примечание 1: В PostgreSQL (UPD: и в MySQL) вместо знака '?' для подстановки используется: %s
UPD: Примечание 3: Благодарю Igelko за упоминание параметра paramstyle — он определяет какой именно стиль используется для подстановки переменных в данном модуле.
Вот ссылка с полезным приемом для работы с разными стилями подстановок.
Делаем множественную вставку строк проходя по коллекции с помощью метода курсора .executemany()
Получаем результаты по одному, используя метод курсора .fetchone()
Он всегда возвращает кортеж или None. если запрос пустой.
Важно! Стандартный курсор забирает все данные с сервера сразу, не зависимо от того, используем мы .fetchall() или .fetchone()
Курсор как итератор
UPD: Повышаем устойчивость кода
Благодарю paratagas за ценное дополнение:
Для большей устойчивости программы (особенно при операциях записи) можно оборачивать инструкции обращения к БД в блоки «try-except-else» и использовать встроенный в sqlite3 «родной» объект ошибок, например, так:
UPD: Использование with в psycopg2
Благодарю KurtRotzke за ценное дополнение:
Последние версии psycopg2 позволяют делать так:
Некоторые объекты в Python имеют __enter__ и __exit__ методы, что позволяет «чисто» взаимодействовать с ними, как в примере выше.
UPD: Ипользование row_factory
Благодарю remzalp за ценное дополнение:
Использование row_factory позволяет брать метаданные из запроса и обращаться в итоге к результату, например по имени столбца.
По сути — callback для обработки данных при возврате строки. Да еще и полезнейший cursor.description, где есть всё необходимое.
Пример из документации:
Дополнительные материалы (на английском)
-
Краткий бесплатный он-лайн курс — Udacity — Intro to Relational Databases — Рассматриваются синтаксис и принципы работы SQL, Python DB-API – и теория и практика в одном флаконе. Очень рекомендую для начинающих!
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 осуществит подключение к нему.
перед строкой с путем стоит символ «r». Это дает понять Python, что речь идет о «сырой» строке, где символы «/» не отвечают за экранирование.
Функция 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. Здесь та же структура. Предположим, нужно удалить любого пользователя с фамилией «Parker». Напишем следующее:
Если затем сделать следующей запрос:
Будет выведен пустой список, подтверждающий, что запись удалена.
Объединение таблиц в SQLite в Python
Наконец, посмотрим, как использовать объединение данных для более сложных запросов. Предположим, нужно сгенерировать запрос, включающий имя и фамилию каждого покупателя заказа.
Для этого напишем следующее:
Тот же подход работает с другими SQL-операциями.
Выводы
В этом материале вы узнали все, что требуется для работы с SQLite в Python: загрузка библиотеки, создание баз и таблиц, добавление, запрос и удаление данных.
Цель этого руководства — продемонстрировать принципы разработки приложений на Python с использованием базы данных SQLite.
Подключение к SQLite в Python
В этом разделе разберем, как создавать базу данных SQLite и подключаться к ней в Python с помощью модуля sqlite3.
Для установки соединения нужно указать название базы данных, к которой требуется подключиться. Если указать название той, что уже есть на диске, то произойдет подключение. Если же указать другое, то SQLite создаст новую базу данных.
Для подключения к SQLite нужно выполнить следующие шаги
- Использовать метод connect() из модуля sqlite3 и передать в качестве аргумента название базы данных.
- Создать объект cursor с помощью объекта соединения, который вернул прошлый метод для выполнения SQLite-запросов из Python.
- Закрыть объект cursor после завершения работы.
- Перехватить исключение базы данных, если в процессе подключения произошла ошибка.
Следующая программа создает файл базы данных sqlite_python.db и выводит подробности о версии SQLite.
Понимание SQLite-подключения в подробностях
- Эта строка импортирует в программу модуль sqlite3. С помощью классов и методов из этого модуля можно взаимодействовать с базой данных SQLite.
- С помощью метода connect() выполняется подключение к базе данных. Этот метод возвращает объект подключения SQLite.
- Объект connection не является потокобезопасным. Модуль sqlite3 не позволяет делиться подключением между потоками. Если попытаться сделать это, то можно получить исключение.
- Метод connect() принимает разные аргументы. В этом примере передается название базы данных.
- С помощью объекта соединения создается объект cursor , который позволяет выполнять SQLite-запросы из Python.
- Для одного соединения можно создать неограниченное количество cursor. Он также не является потокобезопасным. Модуль не позволяет делиться объектами cursor между потоками. Если это сделать, то будет ошибка.
После этого создается запрос для получения версии базы данных.
- С помощью метода execute объекта cursor можно выполнить запрос в базу данных из Python. Он принимает SQLite-запрос в качестве параметра и возвращает resultSet — то есть, строки базы данных
- Получить результат запроса из resultSet можно с помощью методов, например, fetchAll()
- В этом примере SELECT version(); выполняется для получения версии базы данных SQLite.
Блок try-except-finally: весь код расположен в блоке try-except, что позволит перехватить исключения и ошибки базы данных, которые могут появиться в процессе.
cursor.close() и connection.close()
- Хорошей практикой считается закрывать объекты connection и curosor после завершения работы, чтобы избежать проблем с базой данных.
Создание таблицы SQLite в Python
В этом разделе разберемся, как создавать таблицы в базе данных SQLite с помощью Python и модуля sqlite3. Создание таблицы — это DDL-запрос, выполняемый из Python.
В этом примере создадим базу sqlitedb_developers в базе данных sqlite_python.db .
Шаги для создания таблицы в SQLite с помощью Python:
- Соединиться с базой данных с помощью sqlite3.connect() . Речь об этом шла в первом разделе.
- Подготовить запрос создания таблицы.
- Выполнить запрос с помощью cursor.execute(query) .
- Закрыть соединение с базой и объектом cursor .
Типы данных SQLite и соответствие типам Python
Перед переходом к выполнению CRUD-операций в SQLite из Python сначала нужно разобраться с типами данных SQLite и соответствующими им типами данных в Python, которые помогают хранить и считывать данные из таблицы.
У движка SQLite есть несколько классов для хранения значений. Каждое значение, хранящееся в базе данных, имеет один из следующих типов или классов.
Типы данных SQLite:
- NULL — значение NULL
- INTEGER — числовые значения. Целые числа хранятся в 1, 2, 3, 4, 6 и 8 байтах в зависимости от величины
- REAL — числа с плавающей точкой, например, 3.14, число Пи
- TEXT — текстовые значения. Могут храниться в кодировке UTF-8, UTF-16BE или UTF-16LE
- BLOB — бинарные данные. Для хранения изображений и файлов
Следующие типы данных из Python без проблем конвертируются в SQLite. Для конвертации достаточно лишь запомнить эту таблицу.
Таблицы SQLite создаются за счет выполнения скрипта из Python. Вывод:
Примечание: после соединения с SQLite все содержимое файла сохраняется в переменной. Затем используется команда cursor.executescript(script) для выполнения всех инструкций за раз.
Исключения базы данных SQLite
Например, попробуем добавить данные в таблицу, которой не существует и выведем весь стек исключений из Python.
Читайте также: