Dataset derive from pandas data frame?

[PySimpleSQL]

I've been kicking this around for a while. ResultSets and therefore the dataset rows extend simple lists. Pandas would be a much more powerful option.

This is more of a long term goal, but I wanted to at least get it in the system

[ssweber]

That, and it would be nice to load/export a dict/json/etc as a dataset.

Some interesting google results:

https://github.com/petbox-dev/tafra

https://github.com/sfu-db/connector-x
https://github.com/pola-rs/polars

[PySimpleSQL]

I'm done for the night. Was going to look at delete behavior next. Have at it if you're up - I wont be back for 12 hours or so.

It looks pretty simple - the recursion constants returned don't play well with Pandas directly (ambiguous compares), so we need a slightly different solution (maybe even adding them as a status in the ResultSet?)

[ssweber]

Is it because black turned our constant int into a tuple?

I don’t have time to check, but fix this first:

DELETE_CASCADE_RECURSION_LIMIT = (
    15  # Mysql sets this as 15 when using foreign key CASCADE DELETE
)

Should be

# Mysql sets this as 15 when using foreign key CASCADE DELETE
DELETE_CASCADE_RECURSION_LIMIT = 15

[ssweber]

Or maybe that isn’t a tuple (remember, you’re working with a big newbie here)

either way, the way black handles trailing comments leaves something to be desired for sure.

[ssweber]

I’ll take a look at it tomorrow. I return None if the parent doesn’t have a dependent in delete_record_recursive… which probably isn’t right.

[ssweber]

Because then it’s comparing “if Result (None) == int”

[PySimpleSQL]

Just writing a few quick thoughts down

• May need to consider getting rid of ResultSet completely and having SQLDrivers return raw dataframes instead. Even though ResultSet extends DataFrame, a lot of DataFrame methods return another DataFrame in the process, which makes working with ResultSets a little tricky. It's something that we can deal with in the pysimplesql codebase, but tacking on that complexity to an end user that just wants to use DataFrames isn't really desirable.

I might try using DataFrame.attrs to store the extra metadata that is normally associated with a ResultSet. This should make it much easier to use the DataFrame objects

[ssweber]

I was reading up on attrs. When I get to work I’ll check if this bug (pandas-dev/pandas#41572) is still unfixed. Apparently they arnt always persistent in all operations / don’t reattach on copy?

Also see this discussion: pandas-dev/pandas#52166

maybe it’s worth storing them separate eg

Dataset.rows_df (current rows)
Dataset.rows_attrs

[PySimpleSQL]

Yeah, though I think the bug revolves more around DataFrame attrs overwriting Series attrs, which shouldn't affect us. We will see I guess.

I already have huge progress on eliminating ResultSet and using DataFrames directly. I have sorting 90% working now also (I need to rewrite the part that sorts based on the fk relationship). So far there have been no issues in my testing. I'm hoping to push the changes soon, just getting sporadic chances to work on it so far today