Python & SQLite & Pandas Bağlantısı ve Veri İşlemleri
Bu rehberde en basit tanımlamalar ile Python (Pandas dahil) üzerinden SQL veritabanlarına erişim yöntemleri ve kodları paylaşılmaktadır. Bu rehberin ilk yazısında Python üzerinden SQLite ile gerçekleştirilecek aşağıdaki işlemlerden bahsedilecektir. Bir sonraki yazı MySQL üzerine olacaktır.
- SQLite veritabanına bağlanma ve şema oluşturma
- Tablo oluşturma, veri ekleme, değiştirme ve silme
- Pandas ve SQLite bağlantısı ile okuma yazma işlemleri
ÖNEMLİ NOT: Eğer ki Python üzerinden SQL’e bağlanma sebebiniz çalıştığınız kurumdaki veritabanlarına erişmek ise veritabanı yöneticisi (DBA: Database Administrators) ile iletişime geçerek gerekli güvenlik duvarları ayarlarından sonra bağlantıyı beraber gerçekleştirmenizi tavsiye ederim. Açık kaynak bir programlama dili olan Python üzerinden yapacağınız veritabanı işlemleri için bilgi teknolojileri bölümü (IT) gerekli gördüğü takdirde güvenlik duvarı (firewall) hizmetlerini devreye alabilir
Verinin tutulması, saklanması, işlenmesi gibi konularda bilgisayar bilimleri dünyasında birbirinden farklı işleve ve avantajlara sahip yöntem ve yazılımlar geliştirilmiştir. Ancak SQL hemen hemen her kurumda kullanılan ve standart halini almış bir yapı olarak karşımıza çıkmaktadır. Bugün neredeyse birçok sitenin, uygulamanın veya paket programların verilerinin tutulduğu adres SQL veritabanlarıdır. Burada veritabanı araçlarının nitelikleri ve üreticileri değişse bile genel geçer SQL standartları benzerdir. Popüler olanlar aşağıdaki gibidir.
- Oracle RDMS
- MySQL
- Microsoft SQL Server
- IBM Db2
- PostgreSQL
- Google Cloud BigTable
- SQLite
- Amazon RDS
Python üzerinden veritabanları işlemleri için aşağıdaki kütüphaneleri import ediyoruz.
import pandas as pd
import sqlite3
from sqlalchemy import create_engine
import numpy as np- pandas: Verileri tablo olarak gösteren ve geniş bir manipülasyon yeteneğine sahip bir kütüphanedir.
pip install pandas- sqlite3: Son derece hafif ve kullanışlı, yeni başlayanlar için anlaşılması ve öğrenilmesi kolay bir SQL veritabanıdır. Python tarafından desteklendiği için dışarıdan bir kurulum yapılmasına gerek yok. Ancak bu veritabanını açmak için aşağıdaki programı indirebilirsiniz.
DB Browser
https://sqlitebrowser.org/- sqlalchemy: Python ve SQL arasındaki iletişimi kolaylaştırmak amacıyla kullanılan bir (ORM: Object Relational Mapper) kütüphanesidir. SQL kodları yazmaksızın veritabanı işlemlerinin yapılması için sınıf ve fonksiyon yapıları sunar.
pip install sqlalchemy- Numpy: dizi (array), matris işlemlerinin yapılabildiği güçlü matematiksel fonksiyonların yer aldığı bir kütüphanedir. Numpy’da kullanılan temel fonksiyonların yer aldığı yazıma aşağıdaki linkten erişebilirsiniz.
SQLite Veritabanına Bağlanma ve Şema oluşturma
SQLite için bağlantı aşamasında eğer oluşturulmuş bir şema var ise doğrudan aşağıdaki fonksiyonda ilgili alana ismini vererek bağlanılabilir. Eğer yeni bir şema oluşturulacak ise ilgili alana ‘.db’ ile bitecek şekilde oluşturulabilir. Bu uzantının kullanılmasının sebebi; yukarıda indirme linki verilen DB Browser üzerinden şemanın görüntülenebilmesidir.
# target isimli şemayı oluşturuyoruz.
sqlite_con = sqlite3.connect("target.db")
# bağlantının koparılması
sqlite_con.close()target.db adlı dosya Python scriptinin çalıştığı alanda oluşacaktır. DB Browser ile çalıştırıp bakalım. Eğer programı doğru şekilde kurduysanız dosyaya tıkladığınızda otomatik olarak program çalışır.
DB Browser ekranında henüz bir tablo oluşturulmadığı için bomboş bir şemanın olduğu görülmektedir.
Tablo Oluşturma, Veri ekleme, Değiştirme ve Silme İşlemleri
Basit bir SQL komutu ile Python üzerinden bir tablo oluşturalım.
# tablo ve kolon özelliklerinin oluşturulduğu SQL komutu
quary_create_table = """CREATE TABLE sales (
id INTEGER PRIMARY KEY,
sales_age INTEGER NOT NULL,
sales_total_experience INTEGER,
sales_total_amount REAL NOT NULL);"""
# target isimli şemaya bağlantı
sqlite_con = sqlite3.connect("target.db")
# tablo oluşturan SQL sorgusu
sqlite_con.execute(quary_create_table)
# bağlantının koparılması
sqlite_con.close()DB Browser üzerinden kontrol edelim. Eğer tablo görünmüyorsa programı kaydetmeden kapatıp baştan açabilirsiniz.
Tabloda veri işlemleri için iki metodu kullanmamız gerekiyor. Bunlardan cursor ikincisi ise commit. En saf hali ile hazırladığımız SQL sorgusunu içindeki veriler ile veritabanına aktaralım.
table_insert = """
INSERT INTO sales
(id, sales_age
,sales_total_experience
,sales_total_amount)
VALUES
(1, 29, 5, 4300.30);
"""
# target isimli şemaya bağlantı
sqlite_con = sqlite3.connect("target.db")
# cursor tanımlanması
cursor = sqlite_con.cursor()
# cursor sorgunun çalıştırılması
cursor.execute(table_insert)
# verilerin veri tabanına işlenmesi
sqlite_con.commit()
# dükkanı kapatmayı unutmuyoruz
sqlite_con.close()NOT: Primary key tekil olma zorunluluğu geitrdiğimiz için sorguyu bir daha çalıştırdığınız zaman 1 numaralı ID içeride yer aldığından hata mesajı dönecektir.
Python esnek bir programlama dili olduğuna göre birden fazla veri dizisini de ekleyebiliriz. Bunun için SQL script içindeki VALUE değerlerini “?” işareti ile gösterip tanımlayacağımız bir ya da birden fazla veri satırını buraya aktarabiliriz. Bir ya da birden fazla satırlı veri yüklemek için cursor.executemany() metodunu kullanmalıyız.
table_insert = """
INSERT INTO sales
(id, sales_age
,sales_total_experience
,sales_total_amount)
VALUES
(?, ?, ?, ?);
"""
records = [(2, 30, 6, 4825.45)
,(3, 26, 3, 2950.10)]
# target isimli şemaya bağlantı
sqlite_con = sqlite3.connect("target.db")
# cursor tanımlanması
cursor = sqlite_con.cursor()
# cursor sorgunun çalıştırılması
cursor.executemany(table_insert, records)
# verilerin veri tabanına işlenmesi
sqlite_con.commit()
# kapıyı kapatmayı unutmuyoruz
sqlite_con.close()DB Browser üzerinden eklediğimiz verilere bir bakalım.
İşaretli olan bölümden tablo görülebilmektedir. Aynı sekme dizininde yer alan “SQL kodu yarat” bölümünden aşağıdaki gibi istediğiniz sorguyu girebilirsiniz. Yalnız gelişmiş sorgu türleri için çok kullanışlı bir ortam olmadığını hatırlatmak isterim.
Veri değiştirme/güncelleme (update) işlemleri için aşağıdaki sorgu kullanılabilir. Tek bir değer seti gönderdiğimiz için cursor.executemany yerine cursor.execute kullanacağız. Ancak gönderdiğimiz değerleri (update_values değişkeni için) liste içine alırsak executemany fonksiyonunu kullanabiliriz. Bu sefer sonuçlara doğrudan Python üzerinden bakalım.
update = """
UPDATE sales
SET sales_age = ? ,
sales_total_experience = ? ,
sales_total_amount = ?
WHERE id = ?
"""
update_values = (32, 8, 5015.8, 1)
# target isimli şemaya bağlantı
sqlite_con = sqlite3.connect("target.db")
# cursor tanımlanması
cursor = sqlite_con.cursor()
# cursor sorgunun çalıştırılması
cursor.execute(update, update_values)
# verilerin veri tabanına işlenmesi
sqlite_con.commit()
# tüm tablonun çağırılması
for i in cursor.execute("select * from sales"):
print(i)
# ÇIKTI
# (1, 32, 8, 5015.8)
# (2, 30, 6, 4825.45)
# (3, 26, 3, 2950.1)
# dolabı kapatmayı unutmuyoruz
sqlite_con.close()for döngüsünün sonucunda görüldüğü üzere id = 1 olan satırdaki değerler değiştirilmiştir.
Silme işlemlerine bir bakalım.
delete = """DELETE FROM sales WHERE id = ?"""
# target isimli şemaya bağlantı
sqlite_con = sqlite3.connect("target.db")
# cursor tanımlanması
cursor = sqlite_con.cursor()
# cursor sorgunun çalıştırılması
cursor.execute(delete, [3])
# verilerin veri tabanına işlenmesi
sqlite_con.commit()
# sonuçlara başka bir yöntem ile bakalım
results = cursor.execute("SELECT * FROM sales").fetchall()
print(results)
# ÇIKTI
# [(1, 32, 8, 5015.8), (2, 30, 6, 4825.45)]
# evi kapatmayı unutmuyoruz
sqlite_con.close()Sorguda görüldüğü üzere 3 numaralı ID satırı silinmiştir.
Pandas ve SQLite Bağlantısı ile Okuma Yazma İşlemleri
Belki de veri bilimcilerin en sık kullandığı kütüphanelerden birisi olan Pandas ile SQL ortamlarına akmak kadar arzulanabilecek başka bir şey olamaz.
Öncelikle tablomuzdaki veri sayısını artıralım. İçeride yer alan ID kolonu tekil olacağı için 3'ten başlayarak 50'ye kadar 47 adet yeni ID üretelim. Sonra her bir ID için normal dağılıma sahip değişkenlerin değerlerini oluşturalım.
Tablomuz baya kalabalık oldu. Bakalım nasıl görünüyor.
SQLite ortamında bulunan sales tablosunu Pandas DataFrame üzerinden görüntüleyelim.
# target isimli şemaya bağlantı
sqlite_con = sqlite3.connect("target.db")
# dataframe yapısında sorgunun oluşturulması
df = pd.read_sql_query("select * from sales", sqlite_con)Son derece kolay bir şekilde verilerimizi aldık. İlk beş satırı bir inceleyelim.
df.head()
SQL ortamından verilerimizi aldığımıza göre verileri nasıl SQL ortamına aktaracağımıza bir bakalım. Örneğin satışla ilgili değişkenlerin korelasyonunu alıp yeni bir tablo ismiyle veritabanına ekleyebiliriz.
# korelasyon aranacak değişkenler
df_corr = ["sales_age", "sales_total_experience", "sales_total_amount"]
# korelasyon testi
corrResult = df[df_corr].corr()
# sonuç
print(corrResult)
Tabloda görüldüğü üzere tecrübenin (experience = 0.92) yaşa (age = 0.88) göre kıyasla satış tutarı üzerinde daha büyük bir etkisi mevcut. Tecrübe her zaman önemli :)
Şimdi bu tabloyu SQL ortamına yazdıralım.
# veritabanında yeni bir tablo ismi ile verileri aktaralım
# verileri yazdırmak için sqlalchemy kullanıyoruz
# bağlantı için şemanın kayıtlı olduğu SQLite dosya yolu girilmelidir
sqlAc_connect = create_engine('sqlite:///<DOSYAYOLU>target.db')
connection = sqlAc_database_connection.connect()
# Pandas içinde gelen to_sql ile yazdırıyoruz.
corrResult.to_sql(name="sales_corr",con=connection, index=False, if_exists='replace')Korelasyon tablosuna DB Browser üzerinden bakalım.
Sonuç
Burada yazılan kodlar ile temel işlemleri halledebilirsiniz. Ancak sürekli bir araç olarak SQL kullanımında bu kodları fonksiyon ya da bir sınıfa (class) dönüştürürseniz daha verimli bir Python kullanımı sağlamış olursunuz.
