SELECT COUNT not working

[alitaker]

When running a simple query like:
SELECT COUNT(*) AS total from persons
(None,) is returned

    from pymongosql import connect
    connection = connect(
        host="mongodb://user:pwd@127.0.0.1:27017/esplores?authSource=admin&connectTimeoutMS=300"
    )
    cursor = connection.cursor()
    print("SELECT COUNT(*) AS total from persons")
    cursor.execute("SELECT COUNT(*) AS total from persons")
    print(cursor.fetchone())

Can you help?

Debug info

DEBUG:pymongosql.helper:Parsed connection string - Mode: standard, Database: esplores
DEBUG:pymongo.topology:{"message": "Starting topology monitoring", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}}
DEBUG:pymongo.topology:{"message": "Topology description changed", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}, "previousDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Unknown, servers: []>", "newDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>"}
DEBUG:pymongo.topology:{"message": "Starting server monitoring", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection pool created", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.serverSelection:{"message": "Server selection started", "selector": "Primary()", "operation": "ping", "topologyDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}}
DEBUG:pymongo.serverSelection:{"message": "Waiting for suitable server to become available", "selector": "Primary()", "operation": "ping", "topologyDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "remainingTimeMS": 29999}
DEBUG:pymongo.topology:{"message": "Server heartbeat started", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}, "driverConnectionId": 1, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": false}
DEBUG:pymongo.topology:{"message": "Server heartbeat succeeded", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}, "driverConnectionId": 1, "serverConnectionId": 31167, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": false, "durationMS": 0.3463139873929322, "reply": "{\"helloOk\": true, \"ismaster\": true, \"topologyVersion\": {\"processId\": {\"$oid\": \"6a105252faef4a0dd66935b4\"}}, \"maxBsonObjectSize\": 16777216, \"maxMessageSizeBytes\": 48000000, \"maxWriteBatchSize\": 100000, \"localTime\": {\"$date\": \"2026-05-23T07:46:19.047Z\"}, \"logicalSessionTimeoutMinutes\": 30, \"connectionId\": 31167, \"maxWireVersion\": 28, \"ok\": 1.0}"}
DEBUG:pymongo.connection:{"message": "Connection pool ready", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.topology:{"message": "Topology description changed", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}, "previousDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>", "newDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0003463139873929322>]>"}
DEBUG:pymongo.serverSelection:{"message": "Server selection succeeded", "selector": "Primary()", "operation": "ping", "topologyDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0003463139873929322>]>", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.topology:{"message": "Server heartbeat started", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}, "driverConnectionId": 1, "serverConnectionId": 31167, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": true}
DEBUG:pymongo.connection:{"message": "Connection checkout started", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection created", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1}
DEBUG:pymongo.connection:{"message": "Connection ready", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1, "durationMS": 0.004478228976950049}
DEBUG:pymongo.connection:{"message": "Connection checked out", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1, "durationMS": 0.005269964982289821}
DEBUG:pymongo.command:{"message": "Command started", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "command": "{\"ping\": 1, \"lsid\": {\"id\": {\"$binary\": {\"base64\": \"rhSUYkyeREiSn7hc+SGUtQ==\", \"subType\": \"04\"}}}, \"$db\": \"admin\"}", "commandName": "ping", "databaseName": "admin", "requestId": 424238335, "operationId": 424238335, "driverConnectionId": 1, "serverConnectionId": 31169, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.command:{"message": "Command succeeded", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "durationMS": 0.404, "reply": "{\"ok\": 1.0}", "commandName": "ping", "databaseName": "admin", "requestId": 424238335, "operationId": 424238335, "driverConnectionId": 1, "serverConnectionId": 31169, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checked in", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1}
INFO:pymongosql.connection:Successfully connected to MongoDB at mongodb://admin:esplores@127.0.0.1:27017/esplores?authSource=admin&connectTimeoutMS=300:27017
SELECT COUNT(*) AS total from persons
DEBUG:pymongosql.executor:Selected strategy: StandardQueryExecution
DEBUG:pymongosql.executor:Using standard execution for query: SELECT COUNT(*) AS total from persons
DEBUG:pymongosql.sql.parser:Preprocessed SQL: SELECT COUNT(*) AS total from persons
DEBUG:pymongosql.sql.parser:AST validation successful
DEBUG:pymongosql.sql.ast:Starting to parse SQL query
DEBUG:pymongosql.sql.ast:Processing SELECT items
DEBUG:pymongosql.sql.ast:Processing FROM clause
DEBUG:pymongosql.sql.query_handler:Parsed regular collection: persons
DEBUG:pymongosql.sql.ast:Extracted collection: persons
DEBUG:pymongosql.sql.query_builder:Set collection to: persons
DEBUG:pymongosql.sql.query_builder:Added filter conditions: {}
DEBUG:pymongosql.sql.query_builder:Set projection: {'COUNT(*)': 1}
DEBUG:pymongosql.sql.query_builder:Set column aliases to: {'COUNT(*)': 'total'}
DEBUG:pymongosql.sql.parser:Generated execution plan for collection: persons
DEBUG:pymongosql.executor:Executing MongoDB command: {'find': 'persons', 'filter': {}, 'projection': {'COUNT(*)': 1}}
DEBUG:pymongo.serverSelection:{"message": "Server selection started", "selector": "Primary()", "operation": "find", "topologyDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0003463139873929322>]>", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}}
DEBUG:pymongo.serverSelection:{"message": "Server selection succeeded", "selector": "Primary()", "operation": "find", "topologyDescription": "<TopologyDescription id: 6a115b4b9f860eaa7671b679, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0003463139873929322>]>", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checkout started", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checked out", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1, "durationMS": 5.4746982641518116e-05}
DEBUG:pymongo.command:{"message": "Command started", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "command": "{\"find\": \"persons\", \"projection\": {\"COUNT(*)\": 1}, \"lsid\": {\"id\": {\"$binary\": {\"base64\": \"rhSUYkyeREiSn7hc+SGUtQ==\", \"subType\": \"04\"}}}, \"$db\": \"esplores\"}", "commandName": "find", "databaseName": "esplores", "requestId": 719885386, "operationId": 719885386, "driverConnectionId": 1, "serverConnectionId": 31169, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.command:{"message": "Command succeeded", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "durationMS": 0.678, "reply": "{\"cursor\": {\"firstBatch\": [{\"_id\": {\"$oid\": \"6a1063b961dd8302f78ce5b4\"}}, {\"_id\": {\"$oid\": \"6a1063bf61dd8302f78ce5b5\"}}], \"ns\": \"esplores.persons\"}, \"ok\": 1.0}", "commandName": "find", "databaseName": "esplores", "requestId": 719885386, "operationId": 719885386, "driverConnectionId": 1, "serverConnectionId": 31169, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checked in", "clientId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1}
(None,)

DEBUG:pymongo.topology:{"message": "Server heartbeat failed", "topologyId": {"$oid": "6a115b4b9f860eaa7671b679"}, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": true, "durationMS": 501.1249469825998, "failure": "\"_OperationCancelled('operation cancelled')\"", "driverConnectionId": 1}
DEBUG:pymongosql.cursor:Cursor closed successfully

[passren]

@alitaker Thank you for bringing this up. The issue was acknowledged and resolved in version 0.7.1.

[alitaker]

Thank you @passren , it's working as expected.