Generative Data Intelligence

Читання та запис файлів SQL у Pandas

Дата:

Коли я почав вивчати аналіз даних кілька років тому, перше, про що я дізнався, це SQL і Pandas. Як аналітику даних, дуже важливо мати міцну основу для роботи з SQL і Pandas. Обидва є потужними інструментами, які допомагають аналітикам даних ефективно аналізувати та маніпулювати даними, що зберігаються в базах даних.

Огляд SQL і Pandas

SQL (Structured Query Language) — це мова програмування, яка використовується для керування реляційними базами даних і маніпулювання ними. З іншого боку, Pandas — це бібліотека Python, яка використовується для обробки та аналізу даних.

Аналіз даних передбачає роботу з великими обсягами даних, і для зберігання цих даних часто використовуються бази даних. SQL і Pandas надають потужні інструменти для роботи з базами даних, дозволяючи аналітикам даних ефективно витягувати, маніпулювати та аналізувати дані. Використовуючи ці інструменти, аналітики даних можуть отримати цінну інформацію з даних, які інакше було б важко отримати.

У цій статті ми розглянемо, як використовувати SQL і Pandas для читання та запису в базу даних.

Підключення до БД

Встановлення бібліотек

Перш ніж підключитися до бази даних SQL за допомогою Pandas, ми повинні спочатку встановити необхідні бібліотеки. Дві основні необхідні бібліотеки — Pandas і SQLAlchemy. Pandas — популярна бібліотека для обробки даних, яка дозволяє зберігати великі структури даних, як згадувалося у вступі. Навпаки, SQLAlchemy надає API для підключення та взаємодії з базою даних SQL.

Ми можемо встановити обидві бібліотеки за допомогою менеджера пакетів Python, pip, виконавши наступні команди в командному рядку.

$ pip install pandas
$ pip install sqlalchemy

З'єднання

З установленими бібліотеками тепер ми можемо використовувати Pandas для підключення до бази даних SQL.

Для початку ми створимо об’єкт двигуна SQLAlchemy create_engine(), create_engine() функція підключає код Python до бази даних. Він приймає як аргумент рядок підключення, який визначає тип бази даних і деталі підключення. У цьому прикладі ми будемо використовувати тип бази даних SQLite і шлях до файлу бази даних.

Створіть об’єкт двигуна для бази даних SQLite, використовуючи приклад нижче:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

Якщо файл бази даних SQLite, у нашому випадку student.db, знаходиться в тому самому каталозі, що й сценарій Python, ми можемо використовувати назву файлу напряму, як показано нижче.

engine = create_engine('sqlite:///student.db')

Читання файлів SQL за допомогою Pandas

Давайте прочитаємо дані тепер, коли ми встановили з’єднання. У цьому розділі ми розглянемо read_sql, read_sql_table та read_sql_query функції та як їх використовувати для роботи з базою даних.

Виконання запитів SQL за допомогою Panda read_sql() функція

Команда read_sql() це функція бібліотеки Pandas, яка дозволяє нам виконувати запит SQL і отримувати результати у фрейм даних Pandas. The read_sql() функція поєднує SQL і Python, дозволяючи нам скористатися перевагами обох мов. Функція обертається read_sql_table() та read_sql_query(), read_sql() функція внутрішньо маршрутизується на основі наданих вхідних даних, що означає, що якщо вхідні дані призначені для виконання запиту SQL, вони будуть направлені до read_sql_query(), і якщо це таблиця бази даних, вона буде спрямована до read_sql_table().

Команда read_sql() синтаксис такий:

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

Необхідні параметри SQL і con; решта необов'язкові. Однак ми можемо маніпулювати результатом, використовуючи ці додаткові параметри. Розглянемо докладніше кожен параметр.

  • sql: SQL-запит або назва таблиці бази даних
  • con: Об’єкт підключення або URL-адреса підключення
  • index_col: Цей параметр дозволяє нам використовувати один або кілька стовпців із результату SQL-запиту як індекс кадру даних. Він може займати один стовпець або список стовпців.
  • coerce_float: цей параметр визначає, чи потрібно нечислові значення перетворювати на плаваючі числа чи залишати їх у вигляді рядків. За замовчуванням встановлено значення true. Якщо можливо, він перетворює нечислові значення на типи з плаваючою точкою.
  • params: Параметри забезпечують безпечний метод передачі динамічних значень у запит SQL. Ми можемо використовувати параметр params для передачі словника, кортежу або списку. Залежно від бази даних синтаксис параметрів змінюється.
  • parse_dates: Це дозволяє нам вказати, який стовпець у результуючому кадрі даних буде інтерпретуватися як дата. Він приймає один стовпець, список стовпців або словник із ключем як назвою стовпця та значенням як формат стовпця.
  • columns: Це дозволяє нам отримувати лише вибрані стовпці зі списку.
  • chunksize: під час роботи з великим набором даних важливий розмір фрагментів. Він отримує результат запиту меншими фрагментами, підвищуючи продуктивність.

Ось приклад використання read_sql():

код:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

вихід:

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson [email protected] 2000-02-23
2 Peter Griffen [email protected] 2001-04-15
3 Meg Aniston [email protected] 2001-09-20
Date type of dateOfBirth: datetime64[ns]

Після підключення до бази даних ми виконуємо запит, який повертає всі записи з Student і зберігає їх у DataFrame df. Стовпець «Номер списку» перетворюється на індекс за допомогою index_col параметр, а тип даних “dateOfBirth” – “datetime64[ns]” через parse_dates. Ми можемо використовувати read_sql() не тільки для отримання даних, але й для виконання інших операцій, таких як вставка, видалення та оновлення. read_sql() є загальною функцією.

Завантаження конкретних таблиць або представлень з БД

Завантаження певної таблиці або представлення за допомогою Pandas read_sql_table() це ще один метод читання даних із бази даних у фрейм даних Pandas.

Що таке read_sql_table?

Бібліотека Pandas надає read_sql_table функція, яка спеціально розроблена для читання всієї таблиці SQL без виконання будь-яких запитів і повернення результату як фрейм даних Pandas.

Синтаксис read_sql_table() як нижче:

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

Окрім table_name і схема, параметри пояснюються так само, як read_sql().

  • table_name: Параметр table_name це ім'я таблиці SQL у базі даних.
  • schema: цей необов’язковий параметр є назвою схеми, що містить назву таблиці.

Після створення підключення до бази даних ми будемо використовувати read_sql_table функція для завантаження Student таблиці в Pandas DataFrame.

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

вихід:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
1 2 Peter Griffen [email protected] 2001-04-15
2 3 Meg Aniston [email protected] 2001-09-20

Ми припустимо, що це велика таблиця, яка може інтенсивно займати пам’ять. Давайте дослідимо, як ми можемо використовувати chunksize параметр для вирішення цієї проблеми.

Ознайомтеся з нашим практичним практичним посібником із вивчення Git з передовими методами, прийнятими в галузі стандартами та включеною шпаргалкою. Припиніть гуглити команди Git і фактично вчитися це!

код:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

вихід:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
0 2 Peter Griffen [email protected] 2001-04-15
0 3 Meg Aniston [email protected] 2001-09-20

Будь ласка, майте на увазі, що chunksize Я використовую тут 1, оскільки в моїй таблиці є лише 3 записи.

Запит до БД безпосередньо за допомогою синтаксису SQL Pandas

Отримання інформації з бази даних є важливою частиною для аналітиків даних і вчених. Для цього ми використаємо read_sql_query() функції.

Що таке read_sql_query()?

Використання Pandas read_sql_query() ми можемо запускати запити SQL і отримувати результати безпосередньо в DataFrame. The read_sql_query() функція створена спеціально для SELECT заяви. Його не можна використовувати для будь-яких інших операцій, наприклад DELETE or UPDATE.

Синтаксис:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

Усі описи параметрів такі ж, як і в read_sql() функція. Ось приклад read_sql_query():

код:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

вихід:

 firstName lastName
0 Mark Simson

Написання файлів SQL за допомогою Pandas

Припустімо, що під час аналізу даних ми виявили, що потрібно змінити кілька записів або що потрібна нова таблиця чи подання з даними. Щоб оновити або вставити новий запис, скористайтеся одним із методів read_sql() і напишіть запит. Однак цей метод може бути тривалим. Панди пропонують чудовий метод під назвою to_sql() для таких ситуацій.

У цьому розділі ми спочатку створимо нову таблицю в базі даних, а потім відредагуємо існуючу.

Створення нової таблиці в базі даних SQL

Перш ніж створити нову таблицю, давайте спочатку обговоримо to_sql() детально.

Що таке to_sql()?

Команда to_sql() функція бібліотеки Pandas дозволяє нам писати або оновлювати базу даних. The to_sql() функція може зберігати дані DataFrame до бази даних SQL.

Синтаксис для to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

Only name та con параметри є обов’язковими для запуску to_sql(); однак інші параметри забезпечують додаткову гнучкість і можливості налаштування. Розглянемо кожен параметр докладніше:

  • name: ім’я таблиці SQL, яку потрібно створити або змінити.
  • con: об’єкт підключення бази даних.
  • schema: Схема таблиці (за бажанням).
  • if_exists: Значення цього параметра за замовчуванням — «fail». Цей параметр дозволяє нам вирішити, яку дію потрібно виконати, якщо таблиця вже існує. Параметри включають «не вдалося», «замінити» та «додати».
  • index: Параметр index приймає логічне значення. За замовчуванням встановлено значення True, тобто індекс DataFrame буде записаний у таблицю SQL.
  • index_label: цей необов’язковий параметр дозволяє нам вказати мітку стовпця для стовпців індексу. За замовчуванням індекс записується в таблицю, але за допомогою цього параметра можна вказати конкретне ім’я.
  • chunksize: кількість рядків, які потрібно записати одночасно в базу даних SQL.
  • dtype: цей параметр приймає словник із ключами як іменами стовпців і значеннями як їхніми типами даних.
  • method: Параметр method дозволяє вказати метод, який використовується для вставки даних у SQL. За замовчуванням встановлено значення «Немає», що означає, що панди знайдуть найефективніший спосіб на основі бази даних. Існує два основних варіанти параметрів методу:
    • multi: дозволяє вставляти кілька рядків в один SQL-запит. Однак не всі бази даних підтримують багаторядкову вставку.
    • Виклична функція: Тут ми можемо написати спеціальну функцію для вставки та викликати її за допомогою параметрів методу.

Ось приклад використання to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

У базі даних створюється нова таблиця під назвою «Клієнт» із двома полями «Ім’я» та «Вік».

Знімок бази даних:

Вивід to_sql()

Оновлення існуючих таблиць за допомогою фреймів даних Pandas

Оновлення даних у базі даних є складним завданням, особливо при роботі з великими даними. Однак, використовуючи to_sql() функція в Pandas може значно полегшити це завдання. Щоб оновити існуючу таблицю в базі даних, to_sql() функцію можна використовувати з if_exists для параметра встановлено значення «замінити». Це перезапише існуючу таблицю новими даними.

Ось приклад to_sql() що оновлює раніше створений Customer стіл. Припустимо, в Customer ми хочемо оновити вік клієнта на ім’я Пол з 9 до 10. Для цього спочатку ми можемо змінити відповідний рядок у DataFrame, а потім використати to_sql() функція оновлення бази даних.

код:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

В базі даних вік Павла оновлюється:

Вивід to_sql()

Висновок

Підсумовуючи, Pandas і SQL є потужними інструментами для завдань аналізу даних, таких як читання та запис даних у базу даних SQL. Pandas забезпечує простий спосіб підключення до бази даних SQL, читання даних із бази даних у фрейм даних Pandas і запис даних фрейму даних назад у базу даних.

Бібліотека Pandas дозволяє легко маніпулювати даними у кадрі даних, тоді як SQL надає потужну мову для запитів даних у базі даних. Використання як Pandas, так і SQL для читання та запису даних може заощадити час і зусилля в задачах аналізу даних, особливо коли дані дуже великі. Загалом, спільне використання SQL і Pandas може допомогти аналітикам даних і науковцям оптимізувати свій робочий процес.

spot_img

Остання розвідка

spot_img

Зв'яжіться з нами!

Привіт! Чим я можу вам допомогти?