Python + sqlite3: executemany with "constructed" values

Question

I'm trying to use executemany in Python to populate some rows.

However, I'm getting:

sqlite3.OperationalError: 3 values for 4 columns

I understand that in general, you use executemany to stuff raw values into the DB as efficiently as possible, and with that in mind, you generally want the number of values inserted to match the number of columns.

But what if one or more of the columns are "constructed" via SQL, and do not have any corresponding input value?

Small example:

#!/usr/bin/env python2.7
import os
import sys
import sqlite3
from contextlib import closing

DB_FILE = 'test.sqlite'

with closing(sqlite3.connect(DB_FILE)) as db:

    # First, some setup...
    cursor = db.cursor()
    cursor.execute('CREATE TABLE test (id TEXT PRIMARY KEY, a TEXT, b TEXT, c TEXT)')

    sql = '''INSERT INTO test (id, a, b, c) VALUES (?, ?, ?, ?)'''

    data = []
    for i in range(5):
        idStr = str(i)
        data.append( (idStr, 'a{}'.format(i), 'b{}'.format(i), 'c{}'.format(i)) )

    cursor.executemany(sql, data)

    # Now, to exercise the problem:
    # This does an 'upsert', but the same problem could occur in
    # other circumstances.

    sql = '''
INSERT OR REPLACE INTO test (id, a, b, c)
VALUES (
    ?,
    (SELECT a,b FROM test WHERE id = ?),
    ?
)'''

    data = []
    for i in range(3,7):
        idStr = str(i)
        data.append( (idStr, idStr, 'c{}'.format(i)) )

    cursor.executemany(sql, data)

    db.commit()

When you run that, you get the error mentioned at the top (3 values for 4 columns).

I suppose I could work around this by breaking the SELECT a,b into separate SELECT a..., SELECT b... parts, but is there a better way?

The example given is small — in reality, it might be 10 columns that I'll now have to verbosely list with their own (SELECT ...) clauses.

---

UPDATE 1 This is not specific to executemany, as I had originally thought. Even running the plain SQL by hand has the same problem.

UPDATE 2 The answers below suggest using a SELECT rather than VALUES, however, this does no work when a new row is being added (as in my "upsert" case), since the SELECT returns no rows in that case.

Answer 1

Yes! There is a better way! Replace your VALUES clause with a SELECT statement!

The query will look like:

INSERT OR REPLACE INTO test (id, a, b, c)
    SELECT ?, a, b, ? FROM test WHERE id = ?

Remember to change your data list because the parameters are in a different order now.

Answer 2

You SQL requires 4 params into VALUES. The Problem is that SQLite is not able to return multiple values into a subquery, you will run into the following error: sqlite3.OperationalError: only a single result allowed for a SELECT that is part of an expression

What you can do is to use 2 Select statements, one for each of the value you require. Here is what I tried:

sql = '''
INSERT OR REPLACE INTO test (id, a, b, c)
VALUES (
    ?,
    (SELECT a FROM test WHERE id = ?),
    (SELECT b FROM test WHERE id = ?),
    ?
)'''

There is also a more elegant solution for this.

sql = '''
INSERT OR REPLACE INTO test (id, a, b, c)
SELECT
    ?, a, b, ?
FROM test WHERE id = ?
'''

Here is a working example, i made some changes so the database table get populated accordingly:

#!/usr/bin/env python2.7
import os
import sys
import sqlite3
from contextlib import closing

DB_FILE = 'test.sqlite'

with closing(sqlite3.connect(DB_FILE)) as db:

    # First, some setup...
    cursor = db.cursor()
    cursor.execute('CREATE TABLE test (id TEXT PRIMARY KEY, a TEXT, b TEXT, c TEXT)')

    sql = '''INSERT INTO test (id, a, b, c) VALUES (?, ?, ?, ?)'''

    data = []
    for i in range(5):
        idStr = str(i)
        data.append( (idStr, 'a{}'.format(i), 'b{}'.format(i), 'c{}'.format(i)) )

    cursor.executemany(sql, data)

    sql = '''
    INSERT OR REPLACE INTO test (id, a, b, c)
    SELECT
        ?, a, b, ?
    FROM test WHERE id = ?
    '''

    data = []
    for i in range(4, 7):
        data.append((i, 'c{}'.format(i), i - 3))

    cursor.executemany(sql, data)

    db.commit()

Update 1

If you need Default values in case the select returns nothing you can use this:

sql = '''
INSERT OR REPLACE INTO test (id, a, b, c)
VALUES (
    ?,
    (SELECT ifnull((SELECT a FROM test WHERE id = ?), 'Default 1')),
    (SELECT ifnull((SELECT b FROM test WHERE id = ?), 'Default 2')),
    ?
)'''

data = []
for i in range(4, 10):
    data.append((i, i, i, 'c{}'.format(i)))