How to insert the records into postgresql python?

Question

I am trying to insert the pandas dataframe into postgresql table. What I am doing is inserting the record of dataframe loop by loop. I am recursively getting errors, Code is shown below: Code:

    import psycopg2
    df = pd.read_csv('dataframe.csv')
    
    conn = psycopg2.connect(database = "postgres",
                            user = "postgres",
                            password = "12345",
                            host = "127.0.0.1",
                            port = "5432")
    
    cur = conn.cursor()
    
    for i in range(0,len(df)):
        cur.execute("INSERT INTO stock_market_forecasting_new (date, open, high, low, close) \
          VALUES (df['date'][i], df['open'][i], df['high'][i], df['low'][i], df['close'][i])")
    
    conn.commit()
    print("Records created successfully")
    conn.close()

Error:

UndefinedColumn: column "df" does not exist
LINE 1: ..._new (date, open, high, low, close)       VALUES (df['date']...

Edit1:

I am doing like this,

cur.execute("SELECT * from STOCK_MARKET_FORECASTING")
rows = cur.fetchall()
for row in rows:
    print(row)
print("Operation done successfully")
conn.close()

Output giving:

('2021-12-07 00:00:00', 1.12837, 1.12846, 1.1279, 1.128)
('2021-12-07 01:00:00', 1.12799, 1.12827, 1.1276, 1.1282)

Output which I want should be with column names:

       **Date          open      high     low   close**
    ('2021-12-07 00:00:00', 1.12837, 1.12846, 1.1279, 1.128)
    ('2021-12-07 01:00:00', 1.12799, 1.12827, 1.1276, 1.1282)

Answer 1

You didn't use a formatted string. It should be f"VALUES ({df['date'][i]}," ect depending on your data type, but that always leads to issues with quotation marks. I recommend inserting with tuples instead as seen in the documentation:

import psycopg2
df = pd.read_csv('dataframe.csv')

conn = psycopg2.connect(database = "postgres",
                        user = "postgres",
                        password = "12345",
                        host = "127.0.0.1",
                        port = "5432")

cur = conn.cursor()

for i in range(0 ,len(df)):
    values = (df['date'][i], df['open'][i], df['high'][i], df['low'][i], df['close'][i])
    cur.execute("INSERT INTO stock_market_forecasting_new (date, open, high, low, close) VALUES (%s, %s, %s, %s, %s)",
                values)

conn.commit()
print("Records created successfully")
conn.close()

Alternatively, you could see if df.to_sql() (documentation) supports psycopg2 connections:

df.to_sql('stock_market_forecasting_new', con=conn, if_exists='append', index=False)