python 3.x - The letters of a string is being spilt into different rows and added to a sqlite database

Question

I am trying to fetch a single value each time from a list and store them individually in sqlite database. Snippet is as below:

curr.execute('''CREATE TABLE testschritte(indeces INT PRIMARY KEY,test_step TEXT,teil_num INT, FOREIGN KEY(teil_num) REFERENCES testfall(teil_num) ON DELETE SET NULL)''')

def db_data(self):

    samt = Protokoll()
    samt.test_case()
    DML = '''INSERT INTO testfall VALUES(?,?)'''
    data = list(zip_longest(samt.teil_num,samt.cases, fillvalue=None))
    self.curr.executemany(DML, data)
    self.conn.commit()

# Protokoll is a class created in another python file and test_case is the function I want to retreive the values from that file.

Above is working fine. Here below, I want to pick the samt.teil_num(foreign key to this table) individually and add to database.

    test = Testschritte()
    test.test_steps()
    DML = '''INSERT INTO testschritte VALUES(?,?,?)'''
    data = list(zip_longest(test.indeces,test.prop,samt.teil_num[1],fillvalue=None))
    self.curr.executemany(DML, data)
    self.conn.commit()
# Testschritte is a class created in another python file and test_steps is the function I want to retreive the values from that file.

The current output I am getting looks like below:

#Table Testschritte

test.indeces | test.prop | samt.teil_num
             |           |
    5        |    a      |    T
    6        |    b      |    e
    7        |    Null   |    i
    Null     |    c      |    l
    Null     |    Null   |    1

What is expected is:

test.indeces | test.prop | samt.teil_num
             |           |
    5        |    a      |    Teil1
    6        |    b      |    
    7        |    Null   |    
    Null     |    c      |    
    Null     |    Null   |    

samt.teil_num value corresponds to 5,6,7 and a,b,c of other column values hence, I am trying to correspond Teil1 to all of them. Similarly, Teil2 to other column values, but the Teil1 letters are being split to different rows. I am aware that data = list( zip_longest (test.indeces, test.prop, samt.teil_num[1] ,fillvalue=None )) has to be modified but not sure how. I tried specifying samt.teil_num[1] out of zip_longest but no luck.

PS: samt.teil_num is a list that has values Teil0,Teil1,Teil2 etc and samt.teil_num[1] should give me Teil1(already giving) but letters split into different rows.

Been stuck with this since days . Could anybody help me to get the expected output.

Furthermore, if the below is possible:

test.indeces | test.prop | samt.teil_num
             |           |
    5        |    a      |    Teil1
    6        |    b      |    Teil1 
    7        |    Null   |    Teil1
    Null     |    c      |    Teil1
    Null     |    Null   |  

Because indeces 5,6,7 and prop a,b,c share same teil_num value i,e Teil1

question from:https://stackoverflow.com/questions/65937030/the-letters-of-a-string-is-being-spilt-into-different-rows-and-added-to-a-sqlite

Answer 1

For just samt.teil_num[1]:

from itertools import zip_longest
indices = [5,6,7,None,None]
prop = ['a','b',None,'c',None]
teil_num = ['Teil0','Teil1','Teil2','Teil3','Teil4']
data = list(zip_longest(indices,prop,[teil_num[0]],fillvalue=None))
print(data)

The output formed is:

[(5, 'a', 'Teil0'), (6, 'b', None), (7, None, None), (None, 'c', None), (None, None, None)]

if you use teil_num as a whole:

from itertools import zip_longest
indices = [5,6,7,None,None]
prop = ['a','b',None,'c',None]
teil_num = ['Teil0','Teil1','Teil2','Teil3','Teil4']
data = list(zip_longest(indices,prop,teil_num,fillvalue=None))
print(data)

Output:

[(5, 'a', 'Teil0'), (6, 'b', 'Teil1'), (7, None, 'Teil2'), (None, 'c', 'Teil3'), (None, None, 'Teil4')]

The reason for this is the string(teil_num[1]) gets typecasted as list which is as:

['T', 'e', 'i', 'l', '1']