Loading dataframe using copy_from from StringIO into Postgresql psycopg

Question

I cannot seam to figure out how to fix this issue.

My code looks something like

import pandas 
import psycopg2
import io
from sqlalchemy import create_engine
#engine works
cursor = engine.raw_connection()

output = io.StringIO()
df.to_csv(output, sep=',', header=False, index=False)
output.seek(0)
contents = output.getvalue()
print(contents)
cursor.copy_from(output, table_name, sep=",", null="NULL")
connection.commit()

It seems like my integers are interpreted incorrectly:

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "180.0" CONTEXT:  COPY athletes, line 1, column height: "180.0"

Here is sample of contents:

135552,Jan (Johann-) Zybert (Siebert-),M,1908-01-01,NULL,NULL
135553,Galina Ivanovna Zybina (-Fyodorova),F,1931-01-01,168.0,80.0
135554,Bogusaw Zych,M,1952-01-01,182.0,82.0
135556,Bogusaw Stanisaw Zychowicz,M,1961-01-01,189.0,80.0

How can I fix this?

Thank you so much know that I knew that the numbers are floats not int as required by our assignment, I was able to change it the data from the dataframe to ints — xyhlon
– xyhlon, Commented Apr 14, 2021 at 10:57

xyhlon · Accepted Answer · 2021-04-14 11:02:37Z

0

As @Frank Heikens said it had to do with the fields in the database, you could either change them to decimal or as I did it you could also adjust the datatype columns in the dataframe.

Python pandas: output dataframe to csv with integers

def convert(x):
    try:
        return x.astype(int)
    except:
        return x

df = df.apply(convert)

Thanks everyone

answered Apr 14, 2021 at 11:02

xyhlon

952 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Loading dataframe using copy_from from StringIO into Postgresql psycopg

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related