sqlite3 insert issue

[gohanzdad]

I am playing around with the sqlite3 module in preparation for a project I'm going to be working on.  I've used pymysql before without issues, but for some reason with sqlite3 I cannot get the 'executemany' function to work properly.  Everytime I run it I only get the first value in the list (whether with tuples or dictionaries) to insert.  I've looked all over the interwebs for a solution and/or explanation, but I cannot seem to find anything helpful.  Any suggestions with what I'm doing wrong in the code below?  I even commented out the 'executemany' statement and attempted a loop of regular execute statements, but that didn't make a difference.  :(

import sqlite3

table = """CREATE TABLE speakers (
            id INTEGER PRIMARY KEY NOT NULL,
            brand TEXT NOT NULL,
            type TEXT NOT NULL,
            size TEXT NOT NULL,
            price REAL NOT NULL);"""
data = [{"brand": "Logitech", "type": "computer", "size": "small", "price": 4.99},
        {"brand": "Samsung", "type": "sound bar", "size": "medium", "price": 249.99},
        {"brand": "Samsung", "type": "headphones", "size": "small", "price": 24.99}]
data2 = [("Logitech", "computer", "small", 4.99),
         ("Samsung", "sound bar", "medium", 249.99),
         ("Samsung", "headphones", "small", 24.99)]
insert_sql = """INSERT INTO speakers (brand,type,size,price) VALUES (:brand,:type,:size,:price);"""
insert_sql2 = """INSERT INTO speakers (brand,type,size,price) VALUES (?,?,?,?);"""
select_sql = "SELECT * FROM speakers;"

with sqlite3.connect("speakers.db") as conn:
    cur = conn.cursor()
    cur.executescript(table)
    # cur.executemany(insert_sql2, data2)
    for d in data2:
        cur.execute("""INSERT INTO speakers (brand,type,size,price) VALUES (?,?,?,?);""", d)
    conn.commit()
    cur.execute(select_sql)
    results = cur.fetchmany()

    for r in results:
        print(r)

[gohanzdad]

Finally figured this one out.  Feel kind of stupid that this solution did jump out at me the first time I read the sqlite3 docs.

import sqlite3
import sys

table = """CREATE TABLE speakers (
            id INTEGER PRIMARY KEY NOT NULL,
            brand TEXT NOT NULL,
            type TEXT NOT NULL,
            size TEXT NOT NULL,
            price REAL NOT NULL);"""
data = [{"brand": "Logitech", "type": "computer", "size": "small", "price": 4.99},
        {"brand": "Samsung", "type": "sound bar", "size": "medium", "price": 249.99},
        {"brand": "Samsung", "type": "headphones", "size": "small", "price": 24.99}]
data2 = [("Logitech", "computer", "small", 4.99),
         ("Samsung", "sound bar", "medium", 249.99),
         ("Samsung", "headphones", "small", 24.99)]
insert_sql = """INSERT INTO speakers (brand,type,size,price) VALUES (:brand,:type,:size,:price);"""
insert_sql2 = """INSERT INTO speakers (brand,type,size,price) VALUES (?,?,?,?);"""
select_sql = "SELECT * FROM speakers;"

with sqlite3.connect("speakers.db") as conn:
    cur = conn.cursor()
    cur.executescript(table)
    cur.executemany(insert_sql2, data2)
    conn.commit()
    for row in cur.execute(select_sql):
        print(row)

sys.exit(0)