Python / SQL: mengganti string kosong dari DataFrame dengan nilai "Null" untuk memasukkan data ke dalam database
Katakanlah saya memiliki kerangka data ini:
REFERENCE = ["GZF882348G", "SFGUZBJLNJU", "FTLNGZ242112", "DFBHGVGHG543"]
IBAN = ["FR7343563", "FR4832545", "FR9858331", "FR2001045"]
DEBIT = [26, '', 856, '']
CREDIT = ['', 324, '', 876]
MONTANT = [641, 33, '', 968]
df = pd.DataFrame({'Référence' : REFERENCE, 'IBAN' : IBAN, 'Débit' : DEBIT, 'Crédit' : CREDIT, 'Montant' : MONTANT})
Saya memiliki masalah format untuk memasukkan jenis data ini ke dalam database saya. Kolom "Débit", "Crédit", "Montant" ditentukan untuk mendapatkan float sebagai data. Namun data kolom ini tidak hanya bilangan bulat, saya juga memiliki string kosong dan itu adalah masalah saya. Saya tahu bahwa saya harus menulis kondisi yang mengganti string kosong dengan nilai "Null" dalam format SQL, namun saya tidak tahu bagaimana melakukannya di python atau di SQL. Saya menemukan / mempelajari lingkungan SQL.
Ini kode saya:
import pandas as pd
import pyodbc
server = '...'
database = '...'
username = '...'
password = '...'
driver = '...'
connection = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+password)
cursor = connection.cursor()
for i, row in df.iterrows():
sql_exe = "INSERT INTO dbo.tbl_data_xml (Réference,IBAN,Débit,Crédit,Montant) VALUES (?,?,?,?,?)"
cursor.execute(sql_exe, tuple(row))
connection.commit()
Siapapun bisa membantu saya.
Terima kasih
Jawaban
Anda tampaknya jenis pencampuran dalam bingkai data Pandas di mana string,, ''
digabungkan dengan bilangan bulat di kolom yang sama seperti yang dibuktikan oleh semua object
jenis. Dalam database relasional, Anda tidak dapat mencampur tipe data. Dan mengonversi ''
ke string 'NULL'
tidak akan menyelesaikan masalah Anda. Dalam SQL,NULL <> 'NULL'
df.dtypes
# Référence object
# IBAN object
# Débit object
# Crédit object
# Montant object
# dtype: object
Oleh karena itu, ubah kolom menjadi numerik dengan pd.to_numeric
tempat string ''
kosong`` mengonversi NaN
yang entitas ini harus terjemahkan ke NULL
entitas SQL .
df[['Débit', 'Crédit', 'Montant']] = df[['Débit', 'Crédit', 'Montant']].apply(pd.to_numeric)
df.dtypes
# Référence object
# IBAN object
# Débit float64
# Crédit float64
# Montant float64
# dtype: object
df
# Référence IBAN Débit Crédit Montant
# 0 GZF882348G FR7343563 26.0 NaN 641.0
# 1 SFGUZBJLNJU FR4832545 NaN 324.0 33.0
# 2 FTLNGZ242112 FR9858331 856.0 NaN NaN
# 3 DFBHGVGHG543 FR2001045 NaN 876.0 968.0
Kemudian jalankan kueri Anda. Faktanya, hindari for
loop yang lebih lambat dengan iterrows
dan pertimbangkan df.to_numpy
+ cursor.executemany
.
# PREPARED STATEMENT
sql_exe = "INSERT INTO dbo.tbl_data_xml (Réference,IBAN,Débit,Crédit,Montant) VALUES (?,?,?,?,?)"
# CONVERT DATA TO LIST OF NUMPY ARRAYS
sql_data = df.where(pd.notnull(df), None).to_numpy().replace(.tolist()
# EXECUTE ACTION QUERY
cursor.executemany(sql_exe, sql_data)
connection.commit()
Anda bisa menggunakan Pandas.DataFrame.to_sql
seperti
df.to_sql('dbo.tbl_data_xml', con=connection, if_exists='append', index=False )
di mana append
opsi singkatan memasukkan nilai baru ke tabel, jika versi pandas adalah 0.15+
Anda bisa melakukan:
df.loc[df['Débit'].eq(''), 'Débit'] = 'NULL'
df.loc[df['Crédit'].eq(''), 'Crédit'] = 'NULL'
df.loc[df['Montant'].eq(''), 'Montant'] = 'NULL'
print(df)
Keluaran
Référence IBAN Débit Crédit Montant
0 GZF882348G FR7343563 26 NULL 641
1 SFGUZBJLNJU FR4832545 NULL 324 33
2 FTLNGZ242112 FR9858331 856 NULL NULL
3 DFBHGVGHG543 FR2001045 NULL 876 968
Atau sederhananya,
df[df[['Débit', 'Crédit', 'Montant']].eq('')] = "NULL"
print(df)
Keluaran
Référence IBAN Débit Crédit Montant
0 GZF882348G FR7343563 26 NULL 641
1 SFGUZBJLNJU FR4832545 NULL 324 33
2 FTLNGZ242112 FR9858331 856 NULL NULL
3 DFBHGVGHG543 FR2001045 NULL 876 968
Ubah ke numeric
kolom masing-masing danfillna(NULL)
df[['Débit', 'Crédit', 'Montant']]=df.iloc[:,2:].apply(lambda x: pd.to_numeric(x).fillna('NULL'))
Référence IBAN Débit Crédit Montant
0 GZF882348G FR7343563 26 NULL 641
1 SFGUZBJLNJU FR4832545 NULL 324 33
2 FTLNGZ242112 FR9858331 856 NULL NULL
3 DFBHGVGHG543 FR2001045 NULL 876 968