Is it possible to stream from a DB (read by Rust) to Python?

[StuartHadfield]

Hello folks! First off, loving the project - great stuff. Super helpful community too. Thanks for your efforts.

I'm curious as to whether or not it's possible to stream results out of a database read by rust, back to python?

For instance, if I'm using the postgres package to read a postgres instance, and I have the following setup (apologies in advance, this is all dummy code):

use postgres::{Client, NoTLS, RowIter};

#[pyfunction]
fn stream_from_db() -> Result<&PyAny> {
    Ok(produce_stream()?);
}

fn produce_stream() -> Result<RowIter> {
    let client = Client::connect("my_db_url", NoTLS)?;

    let stream = client.query_raw("SELECT * FROM ...", &[&params])?;

    Ok(stream)
}

Obviously this is not going to work. The RowIter that comes back from postgres has a lifetime 'a, which is derived from the actual Postgres connection - and Pyo3 doesn't like lifetimes.

I thought perhaps to get around this I could pass a py token (though admittedly I don't fully understand this token) through with a corresponding lifetime:

use postgres::{Client, NoTLS, RowIter};

#[pyfunction]
fn stream_from_db(py: Python) -> PyResult<&PyAny> {
    Ok(produce_stream(py)?);
}

fn produce_stream<'a>(py: Python<'a>) -> PyResult<&'a PyAny> {
    let client = Client::connect("my_db_url", NoTLS)?;

    let stream = client.query_raw("SELECT * FROM ...", &[&params])?;

    Ok(stream.into_py(py))
}

But of course, this won't work because a RowIter can't do into_py, because it doesn't satisfy AsPyPointer... I think maybe I have to implement this trait somehow, but given I'm outside of the Postgres crate, I think I'd have to define a wrapper struct and impl IntoPyPointer for that...? Maybe??

Another option I thought is I might be able to Box the postgres client to drop the lifetime, but that's really a shot in the dark.

Ultimately what I want to do is, from Python, instruct Rust to execute a query and load results in an iterator fashion (such that I don't load all records into memory at once).

Any suggestions would be most welcome!

Thanks

[birkenfeld]

It doesn't solve the original question (how to provide a Python iterator of rows), but what I've done in a similar situation was to switch to internal iteration. Meaning, the Rust code that iterates over the rows gets a Python function that it executes for each row (or bunch of rows, or ...) and which can, if needed, indicate in its return value whether to break iteration.

Regarding the original problem you could, maybe, move the Connection into a Python object - this guarantees a stable address - and then move the RowIter into another Python object that holds onto a reference to the Connection (this guarantees its being kept alive as long as needed), and the Rust lifetime erased/transmuted to 'static.

But this is obviously very unsafe and probably not sound.

[StuartHadfield]

Thanks @birkenfeld. That's pretty much what I thought too. Will leave open for a little bit and see if @davidhewitt can chime in, else I'll mark this as the answer. :)

[davidhewitt]

I think the above is pretty much correct. You can also consider using the ouroboros crate to have some forms of borrowed data, not sure if it can help here.

[StuartHadfield]

Np, thanks @davidhewitt 🙏 Appreciate you folks taking the time.