CODEWITHSUNDEEP
pythonpsycopg2
BPm
BPm

Reputation: 2994

psycopg2 - how to change NULL type to a string 'NA' instead of None

psycopg2 converts NULL to None through the NoneAdapter (right?) How do I create another adapter that converts all NULL to a string 'NA' ?

Thanks

*UPDATE: @thatisbrilliant
Here is what i have: After doing this query : 

SELECT pg_type.oid
FROM pg_type JOIN pg_namespace
    ON typnamespace = pg_namespace.oid
WHERE typname = 'point'
    AND nspname = 'pg_catalog';

and get the point_oid = 600 so I continue with this: 

def NA(value, cur):
    if value is None:
        return 'NA'
    return value
None2NA = psycopg2.extensions.new_type((600,), 'None2NA', NA)
psycopg2.extensions.register_type(None2NA)

Doesn't work o.o it still returns None

Upvotes: 2

Views: 4544

Answers (2)

Lester Cheung
Lester Cheung

Reputation: 2030

Simply do:

row = cux.fetchone()
field = row[col] if row[col] is not None else 'NA'

Done!

If you want to apply it to all the columns in the row, use map():

row = map(lambda x: x if x is not None else 'NA', cux.fetchone())

Upvotes: 0

thatwasbrilliant
thatwasbrilliant

Reputation: 521

Actually NoneAdapter does the opposite of what you want, converts python's None type into SQL NULL. For info on casting SQL types into python objects, check out the official psycopg2 documentation.

Based on that, it looks like you will have to register your adapter function for every type you want it to work on, but I assume the function would simply be:

def NA(value, cur):
    if value is None:
        return 'NA'
    return value

Then register with psycopg2.extensions.new_type and psycopg2.extensions.register_type.

Upvotes: 4

Related Questions