Postgres how to insert python dictionary from csv file?-CodePudding

I have a csv events.csv:

"PATIENT ID,PATIENT NAME,EVENT TYPE,EVENT VALUE,EVENT UNIT,EVENT TIME"
"1,Jane,HR,82,beats/minute,2021-07-07T02:27:00Z"
"1,Jane,RR,5,breaths/minute,2021-07-07T02:27:00Z"

Then I use python csv to read it:

import csv
with open(r'/Users/williaml/Downloads/events.csv') as csvfile: 
    spamreader = csv.DictReader(csvfile, delimiter=',' ,quotechar=' ')
    for row in spamreader:            
        print(row)

Output:

{'"PATIENT ID': '"1', 'PATIENT NAME': 'Jane', 'EVENT TYPE': 'HR', 'EVENT VALUE': '82', 'EVENT UNIT': 'beats/minute', 'EVENT TIME"': '2021-07-07T02:27:00Z"'}

{'"PATIENT ID': '"1', 'PATIENT NAME': 'Jane', 'EVENT TYPE': 'RR', 'EVENT VALUE': '5', 'EVENT UNIT': 'breaths/minute', 'EVENT TIME"': '2021-07-07T02:27:00Z"'}

And I tried to insert these rows into database:

import psycopg2
conn = psycopg2.connect(host='localhost', dbname='patientdb',user='username',password='password',port='')
cur = conn.cursor()
import csv
with open(r'apps/patients/management/commands/events.csv') as csvfile:
        spamreader = csv.DictReader(csvfile, delimiter=',' ,quotechar=' ')
        for row in spamreader:
                cur.execute(f"""INSERT INTO patients_event (patient_id, event_type_id , event_value ,event_unit, event_time) VALUES
  ({row['"PATIENT ID']},{row['EVENT TYPE']},{row['EVENT VALUE']},
   {row['EVENT UNIT']},{row['EVENT TIME"']})""")

Error:

psycopg2.errors.UndefinedColumn: column "1,HR,82,
   beats/minute,2021-07-07T02:27:00Z" does not exist
LINE 2:   ("1,HR,82,
           ^

However if I directly run the following sql in database command terminal it works:

INSERT INTO patients_event (patient_id, event_type_id , event_value ,event_unit, event_time) VALUES('1','HR','82','beats/minute','2021-07-07T02:27:00Z');

So I think it seems this part of code is incorrect:

cur.execute(f"""INSERT INTO patients_event (patient_id, event_type_id , event_value ,event_unit, event_time) VALUES
      ({row['"PATIENT ID']},{row['EVENT TYPE']},{row['EVENT VALUE']},
       {row['EVENT UNIT']},{row['EVENT TIME"']})""")

Any friend can help?

CodePudding user response：

Use this:

cur.execute(f"""INSERT INTO patients_event (patient_id, event_type_id , event_value ,event_unit, event_time) VALUES
      ({row['"PATIENT ID'][1:]},{row['EVENT TYPE']},{row['EVENT VALUE']},
       {row['EVENT UNIT']},{row['EVENT TIME"'][:-1]})""")

So, this basically handles your extra quotes in the output dict which I have mentioned in the comment here which is causing this issue.

And, that's why

INSERT INTO patients_event (patient_id, event_type_id , event_value ,event_unit, event_time) VALUES('1','HR','82','beats/minute','2021-07-07T02:27:00Z');

passes on db terminal as you can see the difference between values inserted here in both ways.

CodePudding user response：

so one problem with CSV is " at the beginning and end of each line. The way you are interpreting it is causing it to become part of SQL expression.

           here 
LINE 2:   ("1,HR,82

And this is causing error, as it's not correctly closed" and actually it not intended for it to be in the generated SQL.