sql-server - Pandas dataframe with less number of columns to SQL Server Table

Question

I am trying to insert some data into a table in sql-server using sqlalchemy and the function df.to_sql.

The problem is that the dataframe doesn't have the same number of columns (it has less columns than the sql server table) as the table and for the columns that the dataframe doesn't have, it inserts NULL values. Is there any way to replace the nulls with blank spaces.

engine = sqlalchemy.create_engine("mssql+pyodbc://"+creds.un+":"+creds.pw+"@"+creds.svr+"/"+creds.db+"?driver=ODBC+Driver+17+for+SQL+Server")

final_df.to_sql("table", con = engine, if_exists = "append", index = False)

final_df is missing about 2 columns that are not nullable.

标签： sql-serverpython-3.xsqlalchemy

Answer 1

您可以将数据库列设置为默认值 '' 而不是 null 省略值，或者在数据框中指定列，也许做一个 final_df.fillna('', inplace=True)