+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: sql select data in row number (/thread-8839.html)
sql select data in row number - eml - Mar-09-2018
Hi,
Is there any way to code sql to select a single row by index number WITHOUT looping through every record.
E.g: if I have a database of 1000 records, is there any way I can print record 556 without knowing anything about that row other than its index position within the datbase?
Thanks
RE: sql select data in row number - Larz60+ - Mar-09-2018
That depends on the database engine.
Most have a medthod that allows selecting by rowid, but
be aware that that rowid is not immutable and can change at any moment,
RE: sql select data in row number - buran - Mar-09-2018
well, this is over-simplified...
is it just one table, because with multiple tables, JOINs between, etc.... it's not that easy to index the row. by design you may have some auto-increment id field that usually will serve as a primary key, but it's may be missing certain values (e.g. after deletion of records, etc.)
with simple small table like 1000 records you may execute SELECT * FROM TABLE and then fetchall()
this will result in a tuple of tuples and you may retrieve record with index 555 (index is zero-based as you know)
same apply for a more complex detabase, but index will depend on SORT BY clause
EDIT: Of course, Larz60+ advice about rowid (if available) is much more relevant :-)
RE: sql select data in row number - eml - Mar-09-2018
(Mar-09-2018, 07:38 PM)Larz60+ Wrote: That depends on the database engine.
Most have a medthod that allows selecting by rowid, but
be aware that that rowid is not immutable and can change at any moment,
I'm using sqlite3
(Mar-09-2018, 07:56 PM)buran Wrote: well, this is over-simplified...
is it just one table, because with multiple tables, JOINs between, etc.... it's not that easy to index the row. by design you may have some auto-increment id field that usually will serve as a primary key, but it's may be missing certain values (e.g. after deletion of records, etc.)
with simple small table like 1000 records you may execute SELECT * FROM TABLE and then fetchall()
this will result in a tuple of tuples and you may retrieve record with index 555 (index is zero-based as you know)
same apply for a more complex detabase, but index will depend on SORT BY clause
EDIT: Of course, Larz60+ advice about rowid (if available) is much more relevant :-)
Thanks
RE: sql select data in row number - Larz60+ - Mar-09-2018
DSo here's an example of why you cannot rely on it
Output:sqlite> Create table TEMP1 ('First' text, 'Last' text);
sqlite> .schema TEMP1
CREATE TABLE TEMP1 ('First' text, 'Last' text);
sqlite> insert into temp1 values('John', 'Smith');
sqlite> insert into temp1 values('Fred', 'Fuller');
sqlite> insert into temp1 values('Jack', 'Rabbit');
sqlite> select rowid from TEMP1;
1
2
3
sqlite> DELETE from TEMP1 where rowid == 2;
sqlite> select rowid from TEMP1;
1
3
sqlite> select rowid, * from TEMP1;
1|John|Smith
3|Jack|Rabbit
sqlite> VACUUM;
sqlite> select rowid, * from TEMP1;
1|John|Smith
2|Jack|Rabbit
sqlite>