Odczytywanie i zapisywanie plików SQL w Pandach

Kiedy kilka lat temu zacząłem uczyć się analizy danych, pierwszą rzeczą, której się nauczyłem, był SQL i Pandy. Jako analityk danych kluczowe jest posiadanie solidnych podstaw w pracy z SQL i Pandami. Oba są potężnymi narzędziami, które pomagają analitykom danych skutecznie analizować i manipulować danymi przechowywanymi w bazach danych.

Omówienie SQL i Pand

SQL (Structured Query Language) to język programowania używany do zarządzania relacyjnymi bazami danych i manipulowania nimi. Z drugiej strony Pandas to biblioteka Pythona używana do manipulacji i analizy danych.

Analiza danych obejmuje pracę z dużymi ilościami danych, a bazy danych są często używane do przechowywania tych danych. SQL i Pandas dostarczają potężnych narzędzi do pracy z bazami danych, umożliwiając analitykom danych efektywne wyodrębnianie, manipulowanie i analizowanie danych. Wykorzystując te narzędzia, analitycy danych mogą uzyskać cenne spostrzeżenia z danych, które w przeciwnym razie byłyby trudne do uzyskania.

W tym artykule przyjrzymy się, jak używać SQL i Pand do odczytu i zapisu w bazie danych.

Łączenie z bazą danych

Instalowanie bibliotek

Musimy najpierw zainstalować niezbędne biblioteki, zanim będziemy mogli połączyć się z bazą danych SQL za pomocą Pand. Dwie główne wymagane biblioteki to Pandas i SQLAlchemy. Pandas to popularna biblioteka do manipulacji danymi, która pozwala na przechowywanie dużych struktur danych, jak wspomniano we wstępie. W przeciwieństwie do tego SQLAlchemy zapewnia interfejs API do łączenia się i interakcji z bazą danych SQL.

Możemy zainstalować obie biblioteki za pomocą menedżera pakietów Python, pip, uruchamiając następujące polecenia w wierszu polecenia.

$ pip install pandas
$ pip install sqlalchemy

Tworzenie połączenia

Po zainstalowaniu bibliotek możemy teraz używać Pand do łączenia się z bazą danych SQL.

Na początek utworzymy obiekt silnika SQLAlchemy za pomocą create_engine(), create_engine() Funkcja łączy kod Pythona z bazą danych. Jako argument przyjmuje ciąg połączenia, który określa typ bazy danych i szczegóły połączenia. W tym przykładzie użyjemy typu bazy danych SQLite i ścieżki pliku bazy danych.

Utwórz obiekt silnika dla bazy danych SQLite, korzystając z poniższego przykładu:

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

Jeśli plik bazy danych SQLite, w naszym przypadku student.db, znajduje się w tym samym katalogu co skrypt Pythona, możemy użyć nazwy pliku bezpośrednio, jak pokazano poniżej.

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

Odczytywanie plików SQL za pomocą Pand

Odczytajmy dane teraz, gdy nawiązaliśmy połączenie. W tej części przyjrzymy się read_sql, read_sql_table, read_sql_query funkcje i jak ich używać do pracy z bazą danych.

Wykonywanie zapytań SQL przy użyciu Pandy odczyt_sql() Funkcjonować

Połączenia read_sql() to funkcja biblioteki Pandas, która pozwala nam wykonać zapytanie SQL i pobrać wyniki do ramki danych Pandas. The read_sql() Funkcja łączy SQL i Python, pozwalając nam wykorzystać moc obu języków. Funkcja zawija się read_sql_table() i read_sql_query(), read_sql() funkcja jest kierowana wewnętrznie na podstawie dostarczonych danych wejściowych, co oznacza, że ​​jeśli dane wejściowe mają wykonać zapytanie SQL, zostaną skierowane do read_sql_query(), a jeśli jest to tabela bazy danych, zostanie przekierowana do read_sql_table().

Połączenia read_sql() składnia jest następująca:

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

Wymagane są parametry SQL i con; reszta jest opcjonalna. Możemy jednak manipulować wynikiem za pomocą tych opcjonalnych parametrów. Przyjrzyjmy się bliżej każdemu parametrowi.

• sql: Zapytanie SQL lub nazwa tabeli bazy danych
• con: Obiekt połączenia lub adres URL połączenia
• index_col: Ten parametr pozwala nam użyć jednej lub więcej kolumn z wyniku zapytania SQL jako indeksu ramki danych. Może to być pojedyncza kolumna lub lista kolumn.
• coerce_float: Ten parametr określa, czy wartości nieliczbowe powinny być konwertowane na liczby zmiennoprzecinkowe, czy pozostawione jako łańcuchy znaków. Domyślnie jest ustawiony na true. Jeśli to możliwe, konwertuje wartości nieliczbowe na typy zmiennoprzecinkowe.
• params: Parametry zapewniają bezpieczną metodę przekazywania wartości dynamicznych do zapytania SQL. Możemy użyć parametru params, aby przekazać słownik, krotkę lub listę. W zależności od bazy danych składnia parametrów jest różna.
• parse_dates: Pozwala nam to określić, która kolumna w wynikowej ramce danych będzie interpretowana jako data. Akceptuje pojedynczą kolumnę, listę kolumn lub słownik z kluczem jako nazwą kolumny i wartością jako formatem kolumny.
• columns: Pozwala nam to pobrać tylko wybrane kolumny z listy.
• chunksize: Podczas pracy z dużym zestawem danych ważny jest rozmiar porcji. Pobiera wynik zapytania w mniejszych porcjach, zwiększając wydajność.

Oto przykład użycia read_sql():

Kod:

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()

Wyjście:

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

Po połączeniu się z bazą danych wykonujemy zapytanie, które zwraca wszystkie rekordy z bazy danych Student table i przechowuje je w DataFrame df. Kolumna „Roll Number” jest konwertowana na indeks za pomocą index_col parametr, a typem danych „dateOfBirth” jest „datetime64[ns]”. parse_dates. Możemy użyć read_sql() nie tylko do pobierania danych, ale także do wykonywania innych operacji, takich jak wstawianie, usuwanie i aktualizacja. read_sql() jest funkcją ogólną.

Ładowanie określonych tabel lub widoków z bazy danych

Ładowanie określonej tabeli lub widoku za pomocą Pand read_sql_table() to kolejna technika wczytywania danych z bazy danych do ramki danych Pandas.

Co to jest read_sql_table?

Biblioteka Pandas zapewnia read_sql_table funkcja, która jest specjalnie zaprojektowana do odczytywania całej tabeli SQL bez wykonywania jakichkolwiek zapytań i zwracania wyniku jako ramki danych Pandas.

Składnia read_sql_table() jest jak poniżej:

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

Z wyjątkiem table_name i schemat, parametry są wyjaśnione w taki sam sposób jak read_sql().

• table_name: Parametr table_name to nazwa tabeli SQL w bazie danych.
• schema: Ten opcjonalny parametr to nazwa schematu zawierającego nazwę tabeli.

Po utworzeniu połączenia z bazą danych użyjemy read_sql_table funkcja ładowania Student table do 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()

Wyjście:

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

Załóżmy, że jest to duża tabela, która może wymagać dużej ilości pamięci. Przyjrzyjmy się, jak możemy wykorzystać chunksize parametr, aby rozwiązać ten problem.

Kod:

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()

Wyjście:

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

Należy pamiętać, że chunksize Używam tutaj jest 1, ponieważ mam tylko 3 rekordy w mojej tabeli.

Bezpośrednie wysyłanie zapytań do bazy danych za pomocą składni SQL Pandas

Wydobywanie spostrzeżeń z bazy danych jest ważną częścią dla analityków danych i naukowców. W tym celu wykorzystamy tzw read_sql_query() funkcja.

Co to jest read_sql_query()?

Korzystanie z Pand read_sql_query() możemy uruchamiać zapytania SQL i pobierać wyniki bezpośrednio do DataFrame. The read_sql_query() funkcja jest stworzona specjalnie dla SELECT sprawozdania. Nie można go używać do żadnych innych operacji, takich jak DELETE or UPDATE.

Składnia:

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)

Wszystkie opisy parametrów są takie same jak w read_sql() funkcjonować. Oto przykład read_sql_query():

Kod:

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()

Wyjście:

 firstName lastName
0 Mark Simson

Pisanie plików SQL za pomocą Pand

Załóżmy, że analizując dane, odkryliśmy, że trzeba zmodyfikować kilka wpisów lub że potrzebna jest nowa tabela lub widok z danymi. Aby zaktualizować lub wstawić nowy rekord, należy użyć jednej metody read_sql() i napisz zapytanie. Jednak ta metoda może być długa. Pandy zapewniają świetną metodę o nazwie to_sql() dla takich sytuacji.

W tej sekcji najpierw zbudujemy nową tabelę w bazie danych, a następnie edytujemy istniejącą.

Tworzenie nowej tabeli w bazie danych SQL

Zanim utworzymy nową tabelę, najpierw omówmy to_sql() szczegółowo.

Co to jest to_sql()?

Połączenia to_sql() Funkcja biblioteki Pandas pozwala nam pisać lub aktualizować bazę danych. The to_sql() funkcja może zapisywać dane DataFrame w bazie danych SQL.

Składnia dla 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 i con parametry są wymagane do uruchomienia to_sql(); jednak inne parametry zapewniają dodatkową elastyczność i opcje dostosowywania. Omówmy szczegółowo każdy parametr:

• name: Nazwa tabeli SQL, która ma zostać utworzona lub zmieniona.
• con: Obiekt połączenia z bazą danych.
• schema: Schemat tabeli (opcjonalnie).
• if_exists: Domyślna wartość tego parametru to „fail”. Ten parametr pozwala nam zdecydować, jakie działanie należy podjąć, jeśli tabela już istnieje. Opcje obejmują „niepowodzenie”, „zamień” i „dołącz”.
• index: Parametr indeksu przyjmuje wartość logiczną. Domyślnie jest ustawiony na True, co oznacza, że ​​indeks DataFrame zostanie zapisany w tabeli SQL.
• index_label: Ten opcjonalny parametr pozwala nam określić etykietę kolumny dla kolumn indeksu. Domyślnie indeks jest zapisywany do tabeli, ale za pomocą tego parametru można nadać konkretną nazwę.
• chunksize: Liczba wierszy zapisywanych jednocześnie w bazie danych SQL.
• dtype: Ten parametr akceptuje słownik z kluczami jako nazwami kolumn i wartościami jako ich typami danych.
• method: Parametr metody pozwala określić metodę używaną do wstawiania danych do SQL. Domyślnie jest ustawiony na Brak, co oznacza, że ​​pandy znajdą najskuteczniejszy sposób w oparciu o bazę danych. Istnieją dwie główne opcje parametrów metody:
  • multi: Umożliwia wstawianie wielu wierszy w jednym zapytaniu SQL. Jednak nie wszystkie bazy danych obsługują wstawianie wielu wierszy.
  • Funkcja wywoływalna: Tutaj możemy napisać niestandardową funkcję dla wstawienia i wywołać ją przy użyciu parametrów metody.

Oto przykład za pomocą 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()

W bazie danych tworzona jest nowa tabela o nazwie Klient z dwoma polami o nazwach „Imię” i „Wiek”.

Migawka bazy danych:

Aktualizowanie istniejących tabel za pomocą ramek danych Pandas

Aktualizowanie danych w bazie danych jest złożonym zadaniem, szczególnie w przypadku dużych ilości danych. Jednak korzystając z to_sql() funkcja w Pandach może znacznie ułatwić to zadanie. Aby zaktualizować istniejącą tabelę w bazie danych, plik to_sql() funkcja może być używana z if_exists parametr ustawiony na „zamień”. Spowoduje to zastąpienie istniejącej tabeli nowymi danymi.

Oto przykład to_sql() który aktualizuje poprzednio utworzony Customer tabela. Załóżmy, że w Customer chcemy zaktualizować wiek klienta o imieniu Paweł z 9 na 10 lat. W tym celu możemy najpierw zmodyfikować odpowiedni wiersz w DataFrame, a następnie użyć to_sql() funkcja aktualizacji bazy danych.

Kod:

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()

W bazie danych wiek Pawła jest aktualizowany:

Wnioski

Podsumowując, Pandy i SQL to potężne narzędzia do zadań związanych z analizą danych, takich jak odczytywanie i zapisywanie danych w bazie danych SQL. Pandas zapewnia łatwy sposób łączenia się z bazą danych SQL, odczytywania danych z bazy danych do ramki danych Pandas i zapisywania danych ramki danych z powrotem do bazy danych.

Biblioteka Pandas ułatwia manipulowanie danymi w ramce danych, podczas gdy SQL zapewnia potężny język do wykonywania zapytań dotyczących danych w bazie danych. Używanie zarówno Pand, jak i SQL do odczytu i zapisu danych może zaoszczędzić czas i wysiłek w zadaniach związanych z analizą danych, zwłaszcza gdy dane są bardzo duże. Ogólnie rzecz biorąc, wspólne wykorzystanie SQL i Pand może pomóc analitykom danych i naukowcom usprawnić ich przepływ pracy.

• Dystrybucja treści i PR oparta na SEO. Uzyskaj wzmocnienie już dziś.
• PlatoAiStream. Analiza danych Web3. Wiedza wzmocniona. Dostęp tutaj.
• Wybijanie przyszłości w Adryenn Ashley. Dostęp tutaj.
• Kupuj i sprzedawaj akcje spółek PRE-IPO z PREIPO®. Dostęp tutaj.
• Źródło: https://stackabuse.com/reading-and-writing-sql-files-in-pandas/