Sqlite Loop Statements

SQLite loop statements?

You can do this sort of thing in straight SQL if you have an extra table that holds all the integers that you need.

Suppose your StartRange and EndRange range between one and ten and you have a table like this:

sqlite> select i from ints;
i
1
.
.
.
10

This table simply contains all the possible integers that you need (i.e. one through ten).

Then if you also have this:

sqlite> create table t (startrange int not null, endrange int not null);
sqlite> insert into t values(1, 3);
sqlite> create table target (i int not null);

You can do your INSERTs into target with a join:

insert into target (i)
select ints.i
from ints join t on (ints.i >= t.startrange and ints.i <= t.endrange)

The result is this:

sqlite> select * from target;
i
1
2
3

Of course your real t would have more rows so you'd want a WHERE clause to limit which row of t you look at.

Similar things are often done with dates (look up "calendar tables").

So if your ranges are small (for some definition of small) then generate your ints table once, add an index to it, and use the above technique to do all the INSERTs right inside the database. Other databases have their own ways (such as PostgreSQL's generate_series) to do this sort of thing without need an explicit ints table but SQLite is (intentionally) limited.

SQL is generally set-based so loops aren't natural. What is natural is building the appropriate sets by describing what you need. OTOH, sometimes unnatural acts are necessary and sensible.

I don't know if this makes sense for your application, I just thought I'd demonstrate how it can be done. If this approach doesn't make sense in your case then you can generate a bunch of INSERT statements outside the database.

Does `sqlite3` support loops?

SQLite does not support loops. Here is the entire language, you'll notice that structured programming is completely absent.

However, that's not to say that you can't get what you want without loops, using sets or some other SQL construct instead. In your case it might be as simple as:

 select measurement, count( measurement ) from errors GROUP BY measurement

That will give you a list of all measurements in the errors table and a count of how often each one occurs.

In general, SQL engines are best utilized by expressing your query in a single (sometimes complex) SQL statement, which is submitted to the engine for optimization. In your example you've already codified some decisions about the strategy used to get the data from the database -- it's a tenet of SQL that the engine is better able to make those decisions than the programmer.

How do i insert 1000 times in one statement? with SQLITE?

OK, here's a way to do it in pure SQL...

create table if not exists test1(id integer primary key, val integer);

create trigger test1_ins_trigger after insert on test1
  when new.val < 1000 begin
    insert into test1(val) values(new.val + 1);
  end;

pragma recursive_triggers = 1;

insert into test1(val) values(1);

SQLite: Loop over SELECT statement, replacing the WHERE clause

You could use a correlated subquery to get that value for each user ID:

SELECT id,
       username,
       (SELECT MAX(id)
        FROM messages
        WHERE sender    = users.id
           OR recipient = users.id
       ) AS last_message_id
FROM users

This is also possible with GROUP BY.
First join the two table together, then create a group for each user:

SELECT users.id,
       MAX(messages.id)
FROM users
JOIN messages ON users.id = messages.sender OR
                 users.id = messages.recipient
GROUP BY users.id

Trying to break out of while loop with user input of keyword 'exit'. (SQLite3 table insert) [Python]

input is a function and returns a value of the user input. https://www.geeksforgeeks.org/taking-input-in-python/.

if input != 'exit': will always be true, because input is a function, and will never equal 'exit'

You'll need to check the return value of input to see if it matches the string 'exit'.

---

EDIT: try the below - this option should be 'scalable' if you have more prompts or what not. But there are many ways to do what you're trying to do. Below is just one of them. I added comments since it seems like you're new to python!

import sqlite3

conn = sqlite3.connect(':memory:')
c = conn.cursor()
cursor = conn.cursor()
c.execute("""CREATE TABLE IF NOT EXISTS catalog (
       number integer NOT NULL PRIMARY KEY autoincrement,
       type text NOT NULL,
       taxon text,
       species text NOT NULL,
       part text NOT NULL,
       age integer,
       layer text,
       notes text
       )""")

while True:
      print('Please enter individual specimen data: ')
      input_prompts = [
        'Catalog #: ',
        'Type of Specimen: ',
        'Taxon: ',
        'Species: ',
        'Body Part: ',
        'Estimated Age: ',
        'Sedimentary Layer: ',
        'Notes: '
      ]

      responses = []
      response = ''
      for prompt in input_prompts: # loop over our prompts
        response = input(prompt)

        if response == 'exit':
          break # break out of for loop
        responses.append(response)
      
      if response == 'exit':
        break # break out of while loop

      # we do list destructuring below to get the different responses from the list
      c_number, c_type, c_taxon, c_species, c_part, c_age, c_layer, c_notes = responses

      cursor.execute("""
          INSERT OR IGNORE INTO catalog(number, type, taxon, species, part, age, layer, notes)
          VALUES (?,?,?,?,?,?,?,?)
          """,
                     (
          c_number,
          c_type,
          c_taxon,
          c_species,
          c_part,
          c_age,
          c_layer,
          c_notes,
          ))
      conn.commit()
      responses.clear() # clear our responses, before we start our new while loop iteration
      print('Specimen data entered successfully.')

c.execute("""CREATE VIEW catalog
AS
SELECT * FROM catalog;
""")
conn.close()

SELECT queries in a loop

Use ROW_NUMBER() window function:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY rowid) rn 
  FROM tasks 
  WHERE name IN ("TaskA", "TaskD")
)
WHERE rn <= 5

I used the column rowid to define the order of the rows.

If there is another column in your table, like a date column, that can be used to define the order, you can replace rowid with that column.

---

Related Topics