Как добавить в базу данных sql python

Как добавить в базу данных sql python

Рассмотрим добавление в базу данных PostgreSQL на примере следующей таблицы:

CREATE TABLE people ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INTEGER)

Добавление данных

Для добавления данных применяется SQL-инструкция INSERT . Для добавления одной строки используем метод execute() :

import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # добавляем строку в таблицу people cursor.execute("INSERT INTO people (name, age) VALUES ('Tom', 38)") # выполняем транзакцию conn.commit() print("Данные добавлены") cursor.close() conn.close()

Здесь добавляется одна строка, где name = «Tom», а age = 38. Перед выполнением команды INSERT открывается транзакция, для завершения которой необходимо вызвать метод commit() текущего объекта Connection.

Установка параметров

С помощью второго параметра в метод execute() можно передать значения для параметров SQL-запроса:

import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # данные для добавления bob = ("Bob", 42) cursor.execute("INSERT INTO people (name, age) VALUES (%s, %s)", bob) conn.commit() print("Данные добавлены") cursor.close() conn.close()

В данном случае добавляемые в БД значения представляют кортеж bob. В SQL-запросе вместо конкретных значений используются знаки подстановки %s . Вместо этих символов при выполнении запроса будут вставляться данные из кортежа data. Так, первый элемент кортежа — строка «Bob» передается на место первого плейсхолдера %s, второй элемент — число 42 передается на место второго плейсхолдера %s. То есть в итоге команды SQL будет выглядеть следующим образом:

INSERT INTO people (name, age) VALUES ('Bob', 42)

Также обратите внимание, что НЕ надо помещать плейсхолдер %s в кавычки — psycopg2 делает это автоматически.

И если мы посмотрим на содержимое базы данных, то найдем там все добавленные объекты:

Множественная вставка

Метод executemany() позволяет вставить набор строк:

import psycopg2 conn = psycopg2.connect(dbname="metanit", user="postgres", password="123456", host="127.0.0.1") cursor = conn.cursor() # данные для добавления people = [("Sam", 28), ("Alice", 33), ("Kate", 25)] cursor.executemany("INSERT INTO people (name, age) VALUES (%s, %s)", people) conn.commit() print("Данные добавлены") cursor.close() conn.close()

В метод cursor.executemany() по сути передается то же самое выражение SQL, только теперь данные определены в виде списка кортежей people. Фактически каждый кортеж в этом списке представляет отдельную строку — данные отдельного пользователя, и при выполнении метода для каждого кортежа будет создаваться свое выражение INSERT INTO

SQLite

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

Для подключения к бд в этой библиотеке определена функция connect() :

sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False)

Она принимает следующие параметры:

• database : путь к файлу базы данных. Если база данных расположена в памяти, а не на диске, то для открытия подключения используется «:memory:»
• timeout : период времени в секундах, через который генерируется исключение, если файл бд занят другим процессом
• detect_types : управляет сопоставлением типов SQLite с типами Python. Значение 0 отключает сопоставление
• isolation_level : устанавливает уровень изоляции подключения и определяет процесс отрытия неявных транзакций. Возможные значения: «DEFERRED» (значение по умолчанию), «EXCLUSIVE», «IMMEDIATE» или None (неявные транзакции отключены)
• check_same_thread : если равно True (значение по умолчанию), то только поток, который создал подключение, может его использовать. Если равно False , подключение может использоваться несколькими потоками.
• factory : класс фабрики, который применяется для создания подключения. Должен представлять класс, производный от Connection . По умолчанию используется класс sqlite3.Connection
• cached_statements : количество SQL-инструкций, которые должны кэшироваться. По умолчанию равно 128.
• uri : булевое значение, если равно True , то путь к базе данных рассматривается как адрес URI

Обязательным параметром функции является путь к базе данных. Результатом функции является объект подключения (объект класса Connection), через затем можно взаимодействовать с базой данных.

Например, подключение к базе данных «metanit.db», которая располагается в той же папке, что и текущий скрипт (если такая база данных отсутствует, то она автоматически создается):

import sqlite3; con = sqlite3.connect("metanit.db")

Сопоставление типов SQLite и Python

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

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

Получение курсора

Для выполнения выражений SQL и получения данных из БД, необходимо создать курсор. Для этого у объекта Connection вызывается метод cursor() . Этот метод возвращает объект Cursor :

import sqlite3; # создаем подключение con = sqlite3.connect("metanit.db") # получаем курсор cursor = con.cursor()

Выполнение запросов к базе данных

Для выполнения запросов и получения данных класс Cursor предоставляет ряд методов:

• execute(sql, parameters=(), /) : выполняет одну SQL-инструкцию. Через второй параметр в код SQL можно передать набор параметров в виде списка или словаря
• executemany(sql, parameters, /) : выполняет параметризованное SQL-инструкцию. Через второй параметр принимает наборы значений, которые передаются в выполняемый код SQL.
• executescript(sql_script, /) : выполняет SQL-скрипт, который может включать множество SQL-инструкций
• fetchone() : возвращает одну строку в виде кортежа из полученного из БД набора строк
• fetchmany(size=cursor.arraysize) : возвращает набор строк в виде списка. количество возвращаемых строк передается через параметр. Если больше строк нет в наборе, то возвращается пустой список.
• fetchall() : возвращает все (оставшиеся) строки в виде списка. При отсутствии строк возвращается пустой список.

Создание таблицы

Для создания таблицы в SQLite применяется инструкция CREATE TABLE . Например, создадим в базе данных «metanit.db» таблицу people:

import sqlite3; con = sqlite3.connect("metanit.db") cursor = con.cursor() # создаем таблицу people cursor.execute("""CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER) """)

В метод cursor.execute() передается инструкция CREATE TABLE , которая создает таблицу people с тремя столбцами. Столбец id представляет идентификатор пользователя, хранит данные типа Integer, то есть число, и также представляет первичный ключ, значение которого будет автоматически генерироваться и инкрементироваться с каждой новой строкой. Второй столбец — name представляет строку — имя пользователя. И третий столбец — age представляет возраст пользователя.

После выполнения скрипта мы можем открыть базу данных в каком-нибудь браузере баз данных SQLite, например, в DB Browser for SQLite и увидеть созданную таблицу

Вставка данных в таблицу SQLite

В этом материале рассматривается, как выполнять операцию Insert в SQLite из Python для добавления новых строк в таблицу:

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

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

Пример вставки строки в таблицу SQLite

Сейчас таблица sqlitedb_developers пустая, и ее нужно заполнить. Для этого необходимо выполнить следующие шаги:

• Установить SQLite-соединение из Python.
• Создать объект Cursor с помощью объекта соединения.
• Создать INSERT-запрос. Для этого нужно знать таблицу и подробности о колонках.
• Выполнить запрос с помощью cursor.execute() .
• После успешного завершения нужно не забыть выполнить коммит изменений в базу данных.
• Также важно не забыть перехватить исключения SQLite.
• Наконец, следует проверить результат, вернув данные из таблицы.

Посмотрим на программу:

 
import sqlite3 
 
try: 
sqlite_connection = sqlite3.connect('sqlite_python.db') 
cursor = sqlite_connection.cursor() 
print("Подключен к SQLite") 
 
sqlite_insert_query = """INSERT INTO sqlitedb_developers 
(id, name, email, joining_date, salary) 
VALUES 
(1, 'Oleg', 'oleg04@gmail.com', '2020-11-29', 8100);""" 
count = cursor.execute(sqlite_insert_query) 
sqlite_connection.commit() 
print("Запись успешно вставлена ​​в таблицу sqlitedb_developers ", cursor.rowcount) 
cursor.close() 
 
except sqlite3.Error as error: 
print("Ошибка при работе с SQLite", error) 
finally: 
if sqlite_connection: 
sqlite_connection.close() 
print("Соединение с SQLite закрыто") 

Подключен к SQLite Запись успешно вставлена ​​в таблицу sqlitedb_developers 1 Соединение с SQLite закрыто
Теперь можно проверить результат, посмотрев таблицу через программу DB Browser.







Разбор кода

import sqlite3 :

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


sqlite3.connect() и connection.cursor() :

С помощью метода sqlite3.connect() устанавливается соединение с базой данных SQLite из Python.
Дальше используется connection.cursor() для получения объекта cursor из объекта соединения.


После этого готовится INSERT-запрос для вставки данных в таблицу. В нем указываются названия колонок и их значения. Всего в таблице 5 колонок.
cursor.execute() :

С помощью метода execute() объекта сursor выполняется запрос INSERT.
Чтобы сохранить изменения в базе, нужно использовать connection.commit() .
А с помощью cursor.rowcount можно узнать количество отредактированных строк.



И в блоке finally после завершения операции закрываются объекты cursor и соединение.
Использование переменных в запросе INSERT
Иногда в колонку таблицы нужно вставить значение переменной Python. Этой переменной может быть что угодно: целое число, строка, число с плавающей точкой, datetime . Например, при регистрации пользователь вводит свои данные. Их и можно взять вставить в таблицу SQLite.
Для этого есть запрос с параметрами. Он позволяет использовать переменные Python на месте заполнителей (?) в запросе. Пример:






 
import sqlite3 
 
def insert_varible_into_table(dev_id, name, email, join_date, salary): 
try: 
sqlite_connection = sqlite3.connect('sqlite_python.db') 
cursor = sqlite_connection.cursor() 
print("Подключен к SQLite") 
 
sqlite_insert_with_param = """INSERT INTO sqlitedb_developers 
(id, name, email, joining_date, salary) 
VALUES (?, ?, ?, ?, ?);""" 
 
data_tuple = (dev_id, name, email, join_date, salary) 
cursor.execute(sqlite_insert_with_param, data_tuple) 
sqlite_connection.commit() 
print("Переменные Python успешно вставлены в таблицу sqlitedb_developers") 
 
cursor.close() 
 
except sqlite3.Error as error: 
print("Ошибка при работе с SQLite", error) 
finally: 
if sqlite_connection: 
sqlite_connection.close() 
print("Соединение с SQLite закрыто") 
 
insert_varible_into_table(2, 'Viktoria', 's_dom34@gmail.com', '2020-11-19', 6000) 
insert_varible_into_table(3, 'Valentin', 'exp3@gmail.com', '2020-11-23', 6500) 

Вывод: таблица sqlitedb_developers после вставки переменной Python в качестве значения колонки.
Подключен к SQLite Переменные Python успешно вставлены в таблицу sqlitedb_developers Соединение с SQLite закрыто Подключен к SQLite Переменные Python успешно вставлены в таблицу sqlitedb_developers Соединение с SQLite закрыто
Проверить результат можно, получив данные из таблицы.






Вставка нескольких строк с помощью executemany()
В предыдущем примере для вставки одной записи в таблицу использовался метод execute() объекта cursor , но иногда требуется вставить несколько строчек.
Например, при чтении файла, например, CSV, может потребоваться добавить все записи из него в таблицу SQLite. Вместе выполнения запроса INSERT для каждой записи, можно выполнить все операции в один запрос. Добавить несколько записей в таблицу SQLite можно с помощью метода executemany() объекта cursor .
Этот метод принимает два аргумента: запрос SQL и список записей.
 
import sqlite3 
 
def insert_multiple_records(records): 
try: 
sqlite_connection = sqlite3.connect('sqlite_python.db') 
cursor = sqlite_connection.cursor() 
print("Подключен к SQLite") 
 
sqlite_insert_query = """INSERT INTO sqlitedb_developers 
(id, name, email, joining_date, salary) 
VALUES (?, ?, ?, ?, ?);""" 
 
cursor.executemany(sqlite_insert_query, records) 
sqlite_connection.commit() 
print("Записи успешно вставлены в таблицу sqlitedb_developers", cursor.rowcount) 
sqlite_connection.commit() 
cursor.close() 
 
except sqlite3.Error as error: 
print("Ошибка при работе с SQLite", error) 
finally: 
if sqlite_connection: 
sqlite_connection.close() 
print("Соединение с SQLite закрыто") 
 
records_to_insert = [(4, 'Jaroslav', 'idebylos@gmail.com', '2020-11-14', 8500), 
(5, 'Timofei', 'ullegyddomm@gmail.com', '2020-11-15',6600), 
(6, 'Nikita', 'aqillysso@gmail.com', '2020-11-27', 7400)] 
 
insert_multiple_records(records_to_insert) 

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






Подключен к SQLite Записи успешно вставлены в таблицу sqlitedb_developers 3 Соединение с SQLite закрыто

Разберем последний пример:

После подключения к базе данных подготавливается список записей для вставки в таблицу. Каждая из них — это всего лишь строка.
Инструкция SQLite INSERT содержит запрос с параметрами, где на месте каждого значения стоит вопросительный знак.
Дальше с помощью cursor.executemany(sqlite_insert_query, recordList) в таблицу вставляются несколько записей.
Чтобы узнать количество вставленных строк используется метод cursor.rowcount . Наконец, нужно не забыть сохранить изменения в базе.

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







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


Что будем делать
Сегодня мы сделаем то же самое, что и в SQL-запросах, но на Python, используя стандартную библиотеку sqlite3:

создадим базу и таблицы в ней;
наполним их данными;
создадим связи;
проверим, как это работает.

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






Подключаем и создаём базу данных
За работу с SQLite в Python отвечает стандартная библиотека sqlite3:
# подключаем SQLite 
import sqlite3 as sl
Теперь нам нужно указать файл базы данных, с которым мы будем дальше работать. Удобство библиотеки в том, что нам достаточно указать имя файла, а дальше будет такое:

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

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






# открываем файл с базой данных 
con = sl.connect('thecode.db')
Мы указали, что файл называется thecode.db, без указания папок и дисков. Это значит, что файл с базой появится в той же папке, что и наш скрипт — можно в этом убедиться после запуска программы.
Создаём таблицу с товарами
У нас есть база, в которой можно создавать таблицы для хранения данных. Создадим первую таблицу для товаров:
with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)
Если посмотреть внимательно на код, можно заметить, что текст внутри кавычек полностью повторяет обычный SQL-запрос, который мы уже использовали в прошлой статье. Единственное отличие — в SQLite используется INTEGER вместо INT:






CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INT, price INT );
Теперь соберём код вместе и запустим его ещё раз:
# подключаем SQLite import sqlite3 as sl # открываем файл с базой данных con = sl.connect('thecode.db') # создаём таблицу для товаров with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)
Но после второго запуска компьютер почему-то выдаёт ошибку:
❌ sqlite3.OperationalError: table goods already exists
Дело в том, что при повторном запуске программа пытается создать таблицу с товарами, которая уже есть в базе. Так как имена таблиц совпадают, а двух одинаковых имён быть не может, отсюда и возникает ошибка.






Чтобы не попадать в такую ситуацию, добавим проверку: посмотрим, есть ли в базе нужная нам таблица или нет. Если нет — создаём, если есть — двигаемся дальше:
# открываем базу with con: # получаем количество таблиц с нужным нам именем data = con.execute("select count(*) from sqlite_master where type='table' and name='goods'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для товаров with con: con.execute(""" CREATE TABLE goods ( product VARCHAR(20) PRIMARY KEY, count INTEGER, price INTEGER ); """)
Точно так же мы потом сделаем и с остальными таблицами — сразу встроим проверку, и если нужных таблиц не будет, то программа создаст их автоматически.
Теперь наполняем нашу таблицу товарами, используя стандартный SQL-запрос. Например, можно добавить два стола, которые стоят по 3000 ₽:






INSERT INTO goods SET 
product = 'стол', 
count = 2, 
price = 3000;
Но добавлять записи по одному товару за раз — это долго и неэффективно. Проще сразу в одном запросе добавить все нужные товары: стол, стул и табурет:
# подготавливаем множественный запрос sql = 'INSERT INTO goods (product, count, price) values(?, ?, ?)' # указываем данные для запроса data = [ ('стол', 2, 3000), ('стул', 5, 1000), ('табурет', 1, 500) ] # добавляем с помощью множественного запроса все данные сразу with con: con.executemany(sql, data) # выводим содержимое таблицы на экран with con: data = con.execute("SELECT * FROM goods") for row in data: print(row)
В конце мы добавили вывод таблицы — так можно убедиться, что запрос сработал и данные отправились в базу в нужное место.
 
Создаём и заполняем таблицу с товарами
Заведём таблицу clients для клиентов и заполним её точно так же, как мы это сделали с клиентской таблицей. Для этого просто копируем предыдущий код, меняем название таблицы и указываем правильные названия полей.Ещё посмотрите на отличие от обычного SQL в последней строке объявления полей таблицы: вместо id INT AUTO_INCREMENT PRIMARY KEY надо указать id INTEGER PRIMARY KEY . Без этого не будет работать автоувеличение счётчика.
# --- создаём таблицу с клиентами --- # открываем базу with con: # получаем количество таблиц с нужным нам именем — clients data = con.execute("select count(*) from sqlite_master where type='table' and name='clients'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для клиентов with con: con.execute(""" CREATE TABLE clients ( name VARCHAR(40), phone VARCHAR(10) UNIQUE, id INTEGER PRIMARY KEY ); """) # подготавливаем множественный запрос sql = 'INSERT INTO clients (name, phone) values(?, ?)' # указываем данные для запроса data = [ ('Миша', 9208381096), ('Наташа', 9307265198), ('Саша', 9307281096) ] # добавляем с помощью множественного запроса все данные сразу with con: con.executemany(sql, data) # выводим содержимое таблицы с клиентами на экран with con: data = con.execute("SELECT * FROM clients") for row in data: print(row)
Cоздаём таблицу с покупками и связываем всё вместе
У нас всё готово для того, чтобы на основе первых двух таблиц создать третью — в ней будут данные сразу и о покупках, и о том, кто это купил. Если интересно, как это работает в деталях, — почитайте статью про связи в базе данных.
# --- создаём таблицу с покупками --- # открываем базу with con: # получаем количество таблиц с нужным нам именем — orders data = con.execute("select count(*) from sqlite_master where type='table' and name='orders'") for row in data: # если таких таблиц нет if row[0] == 0: # создаём таблицу для покупок with con: con.execute(""" CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, product VARCHAR, amount INTEGER, client_id INTEGER, FOREIGN KEY (product) REFERENCES goods(product), FOREIGN KEY (client_id) REFERENCES clients(id) ); """)
Проверим, что связь работает: добавим в таблицу с заказами запись о том, что Миша купил 2 табурета:
# подготавливаем запрос sql = 'INSERT INTO orders (product, amount, client_id) values(?, ?, ?)' # указываем данные для запроса data = [ ('табурет', 2, 1) ] # добавляем запись в таблицу with con: con.executemany(sql, data) # выводим содержимое таблицы с покупками на экран with con: data = con.execute("SELECT * FROM orders") for row in data: print(row)
Компьютер выдал строку (1, 'табурет', 2, 1), значит, таблицы связались правильно.
Что дальше
Теперь, когда мы знаем, как работать с SQLite в Python, можно использовать эту базу данных в более серьёзных проектах:

хранить результаты парсинга;
запоминать отсортированные датасеты;
вести учёт пользователей и их действий в системе.

Подпишитесь, чтобы не пропустить продолжение про SQLite. А если вам интересна аналитика и работа с данными, приходите на курс «SQL для работы с данными и аналитики».
Данные — это новая нефть
А аналитики данных — новая элита ИТ. Эти люди помогают понять огромные массивы данных и принять правильные решения. Изучите профессию аналитика и начните карьеру в ИТ: старт — бесплатно, а после обучения — помощь с трудоустройством.
   
Получите ИТ-профессию
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.


Похожие публикации:

Где находится предохранитель бензонасоса chevrolet lanos
Как переименовать файл в java
Сколько стоит acura nsx
Что делать если забыл пароль от apple id на айфоне