Python和SQL：将数据帧的空字符串替换为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”、“Balance”和“Montant”被定义为获取浮点数作为数据。然而，这些列的数据不仅仅是整数，我也有空字符串，这是我的问题。我知道我必须编写一个条件，用SQL格式的“Null”值（SQL中的Null值）替换空字符串，但是我不知道如何在python或SQL中这样做。我正在探索/学习SQL环境

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

有人有主意吗

Answer 1

将NaN替换为None

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