How to populate an SQLite table in python with a .txt file

Question

I have a .txt file with this information:

ID          NAME        AGE         ADDRESS     SALARY
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

I want to populate a table with this information.

So far I have tried this:

import sqlite3
import os.path
import csv

miRuta1 = os.path.abspath(os.path.dirname(__file__))
ruta1 = os.path.join(miRuta1, "../problema6/informacion.txt")

miRuta3 = os.path.abspath(os.path.dirname(__file__))
ruta3 = os.path.join(miRuta3, "../problema6/company.sql")

connection = sqlite3.connect(ruta3)
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS Informacion(id INT, name TEXT, age INT, address TEXT, salary REAL, PRIMARY KEY (id))")

with open(ruta1) as archivo:
    next(archivo)
    reader = csv.reader(archivo, delimiter="\t")
    data = [row for row in reader]

cursor.executemany("INSERT INTO Informacion(id, name, age, address, salary) VALUES(?, ?, ?, ?, ?);", data)

but I'm getting this error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 5, and there are 1 supplied.

Are you sure that file is *tab* delimited, and not just spaces? — Schwern, Jun 07 '20 at 21:43
I'm pretty sure my answer was incorrect. I have corrected it. — Schwern, Jun 07 '20 at 21:48

Schwern · Accepted Answer · 2020-06-07T21:47:34.340

There's no need for a program. SQLite can natively import tab separated files.

~~To your question, executemany takes a list of tuples. You've set data to a single list. Instead you need to push rows onto data to create a list of lists.~~

I believe your problem is your file is not tab-delimited. It is fixed-width. csv.reader will interpret each line as a single column. If you print(data) you'll see something like this.

[['1           Paul        32          California  20000.0'],
 ['2           Allen       25          Texas       15000.0'],
 ['3           Teddy       23          Norway      20000.0'],
 ['4           Mark        25          Rich-Mond   65000.0'],
 ['5           David       27          Texas       85000.0'],
 ['6           Kim         22          South-Hall  45000.0'],
 ['7           James       24          Houston     10000.0']
]

Note how each row is a single string. Thus "The current statement uses 5, and there are 1 supplied."

You'll need to parse it as a fixed width file in Python or in SQLite.

How to populate an SQLite table in python with a .txt file

1 Answers1