python script to pass txt file to sqlite3 database

Question

I need to pass the text line per line in a .txt file to a column from a table that got 4 columns, all empty, in a database.

import sqlite3

conn = sqlite3.connect('prueback.db')
c = conn.cursor()


num_lines = sum(1 for line in open('subgenres.txt'))
f=open('subgenres.txt')
lines=f.readlines()
i=1
while True:
    subgenrename = (lines[i])
    c.execute("INSERT INTO subgenero (subgenrename) VALUES (?)", (subgenrename,))
    conn.commit()
    i+=1
    if i == num_lines:
        break


conn.close()

That's an... interesting... way to read your file. See [how to read a file line-by-line into a list](https://stackoverflow.com/questions/3277503/how-to-read-a-file-line-by-line-into-a-list) for more idiomatic python approaches. You might also read up on `executemany()` in the python sqlite3 bindings documentation. — Shawn, Oct 16 '19 at 23:48

topcat · Answer 1 · 2019-10-17T00:53:23.933

This might be a pretty naive way of doing it, but have you try pandas? Pandas might add a little bit of overhead, but it's definitely useful for this kind of tasks, and it's not particularly slow for sqlite (another story with PostgreSQL).

import pandas as pd
import sqlite3

conn = sqlite3.connect('prueback.db')
cur = conn.cursor()

# Don't know which type of file you have. I'm assuming a fixed width column text
data = pd.read_fwf('subgenres.txt') 

# This will create the table, or add rows if exists (just change the if_exists option to "append")
data.to_sql('subgenero', conn, if_exists='replace')

conn.close()

Now, if you have another table in you database you can pull the data from the database using Pandas:

table_db = pd.read_sql("select * from table", con=conn)

# In case they have identifiers (or if they keep the same row order)
table_db['subgenero'] = table_db.merge(suborder, on='id' ,how='inner')

table_db.to_sql('table', conn, if_exists='replace')

Check more information in the Pandas documentation and read with particular attention the pd.to_sql documentation, there is a lot of options that might be particularly helpful depending on your use case.

Thank you !!! It works, it create another table in the database with the info, is there a way to replace or add the information to an existing column in a table ? — Tomahawk, Oct 16 '19 at 23:55
Pandas is a data science library and not needed for this trivial task of text file read. — Parfait, Oct 17 '19 at 01:38
Sure @Parfait, it's a huge overhead, that's why I'm referring that as naive. Although, if the OP needs to add a column to a table it can easily be updated with `pandas` and avoiding the possible constrain problems when inserting. — topcat, Oct 17 '19 at 13:49

score 0 · Answer 2 · answered Oct 17 '19 at 01:36

0

Simply open file using with context manager and iterate line by line. Never use readlines as it can be memory intensive since you read entire file at once.

with open('subgenres.txt') as f:
   for line in f:
       c.execute("INSERT INTO subgenero (subgenrename) VALUES (?)", (line,))
       conn.commit()

answered Oct 17 '19 at 01:36

Parfait

104,375
17
94
125

It sent me an error NOT NULL constraint failed:, because I got 3 more columns in that table but i only want to fill that one. – Tomahawk Oct 17 '19 at 03:07
Then you must have added a `NOT NULL` constraint in the `CREATE TABLE subgenero` call. This has nothing to do with Python code. Post output of `.schema subgenero` in SQLite CLI. Otherwise insert some non-NULL value in other required columns here. – Parfait Oct 17 '19 at 13:02
Thanks it works, do you know how can i separate the text to another column ? For example: I got a .txt file with: Rock also know as: pop. And i want to put the Rock in a column and pop in another. Thank you. – Tomahawk Oct 19 '19 at 22:10
I think that's part of another question, and you can easily find related solved questions (N.B. That's why I recommended `pandas`). – topcat Oct 21 '19 at 14:04

python script to pass txt file to sqlite3 database

2 Answers2