Ανάγνωση και εγγραφή αρχείων SQL σε Pandas

Αναδημοσίευση από τον Πλάτωνα

Ακολουθούν: 0

Όταν άρχισα να μαθαίνω την Ανάλυση Δεδομένων πριν από μερικά χρόνια, το πρώτο πράγμα που έμαθα ήταν η SQL και τα Pandas. Ως αναλυτής δεδομένων, είναι σημαντικό να έχετε ισχυρά θεμέλια στη συνεργασία με την SQL και τα Panda. Και τα δύο είναι ισχυρά εργαλεία που βοηθούν τους αναλυτές δεδομένων να αναλύουν και να χειρίζονται αποτελεσματικά τα αποθηκευμένα δεδομένα σε βάσεις δεδομένων.

Επισκόπηση της SQL και των Panda

Η SQL (Structured Query Language) είναι μια γλώσσα προγραμματισμού που χρησιμοποιείται για τη διαχείριση και το χειρισμό σχεσιακών βάσεων δεδομένων. Από την άλλη πλευρά, το Pandas είναι μια βιβλιοθήκη Python που χρησιμοποιείται για χειρισμό και ανάλυση δεδομένων.

Η ανάλυση δεδομένων περιλαμβάνει εργασία με μεγάλες ποσότητες δεδομένων και συχνά χρησιμοποιούνται βάσεις δεδομένων για την αποθήκευση αυτών των δεδομένων. Η SQL και τα Panda παρέχουν ισχυρά εργαλεία για εργασία με βάσεις δεδομένων, επιτρέποντας στους αναλυτές δεδομένων να εξάγουν, να χειρίζονται και να αναλύουν αποτελεσματικά δεδομένα. Αξιοποιώντας αυτά τα εργαλεία, οι αναλυτές δεδομένων μπορούν να αποκτήσουν πολύτιμες γνώσεις από δεδομένα που διαφορετικά θα ήταν δύσκολο να αποκτηθούν.

Σε αυτό το άρθρο, θα διερευνήσουμε πώς να χρησιμοποιήσετε SQL και Panda για ανάγνωση και εγγραφή σε μια βάση δεδομένων.

Σύνδεση στο DB

Εγκατάσταση των Βιβλιοθηκών

Πρέπει πρώτα να εγκαταστήσουμε τις απαραίτητες βιβλιοθήκες για να μπορέσουμε να συνδεθούμε στη βάση δεδομένων SQL με το Pandas. Οι δύο κύριες βιβλιοθήκες που απαιτούνται είναι οι Pandas και SQLAlchemy. Το Pandas είναι μια δημοφιλής βιβλιοθήκη χειρισμού δεδομένων που επιτρέπει την αποθήκευση μεγάλων δομών δεδομένων, όπως αναφέρεται στην εισαγωγή. Αντίθετα, το SQLAlchemy παρέχει ένα API για σύνδεση και αλληλεπίδραση με τη βάση δεδομένων SQL.

Μπορούμε να εγκαταστήσουμε και τις δύο βιβλιοθήκες χρησιμοποιώντας τον διαχειριστή πακέτων Python, pip, εκτελώντας τις ακόλουθες εντολές στη γραμμή εντολών.

$ pip install pandas
$ pip install sqlalchemy

Κάνοντας τη σύνδεση

Με τις βιβλιοθήκες εγκατεστημένες, μπορούμε πλέον να χρησιμοποιήσουμε τα Panda για να συνδεθούμε στη βάση δεδομένων SQL.

Αρχικά, θα δημιουργήσουμε ένα αντικείμενο κινητήρα SQLAlchemy με create_engine(). ο create_engine() συνάρτηση συνδέει τον κώδικα Python με τη βάση δεδομένων. Λαμβάνει ως όρισμα μια συμβολοσειρά σύνδεσης που καθορίζει τον τύπο της βάσης δεδομένων και τις λεπτομέρειες σύνδεσης. Σε αυτό το παράδειγμα, θα χρησιμοποιήσουμε τον τύπο βάσης δεδομένων SQLite και τη διαδρομή του αρχείου της βάσης δεδομένων.

Δημιουργήστε ένα αντικείμενο μηχανής για μια βάση δεδομένων SQLite χρησιμοποιώντας το παρακάτω παράδειγμα:

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

Εάν το αρχείο βάσης δεδομένων SQLite, student.db στην περίπτωσή μας, βρίσκεται στον ίδιο κατάλογο με το σενάριο Python, μπορούμε να χρησιμοποιήσουμε απευθείας το όνομα του αρχείου, όπως φαίνεται παρακάτω.

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

Ανάγνωση αρχείων SQL με Pandas

Ας διαβάσουμε δεδομένα τώρα που δημιουργήσαμε μια σύνδεση. Σε αυτή την ενότητα, θα εξετάσουμε το read_sql, read_sql_table, να read_sql_query συναρτήσεις και πώς να τις χρησιμοποιήσετε για να εργαστείτε με μια βάση δεδομένων.

Εκτέλεση ερωτημάτων SQL χρησιμοποιώντας Panda's read_sql() Λειτουργία

Η read_sql() είναι μια συνάρτηση βιβλιοθήκης Pandas που μας επιτρέπει να εκτελέσουμε ένα ερώτημα SQL και να ανακτήσουμε τα αποτελέσματα σε ένα πλαίσιο δεδομένων Pandas. ο read_sql() Η λειτουργία συνδέει SQL και Python, επιτρέποντάς μας να εκμεταλλευτούμε τη δύναμη και των δύο γλωσσών. Η λειτουργία αναδιπλώνεται read_sql_table() και read_sql_query(). ο read_sql() Η συνάρτηση δρομολογείται εσωτερικά με βάση την είσοδο που παρέχεται, πράγμα που σημαίνει ότι εάν η είσοδος πρόκειται να εκτελέσει ένα ερώτημα SQL, θα δρομολογηθεί σε read_sql_query(), και αν πρόκειται για πίνακα βάσης δεδομένων, θα δρομολογηθεί σε read_sql_table().

Η read_sql() η σύνταξη έχει ως εξής:

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

Απαιτούνται παράμετροι SQL και con. τα υπόλοιπα είναι προαιρετικά. Ωστόσο, μπορούμε να χειριστούμε το αποτέλεσμα χρησιμοποιώντας αυτές τις προαιρετικές παραμέτρους. Ας ρίξουμε μια πιο προσεκτική ματιά σε κάθε παράμετρο.

sql: Ερώτημα SQL ή όνομα πίνακα βάσης δεδομένων
con: Αντικείμενο σύνδεσης ή διεύθυνση URL σύνδεσης
index_col: Αυτή η παράμετρος μας επιτρέπει να χρησιμοποιήσουμε μία ή περισσότερες στήλες από το αποτέλεσμα του ερωτήματος SQL ως ευρετήριο πλαισίου δεδομένων. Μπορεί να πάρει είτε μία στήλη είτε μια λίστα στηλών.
coerce_float: Αυτή η παράμετρος καθορίζει εάν οι μη αριθμητικές τιμές πρέπει να μετατραπούν σε κυμαινόμενους αριθμούς ή να αφεθούν ως συμβολοσειρές. Έχει οριστεί σε true από προεπιλογή. Εάν είναι δυνατόν, μετατρέπει μη αριθμητικές τιμές σε τύπους float.
params: Οι παράμετροι παρέχουν μια ασφαλή μέθοδο για τη μετάδοση δυναμικών τιμών στο ερώτημα SQL. Μπορούμε να χρησιμοποιήσουμε την παράμετρο παραμέτρων για να μεταβιβάσουμε ένα λεξικό, πλειάδα ή λίστα. Ανάλογα με τη βάση δεδομένων, η σύνταξη των παραμέτρων ποικίλλει.
parse_dates: Αυτό μας επιτρέπει να καθορίσουμε ποια στήλη στο πλαίσιο δεδομένων που προκύπτει θα ερμηνευτεί ως ημερομηνία. Αποδέχεται μια στήλη, μια λίστα στηλών ή ένα λεξικό με το κλειδί ως όνομα στήλης και την τιμή ως μορφή στήλης.
columns: Αυτό μας επιτρέπει να ανακτούμε μόνο επιλεγμένες στήλες από τη λίστα.
chunksize: Όταν εργάζεστε με ένα μεγάλο σύνολο δεδομένων, το chunksize είναι σημαντικό. Ανακτά το αποτέλεσμα του ερωτήματος σε μικρότερα κομμάτια, βελτιώνοντας την απόδοση.

Ακολουθεί ένα παράδειγμα του τρόπου χρήσης read_sql():

Κώδικας:

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

Παραγωγή:

 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]

Μετά τη σύνδεση στη βάση δεδομένων, εκτελούμε ένα ερώτημα που επιστρέφει όλες τις εγγραφές από το Student πίνακα και τα αποθηκεύει στο DataFrame df. Η στήλη "Roll Number" μετατρέπεται σε ευρετήριο χρησιμοποιώντας το index_col παράμετρος και ο τύπος δεδομένων "dateOfBirth" είναι "datetime64[ns]" λόγω parse_dates. Μπορούμε να χρησιμοποιήσουμε read_sql() όχι μόνο για την ανάκτηση δεδομένων αλλά και για την εκτέλεση άλλων λειτουργιών όπως εισαγωγή, διαγραφή και ενημέρωση. read_sql() είναι μια γενική συνάρτηση.

Φόρτωση συγκεκριμένων πινάκων ή προβολών από το DB

Φόρτωση συγκεκριμένου πίνακα ή προβολής με Pandas read_sql_table() είναι μια άλλη τεχνική για την ανάγνωση δεδομένων από τη βάση δεδομένων σε ένα πλαίσιο δεδομένων Pandas.

Τι είναι read_sql_table?

Η βιβλιοθήκη Pandas παρέχει το read_sql_table συνάρτηση, η οποία έχει σχεδιαστεί ειδικά για να διαβάζει έναν ολόκληρο πίνακα SQL χωρίς να εκτελεί κανένα ερώτημα και να επιστρέφει το αποτέλεσμα ως πλαίσιο δεδομένων Pandas.

Η σύνταξη του read_sql_table() είναι ως εξής:

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

Εκτός από table_name και το σχήμα, οι παράμετροι εξηγούνται με τον ίδιο τρόπο όπως read_sql().

table_name: Η παράμετρος table_name είναι το όνομα του πίνακα SQL στη βάση δεδομένων.
schema: Αυτή η προαιρετική παράμετρος είναι το όνομα του σχήματος που περιέχει το όνομα του πίνακα.

Αφού δημιουργήσουμε μια σύνδεση με τη βάση δεδομένων, θα χρησιμοποιήσουμε το read_sql_table λειτουργία για τη φόρτωση του Student πίνακα σε ένα 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()

Παραγωγή:

 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

Θα υποθέσουμε ότι είναι ένα μεγάλο τραπέζι που μπορεί να είναι έντασης μνήμης. Ας εξερευνήσουμε πώς μπορούμε να χρησιμοποιήσουμε το chunksize παράμετρο για την αντιμετώπιση αυτού του ζητήματος.

Ρίξτε μια ματιά στον πρακτικό μας οδηγό για την εκμάθηση του Git, με βέλτιστες πρακτικές, πρότυπα αποδεκτά από τον κλάδο και συμπεριλαμβανόμενο φύλλο εξαπάτησης. Σταματήστε τις εντολές του Git στο Google και πραγματικά μαθαίνουν το!

Κώδικας:

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

Παραγωγή:

 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

Λάβετε υπόψη σας ότι το chunksize Χρησιμοποιώ εδώ είναι 1 γιατί έχω μόνο 3 εγγραφές στον πίνακα μου.

Απευθείας ερώτηση στο DB με τη σύνταξη SQL του Pandas

Η εξαγωγή πληροφοριών από τη βάση δεδομένων είναι ένα σημαντικό μέρος για τους αναλυτές δεδομένων και τους επιστήμονες. Για να το κάνουμε αυτό, θα αξιοποιήσουμε το read_sql_query() λειτουργία.

Τι είναι το read_sql_query();

Χρήση Pandas' read_sql_query() λειτουργία, μπορούμε να εκτελέσουμε ερωτήματα SQL και να μεταφέρουμε τα αποτελέσματα απευθείας σε ένα DataFrame. ο read_sql_query() η λειτουργία έχει δημιουργηθεί ειδικά για SELECT δηλώσεις. Δεν μπορεί να χρησιμοποιηθεί για άλλες λειτουργίες, όπως π.χ DELETE or UPDATE.

Σύνταξη:

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)

Όλες οι περιγραφές παραμέτρων είναι ίδιες με τις read_sql() λειτουργία. Εδώ είναι ένα παράδειγμα του read_sql_query():

Κώδικας:

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

Παραγωγή:

 firstName lastName
0 Mark Simson

Γράψιμο αρχείων SQL με Pandas

Κατά την ανάλυση δεδομένων, ας υποθέσουμε ότι ανακαλύψαμε ότι μερικές εγγραφές πρέπει να τροποποιηθούν ή ότι απαιτείται νέος πίνακας ή προβολή με τα δεδομένα. Για να ενημερώσετε ή να εισαγάγετε μια νέα εγγραφή, μια μέθοδος είναι να χρησιμοποιήσετε read_sql() και γράψε μια ερώτηση. Ωστόσο, αυτή η μέθοδος μπορεί να είναι μακρά. Τα πάντα παρέχουν μια εξαιρετική μέθοδο που ονομάζεται to_sql() για τέτοιες καταστάσεις.

Σε αυτήν την ενότητα, θα δημιουργήσουμε πρώτα έναν νέο πίνακα στη βάση δεδομένων και στη συνέχεια θα επεξεργαστούμε έναν υπάρχοντα.

Δημιουργία νέου πίνακα στη βάση δεδομένων SQL

Πριν δημιουργήσουμε έναν νέο πίνακα, ας συζητήσουμε πρώτα to_sql() λεπτομερώς.

Τι είναι to_sql()?

Η to_sql() Η λειτουργία της βιβλιοθήκης Pandas μας επιτρέπει να γράψουμε ή να ενημερώσουμε τη βάση δεδομένων. ο to_sql() Η λειτουργία μπορεί να αποθηκεύσει δεδομένα DataFrame σε μια βάση δεδομένων SQL.

Σύνταξη για to_sql():

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

Μόνο name και con οι παράμετροι είναι υποχρεωτικές για εκτέλεση to_sql(); Ωστόσο, άλλες παράμετροι παρέχουν πρόσθετη ευελιξία και επιλογές προσαρμογής. Ας συζητήσουμε αναλυτικά κάθε παράμετρο:

name: Το όνομα του πίνακα SQL που πρόκειται να δημιουργηθεί ή να τροποποιηθεί.
con: Το αντικείμενο σύνδεσης της βάσης δεδομένων.
schema: Το σχήμα του πίνακα (προαιρετικό).
if_exists: Η προεπιλεγμένη τιμή αυτής της παραμέτρου είναι "αποτυχία". Αυτή η παράμετρος μας επιτρέπει να αποφασίσουμε την ενέργεια που θα κάνουμε εάν ο πίνακας υπάρχει ήδη. Οι επιλογές περιλαμβάνουν "αποτυχία", "αντικατάσταση" και "προσάρτηση".
index: Η παράμετρος δείκτης δέχεται μια τιμή boolean. Από προεπιλογή, έχει οριστεί σε True, που σημαίνει ότι το ευρετήριο του DataFrame θα γραφτεί στον πίνακα SQL.
index_label: Αυτή η προαιρετική παράμετρος μας επιτρέπει να καθορίσουμε μια ετικέτα στήλης για τις στήλες ευρετηρίου. Από προεπιλογή, το ευρετήριο γράφεται στον πίνακα, αλλά μπορεί να δοθεί ένα συγκεκριμένο όνομα χρησιμοποιώντας αυτήν την παράμετρο.
chunksize: Ο αριθμός των γραμμών που θα γραφτούν κάθε φορά στη βάση δεδομένων SQL.
dtype: Αυτή η παράμετρος δέχεται ένα λεξικό με κλειδιά ως ονόματα στηλών και τιμές ως τύπους δεδομένων τους.
method: Η παράμετρος μεθόδου επιτρέπει τον καθορισμό της μεθόδου που χρησιμοποιείται για την εισαγωγή δεδομένων στο SQL. Από προεπιλογή, έχει οριστεί σε Κανένα, που σημαίνει ότι τα πάντα θα βρουν τον πιο αποτελεσματικό τρόπο με βάση τη βάση δεδομένων. Υπάρχουν δύο κύριες επιλογές για τις παραμέτρους της μεθόδου:
- multi: Επιτρέπει την εισαγωγή πολλών σειρών σε ένα ερώτημα SQL. Ωστόσο, δεν υποστηρίζουν όλες οι βάσεις δεδομένων εισαγωγή πολλών σειρών.
- Κλήσιμη λειτουργία: Εδώ, μπορούμε να γράψουμε μια προσαρμοσμένη συνάρτηση για εισαγωγή και να την καλέσουμε χρησιμοποιώντας παραμέτρους μεθόδου.

Εδώ είναι ένα παράδειγμα χρήσης 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()

Ένας νέος πίνακας που ονομάζεται Πελάτης δημιουργείται στη βάση δεδομένων, με δύο πεδία που ονομάζονται «Όνομα» και «Ηλικία».

Στιγμιότυπο βάσης δεδομένων:

Έξοδος του to_sql()

Ενημέρωση υπαρχόντων πινάκων με πλαίσια δεδομένων Pandas

Η ενημέρωση δεδομένων σε μια βάση δεδομένων είναι μια πολύπλοκη εργασία, ιδιαίτερα όταν πρόκειται για μεγάλα δεδομένα. Ωστόσο, χρησιμοποιώντας το to_sql() Η λειτουργία στο Pandas μπορεί να κάνει αυτήν την εργασία πολύ πιο εύκολη. Για να ενημερώσετε τον υπάρχοντα πίνακα στη βάση δεδομένων, το to_sql() η λειτουργία μπορεί να χρησιμοποιηθεί με το if_exists η παράμετρος έχει οριστεί σε "αντικατάσταση". Αυτό θα αντικαταστήσει τον υπάρχοντα πίνακα με τα νέα δεδομένα.

Ακολουθεί ένα παράδειγμα to_sql() που ενημερώνει το προηγουμένως δημιουργημένο Customer τραπέζι. Ας υποθέσουμε, στο Customer πίνακα θέλουμε να ενημερώσουμε την ηλικία ενός πελάτη που ονομάζεται Paul από 9 σε 10. Για να το κάνουμε αυτό, μπορούμε πρώτα να τροποποιήσουμε την αντίστοιχη σειρά στο DataFrame και στη συνέχεια να χρησιμοποιήσουμε το to_sql() λειτουργία ενημέρωσης της βάσης δεδομένων.

Κώδικας:

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

Στη βάση δεδομένων, η ηλικία του Paul ενημερώνεται:

Έξοδος του to_sql()

Συμπέρασμα

Συμπερασματικά, τα Pandas και SQL είναι και τα δύο ισχυρά εργαλεία για εργασίες ανάλυσης δεδομένων, όπως η ανάγνωση και η εγγραφή δεδομένων στη βάση δεδομένων SQL. Το Pandas παρέχει έναν εύκολο τρόπο σύνδεσης στη βάση δεδομένων SQL, ανάγνωση δεδομένων από τη βάση δεδομένων σε πλαίσιο δεδομένων Pandas και εγγραφή δεδομένων πλαισίου δεδομένων πίσω στη βάση δεδομένων.

Η βιβλιοθήκη Pandas διευκολύνει τον χειρισμό δεδομένων σε ένα πλαίσιο δεδομένων, ενώ η SQL παρέχει μια ισχυρή γλώσσα για την αναζήτηση δεδομένων σε μια βάση δεδομένων. Η χρήση τόσο των Pandas όσο και της SQL για την ανάγνωση και εγγραφή των δεδομένων μπορεί να εξοικονομήσει χρόνο και προσπάθεια σε εργασίες ανάλυσης δεδομένων, ειδικά όταν τα δεδομένα είναι πολύ μεγάλα. Συνολικά, η μόχλευση SQL και Panda από κοινού μπορεί να βοηθήσει τους αναλυτές δεδομένων και τους επιστήμονες να βελτιώσουν τη ροή εργασίας τους.