Python和SQL:将DataFrame的空字符串替换为SQL的"Null"值，以便在数据库中插入

Question

假设我有这个数据帧和这个代码来将我的数据插入数据库：

import pandas as pd
import pyodbc 

REFERENCE = ["GZF882348G", "SFGUZBJLNJU", "FTLNGZ242112", "DFBHGVGHG543", "H353464508749","H353464508749","H353464508749","H353464508749", "H353464508749", "H353464508749", "H353464508749"]
IBAN = ["FR57476", "FR57476", "FR57476", "FR57476", "FR57476", "FR57476", " FR57476", "FR57476", "FR57476", "FR57476", "FR57476"]
DATE = ["2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30", "2020-07-30"]
LIB = ["sdf", "dfsf", "dgsg", "dgfsg", "gsdg", "efsg", "efdg", "egsg", "gjtz", "wqeq", "hfgh"]
DEBIT = [289.2, 72.9, 709.23, 0, 97.3, 17.54, 40.32, 6.54, 1.74, '', 12401.04]
CREDIT = ['', '', '', '', '', '', '', '', '', 45, '']
BALANCE = [23.6,23.6,23.6,23.6,56.6,56,56,56,56,87,34]
B = ["CRDT", "CRDT", "CRDT", "CRDT", "DBIT", "DBIT", "DBIT", "DBIT", "DBIT", "CRDT", "DBIT"]
MONTANT = [-2819.2, -782.9, -709.23, 0, -9397.3, -1768.54, -1740.32, -676.54, -81.74, 16250, -12401.04]
df = pd.DataFrame({'Réference' : REFERENCE, 'IBAN' : IBAN, 'Date' : DATE, 'Libelle' : LIB, 'Débit' : DEBIT, 'Crédit' : CREDIT, 'Balance' : BALANCE, 'Balance DrCr':B, 'Montant' : MONTANT})
df[['Débit', 'Crédit', 'Balance', 'Montant']] = df[['Débit', 'Crédit', 'Balance', 'Montant']].apply(pd.to_numeric)
###### -------- Connection -----------------
server = '...'
database = '...'
username = '...' 
password = '...'
driver = '...'
connection = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+password)
cursor = connection.cursor()
##### ------- Insert into ----------------
sql_exe = "INSERT INTO dbo.tbl_data_xml (Réference,IBAN,Date,Libelle,Débit,Crédit,Balance,[Balance DrCr],Montant) VALUES (?,?,?,?,?,?,?,?,?)"
# CONVERT DATA TO LIST OF NUMPY ARRAYS
sql_data = df.to_numpy().tolist()
# EXECUTE ACTION QUERY
cursor.executemany(sql_exe, sql_data)
connection.commit()

我在数据库中插入此类数据的格式有问题。列"；Débit"Crédit"余额"；以及"；Montant"；定义为将浮点值作为数据获取。然而，这些列的数据不仅是整数，我还有空字符串，这就是我的问题。我知道我必须写一个条件，用一个"；Null"；SQL格式的值(SQL中的null值(，但是我不知道如何在python或SQL中做到这一点。我正在探索/学习SQL环境。

我不知道我是否必须在sql中写一段代码来替换这个值，或者我是否可以在python函数中这样做

有人有主意吗？

Answer 1

用None替换NaN

sql_data = df.replace({np.nan:None}).to_numpy().tolist()