path: root/SQL/SQLite.md

```python
# Создание таблицы
with con:
    con.execute("""
        CREATE TABLE USER (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            age INTEGER
        );
    """)
    
# Вставка записей в таблицу
sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
data = [
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Chris', 23)
]

# Выполнение запросов к базе данных
with con:
    data = con.execute("SELECT * FROM USER WHERE age <= 22")
    for row in data:
        print(row)
```
### Интеграция с pandas
```python
# Объявим датафрейм:  
df_skill = pd.DataFrame({
    'user_id': [1,1,2,2,3,3,3],
    'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning']
})

# Сохранение датафрейма в БД
df_skill.to_sql('SKILL', con)
```
Вот и всё! Нам даже не нужно заранее создавать таблицу. Типы данных и характеристики полей будут настроены автоматически, на основании характеристик датафрейма. Конечно, вы, если надо, можете настроить всё самостоятельно.  
  
Теперь, предположим, нам нужно получить объединение таблиц `USER` и `SKILL` и записать полученные данные в датафрейм pandas. Это тоже очень просто:  
```python
df = pd.read_sql('''
    SELECT s.user_id, u.name, u.age, s.skill 
    FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id
''', con)
```
Здесь нам нужно определить SQL-выражение со знаками вопроса (`?`) в виде местозаполнителей. Учитывая то, что в нашем распоряжении есть объект подключения к базе данных, мы, подготовив выражение и данные, можем вставить записи в таблицу:  
```python
with con:
    con.executemany(sql, data)
```
Сообщений об ошибках после выполнения этого кода не поступает, а это значит, что данные успешно добавлены в таблицу.

Замечательно! А теперь давайте запишем то, что у нас получилось, в новую таблицу с именем `USER_SKILL`:  
```python
df.to_sql('USER_SKILL', con)
```
#### Чтение данных SQLite с помощью Pandas 

С pandas управление базой данных SQLite превращается в увлекательный процесс. Она предоставляет функцию `read_sql`, позволяющую напрямую выполнять инструкции SQL, не заботясь о внутренней инфраструктуре. 

```python
import pandas as pd
df = pd.read_sql("select * from transcript", con)
df    name  grade0   Mike     951   Jane     922  Bella     98
```
#### Обратная запись DataFrame в SQLite

После обработки данных с помощью pandas самое время осуществить обратную запись DataFrame в БД SQLite для долгосрочного хранения. На этот случай в pandas есть метод `to_sql`. Обратимся к соответствующему примеру: 

```python
df['gpa'] = [4.0, 3.8, 3.9]
df.to_sql("transcript", con, if_exists="replace", index=False)
list(cur.execute("select * from transcript order by grade desc"))[('Bella', 98, 3.9), ('Mike', 95, 4.0), ('Jane', 92, 3.8)]
```

- В отличие от ==`read_sql`==, функции из библиотеки pandas, ==`to_sql`== является методом класса ==`DataFrame`==, вследствие чего он непосредственно вызывается объектом ==`DataFrame`==. 
- В методе ==`to_sql`== указывается таблица, в которую сохраняется ==`DataFrame`==. 
- Отметим важность параметра ==`if_exists`==, так как по умолчанию ему задается значение ==`“fail”`==. Это значит, что если таблица уже существует, вы не сможете записать в нее текущий `DataFrame`, поскольку будет вызвана ошибка ==`ValueError`==. В рассматриваемом примере требуется заменить существующую таблицу из-за изменения средних баллов оценок, поэтому параметру ==`if_exisits`== устанавливается значение ==`“replace”`==.  
- В результате установки ==`index=False`== индекс объекта ==`DataFrame`== просто игнорируется при сохранении в таблицу. По своему принципу данное действие аналогично методу ==`to_csv`==, с которым вы наверняка знакомы лучше.


#############################################
```bash
import sqlite3
```
### Подключение к базе данных (или создание новой)
```python
import sqlite3

try:
    with sqlite3.connect('file:sensors.db?mode=rw', uri=True) as conn:
        print('sqlite3.db is OK !')
except:
    with sqlite3.connect('sensors.db') as conn:
        cursor = conn.cursor()
        query = """CREATE TABLE sensors (
                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                sens_loc TEXT(32) NOT NULL,
                sens_name TEXT(32) NOT NULL,
                sens_date TEXT(10) NOT NULL,
                sens_time TEXT(8) NOT NULL,
                sens_temp TEXT(8) NOT NULL,
                sens_pres TEXT(8) NOT NULL,
                sens_hum TEXT(3) NOT NULL,
                sens_vcc TEXT(8) NOT NULL,
                sens_other TEXT(255)
                )"""
        cursor.execute(query)        
        conn.commit()
        print('sqlite3.db is NOT !')


with sqlite3.connect('example.db') as db
    cursor = db.cursor()
    query = """ CREATE TABLE IF NOT EXISTS expenses(id INTEGER, name TEXT)"""
    cursor.execute(query)
```


```bash
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Создание таблицы
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
''')

# Вставка данных
cursor.execute('''
INSERT INTO users (name, age) VALUES (?, ?)
''', ('Alice', 30))

# Сохранение изменений
conn.commit()

# Получение данных
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Закрытие соединения
conn.close()
```
Этот скрипт создает базу данных SQLite, создает таблицу users, добавляет в неё запись и выводит все строки таблицы. Команда cursor.execute используется для выполнения SQL-запросов, а conn.commit сохраняет изменения.
### Обновление возраста пользователя с именем Alice
```python
cursor.execute('''
UPDATE users SET age = ? WHERE name = ?
''', (31, 'Alice'))
conn.commit()
```
В этом примере возраст пользователя с именем Alice обновляется до 31 года. После выполнения запроса изменения сохраняются с помощью conn.commit.
### Удаление пользователя с именем Alice ###
```python
cursor.execute('''
DELETE FROM users WHERE name = ?
''', ('Alice',))
conn.commit()
```
Этот пример показывает, как удалить запись из таблицы по условию. В данном случае удаляется пользователь с именем Alice.
### Работа с параметризованными запросами: ###
Для защиты от SQL-инъекций рекомендуется использовать параметризованные запросы.
```bash
name = 'Bob'
cursor.execute('SELECT * FROM users WHERE name = ?', (name,))
rows = cursor.fetchall()
```
Использование параметризованных запросов помогает избежать уязвимостей, связанных с SQL-инъекциями, передавая параметры запроса через кортеж или список.

========================================
```python
# извлечь один/три/все столбец
SELECT prod_name FROM Products
SELECT prod_id, prod_name, prod price FROM Products
SELECT * FROM Products;

# извлечь уникальные строки
SELECT DISTINCT vend_id 
FROM Products;

# извлечь 5 строк начиная с 2ой
SELECT prod_name
FROM Products 
LIMIT 5 OFFSET 2;

# комментарии
# этокомментарий
/*
	это комментарий
*/
SELECT DISTINCT vend_id   -- это комментарий
FROM Products;

# сортировка 1 или нескольким выбранным/невыбранным столбцам
SELECT prod_name 
FROM Products
ORDER BY prod_name, prod_price; (DESC) -- сортировка наоборот

# можно сортировать по положению столбца (только выбранному)
SELECT prod_name 
FROM Products
ORDER BY 2, 3; (DESC) -- сортировка наоборот

# критерий отбора
SELECT DISTINCT vend_id
FROM Products
WHERE prod_price = 3.49;

/*
	если оба: WHERE и ( ORDER BY - должно быть последним )
 */

# сравнение с диапазоном значений
SELECT DISTINCT vend_id
FROM Products
WHERE prod_price = 3.49 BETWEEN 5 AND 10
( WHERE prod_price IS NULL ) --проверка на NULL

# фильтр по нескольким столбцам (+ можно объединять и много + скобки)
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4  -- И то И то
( WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'  -- ИЛИ то ИЛИ то )

# сравнение
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01') -- это лучше!
( WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ) -- то же самое
ORDER BY prod_name;

# выбор все КРОМЕ
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

# поиск с использованием МЕТАСИМВОЛОВ (только текстовые поля) и любое кол-во
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%'; -- %
( WHERE email LIKE 'b%@forta.com'; ) -- % может стоять в любом месте
WHERE prod_name LIKE '__ inch teddy bear%'; -- __(2) % (_один символ)

# конкатенация (+) или (||)
SELECT vend_name || ' ()' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

# убрать все пробелы справа
SELECT vend_name || ' ()' || 
RTRIM(vend_country) || ')'
FROM Vendors
ORDER BY vend_name; 

# псевдонимы (переименовать столбец (если недопустимые символы, или длинное))
SELECT RTRIM(vend_name) || ' ()' || 
	   RTRIM(vend_country) || ')'
	   AS vend_title   -- псевдоним
FROM Vendors
ORDER BY vend_name;

# арифметические операции
SELECT prod_id, 
	   quantity, 
	   item_price
	   quantity*item_price AS expanded_price -- нов колонка (*)
FROM OrderItems
WHERE order_nam = 20008;

# функции обработки данных
# DATE()
SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = 2020; -- извлекается только год
-- добавить AND для сравнения месяца и года

# ИТОГОВЫЕ ФУНКЦИИ
# AVG()
SELECT AVG(prod_price) AS avg_price -- строки NULL - игнорируются
FROM Products
WHERE vend_id = 'DLL01'; 

SELECT AVG(DISTINCT prod_price) AS avg_price -- только уникальные цены
FROM Products
WHERE vend_id = 'DLL01';

# COUNT()
SELECT COUNT(*) AS num_cust -- подсчет всех строк независимо от значений
FROM Customers;

SELECT COUNT(cust_email) AS num_cust -- подсчет только где есть email
FROM Customers;

# MAX()
# MIN()

# SUM() - строки с NULL - игнорируются !
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_item = 20005;

# комбинирование итоговых функций
SELECT COUNT(*) AS num_items,
	   MIN(prod_price) AS price_min,
	   MAX(prod_price) AS price_max,
	   AVG(prod_price) AS price_avg
FROM Products;

# СОЗДАНИЕ ГРУПП
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
# WHERE -> GROUP BY -> ORDER BY -- только такой порядок !

# HAVING - фильтр группы. (WHERE - фильтр строк)
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT (*) >= 2;

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT (*) >= 3
ORDER BY items, order_num;

# ПОРЯДОК СЛЕДОВАНИЯ: ###################################
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
#########################################################

# ФИЛЬТРАЦИЯ с помощью ПОДЗАПРОСОВ :
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
				  FROM Orders
				  WHERE order_num IN (SELECT order_num
								      FROM OrderItems
								      WHERE prod_id = 'RGAN01'));

# SELECT в подзапросах могут возвращать только один столбец !!!

# СОЗДАНИЕ СОЕДИНЕНИЯ:
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products -- ( Vendors INNER JOIN Products )
WHERE Vendors.vend_id = Products.vend_id;

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
					 ON Vendors.vend_id = Products.vend_id;

SELECT vend_name, prod_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01'));

# Псевдонимы ТАБЛИЦ - только во время выполнения запроса
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'));

# САМО_СОЕДИНЕНИЯ
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
  AND c2.cust_contact = 'Jim Jones';

# ЕСТЕСТВЕННЫЕ СОЕДИНЕНИЯ
SELECT C.*, O.order_num, O.order_date,
	   OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
  AND OI.order_num = O.order_num
  AND prod_id = 'RGAN01';

# ВНЕШНИЕ СОЕДИНЕНИЯ
SELECT Customers.cust_id, Orders.order_num
FROM Customers
INNER JOIN Orders -- LEFT OUTER JOIN
		ON Customers.cust_id = Orders.cust_id;

# ИТОГОВЫЕ СОЕДИНЕНИЯ
SELECT Customers.cust_id, 
	COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders -- LEFT OUTER JOIN
		ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

# КОМБИНИРОВАННЫЕ ЗАПРОСЫ 
# -- UNION - повторяющиеся строки - удаляются!!! UNION ALL - НЕТ !!!
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

# тот же самый запрос:
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
				 OR cust_name = 'Fun4All';
		
# сортировка результатов КОМБИНИРОВАННЫХ ЗАПРОСОВ
SELECT cust_name, cust_contact, cust_email
FROM Customers
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact; -- сортировка ОБОИХ инструкций !!!

#=================================================
# ДОБАВЛЕНИЕ ДАННЫХ -- INSERT
INSERT INTO Customers
VALUES(100000006,
	  'Toy Land',
	  '123 Any Street',
	  'New York',
	  'NY',
	  '11111',
	  'USA',
	  'NULL',
	  'NULL');

# то же самое только более правильно:
# столбцы можно пропускать если он:
# по умолчанию = NULL, или = какое-то значение по умолчанию
# добавляется только ОДНА ! строка:
INSERT INTO Customers(cust_id,
					 cust_name,
					 cust_address,
					 cust_city,
					 cust_state,
					 cust_zip,
					 cust_country,
					 cust_contact,
					 cust_email)
VALUES(100000006,
	  'Toy Land',
	  '123 Any Street',
	  'New York',
	  'NY',
	  '11111',
	  'USA',
	  'NULL',
	  'NULL');

# можно брать значения из другой таблицы с помощью SELECT
# учитывается только положение столбцов инструкции INSERT, 
# а SELECT - нет !
# добавляются ВСЕ! строки из SELECT:
INSERT INTO Customers(cust_id,
					 cust_name,
					 cust_address,
					 cust_city,
					 cust_state,
					 cust_zip,
					 cust_country,
					 cust_contact,
					 cust_email)
SELECT  cust_id,
		cust_name,
		cust_address,
		cust_city,
		cust_state,
		cust_zip,
		cust_country,
		cust_contact,
		cust_email
FROM CustNew;

# ОБНОВЛЕНИЕ ЗАПИСЕЙ в БД
UPDATE Customers
SET cust_email = 'kim@thetoystore.com',
	cust_contact = 'Sam Roberts'
WHERE cust_id = 1000000005;

# удаление значение из столбца
UPDATE Customers
SET cust_email = NULL -- отсутствие хоть какого-то значения
WHERE cust_id = 1000000005;

# УДАЛЕНИЕ ЗАПИСЕЙ (СТРОК полностью, не удаляет таблицу)
DELETE FROM Customers
WHERE cust_id = 1000000005; -- если WHERE нет - удаление ВСЕХ! строк таблицы

# СОЗДАНИЕ ТАБЛИЦ (значение NULL - по умолчанию)
CREATE TABLE Products
(
 prod_id  CHAR(10)   NOT NULL PRIMARY KEY,
 vend_id  CHAR(10)   NOT NULL,
 prod_name  CHAR(254)   NOT NULL,
 prod_price  DECIMAL(8, 2)   NOT NULL,
 prod_desk  VARCHAR(1000)   NULL
);

CREATE TABLE Orders
(
 order_num  INTEGER   NOT NULL,
 order_date DATETIME NOT NULL DEFAULT date('now'),
 cust_id  CHAR(10)   NOT NULL  DEFAULT 1
);

# ОБНОВЛЕНИЕ ТАБЛИЦ
# добавление столбца:
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

# удаление столбца:
ALTER TABLE Vendors
DROP COLUMN vend_phone;

# удаление таблицы (безвозвратно !!!):
DROP TABLE CustCopy;

# ПРЕДСТАВЛЕНИЯ (ВИРТУАЛЬНЫЕ ТАБЛИЦЫ - запросы вместо данных)
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
	AND OrderItems.order_num = Orders.order_num;

SELECT * FROM ProductCustomers; -- вернет список всех клиентов сделавших заказы
WHERE prod_id = 'RGAN01'; -- все клиенты заказавшие товар 'RGAN01'

.............

# ТРАНЗАКЦИИ - единый набор SQL запросов (механизм для управления наборами SQL запросов, которые должны быть выполнены ТОЛЬКО целиком)
BEGIN TRANSACTION
......
COMMIT TRANSACTION
......
ROLLBACK;

# КУРСОРЫ (шаги)

```