Python 和 SQL:将 DataFrame 的空字符串替换为 SQL 的“Null"值以将数据插入数据库而不会出现格式错误 [英] Python and SQL : replacing the empty strings of a DataFrame by a “Null” value of SQL to insert the data in a database without error of format

问题描述

假设我有这个数据框和这个代码来将我的数据插入到数据库中:

Let's say that I have this dataframe and this code to insert my data in the data base :

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"和蒙特"被定义为获取浮点数作为数据.然而，这些列的数据不仅是整数，我也有空字符串，这是我的问题.我知道我必须编写一个条件，用Null"替换空字符串.SQL 格式中的值(SQL 中的值为 null)，但是我不知道如何在 python 或 SQL 中执行此操作.我正在发现/学习 SQL 环境.

I have a problem of format to insert this kind of data in my database. The columns "Débit", "Crédit", "Balance" and "Montant" are defined to get floats as data. However the data of these columns are not only integers, I have empty strings too and that is my issue. I know that I have to write a condition that replace a empty string by a "Null" value in the SQL format (the value null in SQL), however I do not know how to do that in python or in SQL. I am discovering/learning the SQL environment.

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

I do not know if I have to write a code in sql to replace by this value or if I can do it in the python function

有人有想法吗?

推荐答案

Replace NaN 为 None

Replace NaN with None

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

这篇关于Python 和 SQL:将 DataFrame 的空字符串替换为 SQL 的“Null"值以将数据插入数据库而不会出现格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！