Как подключить Python к базе данных SQL

1. Три линии

Перед пояснением: pandas уже умеют читать CSV. Что даст вам прямое подключение к базе данных, чего нет при предварительном экспорте CSV?

import sqlite3
import pandas as pd

con = sqlite3.connect('Chinook_Sqlite.sqlite')

df = pd.read_sql("""
    SELECT c.Country,
           COUNT(*)               AS invoices,
           ROUND(SUM(i.Total), 2) AS revenue
    FROM Invoice i
    JOIN Customer c ON c.CustomerId = i.CustomerId
    GROUP BY c.Country
    ORDER BY revenue DESC
    LIMIT 5
""", con)

print(df)

Country  invoices  revenue
    USA        91   523.06
 Canada        56   303.96
 France        35   195.10
 Brazil        35   190.10
Germany        28   156.48

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

2. Что делает каждая строка

import sqlite3 ничего не стоит и ничего не устанавливает, поскольку он является частью самого Питон. Это стоит знать на заблокированном компьютере, где для установки программного обеспечения требуется разрешение.

sqlite3.connect('Chinook_Sqlite.sqlite') открывает файл и возвращает Связь, это ваша открытая линия к базе данных. Одно предупреждение: если это имя файла не существует, SQLite вместо того, чтобы жаловаться, создает новую пустую базу данных. Таким образом, опечатка в пути приводит к нулевым таблицам и ошибкам, что в первый раз занимает пять минут путаницы.

pd.read_sql(query, con) отправляет SQL, считывает результат и создает DataFrame с именами столбцов из вашего запроса. Обратите внимание, что названия взяты из AS псевдонимы: invoices и revenue — это метки, которые вы выбрали, и присвоение им имен в SQL означает, что вам не придется переименовывать их в Python позже. SQL-псевдонимы покрывает эту привычку.

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

3. Курсор и когда он вам действительно нужен

Другой способ получить данные выглядит следующим образом.

cur = con.cursor()
cur.execute("SELECT COUNT(*) FROM Track")
print(cur.fetchone())

(3503,)

A курсор — это указатель на набор результатов, который вы выполняете самостоятельно. fetchone() занимает одну строку, fetchall() берет их всех. Обратите внимание, что получилось: кортеж, содержащий одно число, без имени столбца и с завершающей запятой, которая однажды сбила всех с толку.

Прежде чем читать дальше, скажите, когда это будет предпочтительнее DataFrame.

Два случая, и оба реальные. Когда ты хочешь одно значение вместо таблицы, как указано выше, создавать целый DataFrame для хранения числа 3503 глупо. И когда ты изменение данных, а не читать их, с помощью INSERT, UPDATE, DELETE или CREATE TABLE курсор является правильным инструментом, и вы должны вызвать con.commit() после этого, иначе ваши изменения исчезнут, когда программа завершится.

Все остальное, что для аналитика является почти всем, читайте в DataFrame. Этикетки не удобны; именно они мешают тебе писать row[3] и ошибаюсь в отношении того, какой столбец находится после того, как кто-то добавляет поле.

4. Безопасная передача значений

Рано или поздно запросу потребуется значение переменной: дата, клиент, порог. Есть очевидный способ сделать это, но он неправильный.

# Do not do this.
threshold = 600000
df = pd.read_sql(f"SELECT Name FROM Track WHERE Milliseconds > {threshold}", con)

Вместо этого напишите его с заполнителем и передайте значение отдельно.

df = pd.read_sql(
    "SELECT Name, Milliseconds FROM Track WHERE Milliseconds > ? LIMIT 3",
    con,
    params=(600000,),
)
print(df)

               Name  Milliseconds
   Sleeping Village        644571
    You Shook Me(2)        619467
How Many More Times        711836

тот ? является заполнителем, и params поставляет то, что туда идет. База данных получает запрос и значение как две отдельные вещи, поэтому значение никогда не может быть прочитано как инструкции. В случае ж-строки они поступают как одна строка, и любой текст внутри нее, который выглядит как SQL, выполняется как SQL. Вот и все, что люди подразумевают под SQL-инъекцией, и это реальная опасность в тот момент, когда значение приходит откуда-то, что вы не контролируете.

Две меньшие причины предпочитать заполнители даже для данных, которым вы доверяете. Форматы кавычек и дат обрабатываются за вас, что устраняет постоянный источник вопросов «почему это работает в инструменте базы данных, а не здесь». И params=(600000,) нужна эта завершающая запятая, потому что без нее скобки — это просто скобки, а не кортеж из одного элемента.

5. Доступ к Постгрес, MySQL и остальным

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

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

python -m pip install sqlalchemy psycopg2-binary   # Postgres
python -m pip install sqlalchemy pymysql            # MySQL

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('postgresql+psycopg2://user:password@host:5432/dbname')
df = pd.read_sql("SELECT * FROM sales LIMIT 5", engine)

Эта строка представляет собой URL-адрес соединения и читается слева направо: какой движок, какой драйвер, кто вы, какой компьютер, какой порт, какая база данных. Менять postgresql+psycopg2 для mysql+pymysql и та же линия достигает MySQL.

Единственное, чего делать не следует: никогда не вводите настоящий пароль в файл, который вы собираетесь сохранить. Прочитайте его из переменной среды с помощью os.environ['DB_PASSWORD'] вместо этого и храните значение вне своего репозитория. Git для аналитиков охватывает то, что еще никогда нельзя совершать.

6. Где провести грань между SQL и pandas

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

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

Так WHERE, JOIN, GROUP BY и HAVING принадлежать к запросу. Построение графиков, изменение формы, присоединение к чему-то, чего нет в базе данных, и все итеративные действия относятся к pandas. Если группирующая половина незнакома, GROUP BY и HAVING и pandas.groupby представляют собой одну и ту же идею на обоих языках.

Теперь представьте себе свой текущий сценарий. Какая строка первой превращает большую вещь в маленькую и сколько строк находится в памяти до нее? Если ответ — все из них, эта строка, вероятно, принадлежит запросу.

Что пошло не так и как это исправить

Шесть, которые появляются постоянно.

«Нет такой таблицы» в базе данных, в которой, как вы знаете, есть таблицы. Путь был неправильным, поэтому SQLite создал новый пустой файл. Проверьте путь и перечислите, что там на самом деле есть с помощью pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", con).

Ваш INSERT или UPDATE ничего не сделал. Ты не звонил con.commit(). Чтение не требует фиксации; пишет, делаю.

База данных заблокирована. В другой программе он открыт с несохраненными изменениями, очень часто в Браузер БД. Нажмите там «Записать изменения» или закройте его.

Числа приходят в виде текста. Редкие из базы данных и общие из CSV, вот в чем суть. Если это произойдет, столбец действительно будет сохранен в виде текста в таблице, и CAST(col AS REAL) в запросе есть честное исправление.

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

Соединение никогда не закрывается. Использовать with sqlite3.connect(path) as con: или позвоните con.close(). Если оставить соединения открытыми, это в конечном итоге приведет к появлению сообщения о заблокированной базе данных, приведенному выше.