insert data csv to db

Question

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

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
– keerthana, Commented Aug 20, 2021 at 9:25
cdn.riddle.com/embeds/v2/images/q_80,c_fill,w_960,h_540/d64/… this is my image url — keerthana
– keerthana, Commented Aug 20, 2021 at 10:19
or any other method to solve the problem plz help me to find solution — keerthana
– keerthana, Commented Aug 20, 2021 at 10:20
always put code, data and full error message as text (not screenshot, not link) in question (not in comment). — furas
– furas, Commented Aug 20, 2021 at 11:33

Naveenkumar M · Accepted Answer · 2021-08-21 03:00:17Z

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.

answered Aug 21, 2021 at 3:00

Naveenkumar M

6794 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Klaver Over a year ago

Three things wrong with this answer: 1) If the CSV file is of any size executemany() will take a long time. See Cursor 2) In psycopg2 the parameter placeholder is %s not ?. See Parameters 3) psycopg2 has COPY support. See Copy.

Collectives™ on Stack Overflow

insert data csv to db

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related