SQL-failide lugemine ja kirjutamine Pandas

Taasavaldanud Platon

järgijaid: 0

Kui ma paar aastat tagasi andmeanalüüsi õppima hakkasin, õppisin esimese asjana SQL-i ja Pandasid. Andmeanalüütikuna on väga oluline omada tugevat alust SQL-i ja Pandadega töötamisel. Mõlemad on võimsad tööriistad, mis aitavad andmeanalüütikutel andmebaasides salvestatud andmeid tõhusalt analüüsida ja nendega manipuleerida.

SQL-i ja Pandade ülevaade

SQL (Structured Query Language) on programmeerimiskeel, mida kasutatakse relatsiooniandmebaaside haldamiseks ja manipuleerimiseks. Teisest küljest on Pandas Pythoni teek, mida kasutatakse andmete töötlemiseks ja analüüsimiseks.

Andmeanalüüs hõlmab tööd suurte andmemahtudega ja nende andmete salvestamiseks kasutatakse sageli andmebaase. SQL ja Pandas pakuvad võimsaid tööriistu andmebaasidega töötamiseks, võimaldades andmeanalüütikutel andmeid tõhusalt ekstraheerida, töödelda ja analüüsida. Neid tööriistu kasutades võivad andmeanalüütikud saada väärtuslikku teavet andmetest, mida muidu oleks raske hankida.

Selles artiklis uurime, kuidas kasutada SQL-i ja Pandasid andmebaasi lugemiseks ja sinna kirjutamiseks.

Ühenduse loomine DB-ga

Teekide installimine

Peame esmalt installima vajalikud teegid, enne kui saame Pandaga SQL-andmebaasiga ühenduse luua. Kaks peamist nõutavat teeki on Pandas ja SQLAlchemy. Pandas on populaarne andmetöötlusteek, mis võimaldab salvestada suuri andmestruktuure, nagu sissejuhatuses mainitud. Seevastu SQLAlchemy pakub API-d SQL-i andmebaasiga ühenduse loomiseks ja sellega suhtlemiseks.

Mõlemad teegid saame installida Pythoni paketihalduri pip abil, käivitades käsureal järgmised käsud.

$ pip install pandas
$ pip install sqlalchemy

Ühenduse loomine

Kui teegid on installitud, saame nüüd Pandasid kasutada SQL-i andmebaasiga ühenduse loomiseks.

Alustuseks loome SQLAlchemy mootoriobjekti create_engine(). create_engine() funktsioon ühendab Pythoni koodi andmebaasiga. See võtab argumendina ühendusstringi, mis määrab andmebaasi tüübi ja ühenduse üksikasjad. Selles näites kasutame andmebaasi SQLite tüüpi ja andmebaasi faili teed.

Looge SQLite'i andmebaasi jaoks mootoriobjekt, kasutades allolevat näidet:

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

Kui SQLite'i andmebaasifail, meie puhul student.db, asub Pythoni skriptiga samas kataloogis, saame failinime kasutada otse, nagu allpool näidatud.

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

SQL-failide lugemine Pandaga

Lugeme nüüd andmeid, kui oleme ühenduse loonud. Selles jaotises vaatleme read_sql, read_sql_tableja read_sql_query funktsioonid ja kuidas neid andmebaasiga töötamiseks kasutada.

SQL päringute täitmine Panda abil read_sql() funktsioon

. read_sql() on Pandase teegi funktsioon, mis võimaldab meil täita SQL-päringut ja tuua tulemused Pandase andmeraami. The read_sql() funktsioon ühendab SQL-i ja Pythoni, võimaldades meil ära kasutada mõlema keele võimsust. Funktsioon murrab read_sql_table() ja read_sql_query(). read_sql() funktsioon marsruutitakse sisemiselt antud sisendi põhjal, mis tähendab, et kui sisend peab täitma SQL-päringu, suunatakse see read_sql_query(), ja kui see on andmebaasi tabel, suunatakse see sinna read_sql_table().

. read_sql() süntaks on järgmine:

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

SQL ja con parameetrid on nõutavad; ülejäänud on valikulised. Siiski saame tulemusega manipuleerida, kasutades neid valikulisi parameetreid. Vaatame iga parameetrit lähemalt.

sql: SQL-päringu või andmebaasitabeli nimi
con: Ühenduse objekt või ühenduse URL
index_col: see parameeter võimaldab meil kasutada andmeraami indeksina ühte või mitut SQL-päringu tulemuse veergu. See võib võtta kas ühe veeru või veergude loendi.
coerce_float: see parameeter määrab, kas mittenumbrilised väärtused tuleb teisendada ujuvarvudeks või jätta stringidena. Vaikimisi on see seatud tõeseks. Võimaluse korral teisendab see mittenumbrilised väärtused ujuvtüüpideks.
params: parameetrid pakuvad turvalist meetodit dünaamiliste väärtuste edastamiseks SQL-päringule. Parameetrit params saame kasutada sõnastiku, korteeži või loendi edastamiseks. Sõltuvalt andmebaasist on parameetrite süntaks erinev.
parse_dates: See võimaldab meil määrata, millist veergu tulemuseks olevas andmeraamis tõlgendatakse kuupäevana. See aktsepteerib ühte veergu, veergude loendit või sõnastikku, mille veeru nimeks on võti ja veeru vorminguks väärtus.
columns: see võimaldab meil tuua loendist ainult valitud veerud.
chunksize: suure andmestikuga töötamisel on tükkide suurus oluline. See otsib päringutulemused väiksemate tükkidena, parandades jõudlust.

Siin on näide kasutamise kohta read_sql():

kood:

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

Väljund:

 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]

Pärast andmebaasiga ühenduse loomist käivitame päringu, mis tagastab kõik failist pärinevad kirjed Student tabelis ja salvestab need DataFrame'i df. Veerg „Roll Number” teisendatakse indeksiks, kasutades index_col parameeter ja andmetüüp "dateOfBirth" on "datetime64[ns]", kuna parse_dates. Me saame kasutada read_sql() mitte ainult andmete toomiseks, vaid ka muude toimingute tegemiseks, nagu sisestamine, kustutamine ja värskendamine. read_sql() on üldine funktsioon.

Konkreetsete tabelite või vaadete laadimine andmebaasist

Konkreetse tabeli või vaate laadimine Pandadega read_sql_table() on veel üks meetod andmete lugemiseks andmebaasist Pandase andmeraami.

Mis on loe_sql_tabel?

Panda raamatukogu pakub read_sql_table funktsioon, mis on spetsiaalselt loodud terve SQL-tabeli lugemiseks ilma päringuid täitmata ja tulemuse tagastamiseks Pandase andmeraamina.

Süntaks read_sql_table() on järgmine:

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

Väljaarvatud table_name ja skeemi, parameetreid selgitatakse samamoodi nagu read_sql().

table_name: parameeter table_name on andmebaasis oleva SQL-tabeli nimi.
schema: see valikuline parameeter on tabeli nime sisaldava skeemi nimi.

Pärast andmebaasiga ühenduse loomist kasutame read_sql_table funktsioon laadimiseks Student tabel Panda DataFrame'i.

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

Väljund:

 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

Oletame, et see on suur tabel, mis võib olla mälumahukas. Uurime, kuidas saame kasutada chunksize parameetrit selle probleemi lahendamiseks.

Tutvuge meie praktilise ja praktilise Giti õppimise juhendiga, mis sisaldab parimaid tavasid, tööstusharus aktsepteeritud standardeid ja kaasas olevat petulehte. Lõpetage Giti käskude guugeldamine ja tegelikult õppima seda!

kood:

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

Väljund:

 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

Palun pidage meeles, et chunksize Ma kasutan siin on 1, sest minu tabelis on ainult 3 kirjet.

Päringu esitamine andmebaasist otse Pandase SQL-i süntaksiga

Andmeanalüütikute ja teadlaste jaoks on andmebaasist ülevaate hankimine oluline osa. Selleks võimendame read_sql_query() funktsiooni.

Mis on read_sql_query()?

Pandade kasutamine read_sql_query() funktsiooni, saame käivitada SQL-päringuid ja saada tulemused otse DataFrame'i. The read_sql_query() funktsioon on loodud spetsiaalselt SELECT avaldused. Seda ei saa kasutada muudeks toiminguteks, nt DELETE or UPDATE.

süntaksit:

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)

Kõik parameetrite kirjeldused on samad, mis read_sql() funktsiooni. Siin on näide sellest read_sql_query():

kood:

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

Väljund:

 firstName lastName
0 Mark Simson

SQL-failide kirjutamine Pandadega

Oletame, et andmete analüüsimisel avastasime, et mõnda kirjet tuleb muuta või et andmetega on vaja uut tabelit või vaadet. Uue kirje värskendamiseks või sisestamiseks on üks meetod read_sql() ja kirjutage päring. See meetod võib aga olla pikk. Pandad pakuvad suurepärast meetodit nimega to_sql() selliste olukordade jaoks.

Selles jaotises koostame esmalt andmebaasis uue tabeli ja seejärel muudame olemasolevat.

Uue tabeli loomine SQL-i andmebaasis

Enne uue tabeli loomist arutleme kõigepealt to_sql() üksikasjalikult.

Mis on to_sql()?

. to_sql() Pandase raamatukogu funktsioon võimaldab meil andmebaasi kirjutada või seda värskendada. The to_sql() funktsioon saab salvestada DataFrame'i andmed SQL-i andmebaasi.

Süntaks jaoks to_sql():

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

Ainult name ja con parameetrid on käitamiseks kohustuslikud to_sql(); aga muud parameetrid pakuvad täiendavat paindlikkust ja kohandamisvõimalusi. Vaatleme üksikasjalikult iga parameetrit:

name: loodava või muudetava SQL-tabeli nimi.
con: andmebaasi ühendusobjekt.
schema: tabeli skeem (valikuline).
if_exists: selle parameetri vaikeväärtus on "fail". See parameeter võimaldab meil otsustada, mida teha, kui tabel on juba olemas. Valikud hõlmavad "ebaõnnestumine", "asendamine" ja "lisa".
index: indeksi parameeter aktsepteerib tõeväärtust. Vaikimisi on see seatud väärtusele Tõene, mis tähendab, et DataFrame'i indeks kirjutatakse SQL-i tabelisse.
index_label: see valikuline parameeter võimaldab meil määrata indeksi veergudele veerusildi. Vaikimisi kirjutatakse indeks tabelisse, kuid selle parameetri abil saab anda konkreetse nime.
chunksize: SQL-andmebaasi korraga kirjutatavate ridade arv.
dtype: see parameeter aktsepteerib sõnastikku, mille veergude nimedeks on võtmed ja andmetüüpidena väärtused.
method: meetodi parameeter võimaldab määrata meetodi, mida kasutatakse andmete SQL-i sisestamiseks. Vaikimisi on see seatud väärtusele Puudub, mis tähendab, et pandad leiavad andmebaasi põhjal kõige tõhusama viisi. Meetodi parameetrite jaoks on kaks peamist valikut:
- multi: võimaldab sisestada ühte SQL-päringusse mitu rida. Kuid mitte kõik andmebaasid ei toeta mitmerealist lisamist.
- Helistatav funktsioon: Siin saame sisestamiseks kirjutada kohandatud funktsiooni ja kutsuda seda meetodi parameetrite abil.

Siin on näide kasutamisest 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()

Andmebaasi luuakse uus tabel Klient, millel on kaks välja nimega "Nimi" ja "Vanus".

Andmebaasi hetktõmmis:

To_sql() väljund

Olemasolevate tabelite värskendamine Panda andmeraamidega

Andmete värskendamine andmebaasis on keeruline ülesanne, eriti kui tegemist on suurte andmetega. Kasutades aga to_sql() funktsioon Pandas võib selle ülesande palju lihtsamaks teha. Andmebaasis olemasoleva tabeli värskendamiseks to_sql() funktsiooni saab kasutada koos if_exists parameeter seatud väärtusele "asendada". See kirjutab olemasoleva tabeli uute andmetega üle.

Siin on näide sellest to_sql() mis värskendab varem loodud Customer laud. Oletame, et Customer Tabelis soovime muuta Pauli-nimelise kliendi vanuse 9-lt 10-le. Selleks saame esmalt muuta DataFrame'i vastavat rida ja seejärel kasutada to_sql() funktsioon andmebaasi värskendamiseks.

kood:

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

Andmebaasis uuendatakse Pauli vanust:

To_sql() väljund

Järeldus

Kokkuvõtteks võib öelda, et Pandas ja SQL on mõlemad võimsad tööriistad andmeanalüüsi ülesannete jaoks, nagu andmete lugemine ja kirjutamine SQL-i andmebaasi. Pandas pakub lihtsat viisi SQL-andmebaasiga ühenduse loomiseks, andmete lugemiseks andmebaasist Panda andmekaadrisse ja andmeraami andmete andmebaasi tagasi kirjutamiseks.

Pandase teek muudab andmeraamis andmetega manipuleerimise lihtsaks, samas kui SQL pakub võimsat keelt andmebaasis olevate andmete päringute tegemiseks. Nii Pandade kui ka SQL-i kasutamine andmete lugemiseks ja kirjutamiseks võib säästa andmeanalüüsi ülesannetes aega ja vaeva, eriti kui andmed on väga suured. Üldiselt võib SQL-i ja Pandade koos kasutamine aidata andmeanalüütikutel ja teadlastel oma töövoogu sujuvamaks muuta.