title: Python in SQL Server: NULL values in INT columns get mapped to -2147483648 rather than None
body: <h1>tl;dr</h1>
  
  <p>I am working with Python in SQL Server 2017. The Python code is wrapped in a stored procedure to which I pass a query. The query gets evaluated and the data passed to Python. If a string column (char, nchar, varchar, nvarchar) within the query contains <code>NULL</code>, it gets mapped in Python to <code>None</code>. But if a <code>int</code> column contains <code>NULL</code>, it gets mapped to <code>-2147483648</code> (I guess minimum integer value).</p>
  
  <p>My question is how to get a <code>NULL</code> value from a <code>int</code> column to be <code>None</code> in Python, rather than <code>-2147483648</code>? The column needs to stay <code>int</code>.</p>
  
  <h1>Reproducable example</h1>
  
  <p>Test data I'm working with:</p>
  
  <pre><code>CREATE TABLE [dbo].[test_table](
      [a-string] [nvarchar](50) NULL,
      [a-date] [date] NULL,
      [a-int] [int] NULL,
      [a-null-int] [int] NULL,
      [a-null-str] [nvarchar](50) NULL
  ) ON [PRIMARY]
  GO
  INSERT [dbo].[test_table] ([a-string], [a-date], [a-int], [a-null-int], [a-null-str]) VALUES (N'asdf', CAST(N'2018-04-11' AS Date), 1, NULL, NULL)
  INSERT [dbo].[test_table] ([a-string], [a-date], [a-int], [a-null-int], [a-null-str]) VALUES (N'fdsa', CAST(N'2008-04-11' AS Date), 2, NULL, NULL)
  INSERT [dbo].[test_table] ([a-string], [a-date], [a-int], [a-null-int], [a-null-str]) VALUES (N'Bob "Bla" Bob', CAST(N'2028-04-11' AS Date), 3, NULL, NULL)
  INSERT [dbo].[test_table] ([a-string], [a-date], [a-int], [a-null-int], [a-null-str]) VALUES (N'Bob, Bob', CAST(N'2038-04-11' AS Date), 4, NULL, NULL)
  INSERT [dbo].[test_table] ([a-string], [a-date], [a-int], [a-null-int], [a-null-str]) VALUES (N'Bob bob', CAST(N'1998-04-11' AS Date), 5, 1, NULL)
  </code></pre>
  
  <p>The two last columns contain some <code>NULL</code> values. The first one is of type <code>int</code>, the second type <code>nvarchar</code>.</p>
  
  <p>The code of the stored proc:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[usp_test]
      @query NVARCHAR(max)
  AS
  BEGIN
  EXEC sp_execute_external_script 
  @language = N'Python', 
  @script = N'
  print(InputDataSet)
  ',
  @input_data_1 = @query
  END;
  </code></pre>
  
  <p>The stored procedure has one parameter with a query which passes the result of the query to the Python code. In the Python code I am printing the data. </p>
  
  <p>How I execute the stored procedure:</p>
  
  <pre><code>EXEC [dbo].[usp_test] N'SELECT [a-string],CAST([a-date] as nvarchar(20)) as [a-date],[a-int],[a-null-int],[a-null-str] FROM [dbo].[test_table]'
  </code></pre>
  
  <p>The Result is:</p>
  
  <pre>
          a-string      a-date  a-int  a-null-int a-null-str
  0           asdf  2018-04-11      1 -2147483648       None
  1           fdsa  2008-04-11      2 -2147483648       None
  2  Bob "Bla" Bob  2028-04-11      3 -2147483648       None
  3       Bob, Bob  2038-04-11      4 -2147483648       None
  4        Bob bob  1998-04-11      5           1       None
  </pre>   
  
  <p>The unexpected behaviour is in the column <code>a-null-int</code>. How do I get it to be <code>None</code> rather than <code>-2147483648</code>, while staying <code>int</code>?</p>
  
  <p>This question strongly relates to SQL Server. According <a href="https://docs.microsoft.com/en-us/sql/advanced-analytics/python/new-components-in-sql-server-to-support-python-integration?view=sql-server-2017" rel="nofollow noreferrer">this documentation</a> by Microsoft, the BxlServer or SQL Satellite (not sure) handles data transfer between SQL Server and Python. I expect the problem to be in one of those services. But I have no idea on how to circumvent it.</p>
  
  <h3>Research done:</h3>
  
  <ul>
  <li>Microsoft Tutorial for Python in SQL Server: <a href="https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/wrap-python-in-tsql-stored-procedure?view=sql-server-2017" rel="nofollow noreferrer">docs.microsoft.com</a></li>
  <li>SQL Server 2017 architecture for running Python: <a href="https://docs.microsoft.com/en-us/sql/advanced-analytics/python/new-components-in-sql-server-to-support-python-integration?view=sql-server-2017" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/advanced-analytics/python/new-components-in-sql-server-to-support-python-integration?view=sql-server-2017</a></li>
  <li>Pandas support for integer <code>NA</code>: <a href="http://pandas.pydata.org/pandas-docs/stable/gotchas.html#support-for-integer-na" rel="nofollow noreferrer">http://pandas.pydata.org/pandas-docs/stable/gotchas.html#support-for-integer-na</a></li>
  </ul>
  
  <h3>Edit1; Is this question a duplicate to the question <a href="https://stackoverflow.com/questions/20399717">How to store empty value as an Integerfield</a>?</h3>
  
  <p>IMO not. It seems the problem there has been a discrepancy between data types (str vs int). This is not the case here. If I check the data type, I get:</p>
  
  <pre><code>print(type(InputDataSet.ix[0,"a-null-int"]))
  &gt;&gt;&gt; &lt;class 'numpy.int32'&gt;
  </code></pre>
  
  <p>This is correct. I am passing a <code>int</code> column in, and it gets mapped to a python <code>int</code>. But what I need is it to be <code>None</code>.</p>
  
  <h3>Edit2; Response to @arun-gurunathan answer:</h3>
  
  <p>Before I begin I need to state, that the <code>[a-null-int]</code> column needs to stay integer typed. For context, I need to export data to a CSV. To illustrate my problem, I changed the value of the last row in the <code>[a-null-int]</code> column from <code>NULL</code> to <code>1</code>. Changed the beginning of the question accordingly.</p>
  
  <p>With <code>RxMissingValues.int32()</code> I get the value used for substituting <code>NULL</code> values, which is <code>-2147483648</code>. I can replace these values with <code>numpy.NaN</code>. It is not a bullet proof fix, because what happens if a column in SQL Server contains exactly this value? Nevertheless, I continued down this path...</p>
  
  <p>The following code I put in the stored procedure above:</p>
  
  <pre><code>import numpy
  from revoscalepy import RxMissingValues
  InputDataSet.loc[InputDataSet["a-null-int"] == RxMissingValues.int32(), ("a-null-int")] = numpy.NaN
  print(InputDataSet)
  </code></pre>
  
  <p>This is what I've got (abbreviated):</p>
  
  <pre>
     a-null-int
  0         NaN
  1         NaN
  2         NaN
  3         NaN
  4         1.0
  </pre>
  
  <p>The <code>[a-null-int]</code> column gets transformed to <code>float</code>. This behavior is documented in <a href="http://pandas.pydata.org/pandas-docs/stable/gotchas.html#support-for-integer-na" rel="nofollow noreferrer">pandas doc</a> and has been discussed on <a href="https://stackoverflow.com/questions/11548005/numpy-or-pandas-keeping-array-type-as-integer-while-having-a-nan-value">stackoverflow</a>.</p>
  
  <p>I expect that my problem is not solvable due to NumPy limitations in handling of <code>NA</code> values. I'll wait some more to see if some more answers pop up on how I can keep the type of column <code>a-null-int</code> as <code>int</code>, or some workaround. Else I will accept @arun-gurunathan answer.</p>