Python Forum
Thread Rating:
  • 1 Vote(s) - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
sqlite3 insert issue
#1
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)
Reply
#2
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)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  sqlite3 Conn Insert Value Error TylerDunbar 3 677 Sep-04-2023, 06:32 PM
Last Post: deanhystad
  sqlite3 operational error on insert query jonesin1974 5 4,212 Jun-26-2018, 03:31 PM
Last Post: Larz60+
  insert list into sqlite3 mepyyeti 3 13,602 Jan-15-2018, 06:35 AM
Last Post: Gribouillis
  Insert using psycopg giving syntax error near "INSERT INTO" olgethorpe 4 15,498 Jul-21-2017, 07:39 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020