Home / Questions / 513867
Accepted
AlejandroR
Asked: 2022-07-10 01:31:44 +0800 CST 2022-07-10 01:31:44 +0800 CST

Inserting data from dataframe (Python) to postgres

  • 772

I am trying to get data from a dataFrame to postgres. I have all the data loaded in the dataframe, but when inserting it in postgres it gives me formatting problems.

I have the table created like this:

 CREATE TABLE IF NOT EXISTS DL.desempleo (id varchar(2) NOT NULL, Periodo VARCHAR (10) NOT NULL, Total DECIMAL);

And insert like this:

INSERT INTO DL.desempleo (id,Periodo, Total) values (%s,%s,%d)

Can someone tell me with what format I put the Total value, which is Decimal (7.55), if I put it as %d it gives me the following error:

Error al conectar con PostgreSQL unsupported format character 'd' (0x64) at index %Id
python

1 Answers

  1. Best Answer

    You can replace the %dwith %s, in the questions and answers about the use of psycopgis commented: https://www.psycopg.org/docs/faq.html#problems-with-type-conversions

    I can't pass an integer or a float parameter to my query: it says a number is required, but it is a number! In your query string, you always have to use %s placeholders, even when passing a number. All Python objects are converted by Psycopg in their SQL representation, so they get passed to the query as strings. See Passing parameters to SQL queries.

    >> cur.execute("INSERT INTO numbers VALUES (%d)", (42,)) # WRONG
>> cur.execute("INSERT INTO numbers VALUES (%s)", (42,)) # correct
    • 1