SQLite Forum

reading multiple lines from multiple sources...

reading multiple lines from multiple sources...

(1.1) By bill (3python) on 2021-08-09 20:40:00 edited from 1.0 [source]

Hello all!

I'm having issues with reading multiple lines from multiple sources (txt files)  into rows. Below is code.. If I use the commented out break, I get the first line of each file into its appropriate row as desired, but no further as intended purpose of break... If I don't use break, I get the last line of each file. How can I get each line printed to the database?

I'm pulling 10 rows from 5 different text files at the moment. 

text files are simple data, below is an example





here is my code...

def printthis():
    db_elements = 'INSERT INTO datasamples(names, colors, shapes, favfood) VALUES(?,?,?,?,?)'
    print("Connected to SQLite")

    i = 0
    while i <= 9:
        with open(f1) as j:
            for line in j:
                line1 = line
                # break
        with open(f2) as q:
            for line in q:
                line2 = line
                # break
        with open(f3) as w:
            for line in w:
                line3 = line
                # break
        with open(f4) as u:
            for line in u:
                line4 = line
                # break
        with open(f5) as k:
            for line in k:
                line5 = line
                # break
        curs.execute(db_elements, (line1, line2, line3, line4, line5))
        i += 1

I know there probably is a simpler way to achieve what I am going for, I'm just running into walls.


(2) By Larry Brasfield (larrybr) on 2021-08-09 21:54:50 in reply to 1.1 [link] [source]

This looks too much like a homework assignment for me to see writing it for you as a favor. However, a couple tips should not defeat your learning curve traverse.

You need to keep all 5 files open, then in each pass of the loop read a line from each of the files to become a datum for each of 5 (or 4?) columns. No break needed.

In programming, the usual way to express using N similar objects similarly is to place them into an N-length array and iterate over it. For your solution, this will mean two, nested loops: An outer one iterating over file lines/DB rows; and an inner one iterating over the files. You will need separate N-long iteration loops to open and close the files.

(3) By RandomCoder on 2021-08-09 22:02:30 in reply to 1.1 [link] [source]

The reason you get the last line of each file is that is exactly what your code is doing, without the break, it's processing each file in turn, then after it's done processing each file, it inserts a row into your database table. You then repeat the process 9 times.

If you want to operate on each line of the file in turn, you'll need to open them up, and enumerate through all of the files in lockstep. There are several ways to do this, one such way is to use the built in Python operator zip to work through the enumeration of each file for you:

#!/usr/bin/env python3

import sqlite3

def printthis():
    db = sqlite3.connect("example.db")
    db.execute("CREATE TABLE IF NOT EXISTS datasamples(names, colors, shapes, favfood);")
    sql = 'INSERT INTO datasamples(names, colors, shapes, favfood) VALUES(?,?,?,?)'
    print("Connected to SQLite")

    with open("names.txt") as f1, open("colors.txt") as f2, open("shapes.txt") as f3, open("favfood.txt") as f4:
        for name, color, shapes, favfood in zip(f1, f2, f3, f4):
            db.execute(sql, (name.strip(), color.strip(), shapes.strip(), favfood.strip()))

    print("-- results --")
    for row in db.execute("SELECT * from datasamples;"):
        print(", ".join(row))

if __name__ == "__main__":

And as a side note: While I've answered the question for you, and fixed a bug in your SQL (you had one too many ? for the bound parameters, really your question had nothing to do with SQLite. You'll probably get much better help with Python specific issues on a forum for Python issues.

(4) By bill (3python) on 2021-08-09 23:06:03 in reply to 3 [link] [source]

Thank you RandomCoder I slightly recall the zip method now. I have been out of the field for several years. Thanks for the explanation, this gets me pointed in the right direction.

My apologies upon reviewing this post would should have been posted to a Python forum.

Thanks friend!