Pandas Dataframe’i Veri Tabanına Yazmanın En Kötü Yolu

Erkan ŞİRİN 1 Ocak 2022 Python, SQL, Veri hazırlığı 1 2647 1069 kelime - 7 dakika34

---

Bu yazıya puan ver

(3 votes, average: 3,67 out of 5)

Loading...

---

Pandas dataframe veri bilimci ve mühendisleri tarafından çok yaygın kullanılan bir araç. Veriyi şekilden şekile sokmak için birebir. Veriyi istediğimiz hale getirdikten sonra çoğu zaman bir yere yazıp saklamak isteriz. Eğer saklanacak yer bir veri tabanı olacak ise bunun bir kaç farklı yöntemi var. Bunlardan birisi de pandas dataframe to_sql metodu. to_csv’ye çok alışığız, bu da onun benzeri. Sadece dosya yolu yerine tablo adını yazıyorsunuz ve bir de veri tabanı bağlantı bilgilerini tutan bağlantı nesnesini veriyorsunuz olup bitiyor.

Ancak bu kolaylığın altında bir bit yeniği aramak ilk başta aklıma gelmemişti. Sizin gelsin lütfen. Yazarken çok dikkatli olmalısınız. df.to_sql dediniz ve hata almadınız. Oh ne güzel deyip arkanızı dönüp gitmeyin. Mutlaka ne yazmış, istediğinizi mi yazmış, tablonun şeması değişmiş mi vs. bir kontrol edin. Bugün burada bu kolay yöntemin arkasındaki gizli tehlikeyi göstereceğiz ve bundan nasıl korunacağımıza dair bir örnek vereceğiz.

Kütüphanelerimizi import edelim.

import pandas as pd
import sqlalchemy

Mysql veri tabanında bir tablo yaratıp içine örnek kayıtlar girelim.

engine = sqlalchemy.create_engine("mysql+mysqldb://vbo_user:VBo*kmfd8(06@192.168.206.140:3306/vbo")

with engine.connect() as conn:
    conn.execute("""
    CREATE TABLE IF NOT EXISTS trainee (id int primary key, name varchar(64), salary numeric(15,4), notes json);
    """)
    
    try:
        query="INSERT INTO `vbo`.`trainee` (`id` ,`name` ,`salary`, `notes`)  VALUES(%s,%s,%s,%s)"
        my_data=[(1416, "George Clooney", 1999.2546, '{"Database": 88, "Network": "A+"}'), 
                 (1417, "Brad Pitt", 2000.5001, '{"Database": 78, "Network": "B+"}'),
                 (1506, "Leonardo DiCaprio", 2100.5430, '{"Database": 98, "Network": "A+"}'),
                 (1527, "Shah Rukh Khan", 1988.9875, '{"Literature": 98, "Geography": "A+"}'),
                 (1525, "Geoffrey Rush", 1975.2506, '{"Economics": 78, "Monetary": "C+"}')]

        id=conn.execute(query, my_data)

        print("Rows Added  = ", id.rowcount)
    
    except:
        print("Database error ")

Şimdi mysql shell’i açalım ve tablonun şemasını bir görelim.

# In mysql shell
mysql> describe trainee;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int           | NO   | PRI | NULL    |       |
| name   | varchar(64)   | YES  |     | NULL    |       |
| salary | decimal(15,4) | YES  |     | NULL    |       |
| notes  | json          | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

Bir de pandas ile okuyalım ve notebook üzerinde veriyi görelim.

trainee = pd.read_sql('trainee', engine)

trainee.head()

Şimdi bu dataframe’i tekrar MySQL’e farklı isimde bir tablo olarak yazalım. Önceden tablo falan yaratmadık, Allah’ın selamıyla öyle pat diye gidiyoruz.

trainee.to_sql('trainee2', engine, if_exists='replace', index=False)

MySQL shell’den trainee2 tablosunun şemasına bakalım.

# In mysql shell
mysql> describe trainee2;
+--------+--------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+--------+--------+------+-----+---------+-------+
| id     | bigint | YES  |     | NULL    |       |
| name   | text   | YES  |     | NULL    |       |
| salary | double | YES  |     | NULL    |       |
| notes  | text   | YES  |     | NULL    |       |
+--------+--------+------+-----+---------+-------+

İlginç bir şekilde id int -> bigint, name varchar(64) -> text, salary decimal(15,4) -> double, notes json -> text olmuş. Hiç biri tam olarak uymuyor.

Peki bu sorunu nasıl çözeceğiz?

Yazarken şema belirterek. Şimdi bir şema yaratalım. Bu şema to_sql metoduna dtype argümanı olarak veriliyor ve veri türü de bir dictionary. Bu dictionary içinde anahtarlar sütün isimlerini, değerler ise veri türlerini belirtiyor. Peki veri türleri mysql mi, pandas mı ne olacak? Hayır sqlalchemy olacak. Bu sayfadan veri türlerini bulabilirsiniz. Çoğu kolayca eşleşir, eşleşmeyenler için de en iyi karşılıkları seçmelisiniz.

from sqlalchemy.types import *

df_schema = {
"id": Integer,    
"name": String(64),
"salary": DECIMAL(15,4),
"notes": JSON
}

Şimdi aynı dataframe’i şema belirterek farklı bir tablo adıyla yazalım.

trainee.to_sql('trainee3', engine, index=False, if_exists='replace', dtype=df_schema)

Şimdi mysql shell’den trainee3 şemasını kontrol edelim.

mysql> describe trainee3;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int           | YES  |     | NULL    |       |
| name   | varchar(64)   | YES  |     | NULL    |       |
| salary | decimal(15,4) | YES  |     | NULL    |       |
| notes  | json          | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

Evet sorun çözülmüş görünüyor. Ancak sadece bir primary key kalmış. Onu da alter table ile çözebiliriz.

with engine.connect() as conn:
    conn.execute("""
    ALTER TABLE trainee3 ADD PRIMARY KEY(id);
    """)

Şemayı tekrar kontrol edelim.

mysql> describe trainee3;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int           | NO   | PRI | NULL    |       |
| name   | varchar(64)   | YES  |     | NULL    |       |
| salary | decimal(15,4) | YES  |     | NULL    |       |
| notes  | json          | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

Evet o da halloldu.

Böylelikle bu sorunu çözmüş görünüyoruz. Ben olsam şema belirtmeksizin to_sql metodunu kullanmayı yasaklardım. Elimden gelse veri tabanlarına ayar yapar bu şekilde elini kolunu sallayarak gelen to_sql yazma isteklerini şiddetle reddederdim.

Yazıyı bitirmeden önce aklıma bir kurt daha düştü. Ya önceden istediğim şemada tablo yaratsam  ve onun üzerine dataframe yazsam. Nasıl olur acaba?

with engine.connect() as conn:
    conn.execute("""
    CREATE TABLE IF NOT EXISTS trainee4 (id int primary key, name varchar(64), salary numeric(15,4), notes json);
    """)

trainee.to_sql('trainee4', engine, index=False, if_exists='replace')

Hemen mysql shell’den kontrol edelim.

mysql> describe trainee4;
+--------+--------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+--------+--------+------+-----+---------+-------+
| id     | bigint | YES  |     | NULL    |       |
| name   | text   | YES  |     | NULL    |       |
| salary | double | YES  |     | NULL    |       |
| notes  | text   | YES  |     | NULL    |       |
+--------+--------+------+-----+---------+-------+

Sonuç yine hüsran. Sanırım tabloyu düşürüp kafasına göre yaratıyor. Bir de bunu append modu ile deneyelim.

trainee.to_sql('trainee4', engine, index=False, if_exists='append')

OperationalError: (MySQLdb._exceptions.OperationalError) (3140, 'Invalid JSON text: "Missing a name for object member." at position 1 in value for column \'trainee4.notes\'.')
[SQL: INSERT INTO trainee4 (id, name, salary, notes) VALUES (%s, %s, %s, %s)]
[parameters: ((1416, 'George Clooney', 1999.2546, {'Network': 'A+', 'Database': 88}), (1417, 'Brad Pitt', 2000.5001, {'Network': 'B+', 'Database': 78}), (1506, 'Leonardo DiCaprio', 2100.543, {'Network': 'A+', 'Database': 98}), (1525, 'Geoffrey Rush', 1975.2506, {'Monetary': 'C+', 'Economics': 78}), (1527, 'Shah Rukh Khan', 1988.9875, {'Geography': 'A+', 'Literature': 98}))]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

O zaman da hata aldık. Çünkü muhtemelen pandas’daki veri yapısıyla mysql’de tanımlı olan tam uyuşmuyor. Json’dan bir itiraz geldi. Biraz daha uğraşasım gelmedi ve burada bırakıyorum.

Bu yazımızdaki kıssaden hisse:

Hata almamış da olsan ne yazdığını mutlaka kontrol et.

Başka bir yazıda görüşmek üzere…

Kapak görseli: Photo by Kelly Sikkema on Unsplash