Convert "None" string to null during Oracle insert

Question

I have a dataframe (df) with the below values. Note that the project code below is the String "None"

   YEAR    QUARTER    ID          AMOUNT       DATE       COMMENTS  PROJECT_CODE
0  2020        3     ABC            300.0   2020-10-07      N/A       ABC_0001
0  2020        3     ABC            500.0   2020-10-07      N/A          None

I would like to convert "None" to None before inserting into an oracle table using the below query:

con3 = cx_Oracle.connect(connstr)
cur3 = con3.cursor()
rows3 = [tuple(x) for x in df.values]
cur3.executemany('''merge into O_TABLE
                   using dual
                   on (YEAR = :1 and QUARTER = :2 and ID = :3 and AMOUNT = :4 and DATE = :5 and COMMENTS = :6 and PROJECT_CODE = :7)
                   when not matched then insert values (:1, :2, :3, :4, :5, :6, :7)
                   where :3 IS NOT NULL''',rows)
con3.commit()
cur3.close()
con3.close()

I would like to insert a null value in place of "None" on the Oracle table. I tried converting "None" to Nan before the insert, but this seemed to result in an error:

df['PROJECT_CODE'] = df['PROJECT_CODE'].replace('None', np.nan)

Error:
Traceback (most recent call last):
  File "./test_program.py", line 266, in <module>
    where :3 IS NOT NULL''',rows3)
TypeError: expecting string or bytes object

How do i insert a true null into the oracle table for all "None" values in my df? I wouldn't want to use to_sql as that limits the ways I can write my sql query. Thanks.

or you can use DataFrame.to_sql() which will convert nan to null — Chetan Ameta
– Chetan Ameta, Commented Oct 7, 2020 at 10:42

Jad · Accepted Answer · 2020-10-14 12:12:16Z

1

Oracle is happy with using an empty string as a null, and when you're comparing in the future, an empty string is equated to null.

You could just pass in an empty string, however you'll also need to do something about the comparison with "PROJECT_CODE", either using

and NVL(PROJECT_CODE, '''None''') = :7

or

and ((:7 is null and PROJECT_CODE is null) or PROJECT_CODE = :7)

e.g.

con3 = cx_Oracle.connect(connstr)
cur3 = con3.cursor()
rows3 = [tuple(x) for x in df.values]
cur3.executemany('''merge into O_TABLE
                   using dual
                   on (YEAR = :1
                       and QUARTER = :2
                       and ID = :3
                       and AMOUNT = :4
                       and DATE = :5 
                       and COMMENTS = :6 
                       and ((:7 is null and PROJECT_CODE is null) or PROJECT_CODE = :7)
                   )
                   when not matched then insert values (:1, :2, :3, :4, :5, :6, :7)
                   where :3 IS NOT NULL''',rows)
con3.commit()
cur3.close()
con3.close()

edited Oct 14, 2020 at 12:12

answered Oct 7, 2020 at 10:50

Jad

1,28812 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shallunsard Over a year ago

Thanks. I'm getting a syntax error on the '''None''' though. ` on (YEAR = :1 and QUARTER = :2 and ID = :3 and AMOUNT = :4 and DATE = :5 and COMMENTS = :6 and NVL(PROJECT_CODE, '''None''') = NVL(:7, '''None''') ^ SyntaxError: invalid syntax `

Jad Over a year ago

I think you probably need to use less quotes, e.g. '' or more quotes '''' ... but I'm guessing less.

Jad Over a year ago

Try using the version after the "or" above, in not sure how the single quotes are being converted

Jad Over a year ago

I've changed my e.g. in the answer above to use the alternative

Collectives™ on Stack Overflow

Convert "None" string to null during Oracle insert

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related