В прошлой статье мы рассказали про SQLite — простую базу данных, которая может работать почти на любой платформе. Теперь проверим теорию на практике: напишем простой код на Python, который сделает нам простую базу и наполнит её данными и связями.

Если это первая статья про базы данных, которую вы читаете, то лучше сделать так, а потом вернуться сюда:

1. Почитать про виды баз данных и посмотреть на схему связей в реляционной базе данных. Там простая схема про магазин — в ней связаны товары, клиенты и покупки.
2. Посмотреть, как работают 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
);
«»»)

1. Точно так же мы потом сделаем и с остальными таблицами — сразу встроим проверку, и если нужных таблиц не будет, то программа создаст их автоматически.
2. Теперь наполняем нашу таблицу товарами, используя стандартный SQL-запрос. Например, можно добавить два стола, которые стоят по 3000 ₽:
3. INSERT INTO goods SET product = 'стол', count = 2,
4. price = 3000;
5. Но добавлять записи по одному товару за раз — это долго и неэффективно. Проще сразу в одном запросе добавить все нужные товары: стол, стул и табурет:

# подготавливаем множественный запрос
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)

У нас всё готово для того, чтобы на основе первых двух таблиц создать третью — в ней будут данные сразу и о покупках, и о том, кто это купил. Если интересно, как это работает в деталях, — почитайте статью про связи в базе данных.

# — создаём таблицу с покупками —
# открываем базу
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 для работы с данными и аналитики».

Качать бесплатно

База данных SQLite в Python

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

Он сочетает в себе простые в использовании запросы типа SQL для управления базой данных, но использует его для внутреннего хранения объектов наиболее удобным способом!

Давайте быстро пройдемся по этой библиотеке и создадим нашу собственную базу данных для нашего приложения!

Python SQLite

Python SQLite поставляется вместе с любой установленной версией Python, поэтому нет необходимости устанавливать его с помощью pip . Правильно, он у вас уже есть!

Чтобы импортировать его, поскольку мы будем использовать Python3, мы импортируем модуль sqlite3 .

import sqlite3

Начнем с основных методов модуля.

Как создать базу данных?

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

Мы создадим соединение с базой данных Python SQLite. Это выделяет память для базы данных и извлекает любые данные из файла базы данных, так что база данных создается из файла db .

import sqlite3
# Establish a connection to the Database and create
# a connection object
conn = sqlite3.connect('database.db')

Здесь database.db — это файл базы данных, в котором будут храниться данные. Итак, мы вызываем базу данных из этого файла.

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

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

import os

def check_db(filename):
return os.path.exists(filename)

Однако есть еще один вариант — напрямую создать базу данных в ОЗУ, если вам просто нужна временная база данных, с помощью специального файла :memory:

import sqlite3
conn = sqlite3.connect(':memory:')

Проблема с вышеуказанным методом заключается в том, что нам нужно явно закрыть соединение в конце, используя conn.close() . У Python есть очень хорошее решение для этого — менеджеры контекста. Менеджер контекста оператора with автоматически сделает это за вас, поэтому теперь наш модифицированный код будет:

import sqlite3

db_file = 'database.db'
with sqlite3.connect(db_file) as conn:
print('Created the connection!')
print('Automatically closed the connection!')

Выход

Created the connection!
Automatically closed the connection!

Создайте объект курсора

Как только соединение установлено, у нас есть объект соединения, из которого мы можем построить объект Cursor .

# Create a cursor to the Database
c = conn.cursor()

Этот курсор является указателем на базу данных, которая используется для навигации по ней, чтобы мы могли перемещаться в места и выполнять SQL-запросы.

Схема базы данных

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

Строки соответствуют фактическим значениям данных, а столбцы соответствуют именам их атрибутов.

Создадим простую схему для хранения изображений. Мы определим любое изображение имеющее имя, размер и дату создания изображения. Фактические данные будут где-то в памяти. Схема только определяет, как организованы данные.

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

Мы можем выполнять запросы с помощью курсора, передав cursor.execute('SQL_QUERY') , используя запрос SQL.

Напишем SQL-запрос для создания этой схемы.

CREATE TABLE images(
name text primary key,
size text,
date date
);

Мы можем передать этот запрос в виде строки, но если вы хотите выполнять более длинные запросы, проще просто создать файл .sql а затем вместо этого читать его, используя cursor.executescript() .

Итак, давайте поместим наш запрос в файл с именем schema.sql и прочитаем из него.

import sqlite3
import os

def check_db(filename):
return os.path.exists(filename)

db_file = 'database.db'
schema_file = 'schema.sql'

if check_db(db_file):
print('Database already exists. Exiting…')
exit(0)

with open(schema_file, 'r') as rf:
# Read the schema from the file
schema = rf.read()

with sqlite3.connect(db_file) as conn:
print('Created the connection!')
# Execute the SQL query to create the table
conn.executescript(schema)
print('Created the Table! Now inserting')
conn.executescript(«»»
insert into images (name, size, date)
values
('sample.png', 100, '2019-10-10'),
('ask_python.png', 450, '2019-05-02'),
('class_room.jpeg', 1200, '2018-04-07');
«»»)
print('Inserted values into the table!')
print('Closed the connection!')

Выход

Created the connection!
Created the Table! Now inserting
Inserted values into the table!
Closed the connection!

Получение значения из таблицы

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

import sqlite3

db_file = 'database.db'

with sqlite3.connect(db_file) as conn:
cursor = conn.cursor()
cursor.execute(«»»
select * from images
«»»)
for row in cursor.fetchall():
name, size, date = row
print(f'{name} {size} {date}')

Выход

sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07

Транзакции в SQLite

Управление транзакциями — одна из функций баз данных SQL, и SQLite также обрабатывает их. Транзакция — это последовательность изменений, в которой вы можете безопасно изменить базу данных, выполнив запрос и затем разместив commit .

Если по какой-то причине непосредственно перед фиксацией вы не хотите завершать транзакцию, вы можете вернуться в предыдущее состояние перед фиксацией, используя rollback .

Точно так же мы также можем просматривать состояние базы данных через эти типы изменений.

import sqlite3

db_filename = 'database.db'

def display_table(conn):
cursor = conn.cursor()
cursor.execute('select name, size, date from images;')
for name, size, date in cursor.fetchall():
print(name, size, date)

with sqlite3.connect(db_filename) as conn1:
print('Before changes:')
display_table(conn1)

cursor1 = conn1.cursor()
cursor1.execute(«»»
insert into images (name, size, date)
values ('JournalDev.png', 2000, '2020-02-20');
«»»)

print('
After changes in conn1:')
display_table(conn1)

print('
Before commit:')
with sqlite3.connect(db_filename) as conn2:
display_table(conn2)

# Commit from the first connection
conn1.commit()
print('
After commit:')
with sqlite3.connect(db_filename) as conn3:
display_table(conn3)

cursor1.execute(«»»
insert into images (name, size, date)
values ('Hello.png', 200, '2020-01-18');
«»»)

print('
Before commit:')
with sqlite3.connect(db_filename) as conn2:
display_table(conn2)

# Revert to changes before conn1's commit
conn1.rollback()
print('
After connection 1 rollback:')
with sqlite3.connect(db_filename) as conn4:
display_table(conn4)

Выход

Before changes:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07

After changes in conn1:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

Before commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07

After commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

Before commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

After connection 1 rollback:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

Здесь, как видите, таблица изменяется только после того, как мы явно завершим транзакцию. Любые изменения до этого фактически не изменяют таблицу. Наконец, мы откатываем запись Hello.png , чтобы она не вставлялась в таблицу.

Учебник по 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(). Для этого выполним следующую последовательность шагов:

1. Создание объекта подключения
2. Объект Cursor создаётся с использованием объекта подключения
3. Используя объект курсора, вызывается метод 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 id = 2')

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 прекрасно подойдет
для проектов у которых мало операций записи, не нужна система прав доступа к БД и ограниченны ресурсы сервера.

SQLite Python: основы работы с базами данных и их введение

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

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

Некоторые из этих баз данных работают только с определенными языками программирования, но есть и универсальные базы данных, которые могут работать с различными языками, такими как Python, Java, Ruby и т.д.

SQLite – это легкий, быстрый и встроенный SQL-движок базы данных, который не требует конфигурации.

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

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

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

SQLite Python: работа с базами данных

SQLite – это легковесная, быстрая и простая в использовании система управления базой данных. Совместно с языком программирования Python, она позволяет создавать базы данных и работать с данными, хранящимися в этих базах, в удобной форме.

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

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

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

В целом, SQLite Python отлично подходит для работы с небольшими базами данных, такими как персональные журналы, список контактов и т. д. Он дает возможность быстро и просто создавать и управлять базами данных, а также производить манипуляции с данными в них.

Что такое SQLite?

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

SQLite является самодостаточной СУБД и может функционировать на многих операционных системах, включая Windows, Linux, macOS, iOS и Android. Она поддерживает стандарт SQL, что делает ее удобным инструментом для работы с базами данных.

SQLite отличается высокой производительностью и небольшим размером. Базы данных могут содержать до нескольких терабайтов данных и работать со скоростью на несколько заказов меньше, чем большие СУБД, такие как MySQL или PostgreSQL.

• SQLite часто используется для хранения данных в мобильных приложениях, веб-приложениях и других приложениях, которые не требуют высокой нагрузки базы данных.
• Преимуществом SQLite является отказоустойчивость, а также простота в использовании и настройке, что делает ее популярным выбором для начинающих разработчиков и малых проектов.
• В целом, SQLite – это мощный и гибкий инструмент для хранения данных, который может быть легко интегрирован в приложения для многих целей.

Описание функционала SQLite

SQLite – это легковесная база данных, которая не требует клиент-серверной архитектуры и устанавливается как библиотека в приложении. Она имеет небольшой размер и не занимает много места на жестком диске. SQLite поддерживает SQL и позволяет работать с данными как с файлами.

Основные характеристики SQLite:

• Многопоточность: разрешено нескольким потокам оперировать с базой данных одновременно.
• Транзакционность: SQLite поддерживает ACID-транзакции (atomicity, consistency, isolation, durability), что гарантирует целостность данных.
• Полнотекстовой поиск: SQLite позволяет осуществлять поиск по ключевым словам и выполнить индексирование.

Типы данных, поддерживаемые SQLite:

Тип данных
Описание
Размер

INTEGER	Целочисленный числовой тип данных	1, 2, 3, 4, 6 или 8 байт. В зависимости от размера числа
REAL	Числовые данные с плавающей точкой	8 байт
TEXT	Строковый тип данных	максимально 2^31-1 байт (2 Гб)
BLOB	Двоичные данные	максимально 2^31-1 байт (2 Гб)
NULL	Пустие данные	0 байт

Как SQLite используется в Python

SQLite является встроенной базой данных в языке Python. Он предоставляет простой и удобный способ хранения и получения данных из базы данных.

Для работы с базами данных SQLite в Python нужно установить библиотеку sqlite3. Она позволяет выполнить SQL запросы к базе данных и получить результаты.

Чтобы использовать базу данных в Python, нужно сначала создать ее. Это можно сделать с помощью команды CREATE TABLE. Затем можно добавлять данные в таблицу с помощью команды INSERT INTO. А для получения данных из таблицы нужно использовать команду SELECT.

Для управления транзакциями в базе данных SQLite в Python используются методы commit() и rollback(). Также можно использовать методы fetchone() и fetchall() для получения результатов запроса.

SQLite также поддерживает индексы, что позволяет ускорить работу с большими объемами данных. Для создания индекса нужно использовать команду CREATE INDEX.

База данных SQLite в Python поддерживает множество типов данных, таких как INTEGER, TEXT, REAL, NULL и BLOB. Каждый тип данных может использоваться для определения столбца в таблице базы данных.

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

Установка SQLite

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

Перед установкой SQLite необходимо скачать дистрибутив программы. На официальном сайте SQLite (https://www.sqlite.org/download.html) можно найти различные версии программы, в том числе и для операционной системы, которая установлена на вашем компьютере.

После того, как дистрибутив SQLite скачан, необходимо запустить установочный файл и следовать инструкциям мастера установки. Мастер установки спросит вас о пути установки, настройках компонентов и др. Все параметры можно оставить по умолчанию, если вы не знаете, что делать.

После завершения установки SQLite, можно подключаться к базе данных и начать работу с ней. Например, с помощью Python и модуля sqlite3 можно создавать таблицы и записывать в них данные. Для этого необходимо импортировать модуль sqlite3 и использовать его функции и методы.

Установка SQLite на компьютер — это первый шаг к работе с базами данных. Это простая и быстрая процедура, которая открывает широкие возможности в работе с данными.

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

Для создания базы данных в SQLite посредством Python необходим модуль sqlite3. Этот модуль позволяет создавать, изменять и удалять таблицы в базе данных.

Для создания базы данных необходимо выполнить несколько шагов:

1. Открыть соединение с базой данных
2. Создать курсор
3. Выполнить SQL-запрос для создания таблицы
4. Закрыть курсор и соединение с базой данных

Пример создания базы данных и таблицы:

Код
Описание

import sqlite3 conn = sqlite3.connect(“mydatabase.db”) c = conn.cursor() c.execute(“”” CREATE TABLE books ( book_id INTEGER PRIMARY KEY, title TEXT, author TEXT, publish_date TEXT ) “””) conn.commit() c.close() conn.close()	Создает базу данных mydatabase.db и таблицу books с четырьмя полями: book_id, title, author, publish_date.

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

Ввод данных вручную предполагает использование SQL-запросов для добавления информации в таблицы. Это может быть утомительным процессом, если в базе данных содержится большое количество данных. Для быстрого ввода данных можно использовать команду INSERT INTO, которая позволяет добавлять несколько записей за один раз.

Импортирование существующих данных в базу данных также является важной задачей. Для этого достаточно подготовить файл с данными, который можно импортировать в базу данных используя команду sqlite3.import в Python.

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

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

Основные SQL-запросы

SQL – это язык запросов, используемый для взаимодействия с базами данных. Существуют основные типы запросов, которые позволяют получать, добавлять, обновлять и удалять данные из таблиц базы данных.

• SELECT

Python + SQLite: основы работы с базами данных : Backend Developer

Разберем основные запросы к базе данных SQLite и обсудим альтернативу реляционным СУБД – модули dbm, pickle и shelves. В конце статьи – решения 10 практических задач, связанных с выборкой данных по различным критериям, редактированием записей и удалением дубликатов.

← Часть 21 Основы разработки игр на Pygame

Python может работать со всеми распространенными СУБД (системами управления базами данных):

• Реляционными (SQL)
• Нереляционными (NoSQL)
• Объектно-ориентированными
• Объектно-реляционными
• Резидентными
• Столбцовыми
• Ключ-значение

В этой статье мы будем изучать приемы работы с реляционной СУБД SQLite, которая поставляется с Python. Еще мы рассмотрим базы типа «ключ-значение», которые отлично подходят для хранения данных в простых приложениях.

Что такое SQL, СУБД, SQLite и ORM

SQL (Structured Query Language) – это специальный язык запросов, который используется для создания, изменения и выборки данных в реляционных базах данных, управляемых определенной СУБД.

Система управления базами данных (СУБД) – это программное обеспечение, которое позволяет сохранять, организовывать, изменять, получать и анализировать данные. Обычно СУБД выполняют следующие функции:

• Создание и управление структурой данных (таблицы, индексы, ограничения и т.д.)
• Хранение данных на диске или в памяти.
• Поиск и выборка нужных данных с помощью запросов.
• Манипулирование данными (добавление, изменение, удаление).
• Обеспечение сохранности и целостности данных (транзакции, резервное копирование, восстановление).
• Обеспечение доступности и безопасности данных (права доступа, шифрование и т.д.)

Реляционные СУБД используют язык SQL для выполнения запросов к данным и управления ими. Данные в реляционных базах хранятся в виде таблиц: каждая строка представляет собой отдельную запись, а каждый столбец – отдельное поле данных.

Надо заметить, что язык SQL – не единственный способ создавать запросы к базе данных: при создании веб-приложений на базе фреймворков Django и Flask разработчики обычно используют ORM.

ORM (объектно-реляционное отображение) – это своеобразная прослойка, которая позволяет программистам работать с данными в реляционных базах данных как с объектами Python.

ORM использует язык SQL под капотом, но разработчикам не нужно его знать – таблицы в базах данных создаются автоматически, а запросы имеют очень простой и понятный синтаксис.

Самые популярные прослойки – Django ORM и SQLAlchemy (для Flask).

SQLite – компактная, быстрая, универсальная СУБД. Хранит данные в локальном файле, не требует отдельного сервера для выполнения запросов или управления данными: вместо этого она использует библиотеку, которая работает внутри приложения. SQLite можно использовать для мобильных, настольных и веб-приложений.

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека питониста» Интересно, перейти к каналу

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

Новую SQLite базу можно создать за несколько простых шагов.

1. Импортировать DB-API 2.0 интерфейс (библиотеку sqlite3):

import sqlite3

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

conn = sqlite3.connect('example.db')

3. Создать объект курсора:

cursor = conn.cursor()

4. Создать таблицу в базе данных:

cursor.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')

5. Закрыть соединение с базой данных:

conn.close()

Полностью код выглядит так:

import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor() cursor.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''') conn.close()

Этот код создает базу данных с названием example. База включает в себя таблицу users, которая содержит поля id, name и age:

• INTEGER означает, что в поле хранятся целочисленные значения.
• PRIMARY KEY указывает на то, что это поле будет использоваться в качестве первичного ключа таблицы. Первичный ключ – это уникальный идентификатор, который используется для создания связи между таблицами в базе данных.
• TEXT – класс данных, который используется для хранения строковых значений. Например, имя пользователя, почтовый адрес или наименование товара могут храниться в поле типа TEXT.

Помимо TEXT и INTEGER SQLite имеет следующие классы для хранения данных:

• NULL – представляет нулевое значение.
• REAL – используется для хранения чисел с плавающей точкой.
• BLOB – применяется для хранения бинарных объектов (изображений, аудио или видео). На практике мультимедийные файлы очень редко хранят в БД – целесообразнее хранить там только ссылки на объекты.

В SQLite не предусмотрены отдельные типы данных для хранения даты и времени, но можно использовать тип данных TEXT для хранения даты и времени в виде строки в формате ISO-8601.

Некоторые другие типы данных при необходимости можно преобразовать в классы данных SQLite. К примеру, для хранения BOOLEAN значений можно использовать INTEGER, присваивая записи значения 0 или 1.

Добавление столбцов в таблицу

В предыдущем примере мы создали таблицу users, в которой хранятся имена и возраст пользователей. Добавим два новых поля – для хранения фамилии и названия факультета:

import sqlite3 # Открываем соединение с базой данных example.db conn = sqlite3.connect('example.db') cursor = conn.cursor() # Выполняем запрос на добавление новых столбцов в таблицу users cursor.execute('''ALTER TABLE users ADD COLUMN surname TEXT''') cursor.execute('''ALTER TABLE users ADD COLUMN faculty TEXT''') # Сохраняем изменения и закрываем соединение с базой conn.commit() conn.close()

Как узнать, что находится в базе данных

Есть два способа посмотреть, что записано в БД:

• Выполнить специальный запрос к системной таблице sqlite_master.
• Воспользоваться визуальным браузером/редактором.

Начнем с первого способа – напишем и выполним запрос, чтобы узнать, какие поля (столбцы) есть в таблице:

import sqlite3 # создаем соединение с нашей базой данных conn = sqlite3.connect('example.db') cursor = conn.cursor() # получаем метаданные для таблицы cursor.execute(«PRAGMA table_info(users)») # выводим названия полей таблицы fields = cursor.fetchall() for field in fields: print(field[1]) # закрываем соединение с базой данных conn.close()

Результат:

id name age surname faculty

Второй способ проще и удобнее – можно сразу увидеть и структуру, и содержимое БД. Нам понадобится любой визуальный редактор, поддерживающий SQLite. Самый минималистичный вариант – sqlite-gui:

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

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

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

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

import sqlite3 # устанавливаем соединение с базой данных conn = sqlite3.connect('example.db') # создаем курсор для выполнения операций с базой данных cursor = conn.cursor() # задаем значения для новой записи name = 'Инна' surname = 'Егорова' age = 20 faculty = 'Прикладная математика' # добавляем новую запись в таблицу users cursor.execute('INSERT INTO users (name, surname, age, faculty) VALUES (?, ?, ?, ?)', (name, surname, age, faculty)) # сохраняем изменения в базе данных conn.commit() # закрываем соединение с базой данных conn.close()

Результат:

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

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

import sqlite3 # создаем соединение с нашей базой данных conn = sqlite3.connect('example.db') cursor = conn.cursor() # обновляем возраст пользователя cursor.execute(«UPDATE users SET age = ? WHERE name = ? AND surname = ?», (19, 'Инна', 'Егорова')) conn.commit() # закрываем соединение с базой данных conn.close()

Результат:

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

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