Python postgreSQL inserting ""(null) value to double precision column

Question

I am a beginner in sql. I am trying to pass the csv file data to my PostgreSQL database. I am using psycopg2 module in python(3.10). In the varchar columns, there are not any problems if the csv segment is empty, which is a empty string. When it comes to empty value in csv file which supposed to be a double, for example:

3709,"Saint George Parish",10,AG,"Antigua And Barbuda",03,,,

I get this error:

Traceback (most recent call last): File "c:\Users\kasim\Documents\PL\06son\csvtry\csvtosql.py", line 137, in imlec.execute(postgres_insert_query, values) psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type double precision: "" LINE 2: ...eorge Parish','10','AG','Antigua And Barbuda','03','','','')

the code:

file = open('csvtry\\states.csv',encoding='utf8')
    csvreader = csv.reader(file)
    rows = []
    for row in csvreader:
            rows.append(row)
    
    db = psycopg2.connect(user = "*",
                          password = "*",
                          host = "*",
                          port = "5432",
                          database = "*")
    
    imlec = db.cursor()
    
    for row in rows:
    #id,name,country_id,country_code,country_name,state_code,type,latitude,longitude
    
            id = row[0]
            name = row [1]
            country_id = row [2]
            country_code = row [3]
            country_name = row [4]
            state_code = row [5]
            type = row [6]
            latitude = row [7]
            
            longitude = row [8]
            
    
            values = (id,name,country_id,country_code,country_name,state_code,type,latitude,longitude)
    
    
            postgres_insert_query = """ INSERT INTO states (id,name,country_id,country_code,country_name,state_code,type,latitude,longitude) 
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
            
            imlec.execute(postgres_insert_query, values)
            db.commit()
    
    
    
    db.close()

Selimhan Baltaş · Accepted Answer · 2022-07-19 16:37:02Z

1

I solved it with inserting null to the database. I already tried to send 'NULL', but as I learnt that it must be 'None' in python.

latitude = row [7]
if latitude == "":
    latitude = None
longitude = row [8]
if longitude == "":
    longitude = None

Full code:

    import psycopg2
    import csv
    
    file = open('csvtry\\states.csv',encoding='utf8')
    
    csvreader = csv.reader(file)
    
    db = psycopg2.connect(user = "*",
                          password = "*",
                          host = "*",
                          port = "5432",
                          database = "*")
    
    imlec = db.cursor()
    
    for row in csvreader:
    
    #id,name,country_id,country_code,country_name,state_code,type,latitude,longitude
    
            id = row[0]
            name = row [1]
            country_id = row [2]
            country_code = row [3]
            country_name = row [4]
            state_code = row [5]
            type = row [6]
            latitude = row [7]
            if latitude == "":
                    latitude = None
            longitude = row [8]
            if longitude == "":
                    longitude = None
    
            values = (id,name,country_id,country_code,country_name,state_code,type,latitude,longitude)
    
    
            postgres_insert_query = """ INSERT INTO states (id,name,country_id,country_code,country_name,state_code,type,latitude,longitude) 
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
            
            imlec.execute(postgres_insert_query, values)
            db.commit()
    
    
    
    db.close()

edited Jul 19, 2022 at 16:37

answered Jul 19, 2022 at 16:29

Selimhan Baltaş

337 bronze badges

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

1 Comment

Adrian Klaver Over a year ago

You can simplify this to: latitude = row[7].strip() or None. This will return the value for row[7] if is True otherwise it will return None. The *.strip() is in there to catch cases like " " and strip them down to "" so they evaluate as False.

Collectives™ on Stack Overflow

Python postgreSQL inserting ""(null) value to double precision column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related