Tutorial SQLAlchemy dengan Contoh

Instal SQLAlchemy

Cukup mudah memasang paket dan mulai menulis kode. 

Anda dapat memasang SQLAlchemy menggunakan Python Package Manager (pip):

pip install sqlalchemy

Jika Anda menggunakan distribusi Anaconda untuk Python, coba masukkan perintah di terminal conda:

conda install -c anaconda sqlalchemy

Mari periksa apakah paket berhasil dipasang:

>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.41'

Bagus, kita berhasil memasang SQLAlchemy versi 1.4.41. 

Mulai

Pada bagian ini, kita akan belajar menghubungkan basis data SQLite, membuat objek tabel, dan menggunakannya untuk menjalankan kueri SQL. 

Menghubungkan basis data

Kita akan menggunakan basis data SQLite European Football dari Kaggle, dan basis data ini memiliki dua tabel: divisions dan matchs.

Pertama, kita akan membuat objek engine SQLite menggunakan create_object dan meneruskan alamat lokasi basis data. Lalu, kita akan membuat objek koneksi dengan menghubungkan engine tersebut. Kita akan menggunakan objek conn untuk menjalankan semua jenis kueri SQL.

from sqlalchemy as db
engine = db.create_engine("sqlite:///european_database.sqlite")

conn = engine.connect() 

Jika Anda ingin menghubungkan basis data PostgreSQL, MySQL, Oracle, dan Microsoft SQL Server, lihat konfigurasi engine untuk konektivitas yang lancar ke server. 

Tutorial SQLAlchemy ini berasumsi bahwa Anda memahami dasar-dasar Python dan SQL. Jika belum, itu tidak masalah. Anda dapat mengikuti jalur keterampilan SQL Fundamentals dan Python Fundamentals untuk membangun dasar yang kuat.                                   

Mengakses tabel

Untuk membuat objek tabel, kita perlu menyediakan nama tabel dan metadata. Anda dapat menghasilkan metadata menggunakan fungsi MetaData() milik SQLAlchemy. 

metadata = db.MetaData() #extracting the metadata
division= db.Table('divisions', metadata, autoload=True, 
autoload_with=engine) #Table object

Mari cetak metadata divisions. 

print(repr(metadata.tables['divisions']))

Metadata memuat nama tabel, nama kolom beserta tipenya, dan skema. 

Table('divisions', MetaData(), Column('division', TEXT(), table=<divisions>), 
Column('name', TEXT(), table=<divisions>), Column('country', TEXT(), 
table=<divisions>), schema=None)

Mari gunakan objek tabel division untuk mencetak nama kolom. 

print(division.columns.keys())

Tabel tersebut terdiri dari kolom division, name, dan country. 

['division', 'name', 'country']

Kueri SQL sederhana

Sekarang bagian yang menyenangkan. Kita akan menggunakan objek tabel untuk menjalankan kueri dan mengekstrak hasilnya. 

Pada kode di bawah, kita memilih semua kolom untuk tabel division. 

query = division.select() #SELECT * FROM divisions
print(query)

Catatan: Anda juga dapat menulis perintah select sebagai db.select([division]).

Untuk melihat kuerinya, cetak objek kueri, dan itu akan menampilkan perintah SQL. 

SELECT divisions.division, divisions.name, divisions.country
FROM divisions

Hasil kueri SQL

Sekarang kita akan mengeksekusi kueri menggunakan objek koneksi dan mengekstrak lima baris pertama. 

• fetchone(): mengekstrak satu baris setiap kali.
• fetchmany(n): mengekstrak n baris setiap kali.
• fetchall(): mengekstrak semua baris.  

exe = conn.execute(query) #executing the query
result = exe.fetchmany(5) #extracting top 5 results
print(result)

Hasilnya menampilkan lima baris pertama dari tabel. 

[('B1', 'Division 1A', 'Belgium'), ('D1', 'Bundesliga', 'Deutschland'), ('D2', '2. Bundesliga', 'Deutschland'), ('E0', 'Premier League', 'England'), ('E1', 'EFL Championship', 'England')]

Contoh SQLAlchemy di Python

Pada bagian ini, kita akan melihat berbagai contoh SQLAlchemy untuk membuat tabel, menyisipkan nilai, menjalankan kueri SQL, analisis data, dan manajemen tabel. 

Anda dapat mengikuti atau melihat buku kerja DataLab ini. Di dalamnya terdapat basis data, kode sumber, dan hasilnya. 

Membuat Tabel

Pertama, kita akan membuat basis data baru bernama datacamp.sqlite. create_engine akan otomatis membuat basis data baru jika belum ada basis data dengan nama yang sama. Jadi, proses membuat dan menghubungkan hampir serupa.

Setelah itu, kita akan menghubungkan basis data dan membuat objek metadata. 

Kita akan menggunakan fungsi Table milik SQLAlchemy untuk membuat tabel bernama “Student”.

Tabel ini terdiri dari kolom:

• Id: Integer dan primary key
• Name: String dan tidak boleh null 
• Major: String dan default = “Math”
• Pass: Boolean dan default = True 

Kita telah membuat struktur tabelnya. Mari tambahkan ke basis data menggunakan `metadata.create_all(engine)`.

engine = db.create_engine('sqlite:///datacamp.sqlite')
conn = engine.connect()
metadata = db.MetaData()

Student = db.Table('Student', metadata,
              db.Column('Id', db.Integer(),primary_key=True),
              db.Column('Name', db.String(255), nullable=False),
              db.Column('Major', db.String(255), default="Math"),
              db.Column('Pass', db.Boolean(), default=True)
              )

metadata.create_all(engine) 

Sisip satu data

Untuk menambahkan satu baris, pertama kita akan menggunakan insert dan menambahkan objek tabelnya. Setelah itu, gunakan values dan tambahkan nilai ke kolom secara manual. Ini bekerja mirip dengan menambahkan argumen ke fungsi Python.   

Terakhir, kita akan mengeksekusi kueri menggunakan koneksi untuk menjalankan fungsinya.

query = db.insert(Student).values(Id=1, Name='Matthew', Major="English", Pass=True)
Result = conn.execute(query)

Mari periksa apakah kita menambahkan baris ke tabel Student dengan mengeksekusi kueri select dan mengambil semua baris. 

output = conn.execute(Student.select()).fetchall()
print(output)

Kita berhasil menambahkan nilainya. 

[(1, 'Matthew', 'English', True)]

Sisip banyak data

Menambahkan nilai satu per satu bukanlah cara praktis untuk mengisi basis data. Mari tambahkan banyak nilai menggunakan list. 

1. Buat kueri insert untuk tabel Student.

2. Buat list berisi beberapa baris dengan nama kolom dan nilainya.

3. Eksekusi kueri dengan argumen kedua sebagai values_list. 

query = db.insert(Student)
values_list = [{'Id':'2', 'Name':'Nisha', 'Major':"Science", 'Pass':False},
              {'Id':'3', 'Name':'Natasha', 'Major':"Math", 'Pass':True},
              {'Id':'4', 'Name':'Ben', 'Major':"English", 'Pass':False}]
Result = conn.execute(query,values_list)

Untuk memvalidasi hasil, jalankan kueri select sederhana.

output = conn.execute(db.select([Student])).fetchall()
print(output)

Sekarang tabel berisi lebih banyak baris. 

[(1, 'Matthew', 'English', True), (2, 'Nisha', 'Science', False), (3, 'Natasha', 'Math', True), (4, 'Ben', 'English', False)]

Kueri SQL sederhana dengan SQLAlchemy

Alih-alih menggunakan objek Python, kita juga bisa mengeksekusi kueri SQL menggunakan String. 

Cukup tambahkan argumen berupa String ke fungsi execute() dan lihat hasilnya menggunakan fetchall().

output = conn.execute("SELECT * FROM Student")
print(output.fetchall())

Keluaran:

[(1, 'Matthew', 'English', 1), (2, 'Nisha', 'Science', 0), (3, 'Natasha', 'Math', 1), (4, 'Ben', 'English', 0)]

Anda bahkan dapat meneruskan kueri SQL yang lebih kompleks. Dalam kasus kita, kita memilih kolom Name dan Major untuk mahasiswa yang lulus ujian. 

output = conn.execute("SELECT Name, Major FROM Student WHERE Pass = True")
print(output.fetchall())

Keluaran:

[('Matthew', 'English'), ('Natasha', 'Math')]

Menggunakan API SQLAlchemy

Pada bagian sebelumnya, kita menggunakan API/Objek SQLAlchemy yang sederhana. Mari selami kueri yang lebih kompleks dan multi-langkah.

Pada contoh di bawah, kita akan memilih semua kolom untuk mahasiswa dengan jurusan English.  

query = Student.select().where(Student.columns.Major == 'English')
output = conn.execute(query)
print(output.fetchall())

Keluaran:

[(1, 'Matthew', 'English', True), (4, 'Ben', 'English', False)]

Mari terapkan logika AND pada kueri WHERE. 

Kali ini, kita mencari mahasiswa yang jurusannya English, dan mereka tidak lulus.  

Catatan: tidak sama dengan ‘!=’ True adalah False. 

query = Student.select().where(db.and_(Student.columns.Major == 'English', Student.columns.Pass != True))
output = conn.execute(query)
print(output.fetchall())

Hanya Ben yang tidak lulus ujian dengan jurusan English. 

[(4, 'Ben', 'English', False)]

Dengan tabel serupa, kita dapat menjalankan semua jenis perintah, seperti yang ditunjukkan pada tabel di bawah. 

Anda dapat menyalin dan menempel perintah ini untuk menguji hasilnya sendiri. Lihat buku kerja DataLab jika Anda menemui kendala pada perintah yang diberikan. 

Untuk mempelajari fungsi dan perintah lain, lihat dokumentasi resmi SQL Statements and Expressions API.

Output ke DataFrame Pandas

Data scientist dan analis menyukai dataframe pandas dan senang bekerja dengannya. Pada bagian ini, kita akan belajar cara mengonversi hasil kueri SQLAlchemy menjadi dataframe pandas. 

Pertama, eksekusi kueri dan simpan hasilnya. 

query = Student.select().where(Student.columns.Major.in_(['English','Math']))
output = conn.execute(query)
results = output.fetchall()

Kemudian, gunakan fungsi DataFrame() dan berikan hasil SQL sebagai argumen. Terakhir, tambahkan nama kolom menggunakan baris pertama hasil results[0] dan .keys().

Catatan: Anda dapat memberikan baris valid mana pun untuk mengekstrak nama kolom menggunakan keys().

data = pd.DataFrame(results)
data.columns = results[0].keys()
data

Analitik data dengan SQLAlchemy

Pada bagian ini, kita akan menghubungkan basis data European football dan menjalankan kueri kompleks serta memvisualisasikan hasilnya.  

Menghubungkan dua tabel

Seperti biasa, kita akan menghubungkan basis data menggunakan fungsi create_engine() dan connect().

Pada kasus kita, kita akan menggabungkan dua tabel, jadi kita harus membuat dua objek tabel: division dan match.  

engine = create_engine("sqlite:///european_database.sqlite")
conn = engine.connect()
metadata = db.MetaData()
division = db.Table('divisions', metadata, autoload=True, autoload_with=engine)
match = db.Table('matchs', metadata, autoload=True, autoload_with=engine)

Menjalankan kueri kompleks

1. Kita akan memilih kolom dari division dan match.
2. Gabungkan menggunakan kolom yang sama: division.division dan match.Div.
3. Pilih semua kolom untuk division E1 dan season 2009.
4. Urutkan hasil berdasarkan HomeTeam.

Anda bahkan dapat membuat kueri yang lebih kompleks dengan menambahkan modul tambahan.

Catatan: untuk menggabungkan dua tabel secara otomatis Anda juga dapat menggunakan: db.select([division.columns.division,match.columns.Div]).

query = db.select([division,match]).\
select_from(division.join(match,division.columns.division == match.columns.Div)).\
where(db.and_(division.columns.division == "E1", match.columns.season == 2009 )).\
order_by(match.columns.HomeTeam)
output = conn.execute(query)
results = output.fetchall()

data = pd.DataFrame(results)
data.columns = results[0].keys()
data

Setelah mengeksekusi kueri, kita mengonversi hasil menjadi dataframe pandas. 

Kedua tabel telah digabung, dan hasil hanya menampilkan division E1 untuk season 2009 yang diurutkan berdasarkan kolom HomeTeam. 

Visualisasi Data

Sekarang kita memiliki dataframe, kita dapat memvisualisasikan hasil dalam bentuk bagan batang menggunakan Seaborn. 

Kita akan:

1. Mengatur tema ke “whitegrid”
2. Mengubah ukuran visualisasi menjadi 15X6
3. Memutar tick sumbu x menjadi 90
4. Mengatur palet warna ke “pastels”
5. Memplot bagan batang "HomeTeam" vs "FTHG" dengan warna Biru.
6. Memplot bagan batang "HomeTeam" vs "FTAG" dengan warna Merah.
7. Menampilkan legenda di kiri atas.
8. Menghapus label x dan y. 
9. Menghilangkan garis tepi kiri dan bawah (despine).

Tujuan utama bagian ini adalah menunjukkan cara menggunakan keluaran kueri SQL untuk membuat visualisasi data yang menarik. 

import seaborn as sns
import matplotlib.pyplot as plt
sns.set_theme(style="whitegrid")

f, ax = plt.subplots(figsize=(15, 6))
plt.xticks(rotation=90)
sns.set_color_codes("pastel")
sns.barplot(x="HomeTeam", y="FTHG", data=data,
            label="Home Team Goals", color="b")

sns.barplot(x="HomeTeam", y="FTAG", data=data,
            label="Away Team Goals", color="r")
ax.legend(ncol=2, loc="upper left", frameon=True)
ax.set(ylabel="", xlabel="")
sns.despine(left=True, bottom=True)

Menyimpan hasil ke CSV

Setelah mengonversi hasil kueri menjadi dataframe pandas, Anda cukup menggunakan fungsi .to_csv() dengan nama file. 

output = conn.execute("SELECT * FROM matchs WHERE HomeTeam LIKE 'Norwich'")
results = output.fetchall()


data = pd.DataFrame(results)
data.columns = results[0].keys()

Hindari menambahkan kolom bernama “Index” dengan menggunakan `index=False`.

data.to_csv("SQl_result.csv",index=False)

File CSV ke tabel SQL

Pada bagian ini, kita akan mengonversi file CSV Stock Exchange Data menjadi tabel SQL. 

Pertama, hubungkan ke basis data sqlite datacamp.

engine = create_engine("sqlite:///datacamp.sqlite")

Kemudian, impor file CSV menggunakan fungsi read_csv(). Terakhir, gunakan fungsi to_sql() untuk menyimpan dataframe pandas sebagai tabel SQL.  

Utamanya, fungsi to_sql() memerlukan koneksi dan nama tabel sebagai argumen. Anda juga dapat menggunakan if_exisits untuk mengganti tabel yang sudah ada dengan nama yang sama dan index untuk menghapus kolom indeks. 

df = pd.read_csv('Stock Exchange Data.csv')
df.to_sql(con=engine, name="Stock_price", if_exists='replace', index=False)
>>> 2222

Untuk memvalidasi hasil, kita perlu menghubungkan basis data dan membuat objek tabel. 

conn = engine.connect()
metadata = db.MetaData()
stock = db.Table('Stock_price', metadata, autoload=True, autoload_with=engine)

Lalu, jalankan kueri dan tampilkan hasilnya.

query = stock.select()
exe = conn.execute(query)
result = exe.fetchmany(5)
for r in result:
    print(r)

Seperti yang Anda lihat, kita berhasil mentransfer semua nilai dari file CSV ke tabel SQL. 

('HSI', '1986-12-31', 2568.300049, 2568.300049, 2568.300049, 2568.300049, 2568.300049, 0, 333.87900637)
('HSI', '1987-01-02', 2540.100098, 2540.100098, 2540.100098, 2540.100098, 2540.100098, 0, 330.21301274)
('HSI', '1987-01-05', 2552.399902, 2552.399902, 2552.399902, 2552.399902, 2552.399902, 0, 331.81198726)
('HSI', '1987-01-06', 2583.899902, 2583.899902, 2583.899902, 2583.899902, 2583.899902, 0, 335.90698726)
('HSI', '1987-01-07', 2607.100098, 2607.100098, 2607.100098, 2607.100098, 2607.100098, 0, 338.92301274)

Manajemen tabel SQL

Memperbarui nilai dalam tabel

Memperbarui nilai itu mudah. Kita akan menggunakan fungsi update, values, dan where untuk memperbarui nilai tertentu dalam tabel. 

table.update().values(column_1=1, column_2=4,...).where(table.columns.column_5 >= 5)

Pada kasus kita, kita mengubah nilai Pass dari False menjadi True untuk mahasiswa bernama Nisha.  

Student = db.Table('Student', metadata, autoload=True, autoload_with=engine)
query = Student.update().values(Pass = True).where(Student.columns.Name == "Nisha")
results = conn.execute(query)

Untuk memvalidasi hasil, mari jalankan kueri sederhana dan tampilkan hasilnya dalam bentuk dataframe pandas. 

output = conn.execute(Student.select()).fetchall()
data = pd.DataFrame(output)
data.columns = output[0].keys()
data

Kita berhasil mengubah nilai Pass menjadi True untuk mahasiswa bernama Nisha.

Menghapus rekaman

Menghapus baris mirip dengan memperbarui. Ini memerlukan fungsi delete() dan where(). 

table.delete().where(table.columns.column_1 == 6)

Pada kasus kita, kita menghapus rekaman mahasiswa bernama Ben.

Student = db.Table('Student', metadata, autoload=True, autoload_with=engine)
query = Student.delete().where(Student.columns.Name == "Ben")
results = conn.execute(query)

Untuk memvalidasi hasil, kita akan menjalankan kueri cepat dan menampilkan hasil dalam bentuk dataframe. Seperti yang Anda lihat, kita telah menghapus baris yang memuat mahasiswa bernama Ben.

output = conn.execute(Student.select()).fetchall()
data = pd.DataFrame(output)
data.columns = output[0].keys()
data

Menjatuhkan tabel

Jika Anda menggunakan SQLite, menjatuhkan tabel akan memunculkan galat database is locked. Mengapa? Karena SQLite adalah versi yang sangat ringan. SQLite hanya dapat melakukan satu fungsi pada satu waktu. Saat ini, ia sedang mengeksekusi kueri select. Kita perlu menutup seluruh eksekusi sebelum menghapus tabel. 

results.close()
exe.close()

Setelah itu, gunakan fungsi drop_all() milik metadata dan pilih objek tabel untuk menjatuhkan satu tabel. Anda juga dapat menggunakan perintah Student.drop(engine) untuk menjatuhkan satu tabel.

metadata.drop_all(engine, [Student], checkfirst=True)

Jika Anda tidak menentukan tabel apa pun untuk fungsi drop_all(), maka semua tabel dalam basis data akan dihapus. 

metadata.drop_all(engine)

Kesimpulan 

Tutorial SQLAlchemy ini mencakup berbagai fungsi SQLAlchemy, dari menghubungkan basis data hingga memodifikasi tabel, dan jika Anda tertarik belajar lebih jauh, cobalah menyelesaikan kursus interaktif Introduction to Databases in Python. Anda akan mempelajari dasar-dasar basis data relasional, penyaringan, pengurutan, dan pengelompokan. Selain itu, Anda akan mempelajari fungsi SQLAlchemy tingkat lanjut untuk manipulasi data.  

Jika Anda mengalami masalah saat mengikuti tutorial, kunjungi buku kerja DataLab dan bandingkan kode Anda dengannya. Anda juga dapat menyalin buku kerja tersebut dan menjalankannya langsung di dalam DataLab.