Replace None in pandas data frame with Null

Question

I have a pandas dataframe and I am getting None for many values. I need to write it to SQL server DB and want to update it with Null. How can i do that?

I cannot use df.to_sql to write to DB, it is very slow. So I use pymsql. I convert the dataframe values as a tuple and form a sql insert statement. Hence i cannot have None, Nan, NAT etc. need to even clear it before writing to tuple.

self.sqlconn = pymssql.connect(server=self.server, user=self.username, password=self.password,database=self.database)

code for writing to sql db

cursor = self.sqlconn.cursor()
            for i in sql_dataframe.values:
                query = 'insert into ' + table_name + ' (' + ','.join(sql_dataframe.columns) + ') values ' + str(
                    tuple(i))

to clarify:- i am looking for NULL. not 'NULL' , 'NaN','None',NaN etc. — renjith, Jun 28 '22 at 20:33
The SQL connector should do that conversion automatically. Python's `None` and SQL's `NULL` are the same concept. Have you tried it? — Tim Roberts, Jun 28 '22 at 20:34
Pandas uses None or NaN or NaT(in datetimelike) as Null in DB. So if you send those value, your DB should understand it(df.to_sql) — Prayson W. Daniel, Jun 28 '22 at 20:35
@PraysonW.Daniel i cannot use df.To_sql because it is very slow. I am using the pymssql.connect(server=self.server, user=self.username, password=self.password, database=self.database) and the inserts are done by creating a query and passing using a cursor. for i in sql_dataframe.values: query = 'insert into ' + table_name + ' (' + ','.join(sql_dataframe.columns) + ') values ' + str( tuple(i)) hence i cannot have the None and kind esp with smallints and all cursor.execute(query) — renjith, Jun 29 '22 at 23:59

score 0 · Answer 1 · answered Jun 28 '22 at 20:13

0

try replacing None with explicit NULLs

df['col'] = df['col'].fillna('NULL')

answered Jun 28 '22 at 20:13

user19438165

1
1

2

i need NULL not 'NULL' string value. – renjith Jun 28 '22 at 20:22
I wonder, if this 'NULL' would be recoginze by some Database Management System as a String. – robni Jun 28 '22 at 20:22
if 'NULL' is passed, then it is a string. If NULL is passed then it is treated as NULL- it can go in string, int, small int and any sort of thing. – renjith Jun 28 '22 at 20:26
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 29 '22 at 04:27

Replace None in pandas data frame with Null

1 Answers1