SQLite3 and lastrowid

[fuglyducky]

I am fairly new to Python (no development experience) and brand new to
using sqlite through Python. With that said, I created a database with
two tables. The first has about 30,000 rows of static data. The second
has 9 rows of static data.

Before I added the second table I could simply run
'print(cursor.lastrowid)' and it would give me the id number. However,
with two tables I am unable to do this.

Does anyone know if there is a way to reference one table or another
to get lastrowid?

Thanks!!!

 

[Ian]

Before I added the second table I could simply run
'print(cursor.lastrowid)' and it would give me the id number. However,
with two tables I am unable to do this.

It would help if you would show the code where you're trying to do
this. Without your actual code to look at, we can't tell you why it
doesn't work.

Does anyone know if there is a way to reference one table or another
to get lastrowid?

cursor.lastrowid is always in reference to the last query executed by
the cursor, not in reference to a table. If you don't capture the
value, and then you execute another query on the same cursor, the
previous value of cursor.lastrowid no longer exists. If you need it
now, then either you should have captured it when you had the chance,
or you should not have executed another query on the same cursor.

But perhaps this is not what you're actually trying to do. I can't
tell, because I haven't seen the code.

Cheers,
Ian

 

[fuglyducky]

It would help if you would show the code where you're trying to do
this.  Without your actual code to look at, we can't tell you why it
doesn't work.


cursor.lastrowid is always in reference to the last query executed by
the cursor, not in reference to a table.  If you don't capture the
value, and then you execute another query on the same cursor, the
previous value of cursor.lastrowid no longer exists.  If you need it
now, then either you should have captured it when you had the chance,
or you should not have executed another query on the same cursor.

But perhaps this is not what you're actually trying to do.  I can't
tell, because I haven't seen the code.

Cheers,
Ian

Thanks for the input. Sorry...I should have included the code...it's
just a simple query...

#####################################################

import sqlite3
import random

db_connect = sqlite3.connect('test.db')
cursor = db_connect.cursor()

print(cursor.lastrowid)

# Choose random index from DB - need to understand lastrowid
#row_count = cursor.lastrowid
#random_row = random.randrange(0, row_count)

cursor.execute("SELECT * FROM table1 WHERE id = 2002")
print(cursor.fetchmany())

#for row in cursor:
# print(row)


db_connect.commit()
cursor.close()

#####################################################

 

[Ian]

db_connect = sqlite3.connect('test.db')
cursor = db_connect.cursor()

print(cursor.lastrowid)

At this point you haven't executed a query yet, so there is no
meaningful value that cursor.lastrowid can take.

# Choose random index from DB - need to understand lastrowid
#row_count = cursor.lastrowid
#random_row = random.randrange(0, row_count)

This is wrong. The lastrowid is not the number of rows in the table.
It's the row-id of the row that was inserted (if any) by the last
query executed by the cursor. In the case of sqlite3, I think the row-
id is just the primary key if it's an integer (but in general the row-
id is database-dependent), so you *might* be able to get away with it
if you always let it autoincrement when inserting, never delete any
rows, and never change their primary keys. But it's unreliable and
only available immediately after an insert, so don't do it that way.

The proper way to get the number of rows is to use the COUNT aggregate
function, e.g., "SELECT COUNT(*) FROM TABLE1", which will return a
single row with a single column containing the number of rows in
table1.

Cheers,
Ian

 

[fuglyducky]

At this point you haven't executed a query yet, so there is no
meaningful value that cursor.lastrowid can take.


This is wrong.  The lastrowid is not the number of rows in the table.
It's the row-id of the row that was inserted (if any) by the last
query executed by the cursor.  In the case of sqlite3, I think the row-
id is just the primary key if it's an integer (but in general the row-
id is database-dependent), so you *might* be able to get away with it
if you always let it autoincrement when inserting, never delete any
rows, and never change their primary keys.  But it's unreliable and
only available immediately after an insert, so don't do it that way.

The proper way to get the number of rows is to use the COUNT aggregate
function, e.g., "SELECT COUNT(*) FROM TABLE1", which will return a
single row with a single column containing the number of rows in
table1.

Cheers,
Ian

Ahhh...great...thanks for the info! I'll do the row count then!!!

 

[Alexander Gattin]

Hello,

The proper way to get the number of rows is to
use the COUNT aggregate function, e.g., "SELECT
COUNT(*) FROM TABLE1", which will return a
single row with a single column containing the
number of rows in table1.

It's better to select count(1) instead of
count(*). The latter may skip rows consisting
entirely of NULLs IIRC.

 

[Alexander Gattin]

It's better to select count(1) instead of
count(*). The latter may skip rows consisting
entirely of NULLs IIRC.

sorry, I'm wrong -- count(*) behaves the same way
as count(1) does:

sqlite> create table t (x number, y char);
sqlite> insert into t(x,y) values(1,'a');
sqlite> insert into t(x,y) values(2,NULL);
sqlite> insert into t(x,y) values(NULL,'c');
sqlite> insert into t(x,y) values(NULL,NULL);
sqlite> insert into t(x,y) values(NULL,NULL);
sqlite> select count(1),count(*),count(x),count(y) from t;
5|5|2|2
sqlite>

P.S. Surprise -- it's true even for Oracle SQL...

 

[Alain Ketterlin]

It's better to select count(1) instead of
count(*). The latter may skip rows consisting
entirely of NULLs IIRC.

Wrong: count(anyname) ignores NULL, whereas count(*) does not.

-- Alain.

 

[Alexander Gattin]

Hello,

Wrong: count(anyname) ignores NULL, whereas count(*) does not.

I'm using count(1), which is a count over constant
non-NULL expression (1). It doesn't ignore NULLs
or duplicate rows, as my attempts with Oracle 10g,
8g and sqlite3 show.

 

[Wolfgang Rohdewald]

It's better to select count(1) instead of
count(*). The latter may skip rows consisting
entirely of NULLs IIRC.

in some data bases count(1) is said to be faster
than count(*), I believe

 

[Alain Ketterlin]

I'm using count(1), which is a count over constant
non-NULL expression (1). It doesn't ignore NULLs
or duplicate rows, as my attempts with Oracle 10g,
8g and sqlite3 show.

I'm not saying anything about count(1) (which works like count(*) as you
explain, even though I see no point in using 1, since *'s hehavior is
well-defined).

What was wrong in your first message is that count(*) ignores NULL
(granted, you've corrected your mistake in a later message, which I
didn't see before posting).

-- Alain.

 

[Alexander Gattin]

Hello,


not true,

not true either. I've heard that count(1) is
preferred to count(*) but forgot why. Your post
reveals the truth about my belief:

in some data bases count(1) is said to be faster
than count(*), I believe

And as it turns out this isn't true anymore on
modern databases (count(1) and count(*) behave
exactly the same). And quite surprisingly, on old
ones count(*) was faster than count(1):

'Oracle Performance Tuning', second edition,
O'Reilly & Associates, Inc, page 175. It says:

"Contrary to popular belief COUNT(*) is faster
than COUNT(1). If the rows are returned via an
index, counting the indexed column - for example,
COUNT(EMP_NO) is faster still. The optimizer
realizes from the existence of the index that the
column must also exist (non-null). We tested the
following statements on several different
computers and found that COUNT(*) consistently
runs between 15% and 20% faster than COUNT(1) and
that COUNT(INDEXED_COLUMN) is 5% faster again."

// http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789

P.S. sorry for starting this discussion.