Citirea și scrierea fișierelor SQL în Pandas

Republicat de Platon

Urmaritori: 0

Când am început să învăț Analiza datelor în urmă cu câțiva ani, primul lucru pe care l-am învățat a fost SQL și Pandas. În calitate de analist de date, este esențial să aveți o bază solidă în lucrul cu SQL și Pandas. Ambele sunt instrumente puternice care îi ajută pe analiștii de date să analizeze și să manipuleze eficient datele stocate în baze de date.

Prezentare generală despre SQL și Pandas

SQL (Structured Query Language) este un limbaj de programare folosit pentru gestionarea și manipularea bazelor de date relaționale. Pe de altă parte, Pandas este o bibliotecă Python folosită pentru manipularea și analiza datelor.

Analiza datelor implică lucrul cu cantități mari de date, iar bazele de date sunt adesea folosite pentru a stoca aceste date. SQL și Pandas oferă instrumente puternice pentru lucrul cu bazele de date, permițând analiștilor de date să extragă, să manipuleze și să analizeze eficient datele. Folosind aceste instrumente, analiștii de date pot obține informații valoroase din date care altfel ar fi dificil de obținut.

În acest articol, vom explora cum să folosiți SQL și Pandas pentru a citi și scrie într-o bază de date.

Conectarea la DB

Instalarea Bibliotecilor

Mai întâi trebuie să instalăm bibliotecile necesare înainte de a ne putea conecta la baza de date SQL cu Pandas. Cele două biblioteci principale necesare sunt Pandas și SQLAlchemy. Pandas este o bibliotecă populară de manipulare a datelor care permite stocarea unor structuri mari de date, așa cum se menționează în introducere. În schimb, SQLAlchemy oferă un API pentru conectarea și interacțiunea cu baza de date SQL.

Putem instala ambele biblioteci folosind managerul de pachete Python, pip, rulând următoarele comenzi la promptul de comandă.

$ pip install pandas
$ pip install sqlalchemy

Realizarea conexiunii

Cu bibliotecile instalate, acum putem folosi Pandas pentru a ne conecta la baza de date SQL.

Pentru început, vom crea un obiect motor SQLAlchemy cu create_engine(). create_engine() funcția conectează codul Python la baza de date. Ia ca argument un șir de conexiune care specifică tipul bazei de date și detaliile conexiunii. În acest exemplu, vom folosi tipul bazei de date SQLite și calea fișierului bazei de date.

Creați un obiect motor pentru o bază de date SQLite folosind exemplul de mai jos:

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

Dacă fișierul bazei de date SQLite, student.db în cazul nostru, se află în același director cu scriptul Python, putem folosi numele fișierului direct, așa cum se arată mai jos.

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

Citirea fișierelor SQL cu Pandas

Să citim datele acum că am stabilit o conexiune. În această secțiune, ne vom uita la read_sql, read_sql_table, și read_sql_query funcții și cum să le folosiți pentru a lucra cu o bază de date.

Executarea interogărilor SQL folosind Panda read_sql() Funcţie

read_sql() este o funcție de bibliotecă Pandas care ne permite să executăm o interogare SQL și să extragem rezultatele într-un cadru de date Pandas. The read_sql() funcția conectează SQL și Python, permițându-ne să profităm de puterea ambelor limbi. Funcția se împachetează read_sql_table() și read_sql_query(). read_sql() funcția este direcționată intern pe baza intrării furnizate, ceea ce înseamnă că, dacă intrarea urmează să execute o interogare SQL, aceasta va fi direcționată către read_sql_query(), iar dacă este un tabel de bază de date, acesta va fi direcționat către read_sql_table().

read_sql() sintaxa este următoarea:

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

Parametrii SQL și con sunt necesari; restul sunt optionale. Cu toate acestea, putem manipula rezultatul folosind acești parametri opționali. Să aruncăm o privire mai atentă la fiecare parametru.

sql: interogare SQL sau nume tabel bazei de date
con: obiect de conexiune sau URL de conexiune
index_col: Acest parametru ne permite să folosim una sau mai multe coloane din rezultatul interogării SQL ca index de cadru de date. Poate lua fie o singură coloană, fie o listă de coloane.
coerce_float: Acest parametru specifică dacă valorile nenumerice trebuie convertite în numere flotante sau lăsate ca șiruri. Este setat implicit la true. Dacă este posibil, convertește valorile non-numerice în tipuri float.
params: Parametrii oferă o metodă sigură pentru transmiterea valorilor dinamice la interogarea SQL. Putem folosi parametrul params pentru a trece un dicționar, un tuplu sau o listă. În funcție de baza de date, sintaxa parametrilor variază.
parse_dates: Acest lucru ne permite să specificăm ce coloană din cadrul de date rezultat va fi interpretată ca dată. Acceptă o singură coloană, o listă de coloane sau un dicționar cu cheia ca nume de coloană și valoarea ca format de coloană.
columns: Acest lucru ne permite să preluăm numai coloanele selectate din listă.
chunksize: Când lucrați cu un set mare de date, dimensiunea fragmentelor este importantă. Acesta preia rezultatul interogării în bucăți mai mici, îmbunătățind performanța.

Iată un exemplu de utilizare read_sql():

Cod:

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

ieșire:

 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]

După conectarea la baza de date, executăm o interogare care returnează toate înregistrările din Student tabel și le stochează în DataFrame df. Coloana „Numărul rolului” este convertită într-un index folosind index_col parametrul, iar tipul de date „dateOfBirth” este „datetime64[ns]” din cauza parse_dates. Putem folosi read_sql() nu numai pentru a prelua date, ci și pentru a efectua alte operațiuni, cum ar fi inserarea, ștergerea și actualizarea. read_sql() este o funcție generică.

Se încarcă anumite tabele sau vizualizări din DB

Încărcarea unui anumit tabel sau vizualizare cu Pandas read_sql_table() este o altă tehnică de citire a datelor din baza de date într-un cadru de date Pandas.

Ce este read_sql_table?

Biblioteca Pandas oferă read_sql_table funcția, care este special concepută pentru a citi un întreg tabel SQL fără a executa nicio interogare și a returna rezultatul ca un cadru de date Pandas.

Sintaxa lui read_sql_table() este după cum urmează:

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

În afară de table_name și schema, parametrii sunt explicați în același mod ca read_sql().

table_name: Parametrul table_name este numele tabelului SQL din baza de date.
schema: Acest parametru opțional este numele schemei care conține numele tabelului.

După crearea unei conexiuni la baza de date, vom folosi read_sql_table funcția pentru a încărca Student tabel într-un 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()

ieșire:

 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

Vom presupune că este o masă mare care poate consuma multă memorie. Să explorăm cum putem folosi chunksize parametru pentru a rezolva această problemă.

Consultați ghidul nostru practic și practic pentru a învăța Git, cu cele mai bune practici, standarde acceptate de industrie și fisa de cheat incluse. Opriți căutarea pe Google a comenzilor Git și de fapt învăţa aceasta!

Cod:

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

ieșire:

 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

Vă rugăm să rețineți că chunksize Folosesc aici 1 pentru că am doar 3 înregistrări în tabelul meu.

Interogarea DB direct cu sintaxa SQL a lui Pandas

Extragerea informațiilor din baza de date este o parte importantă pentru analiștii de date și oamenii de știință. Pentru a face acest lucru, vom valorifica read_sql_query() Funcția.

Ce este read_sql_query()?

Folosind Pandas read_sql_query() funcție, putem rula interogări SQL și obținem rezultatele direct într-un DataFrame. The read_sql_query() funcția este creată special pentru SELECT declarații. Nu poate fi folosit pentru alte operațiuni, cum ar fi DELETE or UPDATE.

Sintaxă:

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)

Toate descrierile parametrilor sunt identice cu cele ale read_sql() funcţie. Iată un exemplu de read_sql_query():

Cod:

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

ieșire:

 firstName lastName
0 Mark Simson

Scrierea fișierelor SQL cu Pandas

În timp ce analizăm datele, să presupunem că am descoperit că câteva intrări trebuie modificate sau că este necesar un nou tabel sau vizualizare cu datele. Pentru a actualiza sau a insera o înregistrare nouă, o metodă este să utilizați read_sql() și scrieți o interogare. Cu toate acestea, această metodă poate fi lungă. Panda oferă o metodă grozavă numită to_sql() pentru situatii de genul acesta.

În această secțiune, vom construi mai întâi un nou tabel în baza de date și apoi vom edita unul existent.

Crearea unui nou tabel în baza de date SQL

Înainte de a crea un nou tabel, să discutăm mai întâi to_sql() detaliat.

Ce este to_sql()?

to_sql() Funcția bibliotecii Pandas ne permite să scriem sau să actualizăm baza de date. The to_sql() funcția poate salva datele DataFrame într-o bază de date SQL.

Sintaxa pentru to_sql():

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

Numai name și con parametrii sunt obligatorii pentru a rula to_sql(); cu toate acestea, alți parametri oferă flexibilitate suplimentară și opțiuni de personalizare. Să discutăm în detaliu fiecare parametru:

name: Numele tabelului SQL care urmează să fie creat sau modificat.
con: Obiectul de conexiune al bazei de date.
schema: Schema tabelului (opțional).
if_exists: Valoarea implicită a acestui parametru este „fail”. Acest parametru ne permite să decidem acțiunea care trebuie întreprinsă dacă tabelul există deja. Opțiunile includ „eșuare”, „înlocuire” și „adăugare”.
index: Parametrul index acceptă o valoare booleană. În mod implicit, este setat la True, ceea ce înseamnă că indexul DataFrame va fi scris în tabelul SQL.
index_label: Acest parametru opțional ne permite să specificăm o etichetă de coloană pentru coloanele de index. În mod implicit, indexul este scris în tabel, dar un nume specific poate fi dat folosind acest parametru.
chunksize: numărul de rânduri care trebuie scrise simultan în baza de date SQL.
dtype: Acest parametru acceptă un dicționar cu chei ca nume de coloane și valori ca tipuri de date.
method: Parametrul method permite specificarea metodei utilizate pentru inserarea datelor în SQL. În mod implicit, este setat la Niciunul, ceea ce înseamnă că panda vor găsi cea mai eficientă cale pe baza bazei de date. Există două opțiuni principale pentru parametrii metodei:
- multi: permite inserarea mai multor rânduri într-o singură interogare SQL. Cu toate acestea, nu toate bazele de date acceptă inserarea pe mai multe rânduri.
- Funcție apelabilă: Aici, putem scrie o funcție personalizată pentru inserare și o putem apela folosind parametrii metodei.

Iată un exemplu de utilizare 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()

Un nou tabel numit Client este creat în baza de date, cu două câmpuri numite „Nume” și „Vârsta”.

Instantaneu baza de date:

Ieșirea to_sql()

Actualizarea tabelelor existente cu Pandas Dataframes

Actualizarea datelor dintr-o bază de date este o sarcină complexă, în special atunci când se ocupă cu date mari. Cu toate acestea, folosind to_sql() funcția din Pandas poate face această sarcină mult mai ușoară. Pentru a actualiza tabelul existent în baza de date, to_sql() funcția poate fi utilizată cu if_exists parametrul setat la „înlocuire”. Acest lucru va suprascrie tabelul existent cu noile date.

Iată un exemplu de to_sql() care actualizează cele create anterior Customer masa. Să presupunem că, în Customer tabelul dorim să actualizăm vârsta unui client pe nume Paul de la 9 la 10 ani. Pentru a face acest lucru, mai întâi, putem modifica rândul corespunzător din DataFrame și apoi folosim to_sql() funcția de actualizare a bazei de date.

Cod:

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

În baza de date, vârsta lui Paul este actualizată:

Ieșirea to_sql()

Concluzie

În concluzie, Pandas și SQL sunt ambele instrumente puternice pentru sarcini de analiză a datelor, cum ar fi citirea și scrierea datelor în baza de date SQL. Pandas oferă o modalitate ușoară de a vă conecta la baza de date SQL, de a citi datele din baza de date într-un cadru de date Pandas și de a scrie datele din cadrul de date înapoi în baza de date.

Biblioteca Pandas facilitează manipularea datelor într-un cadru de date, în timp ce SQL oferă un limbaj puternic pentru interogarea datelor într-o bază de date. Utilizarea atât a Pandas, cât și a SQL pentru a citi și scrie datele poate economisi timp și efort în sarcinile de analiză a datelor, mai ales atunci când datele sunt foarte mari. În general, folosirea SQL și Pandas împreună poate ajuta analiștii de date și oamenii de știință să-și eficientizeze fluxul de lucru.