How to Insert in Postgresql based on data from a Dataframe

Question

I am trying to make an Index of the data I have in a Dataframe (regastei via API GET) for a table I created in Postgresql. I searched a lot but I could not get a return. Someone can help me?

Imported libraries: import requests, import pandas as pd, import urllib, json and import psycopg2

con = psycopg2.connect(
        user = "sa",
        password = "123",
        host = "localhost",
        database = "db",
        port = 5432
)

cur = con.cursor()

with urllib.request.urlopen("Url q vou consumir") as url:
    data = json.loads(url.read().decode())
    #print(data)

    df = pd.DataFrame(data)

    #regional, marca, descricaoregional, nomeoriginal, regioao, versao
    newdf = pd.DataFrame(df, columns = ['regional','marca_2','regional_2','nome_da_regional','norte_e_sul','versao'])



cur.close()

con.close()

Answer 1

Felipe, good night.

I recommend you use it instead of psycopg2 use the SQLAlchemy because it works in an integrated way with pandas making the process less laborious.

Your connection would look something like this:

engine = create_engine('postgresql://sa:123@localhost:5432/db')

After variable declaration newdf you should add this method of dataframe:

newdf.to_sql('<nome da tabela>', con=engine, if_exists='append')

It is a method that handles the entire insertion process, the parameter if_exists can be fail - returns an error if the table already exists, replace - recreates the table and append - adding content to table if it already exists.

If you want to know more about the method you can access this documentation