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?

Answer 1

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)