Select from insert into sql: SQL INSERT INTO SELECT Statement

SQL Insert Tutorial — Вставка записей в базу данных

Научитесь вставлять данные в базы данных SQL, как профессионал!

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

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

Несмотря на то, что изучение SQL существует уже несколько десятилетий, оно по-прежнему является важным навыком для современных специалистов по данным и вообще для всех, кто работает с данными, поскольку SQL используется во всех видах программного обеспечения реляционных баз данных, включая MySQL, SQL Server, Oracle и PostgreSQL.

В этом руководстве мы подробно узнаем об операциях вставки SQL. Вот список тем, которые мы изучим в этом руководстве:

• Вставка SQL
• Вставка записей в базу данных
• Вставка фреймов данных Pandas в базу данных с помощью команды вставки
• Вставка фреймов данных Pandas в базу данных с помощью команды to_sql()
• Чтение записей из базы данных
• Обновление записей в базе данных

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

Вставка SQL

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

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

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

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

Вставка записей в базу данных

В SQL мы используем команду INSERT для добавления записей/строк в данные таблицы. Эта команда не изменит фактическую структуру таблицы, в которую мы вставляем, она просто добавит данные.

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

Теперь давайте представим, что у нас есть новые сотрудники, которых нужно ввести в систему.

Эта таблица сотрудников может быть создана с использованием CREATE TABLE , поэтому мы могли бы использовать эту команду для создания совершенно новой таблицы. Но было бы очень неэффективно создавать совершенно новую таблицу каждый раз, когда мы хотим добавить данные! Вместо этого давайте воспользуемся командой INSERT , чтобы добавить новые данные в нашу существующую таблицу.

Вот основной синтаксис для использования INSERT в SQL:

Мы начинаем с команды INSERT INTO , за которой следует имя таблицы, в которую мы хотим вставить данные. После имени таблицы мы перечисляем столбцы новых данных, которые мы вставляем, столбец за столбцом, в круглых скобках. Затем в следующей строке мы использовали команду ЗНАЧЕНИЯ вместе со значениями, которые мы хотим вставить (по порядку в круглых скобках.

Итак, для нашей таблицы сотрудника , если бы мы добавляли нового сотрудника по имени Кабир, наша команда INSERT могла бы выглядеть так:

Вставка Записи в базу данных из Python

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

Мы можем отправлять и получать данные в базу данных MySQL, установив соединение между Python и MySQL. Существуют различные способы установить эту связь; здесь мы будем использовать pymysql для подключения к базе данных.

Вот общие шаги, которые нам нужно выполнить, чтобы подключить pymysql , вставить наши данные, а затем извлечь данные из MySQL:

Давайте пройдем этот процесс шаг за шагом.

Шаг 1: Импортируйте модуль pymysql.

 # Импорт модуля pymysql
import pymysql 

Шаг 2: Создайте соединение с базой данных MySQL

Создайте соединение, используя функцию pymysql connect() с параметрами хост, пользователь, имя базы данных и пароль.

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

 # Подключиться к базе данных
соединение = pymysql.connect (хост = 'localhost',
                             пользователь = 'корень',
                             пароль='12345',
                             БД='сотрудник') 

Шаг 3: Создайте курсор с помощью функции cursor().

Это позволит нам выполнить SQL-запрос после его написания.

 cursor = connection.cursor() 

Шаг 4: Выполните требуемый запрос SQL

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

Затем мы зафиксируем эти изменения с помощью commit() .

 # Создать новую запись
sql = "ВСТАВИТЬ В  сотрудника  ( EmployeeID ,  Ename ,  DeptID ,  Зарплата ,  Dname ,  Dlocation , %s, %s, VALUES (%s, %s, VALUES (%s, %s, VALUES) %s)"
# Выполнить запрос
курсор.execute(sql, (1008, 'Кабир', 2,5000, 'IT', 'Нью-Дели'))
# по умолчанию соединение не фиксируется автоматически. Поэтому мы должны зафиксировать сохранение наших изменений.
соединение.коммит() 

Давайте быстро проверим, действительно ли запись, которую мы хотели вставить, была вставлена.

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

 # Создайте новый запрос, который выбирает все содержимое  сотрудника 
sql = "ВЫБЕРИТЕ * ИЗ  сотрудника "
курсор.execute(sql)
# Получить все записи и использовать цикл for для вывода их по одной строке за раз
результат = курсор. fetchall()
для я в результате:
    распечатать (я) 

 (1001, «Джон», 2, 4000, «ИТ», «Нью-Дели»)
(1002, «Анна», 1, 3500, «HR», «Мумбаи»)
(1003, «Джеймс», 1, 2500, «HR», «Мумбаи»)
(1004, «Дэвид», 2, 5000, «ИТ», «Нью-Дели»)
(1005, «Марк», 2, 3000, «ИТ», «Нью-Дели»)
(1006, «Стив», 3, 4500, «Финансы», «Мумбаи»)
(1007, «Алиса», 3, 3500, «Финансы», «Мумбаи»)
(1008, "Кабир", 2, 5000, "IT", "Нью-Дели") 

Сработало! Выше мы видим, что новая запись была вставлена ​​и теперь является последней строкой в ​​нашей базе данных MySQL.

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

Теперь, когда мы закончили, мы должны закрыть соединение с базой данных, используя метод close() .

 # Закрыть соединение
connection.close() 

Конечно, было бы лучше написать этот код так, чтобы он лучше обрабатывал исключения и ошибки. Мы можем сделать это, используя try , чтобы содержать тело нашего кода и за исключением вывода ошибок, если таковые возникнут. Затем мы можем использовать finally , чтобы закрыть соединение, как только мы закончим, независимо от того, попытка удалась или не удалась.

Вот как все это выглядит вместе:

 import pymysql
пытаться:
    # Подключиться к базе данных
    соединение = pymysql.connect (хост = 'localhost',
                             пользователь = 'корень',
                             пароль='12345',
                             БД='сотрудник')
    курсор=соединение.курсор()
    # Создать новую запись
    sql = "ВСТАВИТЬ В  сотрудника  ( EmployeeID ,  Ename ,  DeptID  ,  Зарплата  ,  Имя  ,  Местонахождение  ) ЗНАЧЕНИЯ (%s, %s, %s, %s, %s, %s)"
    cursor.execute (sql, (1009, 'Морган', 1,4000, 'HR', 'Мумбаи'))
    # соединение не является автоматическим по умолчанию. Поэтому мы должны зафиксировать сохранение наших изменений.
    соединение.коммит()
    # Выполнить запрос
    sql = "ВЫБЕРИТЕ * ИЗ  сотрудника "
    курсор. execute(sql)
    # Получить все записи
    результат = курсор.fetchall()
    для я в результате:
        печать (я)
кроме ошибки как e:
    печать (е)
окончательно:
    # закрыть соединение с базой данных с помощью метода close().
    соединение.закрыть() 

 ((1001, 'Джон', 2, 4000, 'IT', 'Нью-Дели'), (1002, 'Анна', 1, 3500, 'HR', 'Мумбаи'), (1003, 'Джеймс' , 1, 2500, 'HR', 'Мумбаи'), (1004, 'Дэвид', 2, 5000, 'IT', 'Нью-Дели'), (1005, 'Марк', 2, 3000, 'IT', «Нью-Дели»), (1006, «Стив», 3, 4500, «Финансы», «Мумбаи»), (1007, «Алиса», 3, 3500, «Финансы», «Мумбаи»), (1008, ' Кабир», 2, 5000, «ИТ», «Нью-Дели»), (1009, «Морган», 1, 4000, «HR», «Мумбаи»), (1009, «Морган», 1, 4000, «HR ', 'Мумбаи')) 

Вставка кадров данных Pandas в базы данных с помощью INSERT

При работе с данными в Python мы часто используем pandas , и наши данные часто хранятся в виде pandas DataFrame. К счастью, нам не нужно делать никаких преобразований, если мы хотим использовать SQL с нашими кадрами данных; мы можем напрямую вставить кадр данных pandas в базу данных MySQL, используя INSERT .

И снова пошагово.

Шаг 1: Создать DataFrame с помощью словаря

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

 # Импорт панд
импортировать панд как pd
# Создать фрейм данных
данные = pd.DataFrame({
    'книга_id': [12345, 12346, 12347],
    'title':['Программирование на Python', 'Изучаем MySQL', 'Поваренная книга по науке о данных'],
    'цена': [29, 23, 27]
})
данные 

Шаг 2: Создайте таблицу в нашей базе данных MySQL

Прежде чем вставлять данные в MySQL, мы собираемся создать таблицу book в MySQL для хранения наших данных. Если бы такая таблица уже существовала, мы могли бы пропустить этот шаг.

Мы будем использовать оператор CREATE TABLE, чтобы создать нашу таблицу, за которым следует имя нашей таблицы (в данном случае book_details ), а затем перечислите каждый столбец и соответствующий ему тип данных.

Шаг 3: Создание подключения к базе данных

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

 импорт pymysql
# Подключиться к базе данных
соединение = pymysql.connect (хост = 'localhost',
                         пользователь = 'корень',
                         пароль='12345',
                         БД='книга')
# создать курсор
курсор=соединение.курсор() 

Шаг 4: Создайте список столбцов и вставьте строки

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

(Также можно вставить весь DataFrame сразу, и мы рассмотрим способ сделать это в следующем разделе, но сначала давайте посмотрим, как это сделать построчно).

 # создание списка столбцов для вставки
столбцы = " ,  ".join([str(i) для i в data.columns.tolist()])
# Вставляем записи DataFrame одну за другой.
для i, строка в data.iterrows():
    sql = "ВСТАВИТЬ В  book_details  (  " +cols + "  ) VALUES (" + "%s,"*(len(row)-1) + "%s)"
    курсор.execute (sql, кортеж (строка))
    # соединение не фиксируется автоматически по умолчанию, поэтому мы должны зафиксировать, чтобы сохранить наши изменения
    connection.commit() 

Шаг 5: Запросите базу данных, чтобы проверить нашу работу

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

 # Выполнить запрос
sql = "ВЫБЕРИТЕ * ИЗ  book_details "
курсор.execute(sql)
# Получить все записи
результат = курсор. fetchall()
для я в результате:
    print(i) 

 (12345, «Программирование на Python», 29)
(12346, «Изучение MySQL», 23)
(12347, «Поваренная книга по науке о данных», 27) 

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

 connection.close() 

Вставка фреймов данных Pandas в базу данных с помощью функции to_sql()

Теперь давайте попробуем сделать то же самое — вставить кадр данных pandas в базу данных MySQL — используя другую технику. На этот раз мы будем использовать модуль sqlalchemy для создания нашего соединения и функцию to_sql() для вставки наших данных.

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

 # Модули импорта
импортировать панд как pd
# Создать фрейм данных
данные = pd. DataFrame ({
    'книга_id': [12345,12346,12347],
    'title':['Программирование на Python','Изучаем MySQL','Поваренная книга по науке о данных'],
    'цена': [29,23,27]
})
данные 

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

 # импортировать модуль
из импорта sqlalchemy create_engine
# создаем движок sqlalchemy
engine = create_engine("mysql+pymysql://{пользователь}:{pw}@localhost/{db}"
                       .format (пользователь = «корень»,
                               пв = "12345",
                               db="employee")) 

После подключения мы можем экспортировать весь DataFrame в MySQL, используя функцию to_sql() с именем таблицы параметров, именем движка, if_exists и chunksize.

Мы подробнее рассмотрим, к чему относится каждый из этих параметров, но сначала посмотрим, насколько проще вставить кадр данных pandas в базу данных MySQL, используя этот метод. Мы можем сделать это всего одной строкой кода:

 # Вставить весь DataFrame в MySQL
data.to_sql('book_details', con = engine, if_exists = 'append', chunksize = 1000) 

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

• book_details — это имя таблицы, в которую мы хотим вставить наш DataFrame.
• con = engine предоставляет информацию о соединении (напомним, что мы создали engine, используя наши данные аутентификации на предыдущем шаге).
• if_exists = 'append' проверяет, существует ли уже указанная нами таблица, а затем добавляет новые данные (если они существуют) или создает новую таблицу (если нет).
• размер фрагмента записывает записи партиями заданного размера за раз. По умолчанию все строки будут записаны одновременно.

Чтение записей из базы данных

После того, как мы использовали вставки SQL для внесения наших данных в базу данных, нам нужно иметь возможность прочитать их обратно! До сих пор в этом руководстве мы проверяли наши вставки SQL, просто печатая всю базу данных, но очевидно, что это не лучший вариант для больших баз данных, где вы будете печатать тысячи строк (или больше). Итак, давайте более подробно рассмотрим, как мы можем считывать записи, которые мы создали или вставили в нашу базу данных SQL.

Мы можем читать записи из базы данных SQL с помощью команды SELECT . Мы можем выбрать определенные столбцы или использовать * , чтобы выбрать все из данной таблицы. Мы также можем выбрать возврат только тех записей, которые соответствуют определенному условию, используя команду WHERE .

Вот как выглядит синтаксис этих команд:

Мы начинаем с предложения SELECT , за которым следует список столбцов, или * , если мы хотим выбрать все столбцы. Затем мы будем использовать FROM , чтобы назвать таблицу, которую мы хотели бы просмотреть. ГДЕ можно использовать для фильтрации записей, за которым следует условие фильтрации, и мы также можем использовать ORDER BY для сортировки записей. (Предложения WHERE и ORDER BY являются необязательными).

Для больших баз данных WHERE полезно для возврата только тех данных, которые мы хотим видеть. Так что, если, например, мы только что вставили новые данные об определенном отделе, мы могли бы использовать ГДЕ указать отдел ID в нашем запросе, и он вернет только записи с идентификатором отдела, который соответствует указанному нами.

Сравните, например, результаты этих двух запросов, используя нашу таблицу сотрудника из предыдущей. В первом мы возвращаем все строки. Во втором случае мы возвращаем только те строки, которые запросили. Это может не иметь большого значения, когда наша таблица состоит из семи строк, но когда вы работаете с семью строками тысяч строк или даже семь миллионов , использование WHERE для возврата только нужных результатов очень важно!

Если мы хотим сделать это из Python, мы можем использовать тот же сценарий, который мы использовали ранее в этом руководстве, для запроса этих записей. Единственное отличие состоит в том, что мы скажем pymysql выполнить команду SELECT , а не команду INSERT , которую мы использовали ранее.

 # Модуль импорта
импортировать pymysql
# создать соединение
соединение = pymysql.connect (хост = 'localhost',
                             пользователь = 'корень',
                             пароль='12345',
                             БД='сотрудник')
# Создать курсор
мой_курсор = соединение.курсор()
# Выполнить запрос
my_cursor.execute("ВЫБРАТЬ * от сотрудника")
# Получить записи
результат = my_cursor.fetchall()
для я в результате:
    печать (я)
# Закрыть соединение
соединение.закрыть() 

 (1001, «Джон», 2, 4000, «ИТ», «Нью-Дели»)
(1002, «Анна», 1, 3500, «HR», «Мумбаи»)
(1003, «Джеймс», 1, 2500, «HR», «Мумбаи»)
(1004, «Дэвид», 2, 5000, «ИТ», «Нью-Дели»)
(1005, «Марк», 2, 3000, «ИТ», «Нью-Дели»)
(1006, «Стив», 3, 4500, «Финансы», «Мумбаи»)
(1007, «Алиса», 3, 3500, «Финансы», «Мумбаи»)
(1008, «Кабир», 2, 5000, «ИТ», «Нью-Дели»)
(1009, «Морган», 1, 4000, «HR», «Мумбаи»)
(1009, «Морган», 1, 4000, «HR», «Мумбаи») 

Выше мы выбрали и распечатали всю базу данных, но если мы хотим использовать ГДЕ сделать более тщательный, ограниченный выбор, подход тот же:

 my_cursor. execute("SELECT * FROM employee WHERE DeptID=2") 

Обновление записей в базе данных

Часто нам нужно изменять записи в таблице после их создания.

Например, представьте, что сотрудник в нашей таблице сотрудников получил повышение. Мы хотели бы обновить данные об их зарплате. Команда INSERT INTO нам здесь не поможет, потому что мы не хотим добавлять совершенно новую строку.

Чтобы изменить существующие записи в таблице, нам нужно использовать команду UPDATE . ОБНОВЛЕНИЕ используется для изменения содержимого существующих записей. Мы можем указать определенные столбцы и значения для изменения с помощью SET , а также внести условные изменения с помощью WHERE , чтобы применить эти изменения только к строкам, которые соответствуют этому условию.

Теперь давайте обновим записи из нашей таблицы сотрудников и отобразим результаты. В этом случае, допустим, Дэвид получил повышение — мы напишем запрос, используя ОБНОВЛЕНИЕ , которое устанавливает Заработная плата на 6000 только в столбцах, где идентификатор сотрудника равен 1004 (идентификатор Дэвида).

Будьте осторожны — без предложения WHERE этот запрос обновил бы все записи в таблице, так что не забывайте об этом!

После выполнения приведенного выше запроса обновленная таблица будет выглядеть так:

Заключение

В этом руководстве мы рассмотрели вставки SQL и способы вставки данных в базы данных MySQL из Python. Мы также научились вставлять Pandas DataFrames в базы данных SQL, используя два разных метода, включая высокоэффективный метод 9.0043 метод to_sql() .

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

Также ознакомьтесь с другими нашими бесплатными ресурсами, связанными с SQL:

• Вам нужна сертификация по SQL?
• Вопросы SQL для собеседования для подготовки к собеседованию
• Наша шпаргалка по SQL

Вставка записей в таблицу с помощью SQL — данные для сбора данных

12 февраля 2022 г.

Вот общий синтаксис, который можно использовать для вставки записей в таблицу с помощью SQL:

 INSERT INTO имя_таблицы (столбец_1, столбец_2, столбец_3,...)

ЦЕННОСТИ

('значение_1', 'значение_2', 'значение_3',...)
 

Теперь давайте рассмотрим пример, чтобы увидеть, как применять приведенный выше синтаксис на практике.

Пример

Давайте предположим, что вы создали таблицу под названием « Product ‘ Таблица содержит 3 столбца: Product_id , Product_name , и цена :

 CREATE Product Product Product Create Table Product Create Table Product.
product_id int первичный ключ,
product_name nvarchar(50),
цена инт
)
 

Конечная цель — вставить в таблицу следующие 4 записи:

Вставка записей в таблицу с помощью SQL

Приведенный ниже запрос можно использовать для вставки 4 записей в таблицу «продукт» (обратите внимание, что вы не должны ставить запятую после закрывающей скобки последнего значения):

 ВСТАВИТЬ В продукт (product_id, product_name, цена)

ЦЕННОСТИ

(1, «Компьютер», 800),
(2,'ТВ',1200),
(3, "Принтер", 150),
(4, «Рабочий стол», 400)
 

После выполнения вышеуказанного запроса 4 записи будут вставлены в таблицу «продукт».

Чтобы убедиться, что записи были добавлены в таблицу, запустим следующий запрос SELECT:

 SELECT * FROM product
 

Теперь вы увидите новые вставленные записи:

Вставка дополнительных записей в таблицу

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

Например, добавим две дополнительные записи в таблицу product:

 INSERT INTO product (product_id, product_name, price)

ЦЕННОСТИ

(5, «Стул», 120),
(6, «Планшет», 300)
 

После выполнения запроса две дополнительные записи будут вставлены в таблицу «продукт».