I have a postgresql table created in python that I need to then populate with data from a csv file. The csv file has 4 columns and a header row. When I use a for loop with INSERT INTO it's not working correctly.
I was getting an error: ProgrammingError: can't adapt type 'nump.int64' so I added int in where the data should be an integer and then be able to be accessed using %d in VALUES but now I am getting an error stating ValueError: unsupported format character 'd' (0x66) at index 87
I've looked over all the similar issues reported on other questions and can't seem to find something that works.
The table looks like this (with more lines):
| ID | Gender | Weight | Age |
|---|---|---|---|
| A | F | 121 | 20 |
| B | M | 156 | 31 |
| C | F | 110 | 18 |
The code I am running is the following:
import pandas as pd
df = pd.read_csv('df.csv')
for x in df.index:
cursor.execute("""
INSERT INTO iddata (ID, Gender, Weight, Age)
VALUES (%s, %s, %d, %d)""" % (df.loc[x]['ID'],
df.loc[x]['Gender'],
int(df.loc[x]['Weight']),
int(df.loc[x]['Age'])))
conn.commit
