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

[ssweber]

Awesome. I figured you would be the better judge on if that was a big deal or not.

[ssweber]

@PySimpleSQL I think my most recent push fixes most stuff.

1 issue:

1. 'Sort column is None. Resetting Sort' will allow me to relabel 'A Person' to 'Z Person' in the description column, and it stays in-place. I get why that happens (I think), but wanted to know if you thought that was the behavior you wanted. If you have any column sorted, then behavior works as I would expect 👍

what additional part of _SortCallbackWrapper do you want to integrate into def sort? (if any)

    def __call__(self, column):
        if len(self.frm[self.data_key].rows.index):
            # sort_cycle takes care of storing pk and calling set_by_pk()
            sort_order = self.frm[self.data_key].sort_cycle(column, self.data_key)

            self.frm.update_selectors(self.data_key)
            self.frm.update_elements(edit_protect_only=True)
            self.table_heading.update_headings(self.element, column, sort_order)

[ssweber]

Per above commit, things I didn’t know how to handle:

1. in insert_row, setting the idx is not implemented yet
2. Also, ruff is complaining about:
   F405: 'ResultSet' may be undefined ... but I didn't know what to replace that with. Result, or pd.Dataframe?

[ssweber]

• Need to check if execute exceptions work.

thanks for letting me mop the new floors you put in 😄

[PySimpleSQL]

Thanks! Sorry for leaving things in such an unfinished state - i was supposed to have a nice block of time that fell to pieces on me unfortunately (i worked at 20 hour day yesterday).

I just pulled your changes in and going to give things a whirl to see where everything stands.

I know this has been a giant pain, but having pandas support will be a huge feature. It can be a pain to use for simple things, but it has so much power for manipulating and accessing data!

[PySimpleSQL]

Made some fairly decent progress today. There are still a few oddball issues floating yet (crash when navigating away from changed record and prompting "No" for example).

Just need to find the rest of these small issues and fix them, then the last remaining task will be to update all of the drivers to work properly, using the Sqlite driver as a reference as to what has changed

[PySimpleSQL]

It's all good, not your fault. My schedule has been completely bonkers this week. I've literally had just a few minutes at a time to try to test and pound something out, just to have something else come up. It's been just crazy

…

On Thu, Apr 20, 2023, 4:29 PM ssweber ***@***.***> wrote: Maybe when you have all the drivers changed over, I can help spinning each one up and reporting if there's anything still borked. — Reply to this email directly, view it on GitHub <#281 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2RETG5FI7HMJIWCUPTITXCGMC7ANCNFSM6AAAAAAW4YNJ64> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ssweber]

BTW - I just tested Mysql example on development, and duplicate didn't jump to the right record afterwords there either. So that's not a pandas-specific issue.

[PySimpleSQL]

Yeah, I saw that too. I have a fix for it now. I should get into one of the upcoming pushes. It's working well now in MSAccess

…

On Fri, Apr 21, 2023, 11:10 AM ssweber ***@***.***> wrote: BTW - I just tested Mysql example on development, and duplicate didn't jump to the right record afterwords there either. So that's not a pandas-specific issue. — Reply to this email directly, view it on GitHub <#281 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2REQO6PYBRVBLGAAMUELXCKPM3ANCNFSM6AAAAAAW4YNJ64> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ssweber]

I checked all the sql drivers. The only issues I saw revolves around duplicate... either not jumping to the correct row, or like sqlserver, which needs a duplicate rewrite. Maybe there's a more database-agnostic way to accomplish this than the the hack I found on stackoverflow.

At any rate, all the duplicate problems existed before pandas. So I'm cool if you want to merge this back in!

[ssweber]

Chatgpt and I rewrote duplicate. I realized columninfo had all the information I needed!

I’ll go back and comment more, but this avoids all the silliness of temp-tables.

it’s now working on sqlserver. I’ll double-check MySQL and Postgres.

Maybe we don’t need a separate one for msaccess?

[PySimpleSQL]

There may still be an issue with MSAccess that prevents a generalized solution - DROP TABLE IF EXISTS does not seem to work. I messed with it for quite a while one day

…

On Wed, Apr 26, 2023, 5:08 PM ssweber ***@***.***> wrote: Chatgpt and I rewrote duplicate. I realized columninfo had all the information I needed! I’ll go back and comment more, but this avoids all the silliness of temp-tables. it’s now working on sqlserver. I’ll double-check MySQL and Postgres. Maybe we don’t need a separate one for msaccess? — Reply to this email directly, view it on GitHub <#281 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2REVFUEXNIJULB7ZKD3LXDGFGRANCNFSM6AAAAAAW4YNJ64> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ssweber]

Yeah, hopefully this is more general-purposed.

It uses Dataset.column_info to select all the columns except the pk_column and copies the row right in the same table.

for children, it again uses column_info, but importantly, when selecting columns, sets the fk_column to equal the new parent pk before inserting.

no create/drop necessary :)

———-

it’s been a real pleasure this last week discovering and using some of the gems 💎 you have written. I used column_info to infer checkboxes, and ElementRow for combobox es in the CellEdit logic.

[PySimpleSQL]

Nice! Yeah, that is a much better approach!

…

On Wed, Apr 26, 2023, 8:59 PM ssweber ***@***.***> wrote: Yeah, hopefully this is more general-purposed. It uses Dataset.column_info to select all the columns except the pk_column and copies the row right in the same table. for children, it again uses column_info, but importantly, when selecting columns, sets the fk_column to equal the new parent pk before inserting. no create/drop necessary :) ———- it’s been a real pleasure this last week discovering and using some of the gems 💎 you have written. I used column_info to infer checkboxes, and ElementRow for combobox es in the CellEdit logic. — Reply to this email directly, view it on GitHub <#281 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2REUJ7POMQFADZPWFAIDXDHAIBANCNFSM6AAAAAAW4YNJ64> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ssweber]

I cleaned that up and tested on all sqldrivers. Works perfectly. I hope it looks OK to you.

Is there anything left for us to do before merging pandas in?

[PySimpleSQL]

I'm still wondering if we should switch to using pandas .index vs. a numerical index (loc[ ] vs iloc [ ]). The .index would need reset on sorts, but the rest would be a little cleaner and easier to understand. What are your thoughts? Thanks for your work on this. I'm in an RV right now on a trip- 8bwas hoping to be able to get some work done, but it's just been too chaotic!

…

On Thu, Apr 27, 2023, 11:04 AM ssweber ***@***.***> wrote: I cleaned that up and tested on all sqldrivers. Works perfectly. I hope it looks OK to you. Is there anything left for us to do before merging pandas in? — Reply to this email directly, view it on GitHub <#281 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2RESZI4IFKD6MR2EXQ2LXDKDIBANCNFSM6AAAAAAW4YNJ64> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ssweber]

I may not understand this well. Are you considering set_index the pk_column? Then iloc[1] would grab the pk=1

i think that removing the auto-created index would be a plus, and more expected.

[PySimpleSQL]

That's not quite what I meant, but that's another option as well- indexes can be set to just about anything in pandas. Whether it's a number from 0 to x, or a pk. Most methods in pandas assume that the .index property is being used. however, we are working with the numerical index, similar to a list- and they are very hard and confusing in pandas - especially since the .index property is a separate thing altogether, and isn't necessarily a numerical integer index. If we do use the .index property, we should probably use it as a numerical index, since DataSet.current_index already operates this way.

…

On Thu, Apr 27, 2023, 11:50 AM ssweber ***@***.***> wrote: I may not understand this well. Are you considering set_index the pk_column? Then iloc[1] would grab the pk=1 i think that removing the auto-created index would be a plus, and more expected. — Reply to this email directly, view it on GitHub <#281 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2RER6FW3QOPFODJ73VT3XDKIU5ANCNFSM6AAAAAAW4YNJ64> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ssweber]

I'll have to see what you mean to understand 😅 But you're typically have a good feel for these things!

[ssweber]

Is it worth merging in as-is (it's at a high-level of functionality now) and then working on the iloc to .index transition in a pandas.index branch?

That way I can rebase _EditCell on development, there are a few rough edges on it yet.

[ssweber]

Actually, disregard previous. Take your time thinking about it - enjoy your trip.

This isn't holding me up smoothing out _EditCell (I need to stop procrastinating with my actual day-job work!)

[PySimpleSQL]

Good idea. Yeah, let's do that

…

On Thu, Apr 27, 2023, 2:14 PM ssweber ***@***.***> wrote: Is it worth merging in as-is (it's at a high-level of functionality now) and then working on the iloc to .index transition in a pandas.index branch? That way I can rebase _EditCell on development, there are a few rough edges on it yet. — Reply to this email directly, view it on GitHub <#281 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQX2REQGAPFWPO7E5VHTV7TXDKZSFANCNFSM6AAAAAAW4YNJ64> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[ssweber]

#297

alright!