Коли я почав вивчати аналіз даних кілька років тому, перше, про що я дізнався, це 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()
У базі даних створюється нова таблиця під назвою «Клієнт» із двома полями «Ім’я» та «Вік».
Знімок бази даних:
Оновлення існуючих таблиць за допомогою фреймів даних 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()
В базі даних вік Павла оновлюється:
Висновок
Підсумовуючи, Pandas і SQL є потужними інструментами для завдань аналізу даних, таких як читання та запис даних у базу даних SQL. Pandas забезпечує простий спосіб підключення до бази даних SQL, читання даних із бази даних у фрейм даних Pandas і запис даних фрейму даних назад у базу даних.
Бібліотека Pandas дозволяє легко маніпулювати даними у кадрі даних, тоді як SQL надає потужну мову для запитів даних у базі даних. Використання як Pandas, так і SQL для читання та запису даних може заощадити час і зусилля в задачах аналізу даних, особливо коли дані дуже великі. Загалом, спільне використання SQL і Pandas може допомогти аналітикам даних і науковцям оптимізувати свій робочий процес.
- Розповсюдження контенту та PR на основі SEO. Отримайте посилення сьогодні.
- PlatoAiStream. Web3 Data Intelligence. Розширення знань. Доступ тут.
- Карбування майбутнього з Адріенн Ешлі. Доступ тут.
- Купуйте та продавайте акції компаній, які вийшли на IPO, за допомогою PREIPO®. Доступ тут.
- джерело: https://stackabuse.com/reading-and-writing-sql-files-in-pandas/