Add Support for Returning Query Results as List of Tuples

[bymoye]

Currently, psqlpy returns query results as a list of dictionaries (e.g., [{"a": "a1", "b": "b1"}, {"a": "a2", "b": "b2"}]). While this format is useful for accessing fields by name, it would be beneficial to also support returning results as a list of tuples (e.g., [("a1", "b1"), ("a2", "b2")]), similar to the default behavior of psycopg2's fetchall() method.

I propose adding an option to configure psqlpy to return query results as a list of tuples.

[chandr-andr]

@bymoye
Hello!

Can you check this functionality? https://psqlpy-python.github.io/usage/row_factories/predefined_row_factories.html#tuple-row

[bymoye]

@bymoye Hello!

Can you check this functionality? https://psqlpy-python.github.io/usage/row_factories/predefined_row_factories.html#tuple-row

My apologies, I overlooked this...
Does this operation ultimately return the same data? I haven’t had a chance to test it yet.
Perhaps adding a preview of the return value format to the documentation would be helpful?

[chandr-andr]

If I remember correctly (I made it maybe more than a year ago), yes, it returns the same data. I'll check it today.

[bymoye]

If I remember correctly (I made it maybe more than a year ago), yes, it returns the same data. I'll check it today.

hi，I reviewed the implementation code:

psqlpy/src/query_result.rs

Lines 97 to 112 in 91aa22e

	#[allow(clippy::needless_pass_by_value)]
	#[pyo3(signature = (row_factory, custom_decoders=None))]
	pub fn row_factory<'a>(
	&'a self,
	py: Python<'a>,
	row_factory: Py<PyAny>,
	custom_decoders: Option<Py<PyDict>>,
	) -> PSQLPyResult<Py<PyAny>> {
	let mut result: Vec<Py<PyAny>> = vec![];
	for row in &self.inner {
	let pydict: pyo3::Bound<'_, PyDict> = row_to_dict(py, row, &custom_decoders)?;
	let row_factory_class = row_factory.call(py, (pydict,), None)?;
	result.push(row_factory_class);
	}
	Ok(result.into_py_any(py)?)
	}

It seems like this operation might not be ideal, as it first converts the result to a dictionary before transforming it into another type. Could this be somewhat inefficient?

I mean.. would it be better to provide a native method that doesn’t involve converting to a dictionary before transforming into a tuple?

I’m not very familiar with Rust, so I’m just speculating based on reading the code.

[chandr-andr]

@bymoye In general, you are right. It’s not as efficient as it could be.

There you see such implementation cuz I made it mostly for row factories which are passed from python. And dict was chosen by me as a primary type for transforming Result into std python type

I’ll think how we can speed up the performance in this case.

If you have any ideas, please share them)

[bymoye]

@chandr-andr No, I think this is great! I was just wondering if we could consider adding an additional function that solely returns the result. Something like this, perhaps:

#[allow(clippy::ref_option)]
fn row_to_tuple<'a>(
    py: Python<'a>,
    postgres_row: &'a Row,
    custom_decoders: &Option<Py<PyDict>>,
) -> PSQLPyResult<pyo3::Bound<'a, PyTuple>> {
    let num_columns = postgres_row.columns().len();
    let python_tuple = PyTuple::new_bound(py, (0..num_columns).map(|_| py.None()));
    for column_idx in 0..num_columns {
        let column = &postgres_row.columns()[column_idx];
        let python_value = postgres_to_py(
            py,
            postgres_row,
            column,
            column_idx,
            custom_decoders
        )?;
        python_tuple.set_item(column_idx, python_value)?;
    }
    Ok(python_tuple)
}

[chandr-andr]

@bymoye Looks like some awesome idea.

Maybe you can create a PR with this function?

As an idea, you can add method to QueryResult class named .tuple() (we can think through the naming), without row_factory parameter and return tuple.

I’ll review it when I come home

[bymoye]

@chandr-andr ok, but I don’t know much about Rust, though I can give it a try. I’ll start attempting it after I wake up (it’s already midnight in China, haha).

[chandr-andr]

@bymoye I've found some time and implemented what you want.

Could you please look at the PR and tell me (in the PR) if I missed something?

#147

[bymoye]

@chandr-andr Good morning, I feel something is off. From the code:

psqlpy/src/query_result.rs

Line 49 in ee26552

let timed_tuple = PyTuple::new(py, vec![column.name().into_py_any(py)?, python_type])?;

The final tuple content is: (((column_name, field_value), (column_name, field_value), ,,,), ((column_name, field_value), (column_name, field_value),... ) ,...).

The expected value should be: ((field_value, field_value, ...), (field_value, field_value, ...)).

[chandr-andr]

Thanks for reviewing. Just in case, is ((field_value, field_value, ...), (field_value, field_value, ...)) expected behavior? Could you please provide an example when it can be used? I know only one place where it can be used this way - in the SQLAlchemy dialect.

[bymoye]

@chandr-andr
For example, I currently have a database table:

field_one	field_two	field_three
a1	b1	c1
a2	b2	c2
a3	b3	c3

The data returned by the result is:

[{"field_one": "a1", "field_two": "b1", "field_three": "c1"}, {"field_one": "a2", "field_two": "b2", "field_three": "c2"}, {"field_one": "a3", "field_two": "b3", "field_three": "c3"}]

However, we can see that during the process of passing to Python, the field names (i.e., the table headers) are repeated.

The desired data result for this request is:

[("a1", "b1", "c1"), ("a2", "b2", "c2"), ("a3", "b3", "c3")]

In other words, the table headers are not included in the result.

[bymoye]

https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.fetch

Actually I'm writing tfb

So what is actually returned should be a list[tuple(PyAny)]

Now I have done it like this:

@bs.get('/fortunes')
async def fortunes_test(request):
    async with db_pool.acquire() as connection:
        fortunes_fetch = await connection.fetch("SELECT * FROM Fortune")
        # fortunes = fortunes_fetch.result()
        fortunes = [list(item.values()) for item in fortunes_fetch.result()]
    fortunes.append(ADDITIONAL_ROW)
    fortunes.sort(key=lambda row: row[1])
    data = fortune_template.render(fortunes=fortunes)
    return bs.html(data)

[chandr-andr]

Looks good to me, agree with u