turning a list into pairs and writing them to a .db file with sqlite3

Question

I need to take a list of information such as:

my_list = ['a','1','b','2','c','3','d','4']

I need to write this information as pairs into two separate columns in a .db file with sqlite3.

| a | 1 |
| b | 2 |
| c | 3 |
| d | 4 |

sqlite3 will not allow me to pass a list as an argument so I tired:

connection = sqlite3.connect('mytest.db')
cursor = conn.cursor
cursor.execute('CREATE TABLE IF NOT EXISTS test(c1 TEXT, c2 TEXT)')

for i in my_list[0:len(my_list):2]:
    cursor.execute(INSERT INTO test (c1) VALUES (?)',(i,))
    connection.commit

for i in my_list[1:len(my_list):2]:
    cursor.execute(INSERT INTO test (c2) VALUES (?)',(i,))
    connection.commit

However, this is making the table appear like so:

|  a   | null |
|  b   | null |
|  c   | null |
|  d   | null |
| null |  1   |
| null |  2   |
| null |  3   |
| null |  4   |

Show source
| database   | sqlite   | python-3.x   | sqlite3   2017-01-02 05:01 1 Answers

Answers ( 1 )

  1. 2017-01-02 06:01

    You can do this with a pairwise iteration and executemany():

    def pairwise(iterable):
        a = iter(iterable)
        return zip(a, a)
    
    my_list = ['a','1','b','2','c','3','d','4']
    cursor.executemany("""
        INSERT INTO 
            test (c1, c2) 
        VALUES 
            (?, ?)""", pairwise(my_list))
    connection.commit()  # note: you need to call the commit method
    
◀ Go back