how to make a rowcount in ponyorm? Python

Question

I am using this Python ORM to manage my database in my application: ponyorm.com

I just changed an attribute in my table, I would make a rowcount to me that he return 1 for TRUE and 0 for FALSE.

Ex: Using sqlite3 only I would do so:

user = conn.execute('SELECT * FROM users')

count = user.rowcount

if count == 1:
    print('Return %d lines' %count)
else:
    print('Bad....return %d lines', %count)

Answer 1

Using the rowcount attribute is usually not the right way to count number of rows. According to SQLite documentation regarding rowcount attribute,

Although the Cursor class of the sqlite3 module implements this attribute, the database engine’s own support for the determination of “rows affected”/”rows selected” is quirky.

If you use SQL, the standard way to get count of rows is to use COUNT(*) function. In SQLite it may be achieved in the following way:

cursor = conn.cursor()
cursor.execute('SELECT COUNT(*) FROM users')
rowcount = cursor.fetchone()[0]

With PonyORM you can do count in three alternative ways.

rowcount = select(u for u in User).count()  # approach 1
rowcount = User.select().count()  # approach 2
rowcount = count(u for u in User)  # approach 3

All lines above should produce the same query. You can choose the line which looks the most intuitive to you.

If you want to count not all rows, but only specific ones, you can add condition to the query. For example, to count the number of products which price is greater than 100 you can write any of the following lines:

rowcount = select(p for p in Product if p.price > 100).count()  # approach 1
rowcount = Product.select(lambda p: p.price > 100).count()  # approach 2
rowcount = count(p for p in Product if p.price > 100)  # approach 3

Also you may want to count not the number of rows, but number of different values in specific column. For example, the number of distinct user countries. This may be done in the following way:

user_countries_count = select(count(u.country) for u in User).get()

Hope I answered your question.