В этом уроке мы будем работать с базой данных SQLite3 программно с помощью Python.
SQLite в целом-это бессерверная база данных, которая может использоваться практически во всех языках программирования, включая Python. Бессерверный означает, что нет необходимости устанавливать отдельный сервер для работы с SQLite, чтобы вы могли напрямую подключаться к базе данных.
SQLite-это легкая база данных, которая может обеспечить систему управления реляционными базами данных с нулевой конфигурацией, поскольку нет необходимости настраивать или настраивать что-либо для ее использования.
Мы будем использовать SQLite версии 3 или SQLite3, так что давайте начнем.
Создать соединение
Чтобы использовать SQLite3 в Python, прежде всего, вам нужно будет импортировать модуль sqlite3 , а затем создать объект соединения, который соединит нас с базой данных и позволит нам выполнять операторы SQL.
Объект соединения создается с помощью функции connect() :
import sqlite3
con = sqlite3.connect('mydatabase.db')
Будет создан новый файл под названием “mydatabase.db”, в котором будет храниться наша база данных.
Курсор SQLite3
Для выполнения операторов SQLite в Python вам нужен объект cursor. Вы можете создать его с помощью метода cursor () .
Курсор SQLite3 – это метод объекта соединения. Для выполнения инструкций SQLite3 сначала устанавливается соединение, а затем создается объект курсора с использованием объекта соединения следующим образом:
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
Теперь мы можем использовать объект cursor для вызова метода execute() для выполнения любых SQL-запросов.
Создание базы данных
При создании соединения с SQLite автоматически создается файл базы данных, если он еще не существует. Этот файл базы данных создается на диске, мы также можем создать базу данных в оперативной памяти с помощью функции:memory: with the connect. Эта база данных называется базой данных в памяти.
Рассмотрим приведенный ниже код, в котором мы создали базу данных с блоками try , except и finally для обработки любых исключений:
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect(':memory:')
print("Connection is established: Database is created in memory")
except Error:
print(Error)
finally:
con.close()
sql_connection()
Сначала импортируется модуль sqlite3 , а затем определяется функция с именем sql_connection. Внутри функции у нас есть блок try , где функция connect() возвращает объект соединения после установления соединения.
Тогда у нас есть блок except , который в случае каких – либо исключений выводит сообщение об ошибке. Если ошибок нет, то соединение будет установлено и появится следующее сообщение.
После этого мы закрыли наше соединение в блоке finally . Закрытие соединения необязательно, но это хорошая практика программирования, поэтому вы освобождаете память от любых неиспользуемых ресурсов.
Создать таблицу
Для создания таблицы в SQLite3 можно использовать запрос Create Table в методе execute () . Рассмотрим следующие шаги:
- Создается объект соединения
- Объект курсора создается с помощью объекта соединения
- Используя объект cursor, метод execute вызывается с параметром create table query
Давайте создадим сотрудников со следующими атрибутами:
сотрудники (id, имя, зарплата, отдел, должность, дата найма)
Код будет выглядеть так:
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect('mydatabase.db')
return con
except Error:
print(Error)
def sql_table(con):
cursorObj = con.cursor()
cursorObj.execute("CREATE TABLE employees(id integer PRIMARY KEY, name text, salary real, department text, position text, hireDate text)")
con.commit()
con = sql_connection()
sql_table(con)
В приведенном выше коде мы определили два метода: первый устанавливает соединение, а второй создает объект курсора для выполнения инструкции create table.
Метод commit() сохраняет все внесенные нами изменения. В конце концов вызываются оба метода.
Чтобы проверить, создана ли наша таблица, вы можете использовать браузер DB для sqlite ;для просмотра вашей таблицы. Откройте файл mydatabase.db с помощью программы, и вы увидите свою таблицу:
Вставить в таблицу
Чтобы вставить данные в таблицу, мы используем оператор INSERT INTO. Рассмотрим следующую строку кода:
cursor Obj.execute(“INSERT INTO employees VALUES(1, ‘John’, 700, ‘HR’, ‘Manager’, ‘2017-01-04’)”)
Чтобы проверить, вставлены ли данные, нажмите кнопку Обзор данных в браузере БД:
Мы также можем передавать значения/аргументы оператору INSERT в методе execute () . Вы можете использовать знак вопроса (?) в качестве заполнителя для каждого значения. Синтаксис ВСТАВКИ будет выглядеть следующим образом:
курсор Obj.execute(“‘ВСТАВИТЬ В ЗНАЧЕНИЯ employees(id, name, salary, department, position, HireDate) (?, ?, ?, ?, ?, ?)”‘, сущности)
Где сущности содержат значения для заполнителей следующим образом:
сущности = (2, ‘Andrew’, 800, ‘IT’, ‘Tech’, ‘2018-02-06’)
Весь код выглядит следующим образом:
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_insert(con, entities):
cursorObj = con.cursor()
cursorObj.execute('INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(?, ?, ?, ?, ?, ?)', entities)
con.commit()
entities = (2, 'Andrew', 800, 'IT', 'Tech', '2018-02-06')
sql_insert(con, entities)
Таблица обновления
Чтобы обновить таблицу, просто создайте соединение, затем создайте объект курсора с помощью соединения и, наконец, используйте оператор UPDATE в методе execute () .
Предположим, что мы хотим обновить имя сотрудника, чей идентификатор равен 2. Для обновления мы будем использовать оператор UPDATE, а для сотрудника, чей идентификатор равен 2. Мы будем использовать предложение WHERE в качестве условия для выбора этого сотрудника.
Рассмотрим следующий код:
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_update(con):
cursorObj = con.cursor()
cursorObj.execute('UPDATE employees SET name = "Rogers" where id = 2')
con.commit()
sql_update(con)
Это изменит имя с Эндрю на Роджерс следующим образом:
Инструкция Select
Оператор select используется для выбора данных из определенной таблицы. Если вы хотите выбрать все столбцы данных из таблицы, вы можете использовать звездочку (*). Синтаксис для этого будет следующим:
выберите * из имени таблицы
В SQLite3 оператор SELECT выполняется в методе execute объекта cursor. Например, выделите все столбцы таблицы сотрудников, выполните следующий код:
курсор Obj.execute(‘SELECT * FROM employees ‘)
Если вы хотите выбрать несколько столбцов из таблицы, укажите их следующим образом:
выберите column1, column2 из tables_name
Например,
курсор Obj.execute(‘SELECT id, name FROM employees’)
Оператор select выбирает необходимые данные из таблицы базы данных, и если вы хотите извлечь выбранные данные, используется метод fetchall() объекта cursor. Это показано в следующем разделе.
Извлечение всех данных
Чтобы извлечь данные из базы данных, мы выполним оператор SELECT, а затем используем метод fetchall() объекта cursor для хранения значений в переменной. После этого мы пройдем по циклу через переменную и выведем все значения.
Код будет выглядеть так:
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('SELECT * FROM employees')
rows = cursorObj.fetchall()
for row in rows:
print(row)
sql_fetch(con)
Приведенный выше код распечатает записи в нашей базе данных следующим образом:
Вы также можете использовать fetch all() в одной строке следующим образом:
[[print(row) для строки в курсоре.фетчалл()]
Если вы хотите получить определенные данные из базы данных, вы можете использовать предложение WHERE. Например, мы хотим получить идентификаторы и имена тех сотрудников, чья зарплата превышает 800. Для этого давайте заполним нашу таблицу большим количеством строк, а затем выполним наш запрос.
Вы можете использовать инструкцию insert для заполнения данных или ввести их вручную в программе DB browser.
Теперь, чтобы получить удостоверение личности и имена тех, кто имеет зарплату больше 800:
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('SELECT id, name FROM employees WHERE salary > 800.0')
rows = cursorObj.fetchall()
for row in rows:
print(row)
sql_fetch(con)
В приведенном выше операторе SELECT вместо использования звездочки ( * ) мы указали атрибуты id и name. Результат будет выглядеть следующим образом:
SQLite3 rowcount
SQLite3 rowcount используется для возврата количества строк, которые были затронуты или выбраны последним выполненным SQL-запросом.
Когда мы используем rowcount с оператором SELECT, будет возвращено значение -1, поскольку количество выбранных строк неизвестно до тех пор, пока все они не будут извлечены. Рассмотрим пример ниже:
print(cursor Obj.execute(‘SELECT * FROM employees’).rowcount)
Поэтому, чтобы получить rowcount, вам нужно получить все данные, а затем получить длину результата:
rows.fetchall() print len (rows)
Когда оператор DELETE используется без какого-либо условия (предложение where), все строки в таблице будут удалены, а общее количество удаленных строк будет возвращено rowcount.
print(cursor Obj.execute(‘DELETE FROM employees’).rowcount)
Если ни одна строка не будет удалена, будет возвращено 0.
Список таблиц
Чтобы перечислить все таблицы в базе данных SQLite3, вы должны запросить таблицу sqlite_master, а затем использовать fetchall() для получения результатов из инструкции SELECT.
sqlite_master – это главная таблица в SQLite3, которая хранит все таблицы.
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('SELECT name from sqlite_master where type= "table"')
print(cursorObj.fetchall())
sql_fetch(con)
Это будет список всех таблиц следующим образом:
Проверьте, существует ли таблица или нет
При создании таблицы мы должны убедиться, что она еще не существует. Аналогично, при удалении/удалении таблицы она должна существовать.
Чтобы проверить, не существует ли таблица уже, мы используем “if not exists” с оператором CREATE TABLE следующим образом:
создайте таблицу, если она не существует table_name (column1, column2, …, columnN)
Например:
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('create table if not exists projects(id integer, name text)')
con.commit()
sql_fetch(con)
Аналогично, чтобы проверить, существует ли таблица при удалении, мы используем “if exists” с оператором DROP TABLE следующим образом:
drop table if exists table_name
Например,
cursor Obj.execute(‘drop table if exists projects’)
Мы также можем проверить, существует ли таблица, к которой мы хотим получить доступ, выполнив следующий запрос:
cursorObj.execute('SELECT name from sqlite\_master WHERE type = "table" AND name = "employees"')
print(cursorObj.fetchall())
Если таблица сотрудников существует, то ее имя будет возвращено следующим образом:
Если указанное нами имя таблицы не существует, будет возвращен пустой массив:
Откидной стол
Вы можете удалить/удалить таблицу с помощью оператора DROP. Синтаксис оператора DROP выглядит следующим образом:
drop table table_name
Чтобы удалить таблицу, она должна существовать в базе данных. Поэтому рекомендуется использовать “if exists” с оператором drop следующим образом:
drop table if exists table_name
Например,
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('DROP table if exists employees')
con.commit()
sql_fetch(con)
Исключения SQLite3
Исключение составляют ошибки времени выполнения. В Python programming все исключения являются экземплярами класса , производного от BaseException.
В SQLite3 у нас есть следующие основные исключения Python:
Ошибка базы данных
Любая ошибка, связанная с базой данных, вызывает ошибку базы данных.
IntegrityError
IntegrityError является подклассом DatabaseError и возникает, когда возникает проблема целостности данных,например, внешние данные не обновляются во всех таблицах, что приводит к несогласованности данных.
Ошибка программирования
Исключение ProgrammingError возникает, когда есть синтаксические ошибки или таблица не найдена или функция вызывается с неправильным количеством параметров/аргументов.
OperationalError
Это исключение возникает при сбое операций с базой данных, например при необычном отключении. В этом нет вины программистов.
NotSupportedError
При использовании некоторых методов, которые не определены или не поддерживаются базой данных, возникает исключение ошибки Not Supported.
SQLite3 Execute many (массовая вставка)
Оператор executemany можно использовать для вставки нескольких строк одновременно.
Рассмотрим следующий код:
import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('create table if not exists projects(id integer, name text)')
data = [(1, "Ridesharing"), (2, "Water Purifying"), (3, "Forensics"), (4, "Botany")]
cursorObj.executemany("INSERT INTO projects VALUES(?, ?)", data)
con.commit()
Здесь мы создали таблицу с двумя столбцами, тогда “данные” имеют четыре значения для каждого столбца. Эта переменная передается методу executemany() вместе с запросом.
Обратите внимание, что мы использовали заполнитель для передачи значений.
Приведенный выше код сгенерирует следующий результат:
Тесное Соединение
Как только вы закончите работу с базой данных, рекомендуется закрыть соединение. Соединение может быть закрыто с помощью метода close () .
Чтобы закрыть соединение, используйте объект connection и вызовите метод close() следующим образом:
con = sqlite3.connect('mydatabase.db')
#program statements
con.close()
SQLite3 datetime
В базе данных Python SQLite3 мы можем легко хранить дату или время, импортируя модуль datetime . Следующие форматы являются наиболее часто используемыми форматами для datetime:
YYYY-MM-DD YYYY-MM-DD HH:MM YYYY-MM-DD HH:MM:SS YYYY-MM-DD HH:MM:SS.SSS HH:MM HH:MM:SS HH:MM:SS.SSS now
Рассмотрим следующий код:
import sqlite3
import datetime
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('create table if not exists assignments(id integer, name text, date date)')
data = [(1, "Ridesharing", datetime.date(2017, 1, 2)), (2, "Water Purifying", datetime.date(2018, 3, 4))]
cursorObj.executemany("INSERT INTO assignments VALUES(?, ?, ?)", data)
con.commit()
В этом коде сначала импортируется модуль datetime, и мы создали таблицу с именем assignments с тремя столбцами.
Тип данных третьего столбца-дата. Чтобы вставить дату в столбец, мы использовали datetime.дата . Аналогично, мы можем использовать datetime.time для обработки времени.
Приведенный выше код сгенерирует следующий вывод:
Большая гибкость и мобильность базы данных SQLite3 делают ее первым выбором для любого разработчика, чтобы использовать ее и поставлять с любым продуктом, с которым он работает.
Базы данных SQLite3 используются в проектах Windows, Linux, Mac OS, Android и iOS благодаря своей потрясающей переносимости. Итак, один файл поставляется вместе с вашим проектом, и все готово.
Я надеюсь, что вы найдете этот учебник полезным. Продолжай возвращаться.
Спасибо.