Средняя общеобразовательная школа №14
г о Подольск МО
Практический курс по базам данных
на языке SQL в PYTON
Подготовил:
Учитель высшей категории
Глушков Николай Васильевич
предмет : информатика
Ноябрь
2020 г
Содержание
Введение.
Раздел1. Справочный
материал по созданию баз данных
в Python
на SQL
1.1Базовый синтаксис SQL.
Создание соединения. Создание баз данных, таблицы
1.2. Ввод данных в таблицу. Обновление данных.
1.3. Редактирование и удаление записей.
1.4. Чтение данных. Создание запросов.
Раздел 2. Различные приемы работы с
базами данных.
2.1 Вложенные Select
2.2. Встроенные функции в базах данных
2.3 Присоединение таблиц и псевдонимы
2.4 Доступ к столбцам по имени вместо индекса
2.5 Адаптеры и конверторы по умолчанию.
Раздел 4. Практический курс : создание таблиц и запросов в БД
4.1. Задача БД Сотрудники, расчет зарплаты
4.2. Задача БД Салон Автомашин.
4.3. Задача БД Гостиницы
4.4. Задача БД Поговорки
4.5. Задача БД Пословицы
4.6. Задача БД Композиторы
4.7. Задача БД Склад
4.8. Задача БД Треугольник
Введение
Автором данной работы ранее были опубликованы на учительском сайте (Инфоуроке)
практические разработки по базам данных на языке MYSQL c
использованием виртуальной машины. Настоящая работа посвящена практическим
задачам по базам данных на языке Python . В работе
представлены разделы в виде обзора статей в качестве справочного материала по SQL на PYTHON и
раздел в виде практического курса с авторскими скриптами по базам данных для
облегчения изучения SQL на PYTHON Как
показывает опыт работы с базами данных для учащихся в 11 классе, после
ознакомления их с языком MYSQL, в практикум
можно добавить раздел с задачами по базам данных на языке Python, который
имеет встроенную библиотеку по базам данных-SQLite.
SQLite – это автономный, работающий без сервера транзакционный
механизм базы данных SQL. Python получил модуль sqlite3 в версии 2.5, что
значит что вы можете создавать базу данных SQLite в любой настоящей версии
Python, без необходимости скачивания дополнительных инструментов.
SQL и Python — обязательные инструменты для любого специалиста в
сфере анализа данных. Это руководство — все, что нужно для первоначальной
настройки и освоения основ работы с SQLite в Python.
Основы работы в python на SQL
Схема работы включает следующие пункты:
Загрузка библиотеки
Создание и соединение с базой данных
Создание таблиц базы данных
Добавление данных
Запросы на получение данных
Удаление данных
И многое другое!
Типы
данных SQLite в Python
SQLite
для Python предлагает
меньше типов данных, чем есть в других реализациях SQL. С
одной стороны, это накладывает ограничения, но, с другой стороны, в SQLite
многое сделано проще. Вот основные типы:
·
NULL —
значение NULL
·
INTEGER — целое
число
·
REAL — число с
плавающей точкой
·
TEXT — текст
·
BLOB —
бинарное представление крупных объектов, хранящееся в точности с тем, как его
ввели
К сожалению, других привычных для
SQL типов данных в SQLite нет.
·
SQLite
нативно поддерживает следующие типы: NULL, INTEGER, REAL, TEXT, BLOB.
Нижеперечисленные типы Python могут быть посланы SQLite безо всяких проблем:
|
Типы Python
|
Типы SQLite
|
|
None
|
NULL
|
|
|
|
int
|
INTEGER
|
|
long
|
INTEGER
|
|
float
|
REAL
|
|
str(UTF-8 encoded)
|
TEXT
|
|
uncode
|
TEXT
|
|
buffer
|
BLOB
|
·
SQLite
типы конвертируются в Python по умолчанию следующим образом:
|
Типы SQLite
|
Типы Python
|
|
NULL
|
None
|
|
|
|
INTEGER
|
int или long, в зависимости
от размера
|
|
REAL
|
float
|
|
TEXT
|
зависит от text_factory, по
умолчанию unicode
|
|
BLOB
|
buffer
|
·
Система
типов модуля sqlite3 позволяет расширяться двумя способами: возможно сохранение
дополнительных типов Python в базе данных SQLite через адаптацию объектов или
через преобразователи.
Раздел 1 .Справочный
материал по созданию баз данных
в Python на SQL
1.1Базовый
синтаксис SQL. Создание соединения. Создание баз данных , таблиц
Создание соединения
Чтобы
воспользоваться 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 являются безсерверными
и автономными, поскольку они читают и записывают данные в
файл. Это означает, что, в отличие от MySQL и PostgreSQL, вам даже не нужно
устанавливать и запускать сервер для выполнения операций с базой данных SQLite!
Вот как используется sqlite3 для подключения к
базе данных SQLite в Python:
import sqlite3
from sqlite3 import Error
def create_connection(path):
connection = None
try:
connection = sqlite3.connect(path)
print("Connection to SQLite
DB successful")
except Error as e:
print(f"The error '{e}' occurred")
return connection
Вот как работает этот код:
·
Строки
1 и 2 импортируют модуль sqlite3 и класс Error.
·
Строка
4 определяет функцию .create_connection(),
которая принимает путь к базе данных SQLite.
·
Строка
7 использует .connect() из модуля sqlite3 и
принимает путь к базе данных SQLite в качестве параметра. Если база данных
существует в указанном месте, то соединение с базой данных установлено. В
противном случае создается новая база данных в указанном месте и
устанавливается соединение.
·
Строка
8 печатает статус успешного подключения к базе
данных.
·
Строка
9 ловит любое исключение,
которое может быть выдано, если .connect() не удается установить
соединение.
·
Строка
10 отображает сообщение об ошибке в консоли.
Функция sqlite3.connect(path) возвращает объект connection, который, в свою очередь, возвращается функцией create_connection(). Этот объект connection можно использовать для выполнения запросов к базе данных SQLite.
Следующий скрипт создает соединение с базой данных SQLite:
connection = create_connection("E:\\db_python_app.sqlite")
Выполнив вышеуказанный сценарий, мы увидите, что файл
базы данных db_python_app.sqlite создается в корневом каталоге. Обратите внимание, что мы можем
изменить местоположение в соответствии с вашими настройками.
После создания соединения с SQLite, файл
БД создается автоматически, при условии его отсутствия. Этот файл создается на
диске, но также можно создать базу данных в оперативной памяти, используя
параметр «:memory:» в методе connect.
Создание таблицы
Чтобы создать таблицу в SQLite3, выполним
запрос Create Table в методе execute(). Для этого выполним следующую
последовательность шагов:
1. Создание
объекта подключения
2. Объект
Cursor создается с использованием объекта подключения
3.
Используя
объект курсора, вызывается метод execute с запросом create table в качестве
параметра.
В Python
набираем код: Имя таблицы: table_name
CREATE TABLE table_name (
id
INTEGER,
name
VARCHAR,
make
VARCHAR
model
VARCHAR,
year
DATE,
PRIMARY
KEY (id)
);
Это довольно обобщенный код, но он работает в большей части
случаев. Первое, на что стоит обратить внимание – куча слов прописанных
заглавными буквами. Это команды SQL. Их не всегда нужно вписывать через капс, но это
сделано для того,
чтобы помочь увидеть их. Каждая база данных поддерживает слегка отличающиеся команды.
Большинство будет содержать CREATE TABLE, но типы столбцов баз данных могут быть разными. Нужно
обратить внимание на то, что в этом примере есть базы
данных INTEGER, VARCHAR и DATE.
DATE может
вызывать много разных штук, как и VARCHAR. В любом случае, в этом примере мы создаем базу данных с
пятью столбцами. Первый – это id, который мы настраиваем в качестве нашего
основного ключа. Он не должен быть NULL,
Остальные столбцы говорят сами за себя
1.2 Ввод данных
в таблицу. Обновление данных.
Сейчас наша база
данных пустая. По коду добавляем данные в базу. Вот общий код:
INSERT
INTO table_name (id, name, make, model, year)
VALUES (1, 'Marly', 'Ford', 'Explorer',
'2000');
SQL использует команды INSERT INTO для добавления данных в определенную базу данных. Вы
также указываете, в какие столбцы вы добавляете данные. Когда мы создаем таблицу, мы можем определить необходимый столбец, который может
вызвать ошибку, если мы не добавим в него необходимые данные.
Представим, что мы сделали опечатку в нашем INSERT. Чтобы это
исправить, нам нужно использовать команду SQL под названием UPDATE:
UPDATE table_name
SET name='Chevrolet'
WHERE
id=1;
Команда UPDATE говорит
нам, какая таблица нуждается в обновлении. Далее мы используем SET в
одном или более столбцах для вставки нового значения. Наконец, на нужно указать
базе данных ту строку, которую мы хотим обновить. Мы можем использовать
команду WHERE, чтобы указать базе данных, что мы хотим изменить строчку,
Id которой является 1.
Пример
безопасного метода ввода (для таблицы «albums»-
# Вставляем множество данных в таблицу используя безопасный метод "?"
albums = [('Exodus', 'Andy Hunter',
'7/9/2002', 'Sparrow Records', 'CD'),
('Until
We Have Faces', 'Red', '2/1/2011', 'Essential Records', 'CD'),
('The
End is Where We Begin', 'Thousand Foot Krutch', '4/17/2012', 'TFKmusic', 'CD'),
('The
Good Life', 'Trip Lee', '4/10/2012', 'Reach Records', 'CD')]
cursor.executemany("INSERT INTO
albums VALUES (?,?,?,?,?)", albums)
conn.commit()
Здесь используется
команда INSERT INTO SQL чтобы вставить запись в нашу базу данных. Обратите внимание
на то, что каждый объект находится в одинарных кавычках. Это может
усложнить работу, если вам нужно вставить строчки, которые содержат одинарные
кавычки. В любом случае, чтобы сохранить запись в базе данных, нам нужно
создать её. Следующая часть кода показывает, как добавить несколько записей за
раз при помощи метода курсора executemany.
Обратите внимание на то, что мы используем знаки вопроса (?), вместо
строк замещения (%) чтобы вставить значения. Обратите внимание, что
использование строки замещения не безопасно, так как может стать причиной появления
атаки инъекций SQL . Использование знака вопроса намного лучше, а использование
SQLAlchemy тем более, так как он делаете все необходимое, чтобы уберечь
вас от правки встроенных одинарных кавычек на то, что SQLite в состоянии
принимать.
1.3 Редактирование
и удаление записей
Возможность обновлять
записи в вашей базе данных это ключ к тому, чтобы ваши данные велись
аккуратно, и был полный порядок. Если вы не можете редактировать данные,
тогда ваша база станет бесполезной достаточно скоро. Иногда вам, в том числе,
нужно будет удалять и строки. Мы рассмотрим оба вопроса в данном разделе. Для
начала, давайте немного обновимся.
import sqlite3
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
sql = """
UPDATE albums
SET artist = 'John Doe'
WHERE artist = 'Andy Hunter’ “””
cursor.execute(sql)
conn.commit()
Здесь используется команда SQL UPDATE,
чтобы обновить таблицу альбомов. Здесь можно использовать
команду SET, чтобы изменить поле, так что в нашем случае мы изменим имя
исполнителя на John Doe в каждой записи, где поле исполнителя указано для Andy
Hunter. Если не подтвердить изменения, то они не будут внесены в базу данных. Команда DELETE
настолько же проста. Посмотрим:
import sqlite3
conn =
sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
sql = "DELETE FROM albums WHERE
artist = 'John Doe'"
cursor.execute(sql)
conn.commit()
Удаление еще проще, чем обновление. У SQL это занимает
всего две строчки. В данном случае, все, что нам нужно сделать, это указать SQLite,
из какой таблицы удалить (albums), и какую именно запись при помощи пункта WHERE.
Таким образом, был выполнен поиск записи, в которой присутствует имя “John Doe”
в поле исполнителей, после чего эти данные были удалены.
1.4.Чтение данных. Создание запросов.
Основные
запросы SQLite
Запросы в SQLite очень похожи на те, которые вы
используете в других базах данных, таких как MySQL или Postgres. Мы просто
используем обычный синтаксис SQL для выполнения запросов, после чего объект
cursor выполняет SQL. Вот несколько примеров:
Чтение данных из базы
данных осуществляется при помощи оператора SQL под названием SELECT:
Первый запрос, который мы выполнили, называется SELECT *,
что означает, что мы хотим выбрать все записи, подходящие под переданное
имя исполнителя, в нашем случае это “Red”.
import sqlite3
conn =
sqlite3.connect("mydatabase.db")
#conn.row_factory = sqlite3.Row
cursor = conn.cursor()
sql = "SELECT * FROM albums WHERE
artist=?"
cursor.execute(sql, [("Red")])
print(cursor.fetchall()) # or use
fetchone()
print("Here's a listing of all the
records in the table:")
for row in cursor.execute("SELECT
rowid, * FROM albums ORDER BY artist"):
print(row)
print("Results from a LIKE
query:")
sql = "SELECT * FROM albums WHERE
title LIKE 'The%'"
cursor.execute(sql)
print(cursor.fetchall())
Второй запрос
очень похож на первый, но возвращает каждую запись в базе данных и
упорядочивает результаты по имени артиста в порядке возрастания. Это также
показывает, как мы можем зациклить результаты выдачи. Последний запрос
показывает, как команда LIKE используется при поиске частичных фраз. В
нашем случае, мы искали по всей таблице заголовки, которые начинаются с артикля
The. Знак процента (%) является подстановочным оператором.
SELECT name, make, model
FROM table_name;
Так мы возвращаем
все строчки из нашей базы данных, но результат будет содержать только три части данных: название, создание и модель. Если вы хотите
охватить все данные в базе данных, вы можете выполнить следующее:
SELECT * FROM table_name;
Звездочка в данном случае это подстановка, которая говорит
SQL, что вы хотите охватить все столбцы. Если вы хотите ограничить выбранный
вами охват, вы можете добавить команду WHERE в
вашем запросе:
SELECT name, make, model
FROM table_name
WHERE year >= '2000-01-01'
AND
year
<= '2006-01-01';
Так мы получим информацию о названии, создании и модели для
2000-2006 годов. Существует ряд других команд SQL, которые помогут вам в работе
с запросами. Убедитесь, что ознакомитесь с такими командами как BETWEEN, LIKE, ORDER BY, DISTINCT и JOIN.
DELETE FROM table_name
WHERE name='Ford';
Этот код удалит все строчки, в поле названия которых указано
«Ford» из нашей таблицы. Если вы хотите удалить всю таблицу, вы можете
воспользоваться оператором DROP:
DROP TABLE table_name;
Используйте DROP и DELETE осторожно,
так как вы легко можете потерять все данные, если вызовете оператор
неправильно. Всегда держите хороший, годный, проверенный бекап вашей базы
данных
Также иногда нам нужно отсортировать
выводимые данные. Для этого мы используем ORDER BY "название
столбца". ORDER BY имеет два модификатора: ASC (по возрастанию) (по
умолчанию) и DESC (по убыванию).
SELECT name, weapon FROM "characters" ORDER BY name DESC
Примеры создания запросов
Where
Теперь
мы знаем, как показать только конкретные столбцы, но что если мы хотим включить
в вывод лишь некоторые конкретные строки? Для этого мы используем WHERE. Данное
ключевое слово позволяет нам фильтровать данные по определённому условию.В
следующем запросе мы выведем только тех персонажей, которые в качестве оружия
используют пистолет.
SELECT *
FROM characters
WHERE weapon = 'pistol';
И/или
Условия в WHERE могут быть написаны с использованием логических операторов
(AND/OR) и математические операторы сравнения (=, <, >, <=, >=,
<>).
К примеру, у нас есть табличка, в которой записаны данные о 4 самых продаваемых
музыкальных альбомах всех времён. Давайте выведем только те, жанром которых
является рок, а продажи были меньше, чем 50 миллионов копий.
SELECT
*
FROM
albums
WHERE
genre = 'rock' AND sales_in_millions <= 50
ORDER BY released
In/Between/Like
Условия
в WHERE могут быть записаны с использованием ещё нескольких команд, которыми
являются:
IN - сравнивает значение в столбце с несколькими возможными значениями и возвращает
true, если значение совпадает хотя бы с одним значением
BETWEEN - проверяет, находится ли значение в каком-то промежутке
LIKE - ищет по шаблону
К примеру, мы можем сделать запрос для вывода данных об альбомах в жанре pop
или soul:
SELECT * FROM albums WHERE genre IN ('pop','soul');
Если мы хотим вывести все альбомы, которые были выпущены в промежутке между
1975 и 1985 годом, мы можем использовать следующую запись:
SELECT * FROM albums WHERE released
BETWEEN 1975 AND 1985;
Также, если мы хотим вывести все альбомы,
в названии которых есть буква 'R', мы можем использовать следующую запись:
SELECT * FROM albums WHERE album LIKE '%R%';
Знак % означает любую последовательность
символов (0 символов тоже считается за последовательность).
Если мы хотим вывести все альбомы, первая
буква в названии которых - 'R', то запись слегка изменится:
SELECT * FROM albums WHERE album LIKE 'R%';
В SQL также есть инверсия. Для примера, попробуйте самостоятельно написать NOT
перед любым логическим выражением в условии (NOT BETWEEN и так далее).
Раздел 2. Различные
приемы работы с базами данных.
2.1 Вложенные Select
В предыдущих шагах мы изучили, как делать
простые вычисления с данными. Если мы хотим использовать результат данных
вычислений, то часто нам необходимо использовать так называемые вложенные
запросы. Допустим, нам необходимо вывести артиста, альбом и год выпуска самого
старого альбома в таблице.
Вывести эти столбцы можно, используя следующий запрос:
SELECT artist, album, released FROM albums;
Также мы знаем, как получить самый ранний год из имеющихся:
SELECT MIN(released) FROM album;
Объединить эти запросы можно в WHERE:
SELECT artist,album,released
FROM albums
WHERE released = (
SELECT MIN(released) FROM albums)
2.2 Встроенные функции в базах данных
Часто
используемые:
COUNT() - возвращает число строк
SUM() - возвращает сумму всех полей с числовыми значениями в них
AVG() - возвращает среднее значение среди строк
MIN()/MAX() - возвращает минимальное/максимальное значение среди строк
Чтобы вывести год выпуска самого старого альбома, в таблице можно использовать
следующий запрос:
SELECT MAX(released) FROM albums;
Обратите внимание, что если вы напишете запрос, в котором вам, к примеру, нужно
будет вывести имя и среднее значение чего-либо, то вы получите ошибку на
выводе.
Допустим, вы пишете такой запрос:
SELECT name, avg(age) FROM students;
Чтобы избежать ошибки, вам следует добавить следующую строку:
GROUP BY name
Причиной тому является, что запись avg(age) является совокупной (aggregated), и
вам необходимо группировать значения по имени.
2.3 Присоединение таблиц и псевдонимы
В
сложных базах данных чаще всего у нас есть несколько связанных таблиц. К
примеру, у нас есть две таблицы: про видеоигры и про разработчиков.
В таблице video_games есть столбец
developer_id, в данном случае он является так называемым foreign_key. Чтобы
было проще понять, developer_id - это связывающее звено между двумя таблицами.
Если мы хотим вывести всю информацию об
игре, включая информацию о её разработчике, нам необходимо подключить вторую
таблицу. Чтобы
это сделать, можно использовать INNER JOIN:
SELECT video_games.name, video_games.genre, game_developers.name,
game_developers.country
FROM video_games
INNER JOIN game_developers
ON video_games.developer_id = game_developers.i.
Псевдонимы
Если вы взгляните на предыдущий пример, то
вы заметите, что есть два столбца, названных одинаково: "name". Часто
это может запутать. Решением данной проблемы являются псевдонимы. Они, к слову,
помогают сделать название столбца красивее или понятнее в случае необходимости.
Чтобы присвоить столбцу псевдоним, можно использовать ключевое слово AS:
SELECT games.name, games.genre, devs.name AS developer, devs.country
FROM video_games AS games
INNER JOIN game_developers AS
devs
ON games.developer_id = devs.id;
2.4 Доступ к столбцам
по имени вместо индекса
Одна из полезных возможностей модуля
sqlite3 является класс sqlite3.Row разработанный что бы быть использованным как
фабрика рядов.
К рядам, обернутыми в этот класс, можно
получить доступ как по индексам (подобно кортежам) так и, без влияния регистра,
по имени.
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE people(name_last,age)")
cur.execute("INSERT INTO people values (?,?)",[(u'Степанов'),25])
cur.execute("select name_last, age from people")
for row in cur:
assert row[0] == row["name_last"]
assert row["name_last"] ==
row["nAmE_lAsT"]
assert row[1] == row["age"]
assert row[1] == row["AgE"]
print row[0],row[1]
2.5 Адаптеры
и конверторы по умолчанию.
Для
типов date и datetime в модуле datetime существуют базовые адаптеры, которые
будут посланы в SQLite как ISO даты / ISO временные метки.
Базовые конверторы
зарегистрированы под именем “date” для типа datetime.date и под
именем “timestamp” для типа datetime.datetime.
Таким образом, можно
использовать даты и временные метки из Python без каких либо дополнительных
махинаций в большинстве случаев. Формат адаптеров также совместим с
экспериментальными функциями даты и времени SQLite.
Пример ниже демонстрирует это:
import sqlite3
import datetime
con = sqlite3.connect(":memory:",
detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")
today = datetime.date.today()
now = datetime.datetime.now()
cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print today, "=>", row[0], type(row[0])
print now, "=>", row[1], type(row[1])
cur.execute('select current_date as "d [date]", current_timestamp as
"ts [timestamp]"')
row = cur.fetchone()
print "current_date", row[0], type(row[0])
print "current_timestamp", row[1], type(row[1])
Результат:
2011-03-05 => 2011-03-05 <type 'datetime.date'>
2011-03-05 12:31:20.712000 => 2011-03-05 12:31:20.712000 <type
'datetime.datetime'>
current_date 2011-03-05 <type 'datetime.date'>
current_timestamp 2011-03-05 01:31:20 <type 'datetime.datetime'>
Раздел 4.
Практический курс создания запросов в БД
Следует отметить, что . Вывод и обращение к данным ведется с
помощью класса PYMYSQL
применяется цикл и обращение к данным по именам столбцов
к примеру: for row in cur:
assert row[0] ==
row["id"]
…………………………………
……………………………….
print (row[0],row[1],row[2],row[3],row[4],row[5])
4.1. Задача
БД Сотрудники, расчет зарплаты
В данной задаче создается
таблица people
(«Сотрудники» с полями :первичный ключ, имя, фамилия, возраст, адрес, телефон.
Данные вводятся через безопасный прием . Вывод и обращение к данным ведется
с помощью класса PYMYSQL ;
применяется цикл и обращение к данным по именам столбцов:
Код программы:
# -*- coding: utf-8 -*-
import sqlite3
con =
sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
#Создание таблицы people- сотрудники
cur.execute("CREATE TABLE people(id
integer ,name text, name_last text, age integer,syty text, telefon
text)")
#Ввод данных
cur.execute("INSERT INTO people
values (?,?,?,?,?,?)",[1,'Иван','Степанов',25,'Северная','83567889971'])
cur.execute("INSERT INTO people
values (?,?,?,?,?,?)",[2,'Илья','Иванов',27,'Южная','85985345559'])
cur.execute("INSERT INTO people
values
(?,?,?,?,?,?)",[3,'Николай','Уткин',37,'Покров','85985112359'])
cur.execute("INSERT INTO people
values (?,?,?,?,?,?)",[4,'Инна','Иванова',47,'Мраморная','85123896329'])
cur.execute("INSERT INTO people
values (?,?,?,?,?,?)",[5,'Нина','Пушкина',44,'Южная','89064344119'])
#Запрос : Вывод данных 1 таблицы
print("id , name , name_last, age,
syty")
cur.execute("select * from people
")
#cur.execute("select id,
name,name_last ,age,syty,telefon from people")
for row in cur:
assert row[0] == row["id"]
assert row[1] == row["name"]
assert row[2] ==
row["name_last"]
assert row[3] == row["age"]
assert row[4] == row["syty"]
assert row[5] ==
row["telefon"]
print
(row[0],row[1],row[2],row[3],row[4],row[5])
# Создание таблицы Зарплата
cur.execute("CREATE TABLE zarplata(tid
integer ,tarif integer,dni integer)")
#Ввод данных
cur.execute("INSERT INTO zarplata
values (?,?,?)",[1,16000,23])
cur.execute("INSERT INTO zarplata
values (?,?,?)",[2,18000,20])
cur.execute("INSERT INTO zarplata
values (?,?,?)",[3,10000,22])
cur.execute("INSERT INTO zarplata
values (?,?,?)",[4,12000,17])
cur.execute("INSERT INTO zarplata
values (?,?,?)",[5,14000,15])
#Запрос : Вывод данных 2 таблицы:
код, тариф, отработанные дни,начислено, премия и налог
print('код, тариф, дни, начислено,
премия , налог')
cur.execute("select
tid,tarif,dni,dni*tarif ,dni*tarif*0.5,(dni*tarif+dni*tarif*0.5)*0.13 from
zarplata")
for row in cur:
assert row[0] == row["tid"]
assert row[1] ==
row["tarif"]
assert row[2] == row["dni"]
assert row[3] ==
row["dni*tarif"]
assert row[4] ==
row["dni*tarif*0.5"]
#assert row[5] ==
row["(dni*tarif+dni*tarif*0.5)*0.13"]
#print(row[0],row[1],row[2],row[3],row[4]=row[3]*0.5,row[5])
print(row[0],row[1],row[2],row[3],row[4],(row[3]+row[4])*0.13,row[3]+row[4]-(row[3]+row[4])*0.13)
#Запрос : Вывод данных 2 таблицы:
код, начислено, премия
print('связь 2-х тблиц')
cur.execute("select
people.id,people.name_last,zarplata.dni,zarplata.tarif from people,zarplata
where people.id=zarplata.tid")
for row in cur:
assert row[0] == row["id"]
assert row[1] ==
row["name_last"]
assert row[2] == row["dni"]
assert row[3] ==
row["tarif"]
print(row[0],row[1],row[2],row[3],row[2]*row[3],row[2]*row[3]*0.5)
Результат работы программы:
id , name , name_last, age, syty
1 Иван Степанов 25 Северная 83567889971
2 Илья Иванов 27 Южная 85985345559
3 Николай Уткин 37 Покров 85985112359
4 Инна Иванова 47 Мраморная 85123896329
5 Нина Пушкина 44 Южная 89064344119
код, тариф, дни, начислено, премия ,
налог
1 16000 23 368000 184000.0 71760.0
480240.0
2 18000 20 360000 180000.0 70200.0
469800.0
3 10000 22 220000 110000.0 42900.0
287100.0
4 12000 17 204000 102000.0 39780.0
266220.0
5 14000 15 210000 105000.0 40950.0
274050.0
связь 2-х таблиц
1 Степанов 23 16000 368000 184000.0
2 Иванов 20 18000 360000 180000.0
3 Уткин 22 10000 220000 110000.0
4 Иванова 17 12000 204000 102000.0
5 Пушкина 15 14000 210000 105000.0
4.2. Задача БД Автомашины
Поля: Id INT,
Name TEXT,
sila int,
Price INT,
colors,
kuz int
Первичный ключ, марка, мощность, цена, цвет, коэффициент уценки
#Код программы:
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE Cars(Id INT, Name TEXT,
sila int,Price INT,colors,kuz int)")
# Вносим
данные
cur.execute("INSERT INTO Cars VALUES(1,
'Audi',900, 520642,'pink',20)")
cur.execute("INSERT INTO Cars VALUES(2,
'Mercedes', 390,4000000,'white',15)")
cur.execute("INSERT INTO Cars VALUES(3,
'Skoda',110, 741000,'Brown',10)")
cur.execute("INSERT INTO Cars VALUES(4,
'Lada-Vesta',122, 760860,'yellow',30)")
cur.execute("INSERT INTO Cars VALUES(5,
'nissan',110, 350000,'black',25)")
cur.execute("INSERT INTO Cars VALUES(6,
'Citroen',115, 523000,'orange',22)")
cur.execute("INSERT INTO Cars VALUES(7, 'locus',
1300,414000,'black',15)")
cur.execute("INSERT INTO Cars VALUES(8,
'Bugatti',1500, 210600,'blue',10)")
cur.execute("INSERT INTO Cars VALUES(9,
'Ultimate',1350, 350600,'red',35)")
print("Запрос- вывести данные по всем
машинам")
print(" Id INT, Name TEXT, sila int,Price
INT,colors,kuz int")
cur.execute("select * from Cars ")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
assert row[2] == row["sila"]
assert row[3] == row["price"]
assert row[4] == row["colors"]
print (row[0],row[1],row[2],row[3],row[4])
# Запрос вывести данные по коду 4
print("Запрос- вывести данные по коду
4 и 7")
cur.execute("select * from Cars where cars.id=4
or cars.id=7")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
assert row[2] == row["sila"]
assert row[3] == row["price"]
assert row[4] == row["colors"]
print (row[0],row[1],row[2],row[3],row[4])
print("список автомобилей с уценкой")
#список
автомобилей
с
уценкой
cur.execute("select * from Cars ")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
assert row[3] == row["Price"]
assert row[4] == row["colors"]
assert row[5] == row["kuz"]
print
(row[0],row[1],row[3],row[3]-row[3]*row[5]/100)
print("список автомобилей цена
которых менее 600000")
cur.execute("select * from Cars where
cars.price<600000 ")
for row in
cur:
assert
row[0] == row["Id"]
assert
row[1] == row["Name"]
assert
row[3] == row["Price"]
assert
row[4] == row["colors"]
print
(row[0],row[1],row[3],row[4])
print("список
автомобилей с мощностью менее 150 лс")
cur.execute("select
* from Cars where cars.sila<150 ")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
assert row[2] == row["sila"]
assert row[3] == row["price"]
assert row[4] == row["colors"]
print (row[0],row[1],row[2],row[3],row[4])
Результат работы программы:
Запрос- вывести данные по всем машинам
Id INT, Name TEXT,
sila int, Price INT, colors, kuz int
1 Audi 900 520642 pink
2 Mercedes 390 4000000 white
3 Skoda 110 741000 Brown
4 Lada-Vesta 122 760860 yellow
5 nissan 110 350000 black
6 Citroen 115 523000 orange
7 locus 1300 414000 black
8 Bugatti 1500 210600 blue
9 Ultimate 1350 350600 red
Запрос- вывести данные по коду 4 и 7
4 Lada-Vesta
122 760860 yellow
7 locus
1300 414000 black
список автомобилей с уценкой
1 Audi 520642 416513.6
2 Mercedes 4000000 3400000.0
3 Skoda 741000 666900.0
4 Lada-Vesta 760860 532602.0
5 nissan 350000 262500.0
6 Citroen 523000 407940.0
7 locus 414000 351900.0
8 Bugatti 210600 189540.0
9 Ultimate 350600 227890.0
список автомобилей цена которых менее
600000 р.
1 Audi 520642 pink
5 nissan 350000 black
6 Citroen 523000 orange
7 locus 414000 black
8 Bugatti 210600 blue
9 Ultimate 350600 red
список автомобилей с мощностью менее 150
лс
3 Skoda 110 741000 Brown
4 Lada-Vesta 122 760860 yellow
5 nissan 110 350000 black
6 Citroen 115 523000 orange
4.3.
Задача БД
Гостиницы
Созданы 2 таблицы
: директора и
гостиницы
Поля БД
директора dir(idd
integer , fam text,syty text,telefon text ,kategor integer,lizens text )")
Поля БД
гостиницы(gost(idg
integer , name text, shmest integer,zena3 integer,zena2 integer,zena1 integer)")
(перв. Ключ,
наименование, число мест, цены по категориям
Код программы:
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE dir(idd integer , fam
text,syty text,telefon text,kategor integer, lizens text )")
print(" Запрос
: Директора гостиниц")
print(' idd,fam ,syty ,telefon,kategor,lizens')
cur.execute("INSERT INTO dir values (?,?,?,?,?,?)",[1,'Степанов','Москва','1356788997',1,'y'])
cur.execute("INSERT INTO dir values
(?,?,?,?,?,?)",[2,'Иванов','Санкт-Петербург','25985559',1,'y'])
cur.execute("INSERT INTO dir values
(?,?,?,?,?,?)",[3,'Уткин','Рязань','35985159',2,'n'])
cur.execute("INSERT INTO dir values
(?,?,?,?,?,?)",[4,'Москвин','Н.Новгород','45632569',2,'n'])
cur.execute("select * from dir")
#cur.execute("select id, name,name_last
,age,syty,telefon from people")
for row in cur:
assert row[0] == row["idd"]
assert row[1] == row["fam"]
assert row[2] == row["syty"]
assert row[3] == row["telefon"]
assert row[4] == row["kategor"]
assert row[5] == row["lizens"]
print (row[0],row[1],row[2],row[3],row[4],row[5])
print("****************************************************")
cur.execute("CREATE TABLE gost(idg integer , name
text,shmest integer,zena3 integer,zena2 integer,zena1 integer)")
cur.execute("INSERT INTO gost values
(?,?,?,?,?,?)",[1,'Арбат','1800','8000','5000','2500'])
cur.execute("INSERT INTO gost values
(?,?,?,?,?,?)",[2,'Нева','2500','9000','5000','2500'])
cur.execute("INSERT INTO gost values
(?,?,?,?,?,?)",[3,'Рязань','2000','8000','5500','3000'])
cur.execute("INSERT INTO gost values
(?,?,?,?,?,?)",[4,'Волга','1500','8500','6000','2500'])
print(" Запрос : гостиницы и
реквизиты")
print(' idg ,name ,shmest ,zena3,zena2,zena1')
cur.execute("select * from gost")
for row in cur:
assert row[0] == row["idg"]
assert row[1] == row["name"]
assert row[2] == row["shmest"]
assert row[3] == row["zena3"]
assert row[4] == row["zena2"]
assert row[5] == row["zena1"]
print (row[0],row[1],row[2],row[3],row[4],row[5])
print("запрос связь между
таблицами")
print('idd ,fam ,syty,kategor,name,shmest')
cur.execute("select
dir.idd,dir.fam,dir.syty,dir.kategor,gost.name,gost.shmest from dir, gost where
dir.idd=gost.idg")
for row in cur:
assert row[0] == row["idd"]
assert row[1] == row["fam"]
assert row[2] == row["syty"]
assert row[3] == row["kategor"]
assert row[4] == row["name"]
assert row[5] == row["shmest"]
print(row[0],row[1],row[2],row[3],row[4],row[5])
Результат работы
программы:
Запрос
: Директора гостиниц
idd,fam ,syty ,telefon,kategor,lizens
1 Степанов Москва 1356788997 1 y
2 Иванов Санкт-Петербург 25985559 1 y
3 Уткин Рязань 35985159 2 n
4 Москвин Н.Новгород 45632569 2 n
****************************************************
Запрос : гостиницы и реквизиты
idg ,name ,shmest ,zena3,zena2,zena1
1 Арбат 1800 8000 5000 2500
2 Нева 2500 9000 5000 2500
3 Рязань 2000 8000 5500 3000
4 Волга 1500 8500 6000 2500
запрос связь между таблицами
idd ,fam ,syty,kategor,name,shmest
1 Степанов Москва 1 Арбат 1800
2 Иванов Санкт-Петербург 1 Нева 2500
3 Уткин Рязань 2 Рязань 2000
4 Москвин Н.Новгород 2 Волга 1500
4.4. Задача БД Поговорки
Поля таблицы : TABLE frasa
(Id INT,
Name TEXT)
#Код программы
# База данных
– Поговорки:
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE frasa (Id INT, Name
TEXT)")
# Вносим
данные
cur.execute("INSERT INTO Frasa VALUES(1, 'Беседа
найдет
соседа')")
cur.execute("INSERT INTO Frasa VALUES(2, 'Белая
ворона
(неадекватный человек)')")
cur.execute("INSERT INTO Frasa VALUES(3, 'Белены
объелся
( человек сошел
с
ума)')")
cur.execute("INSERT INTO Frasa VALUES(4, 'Березовая
каша
(Совместная трапеза
в
старину
в
школе)')")
cur.execute("INSERT INTO Frasa VALUES(5, 'Бить
баклуши
(Деревянные заготовки
для
ложек)')")
cur.execute("INSERT INTO Frasa VALUES(6, 'Блудный
сын
(Художник Рембрандт
отразил
Притчу
о
блудном
сыне)')")
cur.execute("INSERT INTO Frasa VALUES(7, 'В
бирюльки
играть(Старинная
игра)')")
cur.execute("INSERT INTO Frasa
VALUES(8, 'Вилами на воде писано (Мы говорим о чем-то сомнительном и
неясном)')")
cur.execute("INSERT INTO Frasa VALUES(9, 'Время
истекло.
Время
течет( Водяные часы - Вавилон,Греция,Рим)')")
cur.execute("INSERT INTO Frasa
VALUES(10, 'Галопом по Европам (Выражение возникло как насмешка над
горе-туристами)')")
cur.execute("INSERT INTO Frasa
VALUES(11, 'Гвоздь программы (Происходит от названия Эйфелевой башни,похожей на
гвоздь)')")
cur.execute("INSERT INTO Frasa
VALUES(12, 'Где раки зимуют (Это значит - большое и трудное занятие - ловля
раков в ледяной воде)')")
cur.execute("INSERT INTO Frasa VALUES(13, 'Языком
кружева
плетет(
о
болтуне)')")
cur.execute("INSERT INTO Frasa
VALUES(14, 'Решетом воду носит (о бестолковости)')")
cur.execute("INSERT INTO Frasa
VALUES(15, 'Горе луковое (Луковые слезы - слезы по пустякам)')")
cur.execute("INSERT INTO Frasa
VALUES(16, 'Двуликий Янус( бог времени, с двумя лицами - молодым и
старческим)')")
cur.execute("INSERT INTO Frasa
VALUES(17, 'Дело в шляпе (В старину для сохранности важные бумаги зашивали в
шляпу)')")
cur.execute("INSERT INTO Frasa VALUES(18,
'Жар загребать чужими руками (Хозяйке легче и проще это делать чужими
руками)')")
cur.execute("INSERT INTO Frasa
VALUES(19, 'Заварить кашу (В старину званный обед, праздник по
поводу....)')")
cur.execute("INSERT INTO Frasa
VALUES(20, 'Знать на зубок (Это значит в чем-нибудь хорошо
разбираться)')")
print("Запрос- вывести данные из
словаря")
# Запрос
по
всем
данным
cur.execute("select * from frasa ")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
print (row[0],row[1])
# Запрос вывести данные по коду 4
print("Запрос- вывести данные по коду
4 и 7")
cur.execute("select * from frasa where frasa.id=4
or frasa.id=7")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
print (row[0],row[1])
Результат работы программы:
Запрос- вывести данные из словаря
1 Беседа найдет соседа
2 Белая ворона (неадекватный человек)
3 Белены объелся ( человек сошел с ума)
4 Березовая каша (Совместная трапеза в
старину в школе)
5 Бить баклуши (Деревянные заготовки для
ложек)
6 Блудный сын (Художник Рембрандт отразил
Притчу о блудном сыне)
7 В бирюльки играть(Старинная игра)
8 Вилами на воде писано (Мы говорим о
чем-то сомнительном и неясном)
9 Время истекло. Время течет( Водяные часы
- Вавилон,Греция,Рим)
10 Галопом по Европам (Выражение возникло
как насмешка над горе-туристами)
11 Гвоздь программы (Происходит от
названия Эйфелевой башни,похожей на гвоздь)
12 Где раки зимуют (Это значит - большое и
трудное занятие - ловля раков в ледяной воде)
13 Языком кружева плетет( о болтуне)
14 Решетом воду носит (о бестолковости)
15 Горе луковое (Луковые слезы - слезы по
пустякам)
16 Двуликий Янус( бог времени, с двумя
лицами - молодым и старческим)
17 Дело в шляпе (В старину для сохранности
важные бумаги зашивали в шляпу)
18 Жар загребать чужими руками (Хозяйке
легче и проще это делать чужими руками)
19 Заварить кашу (В старину званный обед,
праздник по поводу....)
20 Знать на зубок (Это значит в чем-нибудь
хорошо разбираться)
Запрос- вывести данные по коду 4 и 7
4 Березовая каша (Совместная трапеза в
старину в школе)
7 В бирюльки играть(Старинная игра)
4.5. Задача БД Пословицы
#Код программы:
# База данных – Пословицы 1
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE nposl (Id INT, Name
TEXT)")
# Вносим
данные
cur.execute("INSERT INTO nposl VALUES(1, 'Смотрит
в
книгу...')")
cur.execute("INSERT INTO nposl VALUES(2, 'Жизнь
прожить
....')")
cur.execute("INSERT INTO nposl VALUES(3, 'Без
дела
жить
...')")
cur.execute("INSERT INTO nposl VALUES(4, 'Под
лежачий
камень
и
......')")
cur.execute("INSERT INTO nposl VALUES(5, 'Снеси
яйцо
, а ......')")
cur.execute("INSERT INTO nposl VALUES(6, 'Копейка
к
копейке
......')")
cur.execute("INSERT INTO nposl VALUES(7, 'Слушай
ухом
, а ........')")
cur.execute("INSERT INTO nposl VALUES(8, 'На
словах
густо
,а .....')")
cur.execute("INSERT INTO nposl VALUES(9, 'Не
спеши
языком,
а
......')")
cur.execute("INSERT INTO nposl VALUES(10, 'Языком
молоть
- не дрова
колоть:
.....')")
cur.execute("INSERT INTO nposl VALUES(11, 'Верю
кошке
и
ежу
..........')")
cur.execute("INSERT INTO nposl VALUES(12, 'Рот
до
ушей
...........')")
cur.execute("INSERT INTO nposl VALUES(13, 'Верная
указка
- не .......')")
cur.execute("INSERT INTO nposl VALUES(14, 'У
семи
нянек
.......')")
cur.execute("INSERT INTO nposl VALUES(15, ' Волков
бояться.....')")
cur.execute("INSERT INTO nposl VALUES(16, 'Грач
на
горе
........')")
cur.execute("INSERT INTO nposl VALUES(17, 'Чем
богаты
...........')")
cur.execute("INSERT INTO nposl VALUES(18, 'Повторенье
.......')")
cur.execute("INSERT INTO nposl VALUES(19, 'Конец
- .......')")
cur.execute("INSERT INTO nposl VALUES(20, 'Кончил
дело
- .......')")
cur.execute("INSERT INTO nposl VALUES(21, 'Ученье
-свет .......')")
print("Запрос- вывести данные из
словаря")
# Запрос
по
всем
данным
cur.execute("select * from nposl ")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
print (row[0],row[1])
# Запрос вывести данные по коду 4
print("Запрос- вывести данные по коду
4 и 7")
cur.execute("select * from nposl where nposl.id=4
or nposl .id=7")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
print (row[0],row[1])
************************
#Код
программы:
# База данных – Пословицы 2
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE nposl (Id INT, Name
TEXT)")
# Вносим данные
cur.execute("INSERT INTO nposl VALUES(1, 'А видит
фигу..')")
cur.execute("INSERT INTO nposl VALUES(2, 'Не поле
перейти..')")
cur.execute("INSERT INTO nposl VALUES(3, 'Только
небо коптить ...')")
cur.execute("INSERT INTO nposl VALUES(4, 'И вода
не течет')")
cur.execute("INSERT INTO nposl VALUES(5, 'а потом
кудахтай.')")
cur.execute("INSERT INTO nposl VALUES(6, ' проживет
и семейка..')")
cur.execute("INSERT INTO nposl VALUES(7, ' а .не
брюхом.')")
cur.execute("INSERT INTO nposl VALUES(8, 'а в
голове пусто')")
cur.execute("INSERT INTO nposl VALUES(9, '
торопись делом.')")
cur.execute("INSERT INTO nposl VALUES(10, 'спина
не заболит .')")
cur.execute("INSERT INTO nposl VALUES(11, 'а тебе
погожу')")
cur.execute("INSERT INTO nposl VALUES(12, 'хоть
завязочки пришей')")
cur.execute("INSERT INTO nposl VALUES(13, 'не
кулак, а ласка.')")
cur.execute("INSERT INTO nposl VALUES(14, 'дитя
без глазу.')")
cur.execute("INSERT INTO nposl VALUES(15, '- в
лес не ходить ........')")
cur.execute("INSERT INTO nposl VALUES(16, 'весна
на дворе.')")
cur.execute("INSERT INTO nposl VALUES(17, 'тем и
рады ')")
cur.execute("INSERT INTO nposl VALUES(18, ' мать
ученья')")
cur.execute("INSERT INTO nposl VALUES(19, ' -делу
венец ..')")
cur.execute("INSERT INTO nposl VALUES(20, '-гуляй
смело ..')")
cur.execute("INSERT INTO nposl VALUES(21, 'а не
ученье тьма .')")
print("Запрос- вывести
данные из словаря")
# Запрос по всем данным
cur.execute("select * from nposl ")
for row in cur:
assert row[0] == row["Id"]
assert row[1] == row["Name"]
print (row[0],row[1])
# Запрос вывести данные по коду 4
print("Запрос- вывести
данные по коду 4 и 7")
cur.execute("select * from nposl where nposl.id=4
or nposl .id=7")
for row in cur:
assert row[0] == row["Id"]
assert row[1]
== row["Name"]
print
(row[0],row[1])
***********************************
# База данных – Пословицы 3 связь 2 –х таблиц 1,2
# База данных – Поговорки
#Код
программы:
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE posl (Id INT, nm
TEXT)")
# Вносим
данные
cur.execute("INSERT INTO posl VALUES(1, 'Смотрит
в
книгу...')")
cur.execute("INSERT INTO posl VALUES(2, 'Жизнь
прожить
....')")
cur.execute("INSERT INTO posl VALUES(3, 'Без
дела
жить
...')")
cur.execute("INSERT INTO posl VALUES(4, 'Под
лежачий
камень
и
......')")
cur.execute("CREATE TABLE nposl (Id1 INT, Name
TEXT)")
# Вносим
данные
cur.execute("INSERT INTO nposl VALUES(1, 'А
видит
фигу..')")
cur.execute("INSERT INTO nposl VALUES(2, 'Не
поле
перейти..')")
cur.execute("INSERT INTO nposl VALUES(3, 'Только
небо
коптить
...')")
cur.execute("INSERT INTO nposl VALUES(4, 'И
вода
не
течет')")
print("запрос связь между
таблицами")
cur.execute("select
posl.id,posl.nm,nposl.id1,nposl.name from posl,nposl where
posl.id=nposl.id1")
for row in cur:
assert row[0] == row["id"]
assert row[1] == row["nm"]
assert row[2] == row["id1"]
assert row[3] == row["name"]
print(row[0],row[1],row[2],row[3])
Результат работы
программы:
запрос связь
между таблицами
1 Смотрит в книгу... 1 А видит фигу..
2 Жизнь прожить .... 2 Не поле перейти..
3 Без дела жить ... 3 Только небо коптить
...
4 Под лежачий камень и ...... 4 И вода не
течет
>>>
4.6. Задача
БД Композиторы
Поля TABLE
kompositor(idk integer , fam text, year_of_birth text, country text)")
Код программы
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE kompositor(idk integer
, fam text,year_of_birth text,country text)")
print(" Запрос
: Композиторы")
print(' idk ,fam, year_of_birth, country ')
cur.execute("INSERT INTO kompositor values(1,'П.Чайковский','1840-1893','Россия')")
cur.execute("INSERT INTO kompositor values(2,'М.Мусоргский','1839-1881','Россия')")
cur.execute("INSERT INTO kompositor values(3,'С.Прокофьев','1891-1953','Россия')")
cur.execute("INSERT INTO kompositor values(4,'С.Рахманинов','1873-1943','Россия')")
cur.execute("INSERT INTO kompositor values(5,'Н.Римский-Корсаков','1844-1908','Россия')")
cur.execute("INSERT INTO kompositor values(6,'Моцарт','1756-91','Австрийский')")
cur.execute("INSERT INTO kompositor values(7,'А.Скрябин','1872-1915','Россия')")
cur.execute("INSERT INTO kompositor values(8,'И.Стравинский','1882-1971','Россия')")
cur.execute("INSERT INTO kompositor values(9,'А.Бородин','1833-87','Россия')")
cur.execute("INSERT INTO kompositor values(10,'М.Глинка','1804-1857','Россия')")
cur.execute("INSERT INTO kompositor values(11,'Э.Григ','1843-1907','Норвегия')")
cur.execute("INSERT INTO kompositor values(12,'Ф.
Шопен','1810-1849','Польша')")
cur.execute("INSERT INTO kompositor values(13,'Д.Шостакович','1906-1975','Россия')")
cur.execute("select * from kompositor")
#cur.execute("select id, name,name_last
,age,syty,telefon from people")
for row in cur:
assert row[0] == row["idk"]
assert row[1] == row["fam"]
assert row[2] == row["year_of_birth"]
assert row[3] == row["country"]
print (row[0],row[1],row[2],row[3])
print("****************************************************")
cur.execute("CREATE TABLE proiswed(idp integer ,
name text,year_of_birth text)")
cur.execute("INSERT INTO proiswed values(1,'Лебединое
озеро
, оперы и
балеты','1878-')")
cur.execute("INSERT INTO proiswed values(2,'Борис
Годунов
и
драмы','1872-')")
cur.execute("INSERT INTO proiswed values(3,'Война
и
мир,Ромео
и
Джульетта
и
др
оперы','1952-')")
cur.execute("INSERT INTO proiswed values(4,'Концерты,
симфонии
, поэмы','1904-')")
cur.execute("INSERT INTO proiswed values(5,'15 опер
- Снегурочка, Садко
и
др
соч','1872-')")
cur.execute("INSERT INTO proiswed values(6,'Свадьба
Фигаро
и
др
оперы,
симфонии
и
др
сочинения','
1786')")
cur.execute("INSERT INTO proiswed values(7,'Прометей,
Божественная
поэма
и
др
произв.','1898-')")
cur.execute("INSERT INTO proiswed values(8,'Петрушка
и
др.
оперы
и
кантаты','1911')")
cur.execute("INSERT INTO proiswed values(9,'Князь
Игорь,
оперы
и
симфонии','1890')")
cur.execute("INSERT INTO proiswed values(10,'Иван
Сусанин,Руслан
и
людмила
и
др
оперы','1836')")
cur.execute("INSERT INTO proiswed values(11,'Сюиты
к
Пер
Гюнт,
сонаты
и
др
произв',
'')")
cur.execute("INSERT INTO proiswed values(12,'Вальсы
,мазурки,полонезы
и
др
произв','1835
')")
cur.execute("INSERT INTO proiswed values(13,'Оперы
и
оперетты
и
др
сочинения','1936-
')")
print(" Запрос
: произведения")
cur.execute("select * from proiswed")
for row in cur:
assert row[0] == row["idp"]
assert row[1] == row["name"]
assert row[2] == row["year_of_birth"]
print (row[0],row[1],row[2])
print("запрос
связь
между
таблицами")
print('idk ,fam ,name ')
cur.execute("select
kompositor.idk,kompositor.fam, proiswed.name from kompositor, proiswed where
kompositor.idk=proiswed.idp")
for row in cur:
assert row[0] == row["idk"]
assert row[1] == row["fam"]
assert row[2] == row["name"]
print(row[0],row[1],row[2])
Результат работы программы:
Запрос : Композиторы
idk ,fam,
year_of_birth, country
1 П.Чайковский 1840-1893 Россия
2 М.Мусоргский 1839-1881 Россия
3 С.Прокофьев 1891-1953 Россия
4 С.Рахманинов 1873-1943 Россия
5 Н.Римский-Корсаков 1844-1908 Россия
6 Моцарт 1756-91 Австрийский
7 А.Скрябин 1872-1915 Россия
8 И.Стравинский 1882-1971 Россия
9 А.Бородин 1833-87 Россия
10 М.Глинка 1804-1857 Россия
11 Э.Григ 1843-1907 Норвегия
12 Ф. Шопен 1810-1849 Польша
13 Д.Шостакович 1906-1975 Россия
****************************************************
Запрос : произведения
1 Лебединое озеро , оперы и балеты 1878-
2 Борис Годунов и драмы 1872-
3 Война и мир,Ромео и Джульетта и др оперы
1952-
4 Концерты, симфонии , поэмы 1904-
5 15 опер - Снегурочка, Садко и др соч
1872-
6 Свадьба Фигаро и др оперы, симфонии и
др сочинения 1786
7 Прометей, Божественная поэма и др
произв. 1898-
8 Петрушка и др. оперы и кантаты 1911
9 Князь Игорь, оперы и симфонии 1890
10 Иван Сусанин,Руслан и людмила и др
оперы 1836
11 Сюиты к Пер Гюнт, сонаты и др произв
12 Вальсы ,мазурки,полонезы и др произв
1835
13 Оперы и оперетты и др сочинения 1936-
запрос связь между таблицами
idk ,fam ,name
1 П.Чайковский Лебединое озеро , оперы и
балеты
2 М.Мусоргский Борис Годунов и драмы
3 С.Прокофьев Война и мир,Ромео и
Джульетта и др оперы
4 С.Рахманинов Концерты, симфонии , поэмы
5 Н.Римский-Корсаков 15 опер - Снегурочка,
Садко и др соч
6 Моцарт Свадьба Фигаро и др оперы,
симфонии и др сочинения
7 А.Скрябин Прометей, Божественная поэма и
др произв.
8 И.Стравинский Петрушка и др. оперы и
кантаты
9 А.Бородин Князь Игорь, оперы и симфонии
10 М.Глинка Иван Сусанин,Руслан и людмила
и др оперы
11 Э.Григ Сюиты к Пер Гюнт, сонаты и др
произв
12 Ф. Шопен Вальсы ,мазурки,полонезы и др
произв
13 Д.Шостакович Оперы и оперетты и др
сочинения
**************************
4.7. Задача
БД Склад
Поля таблицы-
поставщики
TABLE postawsh(id integer , name_last text, syty text, telefon text, fact
text)")
Поля таблицы
склад
TABLE sklad(sid integer ,towar text,kpostaw integer,kolish integer ,zena
integer)")
Код программы:
# -*- coding: utf-8 -*-
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("CREATE TABLE postawsh(id integer
,name_last text,syty text,telefon text,fact text)")
cur.execute("INSERT INTO postawsh values
(?,?,?,?,?)",[1,'Степанов','Горький','83567889971','автопром'])
cur.execute("INSERT INTO postawsh values
(?,?,?,?,?)",[2,'Иванов','Беларусь','85985559123','продукты'])
cur.execute("INSERT INTO postawsh values
(?,?,?,?,?)",[3,'Уткин','Кострома','75985144678','продукты'])
cur.execute("INSERT INTO postawsh values
(?,?,?,?,?)",[4,'Спасский','127n','79113598517','радиопром'])
cur.execute("INSERT INTO postawsh values
(?,?,?,?,?)",[5,'Пущин','Ленинград','77359851478','промтовары'])
cur.execute("select * from postawsh")
print("запрос данные по
поставщикам")
#cur.execute("select id,name_last
,syty,telefon,fact from postawsh")
for row in cur:
assert row[0] == row["id"]
assert row[1] == row["name_last"]
assert row[2] == row["syty"]
assert row[3] == row["telefon"]
assert row[4] == row["fact"]
print (row[0],row[1],row[2],row[3],row[4])
#cur.execute("CREATE TABLE zakash(zid integer
,fam text,syty text,telefon text,tip text)")
#cur.execute("INSERT INTO zakash values
(?,?,?,?,?)",[1,'Рудской','Москва','8345678910','продукты'])
#cur.execute("INSERT INTO zakash values
(?,?,?,?,?)",[2,'Варгин','Москва','8345678910','Пром'])
#cur.execute("select zid,fam,syty,telefon ,tip
from zakash")
#for row in cur:
#assert row[0] == row["zid"]
#assert row[1] == row["fam"]
#assert row[2] == row["syty"]
#assert row[3] == row["telefon"]
#assert row[4] == row["tip"]
#print(row[0],row[1],row[2],row[3],row[4])
cur.execute("CREATE TABLE sklad(sid integer
,towar text,kpostaw integer,kolish integer ,zena integer)")
cur.execute("INSERT INTO sklad values
(?,?,?,?,?)",[1,'тачка',1,20,4600])
cur.execute("INSERT INTO sklad values
(?,?,?,?,?)",[2,'мясо',2,12000,460])
cur.execute("INSERT INTO sklad values
(?,?,?,?,?)",[3,'рыба',3,20000,300])
cur.execute("INSERT INTO sklad values
(?,?,?,?,?)",[4,'музцентр',4,220,8000])
cur.execute("INSERT INTO sklad values
(?,?,?,?,?)",[5,'дрель',5,420,7000])
print("запрос данные по складу")
cur.execute("select sid,towar,kpostaw,kolish,zena
from sklad")
for row in cur:
assert row[0] == row["sid"]
assert row[1] == row["towar"]
assert row[2] == row["kpostaw"]
assert row[3] == row["kolish"]
assert row[4] == row["zena"]
print(row[0],row[1],row[2],row[3],row[4])
print("запрос связь между таблицами")
cur.execute("select
postawsh.id,postawsh.name_last,sklad.sid,sklad.towar,sklad.kolish,sklad.zena
from postawsh,sklad where postawsh.id=sklad.sid")
for row in cur:
assert row[0] == row["id"]
assert row[1] ==
row["name_last"]
assert row[2] == row["sid"]
assert row[3] ==
row["towar"]
assert row[4] ==
row["kolish"]
assert row[5] ==
row["zena"]
print(row[0],row[1],row[2],row[3],row[4],row[5],row[5]*row[4])
Результат работы программы:
запрос данные по поставщикам
postawsh.id,postawsh.name_last,sklad.sid,sklad.towar,sklad.kolish,sklad.zena
1 Степанов Горький 83567889971 автопром
2 Иванов Беларусь 85985559123 продукты
3 Уткин Кострома 75985144678 продукты
4 Спасский 127n 79113598517 радиопром
5 Пущин Ленинград 77359851478 промтовары
запрос данные по складу
sid,
towar,
kolish,
zena,
1 тачка 1 20 4600
2 мясо 2 12000 460
3 рыба 3 20000 300
4 музцентр 4 220 8000
5 дрель 5 420 7000
запрос связь
между таблицами
id, name_last, sid, towar, kolish, zena, summ
1 Степанов 1 тачка 20 4600 92000
2 Иванов 2 мясо 12000 460 5520000
3 Уткин 3 рыба 20000 300 6000000
4 Спасский 4 музцентр 220 8000 1760000
5 Пущин 5 дрель 420 7000 2940000
4.8. Задача БД Треугольник
# Демонстрируем возможности Row только на 1-ю строку
# Демонстрируем возможности Row на несколько строк
# Демонстрируем возможности Row с применением пользовательской функции
Код программы:
# -*- coding:
utf-8
-*-
import sqlite3
# создаем базу для сторон треугольника
conn =
sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("create table geron(id int,name text,
snash int)")
c.execute("insert into geron
values(1,'a',10)")
c.execute("insert into geron
values(2,'b',20)")
c.execute("insert into geron
values(3,'c',25)")
conn.commit()
c.close()
# Демонстрируем возможности Row только на
1-ю строку
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute('select * from geron')
r = c.fetchone()
print ('type(r) = ',type(r))
print ('r = ',r)
print ('len(r) = ',len(r))
print ('r[2] = ',r[2])
print ('r.keys() = ',r.keys())
print ('r[snash] = ',r['snash'])
for i in r:
print( i )
print("Запрос- вывести все данные из
базы")
# Запрос по всем данным
# Демонстрируем возможности Row на всю
базу
c.execute("select * from geron ")
for row in c:
assert row[0] == row["id"]
assert row[1] == row["name"]
assert row[2] == row["snash"]
print(row[0],row[1],row[2])
print('** Расчет полупериметра
треугольника из базы данных **')
import sqlite3
class MySum:
def __init__(polp):
polp.count = 0
def step(polp, value):
polp.count += value
def finalize(polp):
y=(polp.count)/2
return y
con = sqlite3.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.cursor()
cur.execute("create table test(i)")
cur.execute("insert into test(i) values
(35)")
cur.execute("insert into test(i) values
(45)")
cur.execute("insert into test(i) values
(66)")
cur.execute("select mysum(i) from test")
print('polp=
',cur.fetchall())
Результат работы программы:
type(r) = <class 'sqlite3.Row'>
r = <sqlite3.Row object at 0x0235B590>
len(r) = 3
r[2] = 10
r.keys() = ['id', 'name', 'snash']
r[snash] = 10
1
a
10
Запрос- вывести все данные из базы
1 a 10
2 b 20
3 c 25
** Расчет полупериметра треугольника из
базы данных **
polp= [(73.0,)]
>>>
Заключение
Представленный материал : «Практический
курс по базам данных
на языке SQL
в PYTON» должен
оказать помощь учащимся старших классов образовательных школ в освоении языка
баз данных SQL . Следует отметить, что
структура баз данных SQL на PYTHON
более прозрачна, чем на консоле. Код задач очевиден, легко его создавать и
корректировать. Автор желает молодому поколению успехов в учебе и дальнейшему
освоению языков программирования для решения многих задач в различных отраслях
народного хозяйства. Больших творческих успехов.!
Используемые
ссылки в интернет-ресурсе:
1.https://www.severcart.ru/blog/all/python_sqlite3/ -
Учебник по SQLite3 в Python
2.https://sql-academy.org/ru/guide-
Интерактивный учебник по SQL
3.http://john16blog.blogspot.com/2011/03/python-sqlite3.html-
Материал в переводе
4.http://trinosoft.com/index.php?page=sql§ion=3390 -Язык SQL. Базовый курс Бондарев В., Postgres Professional,
2017, 256 c.
5.https://muff.kiev.ua/files/books/sql_rukovodstvo.pdf
Дж.Боуман, С.Эмерсон, М.Дарновски ПРАКТИЧЕСКОЕ РУКОВОДСТВОПО SQL
6. http://chel-center.ru/python-yfc/2020/03/04/vvedenie-v-sql-biblioteki-python/
Введение в SQL-библиотеки Python
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.