SQL-tiedostojen lukeminen ja kirjoittaminen Pandasissa

Julkaissut Platon

seuraajia: 0

Kun aloin opetella data-analyysiä muutama vuosi sitten, ensimmäinen asia, jonka opin, olivat SQL ja Pandas. Tietoanalyytikkona on erittäin tärkeää, että sinulla on vahva perusta työskennellä SQL:n ja Pandan kanssa. Molemmat ovat tehokkaita työkaluja, jotka auttavat dataanalyytikoita analysoimaan ja käsittelemään tietokantoihin tallennettuja tietoja tehokkaasti.

Yleiskatsaus SQL:stä ja Pandasta

SQL (Structured Query Language) on ohjelmointikieli, jota käytetään relaatiotietokantojen hallintaan ja käsittelyyn. Toisaalta Pandas on Python-kirjasto, jota käytetään tietojen käsittelyyn ja analysointiin.

Tietojen analysointi edellyttää suurten tietomäärien käsittelyä, ja näiden tietojen tallentamiseen käytetään usein tietokantoja. SQL ja Pandas tarjoavat tehokkaita työkaluja tietokantojen kanssa työskentelyyn, jolloin dataanalyytikot voivat poimia, käsitellä ja analysoida tietoja tehokkaasti. Hyödyntämällä näitä työkaluja dataanalyytikot voivat saada arvokkaita oivalluksia tiedoista, joita muuten olisi vaikea saada.

Tässä artikkelissa tutkimme, kuinka käyttää SQL:ää ja Pandaa tietokantaan lukemiseen ja siihen kirjoittamiseen.

Yhdistetään DB:hen

Kirjastojen asentaminen

Meidän on ensin asennettava tarvittavat kirjastot ennen kuin voimme muodostaa yhteyden SQL-tietokantaan Pandasin avulla. Kaksi vaadittua pääkirjastoa ovat Pandas ja SQLAlchemy. Pandas on suosittu tiedonkäsittelykirjasto, joka mahdollistaa suurten tietorakenteiden tallentamisen, kuten johdannossa mainittiin. Sitä vastoin SQLAlchemy tarjoaa API:n yhteyden muodostamiseen SQL-tietokantaan ja vuorovaikutukseen sen kanssa.

Voimme asentaa molemmat kirjastot Python-paketinhallinnan pip avulla suorittamalla seuraavat komennot komentokehotteessa.

$ pip install pandas
$ pip install sqlalchemy

Yhteyden muodostaminen

Kun kirjastot on asennettu, voimme nyt käyttää Pandasta yhteyden muodostamiseen SQL-tietokantaan.

Aluksi luomme SQLAlchemy-moottoriobjektin create_engine(). create_engine() toiminto yhdistää Python-koodin tietokantaan. Se käyttää argumenttina yhteysmerkkijonoa, joka määrittää tietokannan tyypin ja yhteyden tiedot. Tässä esimerkissä käytämme SQLite-tietokantatyyppiä ja tietokantatiedoston polkua.

Luo moottoriobjekti SQLite-tietokantaan alla olevan esimerkin avulla:

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

Jos SQLite-tietokantatiedosto, meidän tapauksessamme student.db, on samassa hakemistossa kuin Python-skripti, voimme käyttää tiedoston nimeä suoraan alla olevan kuvan mukaisesti.

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

SQL-tiedostojen lukeminen Pandan kanssa

Luemme tiedot nyt, kun olemme muodostaneet yhteyden. Tässä osiossa tarkastelemme read_sql, read_sql_tableja read_sql_query toiminnot ja niiden käyttö tietokannan kanssa.

SQL-kyselyjen suorittaminen Pandan avulla read_sql() Toiminto

- read_sql() on Pandas-kirjastotoiminto, jonka avulla voimme suorittaa SQL-kyselyn ja hakea tulokset Pandas-tietokehykseen. The read_sql() funktio yhdistää SQL:n ja Pythonin, jolloin voimme hyödyntää molempien kielten tehoja. Toiminto käärii read_sql_table() ja read_sql_query(). read_sql() toiminto reititetään sisäisesti annetun syötteen perusteella, mikä tarkoittaa, että jos syötteen on tarkoitus suorittaa SQL-kysely, se reititetään read_sql_query(), ja jos se on tietokantataulukko, se reititetään read_sql_table().

- read_sql() syntaksi on seuraava:

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

SQL- ja con-parametrit vaaditaan; loput ovat valinnaisia. Voimme kuitenkin manipuloida tulosta käyttämällä näitä valinnaisia parametreja. Tarkastellaanpa kutakin parametria tarkemmin.

sql: SQL-kyselyn tai tietokantataulukon nimi
con: Yhteysobjekti tai yhteyden URL-osoite
index_col: Tämän parametrin avulla voimme käyttää yhtä tai useampaa saraketta SQL-kyselyn tuloksesta tietokehysindeksinä. Se voi sisältää joko yhden sarakkeen tai sarakeluettelon.
coerce_float: Tämä parametri määrittää, muunnetaanko ei-numeeriset arvot kelluviksi luvuiksi vai jätetäänkö ne merkkijonoiksi. Se on oletuksena tosi. Jos mahdollista, se muuntaa ei-numeeriset arvot float-tyypeiksi.
params: Parametrit tarjoavat suojatun menetelmän dynaamisten arvojen välittämiseksi SQL-kyselyyn. Voimme käyttää params-parametria välittämään sanakirjaa, monikkoa tai luetteloa. Tietokannasta riippuen parametrien syntaksi vaihtelee.
parse_dates: Tämän avulla voimme määrittää, mikä tuloksena olevan tietokehyksen sarake tulkitaan päivämääräksi. Se hyväksyy yksittäisen sarakkeen, sarakeluettelon tai sanakirjan, jonka sarakkeen nimenä on avain ja sarakkeen muotona arvo.
columns: Tämän avulla voimme hakea vain valitut sarakkeet luettelosta.
chunksize: Kun työskentelet suuren tietojoukon kanssa, osien koko on tärkeä. Se hakee kyselytuloksen pienempinä paloina, mikä parantaa suorituskykyä.

Tässä on esimerkki käytöstä read_sql():

Koodi:

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

lähtö:

 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]

Kun olemme muodostaneet yhteyden tietokantaan, suoritamme kyselyn, joka palauttaa kaikki tietueet Student taulukkoon ja tallentaa ne DataFrameen df. "Roll Number" -sarake muunnetaan indeksiksi käyttämällä index_col parametri, ja "dateOfBirth" -tietotyyppi on "datetime64[ns]", koska parse_dates. Voimme käyttää read_sql() ei vain tietojen hakemiseen, vaan myös muiden toimintojen suorittamiseen, kuten lisäämiseen, poistamiseen ja päivittämiseen. read_sql() on yleinen toiminto.

Ladataan tiettyjä taulukoita tai näkymiä tietokannasta

Tietyn taulukon tai näkymän lataaminen Pandailla read_sql_table() on toinen tekniikka tietojen lukemiseksi tietokannasta Pandas-tietokehykseen.

Mikä on lue_sql_taulukko?

Pandas-kirjasto tarjoaa read_sql_table toiminto, joka on erityisesti suunniteltu lukemaan koko SQL-taulukko ilman kyselyitä ja palauttamaan tuloksen Pandas-tietokehyksenä.

Syntaksi read_sql_table() on kuten alla:

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

Paitsi table_name ja skeema, parametrit selitetään samalla tavalla kuin read_sql().

table_name: Parametri table_name on tietokannan SQL-taulukon nimi.
schema: Tämä valinnainen parametri on taulukon nimen sisältävän skeeman nimi.

Kun yhteys tietokantaan on luotu, käytämme read_sql_table toiminto ladataksesi Student taulukko Pandas DataFrameen.

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

lähtö:

 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

Oletetaan, että se on suuri taulukko, joka voi olla muistiintensiivinen. Tutkitaan kuinka voimme käyttää chunksize parametri tämän ongelman ratkaisemiseksi.

Tutustu käytännönläheiseen, käytännölliseen Gitin oppimisoppaaseemme, jossa on parhaat käytännöt, alan hyväksymät standardit ja mukana tuleva huijauslehti. Lopeta Git-komentojen googlailu ja oikeastaan oppia se!

Koodi:

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

lähtö:

 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

Muista, että chunksize Käytän tässä on 1, koska minulla on vain 3 tietuetta taulukossani.

DB:n kysely suoraan Pandasin SQL-syntaksilla

Tietokannan poimiminen tietokannasta on tärkeä osa data-analyytikoille ja tutkijoille. Tätä varten hyödynnämme read_sql_query() toiminto.

Mikä on read_sql_query()?

Pandan käyttäminen read_sql_query() -toimintoa, voimme suorittaa SQL-kyselyitä ja saada tulokset suoraan DataFrame-kehykseen. The read_sql_query() toiminto on luotu erityisesti SELECT lausunnot. Sitä ei voi käyttää muihin toimintoihin, kuten DELETE or UPDATE.

Syntaksi:

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)

Kaikki parametrien kuvaukset ovat samat kuin read_sql() toiminto. Tässä on esimerkki read_sql_query():

Koodi:

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

lähtö:

 firstName lastName
0 Mark Simson

SQL-tiedostojen kirjoittaminen Pandan kanssa

Oletetaan, että olemme havainneet tietoja analysoidessasi, että muutamia merkintöjä on muokattava tai että tarvitaan uusi taulukko tai näkymä tiedoilla. Yksi tapa on päivittää tai lisätä uusi tietue read_sql() ja kirjoita kysely. Tämä menetelmä voi kuitenkin olla pitkä. Pandat tarjoavat loistavan menetelmän nimeltä to_sql() tällaisiin tilanteisiin.

Tässä osiossa rakennamme ensin uuden taulukon tietokantaan ja muokkaamme sitten olemassa olevaa.

Uuden taulukon luominen SQL-tietokantaan

Ennen kuin luomme uuden taulukon, keskustellaan ensin to_sql() yksityiskohtaisesti.

Mikä on to_sql()?

- to_sql() Pandas-kirjaston toiminnon avulla voimme kirjoittaa tai päivittää tietokantaa. The to_sql() toiminto voi tallentaa DataFrame-tiedot SQL-tietokantaan.

Syntaksi for to_sql():

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

Vain name ja con parametrit ovat pakollisia suoritettavaksi to_sql(); muut parametrit tarjoavat kuitenkin lisää joustavuutta ja mukautusvaihtoehtoja. Keskustelemme jokaisesta parametrista yksityiskohtaisesti:

name: Luotavan tai muutettavan SQL-taulukon nimi.
con: Tietokannan yhteysobjekti.
schema: Taulukon skeema (valinnainen).
if_exists: Tämän parametrin oletusarvo on "fail". Tämän parametrin avulla voimme päättää suoritettavasta toimenpiteestä, jos taulukko on jo olemassa. Vaihtoehtoja ovat "epäonnistuminen", "korvaa" ja "liitä".
index: Indeksiparametri hyväksyy loogisen arvon. Oletuksena sen arvo on True, mikä tarkoittaa, että DataFrame-hakemisto kirjoitetaan SQL-taulukkoon.
index_label: Tämän valinnaisen parametrin avulla voimme määrittää sarakkeen nimen hakemistosarakkeille. Oletuksena indeksi kirjoitetaan taulukkoon, mutta tällä parametrilla voidaan antaa tietty nimi.
chunksize: SQL-tietokantaan kerralla kirjoitettavien rivien määrä.
dtype: Tämä parametri hyväksyy sanakirjan, jossa on avaimet sarakkeiden niminä ja arvot tietotyypeinä.
method: Metodiparametri mahdollistaa tietojen lisäämiseen SQL:ään käytetyn menetelmän määrittämisen. Oletuksena se on asetettu arvoon Ei mitään, mikä tarkoittaa, että pandat löytävät tehokkaimman tavan tietokannan perusteella. Menetelmäparametreille on kaksi päävaihtoehtoa:
- multi: Sen avulla voidaan lisätä useita rivejä yhteen SQL-kyselyyn. Kaikki tietokannat eivät kuitenkaan tue usean rivin lisäystä.
- Kutsuttava toiminto: Täällä voimme kirjoittaa mukautetun funktion insertille ja kutsua sitä menetelmäparametreilla.

Tässä on esimerkki käytöstä 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()

Tietokantaan luodaan uusi taulukko nimeltä Asiakas, jossa on kaksi kenttää nimeltä "Nimi" ja "Ikä".

Tietokannan tilannekuva:

To_sql()-lähtö

Olemassa olevien taulukoiden päivittäminen Pandas-tietokehyksillä

Tietokannan tietojen päivittäminen on monimutkainen tehtävä, etenkin kun käsitellään suuria tietoja. Kuitenkin käyttämällä to_sql() Pandas-toiminto voi tehdä tästä tehtävästä paljon helpompaa. Voit päivittää tietokannassa olevan taulukon to_sql() -toimintoa voidaan käyttää if_exists parametri asetetaan "korvaa". Tämä korvaa olemassa olevan taulukon uusilla tiedoilla.

Tässä on esimerkki to_sql() joka päivittää aiemmin luodun Customer pöytä. Oletetaan, että Customer taulukkoon haluamme päivittää Paul-nimisen asiakkaan iän 9:stä 10:een. Tätä varten voimme ensin muokata vastaavaa riviä DataFramessa ja käyttää sitten to_sql() toiminto tietokannan päivittämiseksi.

Koodi:

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

Tietokannassa Paulin ikä on päivitetty:

To_sql()-lähtö

Yhteenveto

Yhteenvetona voidaan todeta, että Pandas ja SQL ovat molemmat tehokkaita työkaluja tietojen analysointitehtäviin, kuten tietojen lukemiseen ja kirjoittamiseen SQL-tietokantaan. Pandas tarjoaa helpon tavan muodostaa yhteys SQL-tietokantaan, lukea tietoja tietokannasta Pandas-tietokehykseen ja kirjoittaa datakehystiedot takaisin tietokantaan.

Pandas-kirjaston avulla on helppo käsitellä datakehyksessä olevia tietoja, kun taas SQL tarjoaa tehokkaan kielen tietokannan tietojen kyselyyn. Sekä Pandan että SQL:n käyttäminen tietojen lukemiseen ja kirjoittamiseen voi säästää aikaa ja vaivaa tietojen analysointitehtävissä, varsinkin kun data on erittäin suuri. Kaiken kaikkiaan SQL:n ja Pandan hyödyntäminen yhdessä voi auttaa dataanalyytikoita ja tutkijoita virtaviivaistamaan työnkulkuaan.