'How to insert nulls into a SQL Server table

I have the following dataframe:

data = [['Alex', 182.2],['Bob', 183.2],['Clarke', 188.4], ['Kelly', NA]]
df = pd.DataFrame(data, columns = ['Name', 'Height'])

I have the following SQL Server table:

create table dbo.heights as (
   name    varchar(10),
   height  float 
)

This is my code to upload the data to my table:

for index,row in df.iterrows():
    cursor.execute('INSERT INTO dbo.heights(name, height) values (?, ?)', row.name, row.height)
    cnxn.commit()
cursor.close()
cnxn.close()

I want to upload the dataframe into my SQL Server table, but it fails on the null value. I tried replacing the NA with an np.nan value and it still failed. I also tried changing the height column to an "object" and replacing the NA with None and that also failed.

pythonsql-serverpandas


Solution 1:[1]

Please use the following instead:

for index, row in df.iterrows():
     query = "INSERT INTO dbo.heights(name, height) values (?, ?)"
     data = [row.name, row.height]
     cursor.execute(query, data)
     cursor.commit()

Or use the following:

query = "INSERT INTO dbo.heights(name, height) values (?, ?)"
data = [row.name, row.height for index, row in df.iterrows()]
cursor.executemany(query, data)
cursor.commit()

You'll see your None values as None in Python and as NULL in your database.

Solution 2:[2]

I tried replacing the NA with an np.nan

Because in such case you have to first define dataframe schema and make it nullable float.

"By default, SeriesSchema/Column objects assume that values are not nullable. In order to accept null values, you need to explicitly specify nullable=True, or else you’ll get an error."

Further Reading

Solution 3:[3]

Try like this

for index,row in df.iterrows():
    cursor.execute("INSERT INTO table (`name`, `height`) VALUES (%s, %s)", (row.name, row.height))
    cnxn.commit()
cursor.close()
cnxn.close()

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Esraa Abdelmaksoud
Solution 2 KumarHarsh
Solution 3 Codemaker

Related Questions