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

В этой статье мы сосредоточимся на чтении данных из SQLite с помощью Python. Это должно помочь вам с вашими навыками Python и SQL.

Шаг 1. Убедитесь, что вы уже установили пакеты PIP.

Для этого проекта мы будем использовать SQLite3 и Pandas.

$ pip install sqlite3
$ pip install pandas

Шаг 2. Загрузите данные, подключитесь к SQLite и запросите таблицы

Давайте загрузим данные опроса о Психическом здоровье в технологической индустрии, который есть на Kaggle.com (Kaggle — отличный ресурс для удивительных наборов данных с открытым исходным кодом!). Перейдите по ссылке и нажмите кнопку Скачать (6MB). Возможно, вам придется зарегистрироваться, прежде чем вы сможете загрузить данные. Я просто загрузил данные в папку Загрузки и разархивировал файл. После распаковки обратите внимание на расширение .sqlite.

Теперь, когда у нас есть данные, давайте подключимся к базе данных SQLite и запросим таблицы.

Чтобы выбрать все данные в одной таблице в SQL, мы просто запускаем: «выбрать * из имя_базы_данных.имя_таблицы». * означает все/все.

import sqlite3
import pandas as pd
# You may need to update the directory path ("../...") below.
db_connection = sqlite3.connect("../Downloads/mental_health.sqlite")
# We're selecting and printing all from the Answer table.
answer_table = pd.read_sql("select * from Answer", db_connection)
print(answer_table)
# answer_table Output #
                AnswerText  SurveyID  UserID  QuestionID
0                       37      2014       1           1
1                       44      2014       2           1
2                       32      2014       3           1
3                       31      2014       4           1
4                       31      2014       5           1
...                    ...       ...     ...         ...
236893               Other      2016    2689         117
236894             Support      2016    2690         117
236895  Back-end Developer      2016    2691         117
236896     DevOps/SysAdmin      2016    2692         117
236897               Other      2016    2693         117
# We're selecting and printing all from the Question table.
question_table = pd.read_sql("select * from Question", db_connection)
print(question_table)
# question_table Output #
                                          questiontext  questionid
0                                    What is your age?           1
1                                 What is your gender?           2
2                         What country do you live in?           3
3    If you live in the United States, which state ...           4
4                               Are you self-employed?           5
..                                                 ...         ...
100  Do you think that team members/co-workers woul...         114
101  If yes, what condition(s) have you been diagno...         115
102  If maybe, what condition(s) do you believe you...         116
103  Which of the following best describes your wor...         117
104                              Do you work remotely?         118
# We're selecting and printing all from the Survey table.
survey_table = pd.read_sql("select * from Survey", db_connection)
print(survey_table)
# survey_table Output #
     SurveyID         Description
0      2014  mental health survey for 2014
1      2016  mental health survey for 2016
2      2017  mental health survey for 2017
3      2018  mental health survey for 2018
4      2019  mental health survey for 2019

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

sql_query = "select count(distinct UserID) as unique_ids from Answer"
distinct_userid_in_answer = pd.read_sql(sql_query, db_connection)
print(distinct_userid_in_answer)
# distinct_userid_in_answer Output #
unique_ids
0        4218

Давайте обсудим некоторые команды sql из этого запроса:

  • count(): означает, что мы собираемся подсчитать все, что находится внутри count().
  • Внутри count() находится различный идентификатор пользователя. отличный означает получить только одно вхождение для каждого значения (в данном случае) UserID.
  • as unique_ids: изменяет вывод имени столбца на (в данном случае) unique_ids.

Итак, теперь мы знаем, что в опросе приняли участие 4218 уникальных пользователей. Но чтобы ответить на другие вопросы, нам нужно соединить наши таблицы вместе, так что давайте попробуем!

Шаг 3: Диаграммы отношений объектов (ERD)

Прежде чем мы перейдем к тому, как соединять таблицы в SQL, давайте сначала визуализируем, как наши таблицы связаны друг с другом, используя диаграммы сущностей-связей (ERD).

Создание ERD — это полезный навык, потому что они упрощают объяснение того, как таблицы взаимодействуют друг с другом.

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

  • BoldColumnName (Pk): первичный ключ (Pk) для каждой таблицы. Pk полезен, потому что он предоставляет уникальное значение, которое может идентифицировать конкретную строку в таблице. Например, в нашем коде в конце шага 2 мы использовали UserID Pk, чтобы получить количество отдельных/уникальных пользователей.
  • ColumnName (Fk): внешний ключ (Fk) для таблицы. Fk одной таблицы — это Pk другой таблицы. Таким образом, Fk дает нам возможность связать (или соединить) две или более таблиц.
  • Отношения один к одному: они показаны справа от Survey_table и слева от question_table. Например, отношение «один к одному» между answer_table и Survey_table означает, что каждый SurveyID в answer_table может быть привязан к одному и только одному SurveyID в Survey_table. Это логично, поскольку SurveyID является первичным ключом в Survey_table.
  • Отношение «один ко многим»: это можно увидеть с обеих сторон таблицы ответов. Отношение «один ко многим» между таблицами question_table и answer_table означает, что каждый questionid в question_table должен быть в answer_table по крайней мере один раз и до нескольких раз. Это логично, потому что в каждом опросе будет задан один и тот же вопрос.

Теперь, когда мы лучше знакомы с ERD, давайте закодируем ERD выше, используя SQL!

Шаг 4: Объединение таблиц

Еще раз взглянув на таблицы, я думаю, что имеет смысл объединить answer_table и question_table (поскольку Survey_table не дает никаких новых данных. SurveyID — это просто дата опроса). Чтобы объединить эти две таблицы вместе, мы просто запускаем:

sql_query = """
select * 
from Question 
inner join Answer on Question.questionid = Answer.QuestionID
"""
question_answer_table = pd.read_sql(sql_query, db_connection)
print(question_answer_table)

Давайте обсудим некоторые команды sql из этого запроса:

  • внутреннее соединение: объединяет строки с совпадающими значениями (вы можете узнать больше о соединениях по ссылкам ниже).
  • on Question.questionid = Answer.QuestionID: это объясняет значения (questionid и QuestionID), которые мы хотим сопоставить.

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

Шаг 5. Фильтрация запросов

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

Чтобы попрактиковаться в фильтрации наших данных, давайте ответим на эти два вопроса:

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

Сколько участников опроса были в возрасте 30 лет или моложе в течение каждого года проведения опроса?

sql_query = """
select SurveyID, count(*) as surveyors_less_than_30
from Question 
inner join Answer on Question.questionid = Answer.QuestionID
where questiontext in ('What is your age?')
and AnswerText < 30
group by SurveyID"""
less_than_30_each_year = pd.read_sql(sql_query, db_connection)
print(less_than_30_each_year)
# less_than_30_each_year Output #
    SurveyID  count(*)
0      2014       521
1      2016       450
2      2017       205
3      2018       117
4      2019        99

Давайте обсудим некоторые команды sql из этого запроса:

  • где текст вопроса («Каков ваш возраст?») и текст ответа ‹ 30: где операторы используются для фильтрации данных. В этом случае нам нужны были только вопросы, в которых задавался вопрос о возрасте опроса и о том, был ли их возраст меньше 30 лет.
  • группировать по SurveyID. Группа группирует одинаковые строки на основе столбца (SurveyID). Таким образом, мы можем узнать, сколько опросов друг от друга.

Сколько участников опроса работает в технологических компаниях, а не в нетехнологических?

sql_query = """
select AnswerText, count(*) as tech_or_nontech
from Question 
inner join Answer on Question.questionid = Answer.QuestionID
where questiontext like ('Is your employer primarily a tech company/orga%')
group by AnswerText"""
tech_or_nontech = pd.read_sql(sql_query, db_connection)
print(tech_or_nontech)

Давайте обсудим некоторые команды sql из этого запроса:

  • где текст вопроса, например ("Является ли ваш работодатель в первую очередь технологической компанией/организацией%"): это утверждение where фильтрует текст вопроса для "Является ли ваш работодатель в основном технологической компанией/организацией…" >вопрос. Оператор нравится означает «похож на». Подстановочный знак % в конце строки означает, что начальный текст должен точно совпадать, но текст после компании/организации может быть любым.

Ваша очередь исследовать больше!

  • Узнайте больше об SQL с помощью этой Шпаргалки!
  • Запросите данные, чтобы ответить на дополнительные вопросы, связанные с полом, страной происхождения или самозанятыми работниками.
  • Используйте для анализа библиотеку визуализации данных Seaborn.

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

Хотите оставаться на связи? Свяжитесь со мной в LinkedIn!