Membaca Dan Menulis File SQL Di Pandas

Diterbitkan Ulang Oleh Plato

Followers: 0

Ketika saya mulai belajar Analisis Data beberapa tahun yang lalu, hal pertama yang saya pelajari adalah SQL dan Panda. Sebagai seorang analis data, sangat penting untuk memiliki dasar yang kuat dalam bekerja dengan SQL dan Panda. Keduanya adalah alat canggih yang membantu analis data menganalisis dan memanipulasi data yang tersimpan dalam database secara efisien.

Tinjauan tentang SQL dan Panda

SQL (Structured Query Language) adalah bahasa pemrograman yang digunakan untuk mengelola dan memanipulasi basis data relasional. Di sisi lain, Pandas adalah pustaka Python yang digunakan untuk manipulasi dan analisis data.

Analisis data melibatkan bekerja dengan data dalam jumlah besar, dan database sering digunakan untuk menyimpan data ini. SQL dan Panda menyediakan alat canggih untuk bekerja dengan basis data, memungkinkan analis data mengekstrak, memanipulasi, dan menganalisis data secara efisien. Dengan memanfaatkan alat ini, analis data dapat memperoleh wawasan berharga dari data yang sebelumnya sulit diperoleh.

Pada artikel ini, kita akan mengeksplorasi bagaimana menggunakan SQL dan Panda untuk membaca dan menulis ke database.

Menghubungkan ke DB

Menginstal Perpustakaan

Pertama-tama kita harus menginstal pustaka yang diperlukan sebelum kita dapat terhubung ke database SQL dengan Pandas. Dua pustaka utama yang dibutuhkan adalah Pandas dan SQLAlchemy. Pandas adalah perpustakaan manipulasi data populer yang memungkinkan penyimpanan struktur data besar, seperti yang disebutkan dalam pendahuluan. Sebaliknya, SQLAlchemy menyediakan API untuk menghubungkan dan berinteraksi dengan database SQL.

Kita dapat menginstal kedua pustaka menggunakan pengelola paket Python, pip, dengan menjalankan perintah berikut di command prompt.

$ pip install pandas
$ pip install sqlalchemy

Membuat Koneksi

Dengan library yang terinstal, kita sekarang dapat menggunakan Pandas untuk terhubung ke database SQL.

Untuk memulai, kita akan membuat objek mesin SQLAlchemy dengan create_engine(). itu create_engine() fungsi menghubungkan kode Python ke database. Dibutuhkan sebagai argumen string koneksi yang menentukan tipe database dan detail koneksi. Dalam contoh ini, kita akan menggunakan tipe database SQLite dan jalur file database.

Buat objek mesin untuk database SQLite menggunakan contoh di bawah ini:

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

Jika file database SQLite, student.db dalam kasus kita, berada di direktori yang sama dengan skrip Python, kita dapat menggunakan nama file secara langsung, seperti yang ditunjukkan di bawah ini.

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

Membaca File SQL dengan Panda

Mari membaca data sekarang setelah kita membuat koneksi. Pada bagian ini, kita akan melihat pada read_sql, read_sql_table, dan read_sql_query fungsi dan cara menggunakannya untuk bekerja dengan database.

Mengeksekusi SQL Query menggunakan Panda baca_sql() fungsi

Grafik read_sql() adalah fungsi perpustakaan Pandas yang memungkinkan kita untuk mengeksekusi kueri SQL dan mengambil hasilnya ke dalam kerangka data Pandas. Itu read_sql() fungsi menghubungkan SQL dan Python, memungkinkan kita memanfaatkan kekuatan kedua bahasa. Fungsi membungkus read_sql_table() dan read_sql_query(). itu read_sql() fungsi dirutekan secara internal berdasarkan input yang diberikan, yang berarti bahwa jika input tersebut adalah untuk mengeksekusi kueri SQL, fungsi tersebut akan dirutekan ke read_sql_query(), dan jika itu adalah tabel database, itu akan dialihkan ke read_sql_table().

Grafik read_sql() sintaks adalah sebagai berikut:

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

Parameter SQL dan con diperlukan; sisanya opsional. Namun, kami dapat memanipulasi hasilnya menggunakan parameter opsional ini. Mari kita lihat lebih dekat setiap parameter.

sql: kueri SQL atau nama tabel basis data
con: Objek koneksi atau URL koneksi
index_col: Parameter ini memungkinkan kita untuk menggunakan satu atau lebih kolom dari hasil kueri SQL sebagai indeks kerangka data. Itu bisa berupa satu kolom atau daftar kolom.
coerce_float: Parameter ini menentukan apakah nilai non-numerik harus dikonversi ke angka mengambang atau dibiarkan sebagai string. Ini diatur ke true secara default. Jika memungkinkan, ini mengubah nilai non-numerik menjadi tipe float.
params: Params menyediakan metode aman untuk meneruskan nilai dinamis ke kueri SQL. Kita dapat menggunakan parameter params untuk mengirimkan kamus, tupel, atau daftar. Bergantung pada database, sintaks params bervariasi.
parse_dates: Hal ini memungkinkan kita menentukan kolom mana dalam kerangka data yang dihasilkan yang akan ditafsirkan sebagai tanggal. Itu menerima satu kolom, daftar kolom, atau kamus dengan kunci sebagai nama kolom dan nilai sebagai format kolom.
columns: Ini memungkinkan kita untuk mengambil hanya kolom yang dipilih dari daftar.
chunksize: Saat bekerja dengan kumpulan data besar, chunksize penting. Itu mengambil hasil kueri dalam potongan yang lebih kecil, meningkatkan kinerja.

Berikut contoh cara menggunakan read_sql():

Kode:

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

Keluaran:

 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]

Setelah tersambung ke database, kami menjalankan kueri yang mengembalikan semua catatan dari Student tabel dan menyimpannya di DataFrame df. Kolom "Nomor Gulungan" diubah menjadi indeks menggunakan index_col parameter, dan tipe data "dateOfBirth" adalah "datetime64[ns]" karena parse_dates. Kita bisa menggunakan read_sql() tidak hanya untuk mengambil data tetapi juga untuk melakukan operasi lain seperti insert, delete, dan update. read_sql() adalah fungsi generik.

Memuat Tabel atau Tampilan Tertentu dari DB

Memuat tabel atau tampilan tertentu dengan Panda read_sql_table() adalah teknik lain untuk membaca data dari database ke dalam kerangka data Pandas.

Apa itu baca_sql_tabel?

Perpustakaan Pandas menyediakan read_sql_table fungsi, yang dirancang khusus untuk membaca seluruh tabel SQL tanpa menjalankan kueri apa pun dan mengembalikan hasilnya sebagai kerangka data Pandas.

Sintaks dari read_sql_table() adalah seperti di bawah ini:

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

Kecuali untuk table_name dan skema, parameternya dijelaskan dengan cara yang sama seperti read_sql().

table_name: parameternya table_name adalah nama tabel SQL dalam database.
schema: Parameter opsional ini adalah nama skema yang berisi nama tabel.

Setelah membuat koneksi ke database, kita akan menggunakan read_sql_table berfungsi untuk memuat Student tabel ke dalam 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()

Keluaran:

 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

Kami akan menganggap itu adalah tabel besar yang bisa memakan banyak memori. Mari jelajahi bagaimana kita dapat menggunakan chunksize parameter untuk mengatasi masalah ini.

Lihat panduan praktis dan praktis kami untuk mempelajari Git, dengan praktik terbaik, standar yang diterima industri, dan termasuk lembar contekan. Hentikan perintah Googling Git dan sebenarnya belajar itu!

Kode:

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

Keluaran:

 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

Harap diingat bahwa chunksize Saya menggunakan di sini adalah 1 karena saya hanya memiliki 3 catatan di meja saya.

Meminta DB Secara Langsung dengan Sintaks SQL Pandas

Mengekstraksi wawasan dari database adalah bagian penting bagi analis data dan ilmuwan. Untuk melakukannya, kami akan memanfaatkan read_sql_query() fungsi.

Apa itu read_sql_query()?

Menggunakan Panda read_sql_query() fungsi, kita dapat menjalankan kueri SQL dan mendapatkan hasilnya langsung ke dalam DataFrame. Itu read_sql_query() fungsi dibuat khusus untuk SELECT pernyataan. Itu tidak dapat digunakan untuk operasi lain, seperti DELETE or UPDATE.

sintaks:

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)

Semua deskripsi parameter sama dengan read_sql() fungsi. Berikut adalah contoh dari read_sql_query():

Kode:

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

Keluaran:

 firstName lastName
0 Mark Simson

Menulis File SQL dengan Panda

Saat menganalisis data, misalkan kami menemukan bahwa beberapa entri perlu dimodifikasi atau diperlukan tabel atau tampilan baru dengan data tersebut. Untuk memperbarui atau menyisipkan record baru, salah satu caranya adalah dengan menggunakan read_sql() dan menulis kueri. Namun, metode itu bisa panjang. Panda menyediakan metode hebat yang disebut to_sql() untuk situasi seperti ini.

Di bagian ini, pertama-tama kita akan membuat tabel baru di database dan kemudian mengedit yang sudah ada.

Membuat Tabel Baru di Database SQL

Sebelum kita membuat tabel baru, mari kita bahas terlebih dahulu to_sql() secara terperinci.

Apa itu ke_sql()?

Grafik to_sql() fungsi library Pandas memungkinkan kita untuk menulis atau mengupdate database. Itu to_sql() fungsi dapat menyimpan data DataFrame ke database SQL.

Sintaks untuk to_sql():

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

Hanya name dan con parameter wajib dijalankan to_sql(); namun, parameter lain memberikan fleksibilitas tambahan dan opsi penyesuaian. Mari kita bahas setiap parameter secara mendetail:

name: Nama tabel SQL yang akan dibuat atau diubah.
con: Objek koneksi dari database.
schema: Skema tabel (opsional).
if_exists: Nilai default dari parameter ini adalah “gagal”. Parameter ini memungkinkan kita untuk memutuskan tindakan yang akan diambil jika tabel sudah ada. Opsi termasuk "gagal", "ganti", dan "tambahkan".
index: Parameter indeks menerima nilai boolean. Secara default, disetel ke True, artinya indeks DataFrame akan ditulis ke tabel SQL.
index_label: Parameter opsional ini memungkinkan kita menentukan label kolom untuk kolom indeks. Secara default, indeks ditulis ke tabel, tetapi nama tertentu dapat diberikan menggunakan parameter ini.
chunksize: Jumlah baris yang akan ditulis sekaligus dalam database SQL.
dtype: Parameter ini menerima kamus dengan kunci sebagai nama kolom dan nilai sebagai tipe datanya.
method: Parameter metode memungkinkan menentukan metode yang digunakan untuk memasukkan data ke dalam SQL. Secara default, ini diatur ke Tidak Ada, yang berarti panda akan menemukan cara paling efisien berdasarkan basis data. Ada dua opsi utama untuk parameter metode:
- multi: Memungkinkan penyisipan beberapa baris dalam satu kueri SQL. Namun, tidak semua database mendukung penyisipan multi-baris.
- Fungsi yang dapat dipanggil: Di sini, kita dapat menulis fungsi khusus untuk disisipkan dan memanggilnya menggunakan parameter metode.

Berikut adalah contoh menggunakan 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()

Tabel baru bernama Pelanggan dibuat di database, dengan dua bidang bernama "Nama" dan "Umur".

Cuplikan basis data:

Keluaran to_sql()

Memperbarui Tabel yang Ada dengan Pandas Dataframes

Memperbarui data dalam database adalah tugas yang kompleks, terutama ketika berhadapan dengan data besar. Namun, menggunakan to_sql() fungsi di Panda dapat membuat tugas ini lebih mudah. Untuk memperbarui tabel yang ada di database, the to_sql() fungsi dapat digunakan dengan if_exists parameter diatur ke "ganti". Ini akan menimpa tabel yang ada dengan data baru.

Ini adalah contoh dari to_sql() yang memperbarui yang dibuat sebelumnya Customer meja. Misalkan, di Customer tabel kami ingin memperbarui usia pelanggan bernama Paul dari 9 menjadi 10. Untuk melakukannya, pertama, kami dapat memodifikasi baris yang sesuai di DataFrame, lalu menggunakan to_sql() berfungsi untuk memperbarui database.

Kode:

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

Dalam database, usia Paul diperbarui:

Keluaran to_sql()

Kesimpulan

Kesimpulannya, Panda dan SQL adalah alat yang ampuh untuk tugas analisis data seperti membaca dan menulis data ke database SQL. Pandas menyediakan cara mudah untuk terhubung ke database SQL, membaca data dari database ke dalam bingkai data Pandas, dan menulis data kerangka data kembali ke database.

Pustaka Pandas memudahkan untuk memanipulasi data dalam kerangka data, sedangkan SQL menyediakan bahasa yang kuat untuk menanyakan data dalam database. Menggunakan Pandas dan SQL untuk membaca dan menulis data dapat menghemat waktu dan tenaga dalam tugas analisis data, terutama bila datanya sangat besar. Secara keseluruhan, memanfaatkan SQL dan Panda bersama-sama dapat membantu analis data dan ilmuwan merampingkan alur kerja mereka.