Issue with multiple sqlite3 inserts

Question

I am creating a web-based POS system. Once the users clicks the "order submit" data is submitted via this schema:

CREATE TABLE orders (
    transaction_id integer primary key autoincrement,
    total_price integer not null
);
CREATE TABLE order_items (
    transaction_id integer REFERENCES orders(transaction_id),
    SKU integer not null,
    product_name text not null,
    unit_price integer not null,
    quantity integer not null
);

Through this flask code:

@app.route('/load_ajax', methods=["GET", "POST"])
def load_ajax():
    if request.method == "POST":
        data = request.get_json()
        for group in groupby(data, itemgetter('name')):
            id, data_list = group
            for d in data_list:
                print d['subtotal']
                db = get_db()
                db.execute('insert into order_items (SKU, product_name, unit_price, quantity) values (?, ?, ?, ?); insert into orders (total_price) values (?)',
                [d['sku'], d['name'], d['price'], d['quantity']],[d['subtotal']])
                db.commit()
        return jsonify(location=url_for('thankyou'))

I am getting a 500 error and I'm not sure why it is happening. Do I need to do two different db.execute statements?

I would suggest taking a look at Flask-SQLAlchemy. You won't have to write SQL queries like that inside your code. — kylieCatt
– kylieCatt, Commented May 23, 2014 at 18:00

Martijn Pieters · Accepted Answer · 2014-05-23 18:05:10Z

2

Yes, you need to use two different .execute() statements. Quoting the cursor.execute() documentation:

execute() will only execute a single SQL statement.

So do so:

db.execute('insert into order_items (SKU, product_name, unit_price, quantity) values (?, ?, ?, ?)',
           (d['sku'], d['name'], d['price'], d['quantity']))
db.execute('insert into orders (total_price) values (?)',
           (d['subtotal'],))

edited May 23, 2014 at 18:05

answered May 23, 2014 at 17:44

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue with multiple sqlite3 inserts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related