Если вы стремитесь стать специалистом по данным, вам придется работать с большим количеством данных. Большая часть данных находится в базах данных, и поэтому вам должно быть удобно получать доступ к данным из баз данных с помощью запросов, а затем работать с ними, чтобы найти ключевые идеи.
Данные составляют неотъемлемую часть жизни специалистов по данным. Сегодня все, от количества пассажиров в аэропорту до количества стационарных пассажиров в книжном магазине, записывается в виде цифровых файлов, называемых базами данных. Базы данных - это не что иное, как электронные списки информации. Некоторые базы данных просты и предназначены для небольших задач, в то время как другие мощные и предназначены для больших данных. Однако все они имеют одинаковые общие черты и выполняют схожие функции. Различные инструменты базы данных хранят эту информацию уникальными способами. Плоские файлы используют таблицу, базы данных SQL используют реляционную модель, а базы данных NoSQL используют модель "ключ-значение".
В этой статье мы сосредоточимся только на реляционных базах данных и доступе к ним в Python. Мы начнем с краткого обзора реляционных баз данных и их важных составляющих.
Реляционная база данных: краткий обзор
Реляционная база данных состоит из одной или нескольких таблиц информации. строки в таблице называются записями, а столбцы в таблице - полями или атрибутами. . База данных, содержащая две или более связанных таблиц, называется реляционной базой данных, то есть взаимосвязанными данными.
Основная идея реляционной базы данных заключается в том, что ваши данные разбиваются на общие темы с одной таблицей, посвященной описанию записей каждой темы.
i) Таблицы базы данных
Каждая таблица в реляционной базе данных имеет один или несколько столбцов, и каждому столбцу назначается определенный тип данных, например целое число, последовательность символов (для текста). , или свидание. Каждая строка в таблице имеет значение для каждого столбца.
Типичный фрагмент таблицы, содержащей информацию о сотрудниках, может выглядеть следующим образом:
Таблицы реляционной базы данных имеют несколько важных характеристик:
- Порядок столбцов или строк не имеет значения.
- Каждая строка содержит одно и только одно значение для каждого столбца.
- Каждое значение для данного столбца имеет один и тот же тип.
- Каждая таблица в базе данных должна содержать информацию только о конкретной вещи, например о сотрудниках, продуктах или клиентах.
Такое проектирование базы данных помогает устранить избыточность и несогласованность. Например, и отдел продаж, и отдел счетов к оплате могут искать информацию о клиентах. В реляционной базе данных информация о клиентах вводится только один раз в таблицу, к которой имеют доступ оба отдела.
Реляционная база данных - это набор связанных таблиц. Вы используете первичный и внешний ключи для описания отношений между информацией в разных таблицах.
ii) Первичный и внешний ключи
Первичный и внешний ключи определяют реляционную структуру базы данных. Эти ключи позволяют идентифицировать каждую строку в таблицах базы данных и определять отношения между таблицами.
- Первичный ключ
Первичный ключ реляционной таблицы однозначно идентифицирует каждую запись в таблице. Это столбец или набор столбцов, который позволяет однозначно идентифицировать каждую строку в таблице. Никакие две строки в таблице с первичным ключом не могут иметь одинаковое значение первичного ключа.
Представьте, что у вас есть таблица CUSTOMERS, содержащая запись для каждого покупателя, посещающего магазин. Уникальный номер клиента - хороший выбор в качестве первичного ключа. Имя и фамилия клиента - не лучший выбор, потому что всегда есть вероятность, что несколько клиентов могут иметь одно и то же имя.
- Внешний ключ
Внешний ключ - это поле в реляционной таблице, которое соответствует столбцу первичного ключа другой таблицы.
Приведенный выше пример дает хорошее представление о первичном и внешнем ключах.
Системы управления базами данных
Система управления базой данных (СУБД) - это программное обеспечение, которое взаимодействует с конечными пользователями, приложениями и самой базой данных для сбора и анализа данных. СУБД, используемая для реляционных баз данных, называется Системы управления реляционными базами данных (СУБД). Большинство коммерческих СУБД используют язык структурированных запросов (SQL), декларативный язык для управления данными, для доступа к базе данных. Основными СУБД являются Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Microsoft Access и SQLite.
Мы едва прикоснулись к базам данных здесь. Подробности выходят за рамки этой статьи, однако мы рекомендуем вам изучить экосистему баз данных, поскольку они составляют важную часть инструментария специалиста по данным.
В этой статье основное внимание будет уделено использованию Python для доступа к реляционным базам данных. Мы будем работать с очень простым в использовании ядром базы данных под названием SQLite.
SQLite
SQLite - это система управления реляционными базами данных, основанная на языке SQL, но оптимизированная для использования в небольших средах, таких как мобильные телефоны или небольшие приложения. Он самодостаточный, бессерверный, не требующий настройки и транзакционный. Это очень быстро и легко, и вся база данных хранится в одном файле на диске. SQLite создан для простоты и скорости по сравнению с размещенной реляционной базой данных клиент-сервер, такой как MySQL. Он жертвует изощренностью ради полезности и сложности ради размера. Запросы в SQLite почти идентичны другим вызовам SQL.
Модуль Python sqlite3
SQLite можно интегрировать с Python с помощью модуля Python под названием sqlite3. Вам не нужно устанавливать этот модуль отдельно, потому что он поставляется вместе с Python версии 2.5.x и более поздних версий. Эта статья шаг за шагом покажет вам, как работать с базой данных SQLite с помощью Python.
Перед тем, как начать, я настоятельно рекомендую вам установить Браузер БД для SQLite. Браузер можно легко скачать с их официальной страницы. Браузер БД для SQLite - это высококачественный визуальный инструмент с открытым исходным кодом для создания, проектирования и редактирования файлов баз данных, совместимых с SQLite. Это поможет нам видеть создаваемые и редактируемые базы данных в реальном времени.
Поскольку все на месте, приступим к работе.
Содержание:
- ПОДКЛЮЧЕНИЕ к базе данных
- СОЗДАНИЕ таблицы
- ВСТАВКА записей в ТАБЛИЦУ
- ВЫБОР записей из ТАБЛИЦЫ
- ОБНОВЛЕНИЕ записей в ТАБЛИЦЕ
- УДАЛИТЬ операцию
- Пример пошагового руководства
Подключение к базе данных
- Откройте любую IDE Python по вашему выбору и введите следующие команды. Вы даже можете использовать Jupyter Notebook для того же. В общем, единственное, что нужно сделать, прежде чем мы сможем выполнить какую-либо операцию с базой данных SQLite через модуль Python
sqlite3, - это открыть соединение с файлом базы данных SQLite:
import sqlite3
conn = sqlite3.connect('my_database.sqlite')
cursor = conn.cursor()
print("Opened database successfully")
Приведенный выше код Python позволяет нам подключаться к существующей базе данных с помощью объекта соединенияconn. Если база данных не существует, она будет создана и, наконец, будет возвращен объект базы данных . Объект Acursor - это наш интерфейс к базе данных, который позволяет запускать anySQL query в нашей базе данных.
Если все пойдет хорошо, при запуске скрипта будут возвращены следующие строки:
Opened database successfully
Давайте теперь откроем и просмотрим вновь созданную базу данных в браузере БД.
Действительно, создается новая база данных с именем my_database.sqlite, которая в настоящее время пуста.
Прежде чем продолжить, стоит упомянуть еще две вещи. Если мы закончили наши операции с файлом базы данных, мы должны закрыть соединение с помощью метода .close():
conn.close()И если мы выполнили операцию с базой данных, отличную от отправки запросов, нам нужно зафиксировать эти изменения с помощью метода .commit(), прежде чем мы закроем соединение:
conn.commit()
conn.close()Мы всегда должны помнить commit текущую транзакцию. Поскольку по умолчанию Connector / Python не выполняет автоматическую фиксацию, важно вызывать этот метод после каждой транзакции, которая изменяет данные для таблиц, использующих механизмы хранения транзакций. Если вы не вызовете этот метод, все, что вы сделали с момента последнего вызова to commit(), не будет видно из других подключений к базе данных.
2. Создание таблицы
Теперь мы создадим table в ранее созданной базе данных. Введите следующий код в IDE.
cursor.execute('''CREATE TABLE SCHOOL
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
MARKS INT);''')
cursor.close()
Programconn.execute выполняет инструкцию SQL. Здесь мы создаем таблицу с SCHOOL полями: ID, NAME, AGE, ADDRESS и MARKS Мы также обозначаем ID Первичный ключ, а затем закрываем соединение. Давайте посмотрим на эти детали в браузере БД.
3. ВСТАВКА записей в ТАБЛИЦУ
Теперь давайте обратимся к INSERT записям студентов в таблицеSCHOOL, созданной в приведенном выше примере.
import sqlite3
conn = sqlite3.connect('my_database.sqlite')
cursor = conn.cursor()
cursor.execute("INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,MARKS) \
VALUES (1, 'Rohan', 14, 'Delhi', 200)");
cursor.execute("INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,MARKS) \
VALUES (2, 'Allen', 14, 'Bangalore', 150 )");
cursor.execute("INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,MARKS) \
VALUES (3, 'Martha', 15, 'Hyderabad', 200 )");
cursor.execute("INSERT INTO SCHOOL (ID,NAME,AGE,ADDRESS,MARKS) \
VALUES (4, 'Palak', 15, 'Kolkata', 650)");
conn.commit()
conn.close()
Когда вышеуказанная программа будет выполнена, она создаст указанные записи в tableSCHOOL.
4. ВЫБОР записей из ТАБЛИЦЫ
Допустим, мы хотим выбрать некоторые конкретные записи из таблицы, то есть только ID, NAME и MARKS. Это легко сделать с помощью commandSELECT.
import sqlite3
conn = sqlite3.connect('my_database.sqlite')
cursor = conn.cursor()
for row in cursor.execute("SELECT id, name, marks from SCHOOL"):
print("ID = ", row[0])
print("NAME = ", row[1])
print("MARKS = ", row[2], "\n")
conn.commit()
conn.close()
Когда вышеуказанная программа будет выполнена, она выдаст следующий результат. Мы видим только то, что адрес и возраст не возвращены.
ID = 1 NAME = Rohan MARKS = 200 ID = 2 NAME = Allen MARKS = 150 ID = 3 NAME = Martha MARKS = 200 ID = 4 NAME = Palak MARKS = 650
5. ОБНОВЛЕНИЕ записей в ТАБЛИЦЕ
Давайте посмотрим, как использовать команду UPDATE для обновления любой записи, а затем извлечь и отобразить обновленные записи из таблицы SCHOOL. Здесь мы обновим оценки Марты с 200 до 250 и снова получим записи.
import sqlite3
conn = sqlite3.connect('my_database.sqlite')
cursor = conn.cursor()
conn.execute("UPDATE SCHOOL set MARKS = 250 where ID = 3")
conn.commit()
for row in cursor.execute("SELECT id, name, address, marks from SCHOOL"):
print("ID = ", row[0])
print("NAME = ", row[1])
print("MARKS = ", row[2], "\n")
conn.commit()
conn.close()
Когда приведенная выше программа будет выполнена, оценка Марты изменится с 200 на 250.
ID = 1 NAME = Rohan MARKS = 200 ID = 2 NAME = Allen MARKS = 150 ID = 3 NAME = Martha MARKS = 250 ID = 4 NAME = Palak MARKS = 650
6. Операция УДАЛИТЬ
Мы можем использовать операциюDELETE, чтобы удалить любую запись из таблицы SCHOOL. Допустим, Аллен окончательно покинул школу, и мы хотим удалить его записи из базы данных. Теперь давайте возьмем данные обо всех других студентах из записей.
import sqlite3
conn = sqlite3.connect('my_database.sqlite')
cursor = conn.cursor()
conn.execute("DELETE from SCHOOL where ID = 2")
conn.commit()
for row in cursor.execute("SELECT id, name, address, marks from SCHOOL"):
print("ID = ", row[0])
print("NAME = ", row[1])
print("ADDRESS = ", row[2])
print("MARKS = ", row[3], "\n")
conn.commit()
conn.close()
Когда вышеуказанная программа будет выполнена, она выдаст следующий результат.
ID = 1 NAME = Rohan ADDRESS = Delhi MARKS = 200 ID = 3 NAME = Martha ADDRESS = Hyderabad MARKS = 250 ID = 4 NAME = Palak ADDRESS = Kolkata MARKS = 650
То же самое можно увидеть в браузере БД. Вторая запись удалена.
В предыдущем разделе мы узнали, как создать базу данных и выполнять с ней различные операции. В этом разделе давайте поработаем с реальным примером базы данных, чтобы увидеть, как мы можем применить только что изученные основы.
Пример пошагового руководства: база данных футбола
Мы будем работать с базой данных футбола от Kaggle. Это лучшая база данных Soccer для анализа данных и машинного обучения, и все подробности доступны из kaggle. База данных содержит 8 таблиц.
Предварительные условия: вам пригодятся базовые знания Python и библиотек, таких как pandas.
Загрузите набор данных в формате SQLite из Kaggle и сохраните его в том же каталоге, что и блокнот jupyter.
Импорт базовых библиотек
import sqlite3 import pandas as pd import numpy as np import seaborn as sns import matplotlib.pyplot as plt %matplotlib inline
Доступ к базе данных с помощью пакета sqlite3
# Creating the connection to database
con = sqlite3.connect('soccer.sqlite')
cursor = con.cursor()
Получение списка всех таблиц, сохраненных в базе данных
for row in cursor.execute("SELECT name FROM sqlite_master WHERE type='table';"):
print(row)
('sqlite_sequence',)
('Player_Attributes',)
('Player',)
('Match',)
('League',)
('Country',)
('Team',)
('Team_Attributes',)
Чтение всех ТАБЛИЦ с библиотекой pandas
country_table = pd.read_sql_query("SELECT * FROM Country", con)
league_table = pd.read_sql_query("SELECT * FROM League", con)
match_table = pd.read_sql_query("SELECT * FROM Match", con)
player_table = pd.read_sql_query("SELECT * FROM Player", con)
player_att_table = pd.read_sql_query("SELECT * FROM Player_Attributes", con)
team_table = pd.read_sql_query("SELECT * FROM Team", con)
team_att_table = pd.read_sql_query("SELECT * FROM Team_Attributes", con)
Исследовательский анализ данных
Здесь мы будем анализировать только Player таблицу. Но не стесняйтесь анализировать и все остальные таблицы.
Таблица игроков
# Dimensions player_table.shape (11060, 7) # player_table.info() Data columns (total 7 columns): id 11060 non-null int64 player_api_id 11060 non-null int64 player_name 11060 non-null object player_fifa_api_id 11060 non-null int64 birthday 11060 non-null object height 11060 non-null float64 weight 11060 non-null int64 dtypes: float64(1), int64(4), object(2)
Доступ к первым 5 записям таблицы Player
player_table.head()
Теперь у нас есть фрейм данных pandas, и мы можем легко работать с ним, чтобы получить желаемую информацию, например:
Поиск всех игроков ростом ›150 см.
height_150 = pd.read_sql_query("SELECT * FROM Player WHERE height >= 150 ", con)
Точно так же вы можете изучить все другие таблицы дальше, чтобы получить другие важные идеи. Пожалуйста, найдите код в записной книжке Jupyter ниже. Код не требует пояснений.
Заключение
В этом руководстве мы увидели, как легко начать работу с базами данных SQLite через Python. Модуль sqlite3 очень прост в использовании и очень удобен при работе с большими системами данных. Надеюсь, эта статья была вам полезна. Дайте мне знать, если у вас есть какие-либо сомнения или предложения в разделе комментариев ниже.
