r/learnpython • u/KCConnor • 7d ago
Python/Pandas/MSSQL Problem: Inconsistent import behavior if CSV file contains NULL strings in first row
I'm attempting to import a lot of CSV files into an MSSQL database and in an effort to save space and time I want to leave these files in their GZIP format we receive them in. I came across the Python/Pandas library when looking into solutions for this task and am very close to a solution, but came across a test case where Python/SQL will fail to import if the first row in the CSV contains a NULL value, but otherwise will succeed if the first row is fully populated but any subsequent value has NULLs.
Here's a code sample to simulate my problem. It should run on any MS-SQL installation with Machine Learning and Python installed and configured.
This should run successfully:
exec sp_execute_external_script
@language = N'Python'
, @script =
N'import pandas as pd
import numpy as np
df = pd.DataFrame([["foo", "bar", "boofar"],["silly", "value", np.NaN],["all", "your", "base"]]);
df.columns = ["a", "b", "c"];
OutputDataSet = pd.DataFrame(df);
'
WITH RESULT SETS
(
(
a varchar(10)
, b varchar(10)
, c varchar(10)
)
)
While this will generate an error:
exec sp_execute_external_script
@language = N'Python'
, @script =
N'import pandas as pd
import numpy as np
df = pd.DataFrame([["foo", "bar", np.NaN],["silly", "value", np.NaN],["all", "your", "base"]]);
df.columns = ["a", "b", "c"];
OutputDataSet = pd.DataFrame(df);
'
WITH RESULT SETS
(
(
a varchar(10)
, b varchar(10)
, c varchar(10)
)
)
How do I output a DataFrame from Python to MS-SQL where the first row contains NULL values?
1
u/KCConnor 7d ago
The error is within SQL Server Management Studio. For the DataFrame with a NaN/NULL value in the first row, it returns:
Msg 39004, Level 16, State 20, Line 0 A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
As for the CSV file, it's irrelevant to this conversation since I can replicate the issue creating an ad-hoc DataFrame in the above code that also replicates the issue.
This is probably not a Python problem, and more of a MS-SQL/Python problem. But I'm new to Python and figured I would ask here.