May-16-2017, 04:19 PM
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)