1

I am writing some SQL queries (which I'm pretty new at) for a SQLite DB using Python 3.3 and sqlite3. I have 2 tables that are relevant to this query Bins and Shelves. I ask the user for some input to create a bin and place it on a shelf. Then the query takes the name of the shelf from the user and uses it to get the shelf_id. Then the data the user entered to create the bin (just a name at this point) is used alongside the shelf_id to create a new record (the bin table contains bin_id, name and shelf_id columns).

Now I have this working with these queries but I don't want to query the DB twice if I can do it in one shot:

results = cur.execute('SELECT shelf_id FROM Shelves WHERE name = ?', [n_bin_shelf])
shelf_id = results.fetchone()
cur.execute("INSERT into Bins (name, shelf_id) VALUES (?,?)", [n_name, shelf_id[0]])

So I wrote this to do it all at once:

cur.execute(
            "SELECT
            shelf_id s_id
            FROM Shelves s
            WHERE s.name = ?;
            INSERT into Bins (name, shelf_id) VALUES (?,s_id)", [n_bin_shelf, n_name]
        )

The problem is the alias s_id isn't preserved after the semi-colon. It executes fine if I substitute the literal for the shelf_id instead of the alias.

How can I preserve the alias across the semi-colon to execute this query successfully?

UPDATE: What I ended up using that worked perfectly and is much cleaner:

cur.execute(
    "INSERT INTO bins ( name, shelf_id ) VALUES(?, (SELECT  shelf_id s_id FROM Shelves s WHERE s_id = 1))",
    [n_name]
)
kylieCatt
  • 10,672
  • 5
  • 43
  • 51
  • From the [documentation](http://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.execute): *execute() will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use executescript() if you want to execute multiple SQL statements with one call.* – Martijn Pieters Jan 10 '14 at 22:28
  • 1
    Can this post help you https://stackoverflow.com/questions/25969/sql-insert-into-values-select-from ? (see second answer) – Cilyan Jan 10 '14 at 22:34
  • @Cilyan That post was very helpful. It solved my problem completely. Thanks for the link! – kylieCatt Jan 10 '14 at 23:29
  • Could you please close you question as duplicate and eventually add your answer to that post (your notation doesn't seem to be presented there)? – Cilyan Jan 10 '14 at 23:37

0 Answers0