BUG: to_sql() method inserts NULL for NaN which bypasses DEFAULT behaviour of database

[robinstauntoncollins]

In my database ddl (SQL Server) I have fields which have default values:

	my_database_field varchar(255)  NOT NULL 
		 DEFAULT  'NVT'

However, when I try to insert data into this table using DataFrame.to_sql() I get the following error:

Cannot insert the value NULL into column 'my_database_field', table 'MYDB.dbo.my_table'; column does not allow nulls. INSERT fails. 

Here is the code:

    with engine.begin() as conn:
        dataframe.to_sql(table_name, conn, index=False, if_exists='append')

The column 'my_database_field' has NaN values:

data['my_database_field']
0      NaN
1      NaN
2      NaN
3      NaN
4      NaN
        ..
4154   NaN
4155   NaN
4156   NaN
4157   NaN
4158   NaN

This seems to be because the to_sql method is replacing NaN values with NULLs (probably how it should be)

I want the option to insert NaN values and have the database use the DEFAULT value defined in the schema instead of inserting NULL directly when using the to_sql method.

[youssefabdelwahab]

Hey,

I think the issue might be with how you did not select the appropriate driver for your database. I have built this function for a mysql connection and it works fine.

Check this connection documentation from sqlalchemy to use the right syntax for your SQL provider
https://docs.sqlalchemy.org/en/14/core/engines.html

import sqlalchemy as sql_a`

def insert_into_table_from_df(driver:str , username:str , password:str , port:str , database_name:str , dataframe , table_name:str):
    """
     The purpose of this function is to parse rows from a dataframe and insert them into a database table
    Arguments
    ----------
    driver: driver specific to you SQL server must follow DPAPI
    username: SQL server username 
    password: SQL server password 
    port: Entry point for your SQL server 
    database_name: Name of database where your table is located 
    dataframe: Name of dataframe that you wish to parse through 
    table_name: Name of table that you would like to insert into
    """
    try: 
        #establishing a connection with the SQL server 
        connection = sql_a.create_engine(f'mysql+{driver}://{username}:{password}@:{port}/{database_name}')
        #parsing through dataframe rows and if table doesnt exisist it will create one if not it will append to it 
        dataframe.to_sql(name = table_name,con = connection , index = False , if_exists = 'append')
        #printing success message
        print('Table Created Sucessfully')
        #handling error
    except Error as e:
        #printing error message
        print('An Error has occured',e)


insert_into_table_from_df(driver= 'mysqlconnector', 
                         username=username, 
                         password=password, 
                         port = '3306', 
                         database_name= 'coffeeshop', 
                         dataframe= txs_copy, 
                         table_name='Transactions')

[ai-monster]

This is a late response and perhaps not needed anymore but if there's anyone one out there who's struggling with this I'd say make sure the dataframe column is not an object which is a flexible dtype and will default to the database definition for empty. You dataframe column should be casted to i.e String, Float, Int e.t.c but not object. When you write to the database it will adhere strictly to the dataframe specification and NaN in your dataframe will be preserved. If dytpe is left as an object and you want to insert NaN, it will be changed to NULL (which i believe is default) in the SQL server database.