SQLite
Python по умолчанию поддерживает работу с базой данных SQLite. Для этого применяется встроенная библиотека sqlite3 , которая в python доступна в виде одноименного модуля.
Для подключения к бд в этой библиотеке определена функция connect() :
sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False)
Она принимает следующие параметры:
- database : путь к файлу базы данных. Если база данных расположена в памяти, а не на диске, то для открытия подключения используется «:memory:»
- timeout : период времени в секундах, через который генерируется исключение, если файл бд занят другим процессом
- detect_types : управляет сопоставлением типов SQLite с типами Python. Значение 0 отключает сопоставление
- isolation_level : устанавливает уровень изоляции подключения и определяет процесс отрытия неявных транзакций. Возможные значения: «DEFERRED» (значение по умолчанию), «EXCLUSIVE», «IMMEDIATE» или None (неявные транзакции отключены)
- check_same_thread : если равно True (значение по умолчанию), то только поток, который создал подключение, может его использовать. Если равно False , подключение может использоваться несколькими потоками.
- factory : класс фабрики, который применяется для создания подключения. Должен представлять класс, производный от Connection . По умолчанию используется класс sqlite3.Connection
- cached_statements : количество SQL-инструкций, которые должны кэшироваться. По умолчанию равно 128.
- uri : булевое значение, если равно True , то путь к базе данных рассматривается как адрес URI
Обязательным параметром функции является путь к базе данных. Результатом функции является объект подключения (объект класса Connection), через затем можно взаимодействовать с базой данных.
Например, подключение к базе данных «metanit.db», которая располагается в той же папке, что и текущий скрипт (если такая база данных отсутствует, то она автоматически создается):
import sqlite3; con = sqlite3.connect("metanit.db")
Сопоставление типов SQLite и Python
Прежде чем начать работать с базой данных, следует понимать, как сопоставляются типы SQLite и типы Python. По умолчанию применяются следующие сопоставления:
Следует отметить, что при необходимости мы можем переопределять сопоставление, применяя кастомные конвертеры типов.
Получение курсора
Для выполнения выражений SQL и получения данных из БД, необходимо создать курсор. Для этого у объекта Connection вызывается метод cursor() . Этот метод возвращает объект Cursor :
import sqlite3; # создаем подключение con = sqlite3.connect("metanit.db") # получаем курсор cursor = con.cursor()
Выполнение запросов к базе данных
Для выполнения запросов и получения данных класс Cursor предоставляет ряд методов:
- execute(sql, parameters=(), /) : выполняет одну SQL-инструкцию. Через второй параметр в код SQL можно передать набор параметров в виде списка или словаря
- executemany(sql, parameters, /) : выполняет параметризованное SQL-инструкцию. Через второй параметр принимает наборы значений, которые передаются в выполняемый код SQL.
- executescript(sql_script, /) : выполняет SQL-скрипт, который может включать множество SQL-инструкций
- fetchone() : возвращает одну строку в виде кортежа из полученного из БД набора строк
- fetchmany(size=cursor.arraysize) : возвращает набор строк в виде списка. количество возвращаемых строк передается через параметр. Если больше строк нет в наборе, то возвращается пустой список.
- fetchall() : возвращает все (оставшиеся) строки в виде списка. При отсутствии строк возвращается пустой список.
Создание таблицы
Для создания таблицы в SQLite применяется инструкция CREATE TABLE . Например, создадим в базе данных «metanit.db» таблицу people:
import sqlite3; con = sqlite3.connect("metanit.db") cursor = con.cursor() # создаем таблицу people cursor.execute("""CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER) """)
В метод cursor.execute() передается инструкция CREATE TABLE , которая создает таблицу people с тремя столбцами. Столбец id представляет идентификатор пользователя, хранит данные типа Integer, то есть число, и также представляет первичный ключ, значение которого будет автоматически генерироваться и инкрементироваться с каждой новой строкой. Второй столбец — name представляет строку — имя пользователя. И третий столбец — age представляет возраст пользователя.
После выполнения скрипта мы можем открыть базу данных в каком-нибудь браузере баз данных SQLite, например, в DB Browser for SQLite и увидеть созданную таблицу
Учебник по SQLite3 в Python
SQLite – это C библиотека, реализующая легковесную дисковую базу данных (БД), не требующую отдельного серверного процесса и позволяющую получить доступ к БД с использованием языка запросов SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также возможно создать прототип приложения с использованием SQLite, а затем перенести код в более многофункциональную БД, такую как PostgreSQL или Oracle.
Модуль sqlite3 реализует интерфейс SQL, соответствующий спецификации DB-API 2.0, описанной в PEP 249.
Создание соединения
Чтобы воспользоваться SQLite3 в Python необходимо импортировать модуль sqlite3, а затем создать объект подключения к БД.
Объект подключения создается с помощью метода connect() :
import sqlite3 con = sqlite3.connect('mydatabase.db') Курсор SQLite3
Для выполнения операторов SQL, нужен объект курсора, создаваемый методом cursor() .
Курсор SQLite3 – это метод объекта соединения. Для выполнения операторов SQLite3 сначала устанавливается соединение, а затем создается объект курсора с использованием объекта соединения следующим образом:
con = sqlite3.connect('mydatabase.db') cursorObj = con.cursor() Теперь можно использовать объект курсора для вызова метода execute() для выполнения любых запросов SQL.
Создание базы данных
После создания соединения с SQLite, файл БД создается автоматически, при условии его отсутствия. Данный файл создаётся на диске, но также можно создать базу данных в оперативной памяти, используя параметр «:memory:» в методе 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() возвращает объект соединения после установления соединения.
Затем определен блок исключений, который в случае каких-либо исключений печатает сообщение об ошибке. Если ошибок нет, соединение будет установлено, тогда скрипт распечатает текст «Connection is established: Database is created in memory».
Далее производится закрытие соединения в блоке finally . Закрытие соединения необязательно, но это хорошая практика программирования, позволяющая освободить память от любых неиспользуемых ресурсов.
Создание таблицы
Чтобы создать таблицу в SQLite3, выполним запрос Create Table в методе execute() . Для этого выполним следующую последовательность шагов:
- Создание объекта подключения
- Объект Cursor создаётся с использованием объекта подключения
- Используя объект курсора, вызывается метод execute с SQL запросом create table в качестве параметра.
Давайте создадим таблицу Employees со следующими колонками:
employees (id, name, salary, department, position, hireDate) Код будет таким:
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) В приведенном выше коде определено две функции: первая устанавливает соединение; а вторая — используя объект курсора выполняет SQL оператор create table .
Метод commit() сохраняет все сделанные изменения. В конце скрипта производится вызов обеих функций.
Для проверки существования таблицы воспользуемся браузером БД для sqlite.
Вставка данных в таблицу
Чтобы вставить данные в таблицу воспользуемся оператором INSERT INTO . Рассмотрим следующую строку кода:
cursorObj.execute("INSERT INTO employees VALUES(1, 'John', 700, 'HR', 'Manager', '2017-01-04')") Также можем передать значения / аргументы в оператор INSERT в методе execute () . Также можно использовать знак вопроса ( ? ) в качестве заполнителя для каждого значения. Синтаксис INSERT будет выглядеть следующим образом:
cursorObj.execute('''INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(?, ?, ?, ?, ?, ?)''', entities) Где картеж entities содержат значения для заполнения одной строки в таблице:
entity = (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) Обновление таблицы
Предположим, что нужно обновить имя сотрудника, чей идентификатор равен 2. Для обновления будем использовать инструкцию UPDATE . Также воспользуемся предикатом WHERE в качестве условия для выбора нужного сотрудника.
Рассмотрим следующий код:
import sqlite3 con = sqlite3.connect('mydatabase.db') def sql_update(con): cursorObj = con.cursor() cursorObj.execute('UPDATE employees SET name = "Rogers" where con.commit() sql_update(con) Это изменит имя Andrew на Rogers.
Оператор SELECT
Оператор SELECT используется для выборки данных из одной или более таблиц. Если нужно выбрать все столбцы данных из таблицы, можете использовать звёздочку (*). SQL синтаксис для этого будет следующим:
select * from table_name В SQLite3 инструкция SELECT выполняется в методе execute объекта курсора. Например, выберем все стрики и столбцы таблицы employee :
cursorObj.execute('SELECT * FROM employees ') Если нужно выбрать несколько столбцов из таблицы, укажем их, как показано ниже:
select column1, column2 from tables_name cursorObj.execute('SELECT id, name FROM employees') Оператор SELECT выбирает все данные из таблицы employees БД.
Выборка всех данных
Чтобы извлечь данные из БД выполним инструкцию SELECT , а затем воспользуемся методом fetchall() объекта курсора для сохранения значений в переменной. При этом переменная будет являться списком, где каждая строка из БД будет отдельным элементом списка. Далее будет выполняться перебор значений переменной и печатать значений.
Код будет таким:
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) Также можно использовать fetchall() в одну строку:
[print(row) for row in cursorObj.fetchall()] Если нужно извлечь конкретные данные из БД, воспользуйтесь предикатом WHERE . Например, выберем идентификаторы и имена тех сотрудников, чья зарплата превышает 800. Для этого заполним нашу таблицу большим количеством строк, а затем выполним запрос.
Можете использовать оператор INSERT для заполнения данных или ввести их вручную в программе браузера БД.
Теперь, выберем имена и идентификаторы тех сотрудников, у кого зарплата больше 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 используется для возврата количества строк, которые были затронуты или выбраны последним выполненным запросом SQL.
Когда вызывается rowcount с оператором SELECT , будет возвращено -1, поскольку количество выбранных строк неизвестно до тех пор, пока все они не будут выбраны. Рассмотрим пример:
print(cursorObj.execute('SELECT * FROM employees').rowcount) Поэтому, чтобы получить количество строк, нужно получить все данные, а затем получить длину результата:
rows = cursorObj.fetchall() print(len(rows)) Когда оператор DELETE используется без каких-либо условий (предложение where ), все строки в таблице будут удалены, а общее количество удаленных строк будет возвращено rowcount .
print(cursorObj.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 следующим образом:
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 not exists» с инструкцией DROP TABLE следующим образом:
cursorObj.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 . Например, удалим таблицу employees :
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 все исключения являются экземплярами класса производного от BaseException .
В SQLite3 у есть следующие основные исключения Python:
DatabaseError
Любая ошибка, связанная с базой данных, вызывает ошибку DatabaseError .
IntegrityError
IntegrityError является подклассом DatabaseError и возникает, когда возникает проблема целостности данных, например, когда внешние данные не обновляются во всех таблицах, что приводит к несогласованности данных.
ProgrammingError
Исключение ProgrammingError возникает, когда есть синтаксические ошибки или таблица не найдена или функция вызывается с неправильным количеством параметров / аргументов.
OperationalError
Это исключение возникает при сбое операций базы данных, например, при необычном отключении. Не по вине программиста.
NotSupportedError
При использовании некоторых методов, которые не определены или не поддерживаются базой данных, возникает исключение NotSupportedError .
Массовая вставка строк в Sqlite
Для вставки нескольких строк одновременно использовать оператор 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() .
Чтобы закрыть соединение, используйте объект соединения с вызовом метода close() следующим образом:
con = sqlite3.connect('mydatabase.db') #program statements con.close() SQLite3 datetime
В базе данных Python SQLite3 можно легко сохранять дату или время, импортируя Python модуль 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.date . Точно так же можно использовать datetime.time для обработки времени.
Вывод
SQLite можно использовать в своих разработках, но с учетом особенностей этой БД. SQLite прекрасно подойдет для проектов у которых мало операций записи, не нужна система прав доступа к БД и ограниченны ресурсы сервера.
sqlite3 в Python
На прошлой неделе вы познакомились с реляционными базами данных и языком запросов SQL. Для работы с БД мы использовали СУБД SQLite. Сегодня мы будем использовать SQLite библиотеку прямо из Python. Для этих целей есть стандартная библиотека sqlite3. По возможности рекомендую ознакомится с ее официальной документацией.
Соответствие типов данных
SQLite и Python имеют достаточно простое преобразование между типами:
- NULL ⟷ None
- INTEGER ⟷ int
- REAL ⟷ float
- TEXT ⟷ str
- BLOB ⟷ bytes
Common practice
В этой части будут рассмотрены основные принципы работы с библиотекой. За полным списком функций и методов и их аргументов обращайтесь к документации.
Для работы с БД сначала необходимо создать объект Connection . Создается он при помощи функции connect , которой необходимо передать путь до файла БД или :memory: для создания БД непосредственно в RAM.
import sqlite3 conn = sqlite3.connect("my_data.db")
Когда соединение создано, можно работать с БД. Для этого используется специальный объект Cursor , получит который можно методом Connection.cursor() . При помощи метода Cursor.execute() курсор исполняет написанный на языке SQL запрос. Следует помнить, что запросы на изменение БД носят временный характер. Для сохранения изменений необходимо использовать Connection.commit() , а для отката изменений Connection.rollback() . По завершении работы с БД не забывайте закрывать соединение.
c = conn.cursor() # Create table c.execute('''CREATE TABLE stocks (date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL)''') # Insert a row of data c.execute('''INSERT INTO stocks VALUES ('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00)''') # Save (commit) the changes conn.commit() # We can also close the connection if we are done with it. # Just be sure any changes have been committed or they will be lost. conn.close()
Запрос SELECT несколько отличается. Для получения его результатов необходимо использовать методы
- fetchone() — возвращает следующую строку из результата
- fetchmany() — возвращает указанное количество строк
- fetchall() — возвращает все оставшиеся строки
Или использовать курсор как итератор.
import sqlite3 conn = sqlite3.connect("my_data.db") c = conn.cursor() c.execute("SELECT * FROM stocks WHERE symbol='RHAT'") print(c.fetchone()) for row in c.execute("SELECT * FROM stocks ORDER BY price"): print(row) conn.close()
Однако, работа с курсором напрямую необязательна. Класс Connection предоставляет методы-обертки над одноименными методами класса Cursor : execute() , executemany() , executescript() . Эти методы возвращают курсор.
import sqlite3 persons = [ ("Hugo", "Boss"), ("Calvin", "Klein") ] conn = sqlite3.connect(":memory:") # Create the table conn.execute("create table person(firstname, lastname)") # Fill the table conn.executemany("insert into person(firstname, lastname) values (?, ?)", persons) # Print the table contents for row in conn.execute("select firstname, lastname from person"): print(row) print("I just deleted", conn.execute("delete from person").rowcount, "rows") # close is not a shortcut method and it's not called automatically, # so the connection object should be closed manually conn.close()
Стоит обратить внимание на метод executemany() . Данный метод позволяет применить один и тот же запрос для разных входных данных. Данные подаются в виде объекта-коллекции, итератора или генератора. Подстановки данных выполняюстя при помощи вопросительных знаков или именованных параметров. В случае вопросительных знаков данные подаются в виде кортежа, даже если подставляется одно значение. Для именованных параметров используется словарь.
import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("create table people (name_last, age)") who = "Yeltsin" age = 72 # This is the qmark style: cur.execute("insert into people values (?, ?)", (who, age)) # And this is the named style: cur.execute("select * from people where name_last=:who and age=:age", "who": who, "age": age>) print(cur.fetchone()) conn.close()
В рассмотренных ранее примерах все изменения необходимо коммитить. Однако есть возможность применять эти изменения автоматически. Первый вариант — использовать executescript() . Этот метод принимает один аргумент — строку с полноценным SQL скриптом — и выполняет записанные в ней запросы. Не забывайте про ; в конце каждого запроса в скрипте.
import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() cur.executescript(""" create table person( firstname, lastname, age ); create table book( title, author, draft ); insert into book(title, author, draft) values ( 'Dirk Gently''s Holistic Detective Agency', 'Douglas Adams', 1987 ); """) con.close()
Второй вариант — контекстный менеджер. Использование соединения в контекстном менеджере позволяет автоматически коммитить изменения в случае успеха и откатывать в случае ошибки.
import sqlite3 conn = sqlite3.connect(":memory:") con.execute("create table person (id integer primary key, firstname varchar unique)") # Successful, conn.commit() is called automatically afterwards with conn: conn.execute("insert into person(firstname) values (?)", ("Joe",)) # conn.rollback() is called after the with block finishes with an exception, the # exception is still raised and must be caught try: with conn: conn.execute("insert into person(firstname) values (?)", ("Joe",)) except sqlite3.IntegrityError: print("couldn't add Joe twice") # Connection object used as context manager only commits or rollbacks transactions, # so the connection object should be closed manually conn.close()
Последнее, что надо рассмотреть, это возможность получать результаты SELECT в произвольном виде. По умолчанию, каждая строка представлена кортежем. Однако это представление можно поменять. Для этого используется атрибут соединения row_factory , которому можно присвоить функцию следующего вида:
def dict_factory(cursor, row): d = <> for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d
Здесь cursor.description возвращает список названий столбцов. Каждый столбец характеризуется кортежем из 7 элементов, имя в нулевом элементе.
При необходимости, такая функция может создавать объекты пользовательского класса. Библиотека sqlite3 для удобства содержит класс Row . Row в основном ведет себя как кортеж, но при этом дополнительно поддерживает обращение по именам столбцов. Перепишем пример для SELECT с использованием этого класса.
import sqlite3 conn = sqlite3.connect("my_data.db") conn.row_factory = sqlite3.Row c = conn.cursor() c.execute("SELECT * FROM stocks WHERE symbol='RHAT'") r = c.fetchone() print(r.keys()) for key in r.keys(): print(r[key]) conn.close()
Упражнение
Используя базу данных с предыдущего занятия, напишите консольное приложение для работы с ней. Ваше приложение должно поддерживать команды:
- Вывести список книг
- Вывести список читателей
- Добавить книгу.
- Добавить читателя.
- Выдать книгу читателю
- Принять книгу.
По желанию можно дополнительно добавить поддержку произвольных запросов.
Сайт построен с использованием Pelican. За основу оформления взята тема от Smashing Magazine. Исходные тексты программ, приведённые на этом сайте, распространяются под лицензией GPLv3, все остальные материалы сайта распространяются под лицензией CC-BY.
Создаём и наполняем базу данных SQLite в Python
В прошлой статье мы рассказали про SQLite — простую базу данных, которая может работать почти на любой платформе. Теперь проверим теорию на практике: напишем простой код на Python, который сделает нам простую базу и наполнит её данными и связями.
Предыстория
Если это первая статья про базы данных, которую вы читаете, то лучше сделать так, а потом вернуться сюда:
- Почитать про виды баз данных и посмотреть на схему связей в реляционной базе данных. Там простая схема про магазин — в ней связаны товары, клиенты и покупки.
- Посмотреть, как работают SQL-запросы: что это такое, как база на них реагирует и что получается в итоге. В статье мы с помощью SQL-запросов сделали базу данных по магазинной схеме.
Что будем делать
Сегодня мы сделаем то же самое, что и в SQL-запросах, но на Python, используя стандартную библиотеку sqlite3:
- создадим базу и таблицы в ней;
- наполним их данными;
- создадим связи;
- проверим, как это работает.
После этого мы сможем использовать такой же подход в других проектах и хранить все данные не в текстовых файлах, а в полноценной базе данных.
Подключаем и создаём базу данных
За работу с SQLite в Python отвечает стандартная библиотека sqlite3:
# подключаем SQLite
import sqlite3 as sl
Теперь нам нужно указать файл базы данных, с которым мы будем дальше работать. Удобство библиотеки в том, что нам достаточно указать имя файла, а дальше будет такое:
- если этого файла нет, то программа создаст пустую базу данных с таким именем;
- если указанный файл есть, то программа подключится к нему и будет с ним работать.
Получается, нам неважно, есть файл с базой или нет — мы в любом случае после запуска получим то, что нам нужно. Для этого пишем команду:
# открываем файл с базой данных
con = sl.connect(‘thecode.db’)
Мы указали, что файл называется thecode.db, без указания папок и дисков. Это значит, что файл с базой появится в той же папке, что и наш скрипт — можно в этом убедиться после запуска программы.
Создаём таблицу с товарами
У нас есть база, в которой можно создавать таблицы для хранения данных. Создадим первую таблицу для товаров:
with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)Если посмотреть внимательно на код, можно заметить, что текст внутри кавычек полностью повторяет обычный SQL-запрос, который мы уже использовали в прошлой статье. Единственное отличие — в SQLite используется INTEGER вместо INT:
CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INT, price INT );Теперь соберём код вместе и запустим его ещё раз:
# подключаем SQLite import sqlite3 as sl # открываем файл с базой данных con = sl.connect('thecode.db') # создаём таблицу для товаров with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)Но после второго запуска компьютер почему-то выдаёт ошибку:
❌ sqlite3.OperationalError: table goods already exists
Дело в том, что при повторном запуске программа пытается создать таблицу с товарами, которая уже есть в базе. Так как имена таблиц совпадают, а двух одинаковых имён быть не может, отсюда и возникает ошибка.
Чтобы не попадать в такую ситуацию, добавим проверку: посмотрим, есть ли в базе нужная нам таблица или нет. Если нет — создаём, если есть — двигаемся дальше:
# открываем базу with con: # получаем количество таблиц с нужным нам именем data = con.execute("select count(*) from sqlite_master where type='table' and name='goods'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для товаров with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)Точно так же мы потом сделаем и с остальными таблицами — сразу встроим проверку, и если нужных таблиц не будет, то программа создаст их автоматически.
Теперь наполняем нашу таблицу товарами, используя стандартный SQL-запрос. Например, можно добавить два стола, которые стоят по 3000 ₽:
INSERT INTO goods SET
product = ‘стол’,
count = 2,
price = 3000;
Но добавлять записи по одному товару за раз — это долго и неэффективно. Проще сразу в одном запросе добавить все нужные товары: стол, стул и табурет:
# подготавливаем множественный запрос sql = 'INSERT INTO goods (product, count, price) values(?, ?, ?)' # указываем данные для запроса data = [ ('стол', 2, 3000), ('стул', 5, 1000), ('табурет', 1, 500) ] # добавляем с помощью множественного запроса все данные сразу with con: con.executemany(sql, data) # выводим содержимое таблицы на экран with con: data = con.execute("SELECT * FROM goods") for row in data: print(row)В конце мы добавили вывод таблицы — так можно убедиться, что запрос сработал и данные отправились в базу в нужное место.
Создаём и заполняем таблицу с товарами
Заведём таблицу clients для клиентов и заполним её точно так же, как мы это сделали с клиентской таблицей. Для этого просто копируем предыдущий код, меняем название таблицы и указываем правильные названия полей.Ещё посмотрите на отличие от обычного SQL в последней строке объявления полей таблицы: вместо id INT AUTO_INCREMENT PRIMARY KEY надо указать id INTEGER PRIMARY KEY . Без этого не будет работать автоувеличение счётчика.
# --- создаём таблицу с клиентами --- # открываем базу with con: # получаем количество таблиц с нужным нам именем — clients data = con.execute("select count(*) from sqlite_master where type='table' and name='clients'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для клиентов with con: con.execute(""" CREATE TABLE clients ( name VARCHAR(40), phone VARCHAR(10) UNIQUE, id INTEGER PRIMARY KEY ); """) # подготавливаем множественный запрос sql = 'INSERT INTO clients (name, phone) values(?, ?)' # указываем данные для запроса data = [ ('Миша', 9208381096), ('Наташа', 9307265198), ('Саша', 9307281096) ] # добавляем с помощью множественного запроса все данные сразу with con: con.executemany(sql, data) # выводим содержимое таблицы с клиентами на экран with con: data = con.execute("SELECT * FROM clients") for row in data: print(row)Cоздаём таблицу с покупками и связываем всё вместе
У нас всё готово для того, чтобы на основе первых двух таблиц создать третью — в ней будут данные сразу и о покупках, и о том, кто это купил. Если интересно, как это работает в деталях, — почитайте статью про связи в базе данных.
# --- создаём таблицу с покупками --- # открываем базу with con: # получаем количество таблиц с нужным нам именем — orders data = con.execute("select count(*) from sqlite_master where type='table' and name='orders'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для покупок with con: con.execute(""" CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, product VARCHAR, amount INTEGER, client_id INTEGER, FOREIGN KEY (product) REFERENCES goods(product), FOREIGN KEY (client_id) REFERENCES clients(id) ); """)Проверим, что связь работает: добавим в таблицу с заказами запись о том, что Миша купил 2 табурета:
# подготавливаем запрос sql = 'INSERT INTO orders (product, amount, client_id) values(?, ?, ?)' # указываем данные для запроса data = [ ('табурет', 2, 1) ] # добавляем запись в таблицу with con: con.executemany(sql, data) # выводим содержимое таблицы с покупками на экран with con: data = con.execute("SELECT * FROM orders") for row in data: print(row)Компьютер выдал строку (1, ‘табурет’, 2, 1), значит, таблицы связались правильно.
Что дальше
Теперь, когда мы знаем, как работать с SQLite в Python, можно использовать эту базу данных в более серьёзных проектах:
- хранить результаты парсинга;
- запоминать отсортированные датасеты;
- вести учёт пользователей и их действий в системе.
Подпишитесь, чтобы не пропустить продолжение про SQLite. А если вам интересна аналитика и работа с данными, приходите на курс «SQL для работы с данными и аналитики».
Данные — это новая нефть
А аналитики данных — новая элита ИТ. Эти люди помогают понять огромные массивы данных и принять правильные решения. Изучите профессию аналитика и начните карьеру в ИТ: старт — бесплатно, а после обучения — помощь с трудоустройством.
Получите ИТ-профессию
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.