how to insert the data from csv file into sqlite db?

Question

There is a csv file in the format.

x1  0  1  3  6
x2  4  9  4  5
x3  8  6  7  1

I want insert it into a database with three fields "x1","x2","x3" into the following data.

x1|x2|x3
0|4|8
1|9|6
3|10|7
6|5|1

I think that i have to transform the data when i read the csv file.

import sqlite3
db="test.sqlite"
con = sqlite3.connect(db)
cur = con.cursor()
dat=open("data","r")
for id,line in enumerate(dat.readlines()):
    ?

con.executemany('insert into test values(?,?,?)', value)

how to get the right format of value here?

Martijn Pieters · Accepted Answer · 2014-03-22 07:58:20Z

2

Transpose the data with zip(*rows), remove the first line and insert with an .executemany() call:

import csv
import sqlite3

with open('data', 'r', newline='') as infh:
    rows = list(csv.reader(infh))
    columns = zip(*rows)
    next(columns, None)  # skip the first column

    with sqlite3.connect('test.sqlite') as conn:
        cursor = conn.cursor()
        cursor.executemany('insert into test values (?, ?, ?)', columns)

edited Mar 22, 2014 at 7:58

answered Mar 21, 2014 at 13:08

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

8 Comments

showkey Over a year ago

a little bug here,it should be with open('data', 'r') as infh: not rb.

Martijn Pieters Over a year ago

No, it should be opened in binary mode; this is intentional.

Martijn Pieters Over a year ago

Unless you are using Python 3; use open('data', 'r', newline='') in that case. The csv module handles newlines directly and if you open the file in text mode (Python 2) or with universal line ending handling (Python 3) you bypass that functionality.

showkey Over a year ago

i am in python3.3 ,with open('data', 'r') as infh: works fine for me,with open('data', 'rb') can't.

showkey Over a year ago

I revise it into ` rows=[row[1:] for row in rows] col=zip(*rows)` ,it's ok now.

|

Collectives™ on Stack Overflow

how to insert the data from csv file into sqlite db?

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related