Python / SQL:DataFrameの空の文字列を「Null」値に置き換えて、データベースにデータを挿入します

Nov 29 2020

私がこのデータフレームを持っているとしましょう:

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})

この種のデータをデータベースに挿入するためのフォーマットに問題があります。「Débit」、「Crédit」、「Montant」の列は、フロートをデータとして取得するように定義されています。ただし、これらの列のデータは整数だけでなく、空の文字列もあり、それが私の問題です。空の文字列をSQL形式の「Null」値に置き換える条件を作成する必要があることはわかっていますが、PythonまたはSQLでそれを行う方法がわかりません。SQL環境を発見/学習しています。

これが私のコードです:

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()

誰でも私を助けてください。

ありがとうございました

回答

1 Parfait Nov 30 2020 at 02:13

''すべてのobjectタイプで証明されているように、同じ列で文字列、、が整数と結合されているPandasデータフレームでタイプが混在しているようです。リレーショナルデータベースでは、データ型を混在させることはできません。また''、文字列に変換し'NULL'ても問題は解決しません。SQLでは、NULL <> 'NULL'

df.dtypes

# Référence    object
# IBAN         object
# Débit        object
# Crédit       object
# Montant      object
# dtype: object

したがって、列を数値に変換しますpd.to_numeric。空の文字列、、は''NaNこのエンティティがSQLのNULLエンティティに変換される場所に変換されます。

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

次に、クエリを実行します。実際、遅いforループを避けて+iterrowsを検討してください。df.to_numpycursor.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()
1 BarbarosÖzhan Nov 29 2020 at 21:32

あなたはPandas.DataFrame.to_sqlそのような使用することができます

df.to_sql('dbo.tbl_data_xml', con=connection, if_exists='append', index=False )

appendpandasのバージョンが0.15+の場合、optionはテーブルに新しい値を挿入することを表します。

DaniMesejo Nov 29 2020 at 21:30

あなたができること:

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)

出力

      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

または単に、

df[df[['Débit', 'Crédit', 'Montant']].eq('')] = "NULL"
print(df)

出力

      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
wwnde Nov 29 2020 at 21:35

numericそれぞれの列に変換し、fillna(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