0
import csv
import psycopg2
conn = psycopg2.connect(database=" ", user=" ", password=" ", host=" ", port= )

cur = conn.cursor()
with open('21.csv', 'r') as f:
       next(f)
       cur.copy_from(f, 'temp_questions', sep=',')
                
conn.commit()

i have try to insert data into my db i got error:

cur.copy_from(f, 'temp_questions', sep=',')
psycopg2.errors.QueryCanceled: COPY from stdin failed: error in .read() call: exceptions.ValueError Mixing iteration and read methods would lose data

CONTEXT: COPY temp_questions, line 1

in my csv file -i have 18 column and table(database)- id with 18 column i don't know how to insert data

deceze
  • 510,633
  • 85
  • 743
  • 889
keerthana
  • 1
  • 6
  • cur.copy_from(f, 'temp_questions', sep=',',columns=['question', 'choice_a', 'choice_a_media_url', 'choice_b', 'choice_b_media_url', 'choice_c', 'choice_c_media_url', 'choice_d','choice_d_media_url','choice_e','choice_e_media_url','answer','ctime','utime','question_media_url','status','qtype','wiki_url']) i change code added column name(table column) but same error – keerthana Aug 20 '21 at 09:25
  • https://cdn.riddle.com/embeds/v2/images/q_80,c_fill,w_960,h_540/d64/d64444212e5390346d0a15321342cfc6.jpg this is my image url – keerthana Aug 20 '21 at 10:19
  • here also having semicolon i want to remove this how to do? – keerthana Aug 20 '21 at 10:20
  • or any other method to solve the problem plz help me to find solution – keerthana Aug 20 '21 at 10:20
  • always put code, data and full error message as text (not screenshot, not link) in question (not in comment). – furas Aug 20 '21 at 11:33
  • Check https://stackoverflow.com/a/30059899/2235381 – lojza Aug 20 '21 at 14:30
  • Lose the `next(f)` that is where the error is coming from. Read the docs [Copy from](https://www.psycopg.org/docs/cursor.html#cursor.copy_from). – Adrian Klaver Aug 20 '21 at 15:14

1 Answers1

0
import csv
db=conn.connect('test.db')
print("connected succesfully")
csv_file="test.csv"
with open(csv_file,'r') as csv_file:
    csvreader=csv.reader(csv_file)
    fields=next(csvreader)
    sql_insert_query='INSERT INTO Test (name,age) VALUES(?,?)'
    db.executemany(sql_insert_query, csvreader)
    print("inserted")
data=db.execute("SELECT * FROM Test")
for i in data:
    print(i)

Read the data from csv file and use executemany to insert an array of elements to the database.

Naveenkumar M
  • 616
  • 3
  • 17
  • Three things wrong with this answer: 1) If the CSV file is of any size `executemany()` will take a long time. See [Cursor](https://www.psycopg.org/docs/cursor.html) 2) In `psycopg2` the parameter placeholder is `%s` not `?`. See [Parameters](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries) 3) `psycopg2` has `COPY` support. See [Copy](https://www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from). – Adrian Klaver Aug 21 '21 at 14:19