How to Return Dictionaries Instead of Tuples in aiosql

In a backend development community I participate in, a developer recently asked: “How to set the cursor of aiosql to use real dictionary instead of tuple of tuples?”

This is a common point of friction. Because aiosql acts as a wrapper around your database driver (like sqlite3, aiosqlite, psycopg, or asyncpg), it doesn’t actually manage the cursor type itself. Instead, it delegates that responsibility to the underlying connection object you provide.

The Immediate Fix

To get dictionaries, you must configure the underlying driver to use a dictionary-based row factory or record type before passing the connection to aiosql.

For aiosqlite (SQLite)

If you are using aiosqlite, set the row_factory on the connection object.

# Python 3.10+, aiosqlite 0.19.0
import aiosqlite
import aiosql

queries = aiosql.from_path("queries.sql", "aiosqlite")

async def get_data():
    async with aiosqlite.connect("database.db") as conn:
        # This tells the driver to return rows that behave like dicts
        conn.row_factory = aiosqlite.Row

        # Now aiosql calls will return 'Row' objects (accessible via keys)
        users = await queries.get_all_users(conn)
        for user in users:
            print(user["username"]) # Success! No more user[0]

---

Detailed Explanation

aiosql is “driver-agnostic” in its design. It parses your SQL files and maps them to Python methods, but when it executes a query, it simply calls the execute method on the connection object you pass in.

If you don’t configure the connection, the default behavior of most Python DB-API 2.0 drivers is to return rows as standard Python tuples. To change this, you have two primary strategies:

Solution 1: Driver-Level Row Factories (The Direct Approach)

As shown in the immediate fix, you configure the connection object directly. This is the most performant way because the transformation happens at the C or driver level.

For Psycopg 3 (PostgreSQL): Psycopg 3 uses “Row Factories.” You can specify dict_row globally or per cursor.

# Python 3.11, psycopg 3.1.x — illustrative example
from psycopg import AsyncConnection
from psycopg.rows import dict_row
import aiosql

queries = aiosql.from_path("users.sql", "psycopg")

async def main():
    # Pass row_factory to the connection
    async with await AsyncConnection.connect(conninfo="...", row_factory=dict_row) as conn:
        user = await queries.get_user_by_id(conn, user_id=1)
        print(user["email"])

Solution 2: Mapping to Data Classes or Pydantic (The “Clean Code” Approach)

If you want something more robust than a dictionary—such as a typed object—you can use aiosql’s internal ability to map results to a specific constructor. This is useful if you want to ensure the data adheres to a specific schema.

# Python 3.10+, aiosql 1.8+
from dataclasses import dataclass
import aiosqlite
import aiosql

@dataclass
class User:
    user_id: int
    username: str
    email: str

# Define the query loader with a record_class
queries = aiosql.from_path("users.sql", "aiosqlite")

async def get_users():
    async with aiosqlite.connect("db.sqlite") as conn:
        # Manually map the result to the Dataclass
        # Note: Your SQL must return columns in the same order as the constructor
        results = await queries.get_all_users(conn)
        users = [User(*row) for row in results]
        return users

---

Edge Cases and Considerations

• Memory Overhead: Dictionary cursors (or Row objects) consume slightly more memory than tuples because they store column name mappings. For 99% of web applications, this is negligible, but for processing millions of rows in a single batch, stick to tuples.
• Property Access vs. Key Access: aiosqlite.Row and psycopg.rows.dict_row allow you to access data via row["column_name"]. However, some older DictCursor implementations allowed row.column_name. Be sure to check which syntax your specific driver version supports to avoid AttributeError.
• Duplicate Column Names: If your SQL query joins two tables that both have an id column (e.g., SELECT * FROM users JOIN posts...), a dictionary-based result will often overwrite the first id with the second. Always alias your columns (SELECT u.id as user_id, p.id as post_id...) when using dictionary outputs.

---

Related Follow-up Questions

1. Can I use aiosql with SQLAlchemy’s connection pool? Yes. Since aiosql just needs an object with an execute method, you can pass a SQLAlchemy AsyncConnection. However, SQLAlchemy returns Row objects by default, which already support mapping-style access (e.g., row.username or row._mapping["username"]).

2. Is there a performance hit when using dict_row in PostgreSQL? In psycopg3, the dict_row factory is highly optimized. While there is a micro-overhead compared to a raw tuple, the developer productivity gains and code readability almost always outweigh the nanoseconds lost in row instantiation.

3. What if I am using asyncpg? asyncpg (often used with aiosql) does not return tuples or dicts by default; it returns Record objects. These records are already very powerful—they allow both positional access (row[0]) and key-based access (row["id"]). You generally do not need to change any settings for asyncpg to get dictionary-like behavior.