Python sqlite3 вывести все таблицы

Вывод данных из таблицы SQLite [Часть 3]

В предыдущей статьи [часть 2] мы рассмотрели моменты создания таблицы в базу данных и внесения данных в эту таблицу. В данной статье покажем пример как получить данные из таблицы.

В этом примере, мы извлекаем все данные из таблицы Cars.

Этот SQL запрос выбирает все данные из таблицы Cars.

Метод fetchall() получает все записи. Он возвращает результирующий набор. Технически, это кортеж. Каждый из внутренних кортежей представляет строку в таблице.

Мы выводим данные в консоль, строка за строкой.

Это пример данных.

Получить все данные сразу нельзя, можно только в строковом виде.

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

Данный скрипт можно запускать и у себя на сервере, будь то личный компьютер или VPS от host virtual server на mirohost.net которые знамениты своим качеством предоставления услуг и быстрой технической поддержкой.

Мы обращаемся к данным из цикла «While». Когда мы читаем последнюю строку, цикл завершается.

Метод fetchone() возвращает следующую строку из таблицы. Если данных там больше не осталось, он возвращает None. В этом случае, мы прерываем цикл.

Данные возвращаются в форме кортежа. Здесь мы выбираем записи из кортежа. Первая – это ID, вторая – название машины и третья – цена машины.

Такой результат скрипта мы получили после его выполнения.

Источник

Учебник по SQLite3 в Python

SQLite – это C библиотека, реализующая легковесную дисковую базу данных (БД), не требующую отдельного серверного процесса и позволяющую получить доступ к БД с использованием языка запросов SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также возможно создать прототип приложения с использованием SQLite, а затем перенести код в более многофункциональную БД, такую как PostgreSQL или Oracle. См. также документацию по модулю SQLite Python 3.

Модуль sqlite3 реализует интерфейс SQL, соответствующий спецификации DB-API 2.0, описанной в PEP 249.

Создание соединения

Чтобы воспользоваться SQLite3 в Python необходимо импортировать модуль sqlite3, а затем создать объект подключения к БД.

Объект подключения создается с помощью метода connect():

Курсор SQLite3

Для выполнения операторов SQL, нужен объект курсора, создаваемый методом cursor().

Курсор SQLite3 – это метод объекта соединения. Для выполнения операторов SQLite3 сначала устанавливается соединение, а затем создается объект курсора с использованием объекта соединения следующим образом:

Теперь можно использовать объект курсора для вызова метода execute() для выполнения любых запросов SQL.

Создание базы данных

После создания соединения с SQLite, файл БД создается автоматически, при условии его отсутствия. Этот файл создается на диске, но также можно создать базу данных в оперативной памяти, используя параметр «:memory:» в методе connect. При этом база данных будет называется инмемори.

Рассмотрим приведенный ниже код, в котором создается БД с блоками try, except и finally для обработки любых исключений:

Сначала импортируется модуль sqlite3, затем определяется функция с именем sql_connection. Внутри функции определен блок try, где метод connect() возвращает объект соединения после установления соединения.

Затем определен блок исключений, который в случае каких-либо исключений печатает сообщение об ошибке. Если ошибок нет, соединение будет установлено, тогда скрипт распечатает текст «Connection is established: Database is created in memory».

Далее производится закрытие соединения в блоке finally. Закрытие соединения необязательно, но это хорошая практика программирования, позволяющая освободить память от любых неиспользуемых ресурсов.

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

Чтобы создать таблицу в SQLite3, выполним запрос Create Table в методе execute(). Для этого выполним следующую последовательность шагов:

1. Создание объекта подключения
2. Объект Cursor создается с использованием объекта подключения
3. Используя объект курсора, вызывается метод execute с запросом create table в качестве параметра.

Давайте создадим таблицу Employees со следующими колонками:

Код будет таким:

В приведенном выше коде определено две функции: первая устанавливает соединение; а вторая — используя объект курсора выполняет SQL оператор create table.

Метод commit() сохраняет все сделанные изменения. В конце скрипта производится вызов обеих функций.

Для проверки существования таблицы воспользуемся браузером БД для sqlite.

Вставка данных в таблицу

Чтобы вставить данные в таблицу воспользуемся оператором INSERT INTO. Рассмотрим следующую строку кода:

Также можем передать значения / аргументы в оператор INSERT в методе execute (). Также можно использовать знак вопроса (?) в качестве заполнителя для каждого значения. Синтаксис INSERT будет выглядеть следующим образом:

Где картеж entities содержат значения для заполнения одной строки в таблице:

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

Обновление таблицы

Предположим, что нужно обновить имя сотрудника, чей идентификатор равен 2. Для обновления будем использовать инструкцию UPDATE. Также воспользуемся предикатом WHERE в качестве условия для выбора нужного сотрудника.

Рассмотрим следующий код:

Это изменит имя Эндрю на Роджерс.

Оператор SELECT

Оператор SELECT используется для выборки данных из одной или более таблиц. Если нужно выбрать все столбцы данных из таблицы, можете использовать звездочку (*). SQL синтаксис для этого будет следующим:

В SQLite3 инструкция SELECT выполняется в методе execute объекта курсора. Например, выберем все стрики и столбцы таблицы employee:

Если нужно выбрать несколько столбцов из таблицы, укажем их, как показано ниже:

Оператор SELECT выбирает все данные из таблицы employees БД.

Выборка всех данных

Чтобы извлечь данные из БД выполним инструкцию SELECT, а затем воспользуемся методом fetchall() объекта курсора для сохранения значений в переменной. При этом переменная будет являться списком, где каждая строка из БД будет отдельным элементом списка. Далее будет выполняться перебор значений переменной и печатать значений.

Код будет таким:

Также можно использовать fetchall() в одну строку:

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

Можете использовать оператор INSERT для заполнения данных или ввести их вручную в программе браузера БД.

Теперь, выберем имена и идентификаторы тех сотрудников, у кого зарплата больше 800:

В приведенном выше операторе SELECT вместо звездочки (*) были указаны атрибуты id и name.

SQLite3 rowcount

Счетчик строк SQLite3 используется для возврата количества строк, которые были затронуты или выбраны последним выполненным запросом SQL.

Когда вызывается rowcount с оператором SELECT, будет возвращено -1, поскольку количество выбранных строк неизвестно до тех пор, пока все они не будут выбраны. Рассмотрим пример:

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

Когда оператор DELETE используется без каких-либо условий (предложение where), все строки в таблице будут удалены, а общее количество удаленных строк будет возвращено rowcount.

Если ни одна строка не удалена, будет возвращено 0.

Список таблиц

Чтобы вывести список всех таблиц в базе данных SQLite3, нужно обратиться к таблице sqlite_master, а затем использовать fetchall() для получения результатов из оператора SELECT.

Sqlite_master — это главная таблица в SQLite3, в которой хранятся все таблицы.

Проверка существования таблицы

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

Чтобы проверить, если таблица еще не существует, используем «if not exists» с оператором CREATE TABLE следующим образом:

Точно так же, чтобы проверить, существует ли таблица при удалении, мы используем «if not exists» с инструкцией DROP TABLE следующим образом:

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

Если указанное имя таблицы не существует, будет возвращен пустой массив.

Удаление таблицы

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

Чтобы удалить таблицу, таблица должна существовать в БД. Поэтому рекомендуется использовать «if exists» с оператором DROP. Например, удалим таблицу employees:

Исключения SQLite3

Исключением являются ошибки времени выполнения скрипта. При программировании на Python все исключения являются экземплярами класса производного от BaseException.

В SQLite3 у есть следующие основные исключения Python:

DatabaseError

Любая ошибка, связанная с базой данных, вызывает ошибку DatabaseError.

IntegrityError

IntegrityError является подклассом DatabaseError и возникает, когда возникает проблема целостности данных, например, когда внешние данные не обновляются во всех таблицах, что приводит к несогласованности данных.

ProgrammingError

Исключение ProgrammingError возникает, когда есть синтаксические ошибки или таблица не найдена или функция вызывается с неправильным количеством параметров / аргументов.

OperationalError

Это исключение возникает при сбое операций базы данных, например, при необычном отключении. Не по вине программиста.

NotSupportedError

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

Массовая вставка строк в Sqlite

Для вставки нескольких строк одновременно использовать оператор executemany.

Рассмотрим следующий код:

Здесь создали таблицу с двумя столбцами, тогда у «данных» есть четыре значения для каждого столбца. Эта переменная передается методу executemany() вместе с запросом.

Обратите внимание, что использовался заполнитель для передачи значений.

Закрытие соединения

Когда работа с БД завершена, рекомендуется закрыть соединение. Соединение может быть закрыто с помощью метода close ().

Чтобы закрыть соединение, используйте объект соединения с вызовом метода close() следующим образом:

SQLite3 datetime

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

Рассмотрим следующий код:

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

Тип данных третьего столбца — дата. Чтобы вставить дату в столбец, воспользовались datetime.date. Точно так же можно использовать datetime.time для обработки времени.

Вывод

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

Источник

Руководство по SQLite в Python

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

перед строкой с путем стоит символ «r». Это дает понять Python, что речь идет о «сырой» строке, где символы «/» не отвечают за экранирование.

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

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

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

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

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

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

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

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

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

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

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

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

1. Функция execute отвечает за SQL-запрос
2. SQL генерирует таблицу users
3. IF NOT EXISTS поможет при попытке повторного подключения к базе данных. Запрос проверит, существует ли таблица. Если да — проверит, ничего ли не поменялось.
4. Создаем первые четыре колонки: userid , fname , lname и gender . Userid — это основной ключ.
5. Сохраняем изменения с помощью функции 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: загрузка библиотеки, создание баз и таблиц, добавление, запрос и удаление данных.

Источник