SQL-fájlok olvasása és írása Pandasban

Amikor néhány évvel ezelőtt elkezdtem tanulni az adatelemzést, az első dolog, amit megtanultam, az SQL és a Panda volt. Adatelemzőként kulcsfontosságú, hogy erős alapokkal rendelkezzen az SQL-lel és a Pandákkal való együttműködésben. Mindkettő hatékony eszköz, amely segíti az adatelemzőket az adatbázisokban tárolt adatok hatékony elemzésében és kezelésében.

Az SQL és a Pandák áttekintése

Az SQL (Structured Query Language) egy programozási nyelv, amelyet relációs adatbázisok kezelésére és manipulálására használnak. Másrészt a Pandas egy Python-könyvtár, amelyet adatok manipulálására és elemzésére használnak.

Az adatelemzés nagy mennyiségű adattal való munkavégzést jelent, és gyakran adatbázisokat használnak ezen adatok tárolására. Az SQL és a Pandas hatékony eszközöket biztosítanak az adatbázisokkal való munkához, lehetővé téve az adatelemzők számára az adatok hatékony kinyerését, kezelését és elemzését. Ezen eszközök kihasználásával az adatelemzők értékes betekintést nyerhetnek olyan adatokból, amelyeket egyébként nehéz lenne megszerezni.

Ebben a cikkben megvizsgáljuk, hogyan használhatjuk az SQL-t és a Pandákat adatbázisok olvasására és írására.

Csatlakozás a DB-hez

A könyvtárak telepítése

Először telepítenünk kell a szükséges könyvtárakat, mielőtt a Pandával kapcsolódhatnánk az SQL adatbázishoz. A két fő szükséges könyvtár a Pandas és az SQLAlchemy. A Pandas egy népszerű adatkezelési könyvtár, amely nagy adatstruktúrák tárolását teszi lehetővé, ahogy azt a bevezetőben is említettük. Ezzel szemben az SQLAlchemy API-t biztosít az SQL-adatbázishoz való csatlakozáshoz és az azzal való interakcióhoz.

Mindkét könyvtárat telepíthetjük a Python csomagkezelő, pip segítségével, a következő parancsok futtatásával a parancssorban.

$ pip install pandas
$ pip install sqlalchemy

A kapcsolat létrehozása

A telepített könyvtárak után a Pandas segítségével csatlakozhatunk az SQL-adatbázishoz.

Kezdésként létrehozunk egy SQLAlchemy motorobjektumot a következővel create_engine() Az create_engine() függvény összekapcsolja a Python kódot az adatbázissal. Argumentumként egy kapcsolati karakterláncot vesz fel, amely megadja az adatbázis típusát és a kapcsolat részleteit. Ebben a példában az SQLite adatbázistípust és az adatbázisfájl elérési útját fogjuk használni.

Hozzon létre egy motorobjektumot egy SQLite adatbázishoz az alábbi példa segítségével:

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

Ha az SQLite adatbázis-fájl, esetünkben a student.db, ugyanabban a könyvtárban van, mint a Python-szkript, akkor közvetlenül használhatjuk a fájlnevet, az alábbiak szerint.

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

SQL-fájlok olvasása Pandákkal

Olvassunk adatokat most, miután létrehoztuk a kapcsolatot. Ebben a részben megvizsgáljuk a read_sql, read_sql_tableés read_sql_query funkciókat, és hogyan használhatjuk őket az adatbázisokkal való munkához.

SQL lekérdezések végrehajtása a Panda segítségével read_sql() Funkció

A read_sql() egy Pandas könyvtár funkció, amely lehetővé teszi számunkra, hogy SQL lekérdezést hajtsunk végre, és az eredményeket lekérjük egy Pandas adatkeretbe. A read_sql() függvény összekapcsolja az SQL-t és a Pythont, lehetővé téve számunkra, hogy mindkét nyelv erejét kihasználjuk. A függvény tördeli read_sql_table() és a read_sql_query() Az read_sql() függvény belső irányítása a megadott bemenet alapján történik, ami azt jelenti, hogy ha a bemenet SQL lekérdezést hajt végre, akkor az read_sql_query(), és ha ez egy adatbázistábla, akkor a rendszer ide lesz irányítva read_sql_table().

A read_sql() szintaxisa a következő:

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

SQL és con paraméterek szükségesek; a többi nem kötelező. Azonban ezekkel az opcionális paraméterekkel manipulálhatjuk az eredményt. Nézzük meg közelebbről az egyes paramétereket.

• sql: SQL lekérdezés vagy adatbázistábla neve
• con: Csatlakozási objektum vagy kapcsolat URL-je
• index_col: Ez a paraméter lehetővé teszi, hogy az SQL lekérdezés eredményéből egy vagy több oszlopot használjunk adatkeretindexként. Egy oszlopot vagy oszloplistát is foglalhat.
• coerce_float: Ez a paraméter határozza meg, hogy a nem numerikus értékeket lebegő számokká kell-e konvertálni, vagy karakterláncként kell hagyni. Alapértelmezés szerint igazra van állítva. Ha lehetséges, a nem numerikus értékeket lebegő típusokká alakítja.
• params: A paraméterek biztonságos módszert biztosítanak a dinamikus értékek SQL-lekérdezésnek való átadásához. A params paramétert használhatjuk szótár, sor vagy lista átadására. Az adatbázistól függően a paraméterek szintaxisa eltérő.
• parse_dates: Ezzel megadhatjuk, hogy az eredményül kapott adatkeret melyik oszlopa legyen dátumként értelmezve. Egyetlen oszlopot, oszloplistát vagy szótárt fogad el, amelynek oszlopnévként a kulcs, oszlopformátumként pedig az érték.
• columns: Ez lehetővé teszi, hogy csak a kiválasztott oszlopokat töltsük le a listából.
• chunksize: Ha nagy adatkészlettel dolgozik, a darabok mérete fontos. Kisebb darabokban kéri le a lekérdezés eredményét, javítva a teljesítményt.

Íme egy példa a használatára read_sql():

Kód:

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

output:

 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]

Az adatbázishoz való csatlakozás után egy lekérdezést hajtunk végre, amely az összes rekordot visszaadja a Student táblázatot, és a DataFrame-ben tárolja df. A „Tekercsszám” oszlop indexké alakul a index_col paramétert, a „dateOfBirth” adattípus pedig „datetime64[ns]”, mivel parse_dates. Tudjuk használni read_sql() nem csak az adatok lekérésére, hanem egyéb műveletek végrehajtására is, például beszúrásra, törlésre és frissítésre. read_sql() egy általános függvény.

Adott táblák vagy nézetek betöltése az adatbázisból

Egy adott táblázat vagy nézet betöltése Pandákkal read_sql_table() egy másik technika az adatok beolvasására az adatbázisból egy Pandas adatkeretbe.

Mi read_sql_table?

A Pandas könyvtár biztosítja a read_sql_table függvény, amely kifejezetten egy teljes SQL-tábla beolvasására szolgál lekérdezések végrehajtása nélkül, és az eredményt Pandas adatkeretként adja vissza.

A szintaxisa read_sql_table() az alábbiak szerint van:

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

Kivéve table_name és séma, a paraméterek magyarázata ugyanúgy történik, mint read_sql().

• table_name: A paraméter table_name az SQL tábla neve az adatbázisban.
• schema: Ez az opcionális paraméter a tábla nevét tartalmazó séma neve.

Az adatbázishoz való kapcsolat létrehozása után a read_sql_table funkció betöltéséhez Student táblázatot egy Pandas DataFrame-be.

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

output:

 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

Tételezzük fel, hogy ez egy nagy tábla, amely memóriaigényes lehet. Vizsgáljuk meg, hogyan használhatjuk fel a chunksize paramétert a probléma megoldásához.

Kód:

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

output:

 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

Kérjük, ne feledje, hogy a chunksize Itt az 1-et használom, mert csak 3 rekord van a táblázatomban.

A DB közvetlen lekérdezése a Pandas SQL szintaxisával

Az adatelemzők és tudósok fontos része az adatbázisból származó információk kinyerése. Ennek érdekében kihasználjuk a read_sql_query() funkciót.

Mi az a read_sql_query()?

Pandák használata read_sql_query() függvény segítségével SQL lekérdezéseket futtathatunk, és az eredményeket közvetlenül egy DataFrame-be kaphatjuk. A read_sql_query() funkció kifejezetten erre lett létrehozva SELECT nyilatkozatok. Nem használható semmilyen más műveletre, mint pl DELETE or UPDATE.

Syntax:

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)

Minden paraméter leírása megegyezik a read_sql() funkció. Íme egy példa arra read_sql_query():

Kód:

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

output:

 firstName lastName
0 Mark Simson

SQL fájlok írása Pandákkal

Az adatok elemzése során tegyük fel, hogy felfedeztük, hogy néhány bejegyzést módosítani kell, vagy új táblázatra vagy nézetre van szükség az adatokkal. Az egyik módszer a rekord frissítése vagy beszúrása read_sql() és írjon lekérdezést. Ez a módszer azonban hosszadalmas lehet. A pandák nagyszerű módszert kínálnak az úgynevezett to_sql() az ehhez hasonló helyzetekre.

Ebben a részben először egy új táblát építünk az adatbázisban, majd szerkesztünk egy meglévőt.

Új tábla létrehozása az SQL-adatbázisban

Mielőtt új táblázatot hoznánk létre, először beszéljük meg to_sql() részletesen.

Mi to_sql()?

A to_sql() A Pandas könyvtár funkciója lehetővé teszi az adatbázis írását vagy frissítését. A to_sql() függvény a DataFrame adatokat SQL adatbázisba mentheti.

Szintaxis a számára to_sql():

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

Csak name és a con paraméterek futtatása kötelező to_sql(); más paraméterek azonban további rugalmasságot és testreszabási lehetőségeket biztosítanak. Vizsgáljuk meg részletesen az egyes paramétereket:

• name: A létrehozandó vagy módosítandó SQL-tábla neve.
• con: Az adatbázis kapcsolati objektuma.
• schema: A táblázat sémája (opcionális).
• if_exists: Ennek a paraméternek az alapértelmezett értéke „sikertelen”. Ez a paraméter lehetővé teszi számunkra, hogy eldöntsük, mi a teendő, ha a tábla már létezik. A lehetőségek közé tartozik a „sikertelen”, „csere” és „hozzáfűzés”.
• index: Az index paraméter logikai értéket fogad el. Alapértelmezés szerint True értékre van állítva, ami azt jelenti, hogy a DataFrame indexe be lesz írva az SQL táblába.
• index_label: Ez az opcionális paraméter lehetővé teszi, hogy oszlopcímkét adjunk meg az indexoszlopokhoz. Alapértelmezés szerint az index a táblába van írva, de ezzel a paraméterrel konkrét név adható.
• chunksize: Az SQL adatbázisban egyszerre írandó sorok száma.
• dtype: Ez a paraméter olyan szótárakat fogad el, amelyek oszlopnévként kulcsokat, adattípusként pedig értékeket tartalmaznak.
• method: A metódus paraméter lehetővé teszi az adatok SQL-be ​​történő beillesztéséhez használt metódus megadását. Alapértelmezés szerint None értékre van állítva, ami azt jelenti, hogy a pandák megtalálják a leghatékonyabb módszert az adatbázis alapján. A módszer paramétereinek két fő lehetősége van:
  • multi: Lehetővé teszi több sor beszúrását egyetlen SQL lekérdezésbe. Azonban nem minden adatbázis támogatja a többsoros beszúrást.
  • Hívható funkció: Itt írhatunk egy egyedi függvényt az insert-hez, és metódusparaméterek segítségével hívhatjuk meg.

Íme egy példa a használatára 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()

Az adatbázisban létrejön egy Ügyfél nevű új tábla, két mezővel: „Név” és „Kor”.

Adatbázis pillanatkép:

Meglévő táblák frissítése Pandas adatkeretekkel

Az adatbázisban lévő adatok frissítése összetett feladat, különösen nagy mennyiségű adat kezelésekor. Azonban a to_sql() funkció a Pandasban sokkal könnyebbé teheti ezt a feladatot. Az adatbázisban meglévő tábla frissítéséhez a to_sql() funkció használható a if_exists paraméter beállítása „csere”. Ez felülírja a meglévő táblát az új adatokkal.

Itt van egy példa erre to_sql() amely frissíti a korábban létrehozott Customer asztal. Tegyük fel, hogy a Customer táblázatban szeretnénk frissíteni egy Paul nevű ügyfél életkorát 9-ről 10-re. Ehhez először módosíthatjuk a megfelelő sort a DataFrame-ben, majd használhatjuk a to_sql() funkciót az adatbázis frissítéséhez.

Kód:

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

Az adatbázisban Paul életkora frissül:

Következtetés

Összefoglalva, a Pandas és az SQL egyaránt hatékony eszköz az adatelemzési feladatokhoz, mint például az adatok SQL-adatbázisba való beolvasása és írása. A Pandas egyszerű módot kínál az SQL-adatbázishoz való csatlakozásra, az adatbázisból az adatok Pandas adatkeretbe történő beolvasására és az adatkeret adatok visszaírására az adatbázisba.

A Pandas könyvtár megkönnyíti az adatkeretben lévő adatok kezelését, míg az SQL hatékony nyelvet biztosít az adatbázisban lévő adatok lekérdezéséhez. Ha a Pandákat és az SQL-t is használja az adatok olvasására és írására, időt és erőfeszítést takaríthat meg az adatelemzési feladatok során, különösen akkor, ha az adatok nagyon nagyok. Összességében az SQL és a Pandák együttes kihasználása segíthet az adatelemzőknek és a tudósoknak a munkafolyamat egyszerűsítésében.

• SEO által támogatott tartalom és PR terjesztés. Erősödjön még ma.
• PlatoAiStream. Web3 adatintelligencia. Felerősített tudás. Hozzáférés itt.
• A jövő pénzverése – Adryenn Ashley. Hozzáférés itt.
• Részvények vásárlása és eladása PRE-IPO társaságokban a PREIPO® segítségével. Hozzáférés itt.
• Forrás: https://stackabuse.com/reading-and-writing-sql-files-in-pandas/