Add a streaming recordset generator

TomMalkin commented 5 years ago

This will be useful for streaming large quantities of data in defined chunks (similar to panda's read_sql).

ZackBotkin commented 5 years ago

This looks cool, I'd be willing to take a crack at this once I have a bit more time. Do we have a strategy for testing this out? Seems like we would need to come across a large amount of data first haha

TomMalkin commented 5 years ago

Yeah this could be awesome. We wouldn't need too much data to test it, just say 100 rows and we stream them in 10 chunks of 10. The main items here would be:

How do we actually stream data? Is it different in different databases? I know pandas can do it now with chunk_size so it might just be a case of going through their source code and work out how they did it. I would hate to add a row_number to each row (like actually edit the sql sent across the connection) and use that because that seems fraught with danger.

how do we deal with the headings of the columns? the options here are:
- send it as part of a tuple of each block (seems inefficient to send them every time but maybe not that inefficient)
- send in just the first chunk (the user would then have to deal with the first block differently)
- send as it's own chunk either first or last (len() calcs would then be off and it doesn't really make much sense)
- don't send at all (could be confusing when using the function)
- let the user decide one of the above
whether it's worth it: pandas already does it, and you probably want to store it in an efficient array if you are dealing with data that is too much to download in one chunk
what happens when the underlying data changes during iteration like deletion of rows.

This feature might be better off in v2 actually because it could be a little complicated haha

ZackBotkin commented 5 years ago

Just getting the generator working shouldn't be too bad.. it's definitely possible to query the TOP # records in sql, presumably an offset exists as well? The other things you brought up may give some issues though. You may be right that it might not be worth the effort until we have a use case.

On another note, I'm probably going to try and start using simqle in a stock buying/selling project I am hacking around on. Very simple right now, it essentially just allows the user the ability build a custom market position and save it. I had the idea of trying to incorporate some machine learning and teach the computer to pick good stocks based on historical data, but that's obviously way down the road. For now I just need a solid model/database. Using simqle here should give us some more good ideas to make managing the connections even easier.

TomMalkin commented 5 years ago

I'm sure there is a way to do top # with offset but I wonder if it's different depending on the language?

You'll have to let me know how your project goes (I believe we both work in finance). I use simqle as the basis of heaps of projects and analysis at work as well.

re. this issue - we could do some research on the above points and post here but happy to ice in lieu of other issues

ZackBotkin commented 5 years ago

I'm good with that. What do you think a completed version 1 would look like? Have we hit everything in the roadmap?

On Tue, Jun 18, 2019, 5:52 PM Tom Malkin notifications@github.com wrote:

I'm sure there is a way to do top # with offset but I wonder if it's different depending on the language?

You'll have to let me know how your project goes (I believe we both work in finance). I use simqle as the basis of heaps of projects and analysis at work as well.

re. this issue - we could do some research on the above points and post here but happy to ice in lieu of other issues

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Harlekuin/SimQLe/issues/35?email_source=notifications&email_token=AALIDGOVXMMIZ534NV4V7FTP3FRJXA5CNFSM4HWG7M22YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYAF7EI#issuecomment-503340945, or mute the thread https://github.com/notifications/unsubscribe-auth/AALIDGOWMQJR2V4M7MCEKDLP3FRJXANCNFSM4HWG7M2Q .

ZackBotkin commented 5 years ago

By the way, really impressed w the direction you've taken the behave tests. Everything looks clean and thorough

On Wed, Jun 19, 2019, 8:30 AM Zack Botkin zackbotkin@gmail.com wrote:

I'm good with that. What do you think a completed version 1 would look like? Have we hit everything in the roadmap?

On Tue, Jun 18, 2019, 5:52 PM Tom Malkin notifications@github.com wrote:

I'm sure there is a way to do top # with offset but I wonder if it's different depending on the language?

You'll have to let me know how your project goes (I believe we both work in finance). I use simqle as the basis of heaps of projects and analysis at work as well.

re. this issue - we could do some research on the above points and post here but happy to ice in lieu of other issues

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Harlekuin/SimQLe/issues/35?email_source=notifications&email_token=AALIDGOVXMMIZ534NV4V7FTP3FRJXA5CNFSM4HWG7M22YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYAF7EI#issuecomment-503340945, or mute the thread https://github.com/notifications/unsubscribe-auth/AALIDGOWMQJR2V4M7MCEKDLP3FRJXANCNFSM4HWG7M2Q .

TomMalkin commented 5 years ago

I'm good with that. What do you think a completed version 1 would look like? Have we hit everything in the roadmap?

I've got the v1 roadmap in milestone 1, only 5 more things:

integration tests that require docker: basically adding more databases as tested by using travis' docker support (https://docs.travis-ci.com/user/docker/) with the SQL Server image (https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017&pivots=cs1-bash)

add the ability to return a recordset as a list of dicts just as a convenience function, for example:

[{"name": "Jim", "age": 34}, {"name": "leonard": "age": 19}]

looking for default .connections.yaml: basically (depending on the OS) checking for connection files in user directories, or certain system directories, so you can write user or system based scripts without having to define the location of the file
default parameter: if your a connection has the default: True parameter, then when con_name isn't specified in a recordset() or execute_sql() function, that connection would be used
proper docs - would love a readthedocs build to be part of the pypi upload, haven't looked into how to do that yet though.

By the way, really impressed w the direction you've taken the behave tests. Everything looks clean and thorough

Thanks!

ZackBotkin commented 5 years ago

Sorry I've been MIA. Lots of weddings/work and family. How are these coming?

TomMalkin commented 5 years ago

Haven't added any of the 5 yet but all should be very easy. I added another one (#44) as well but not sure if that should be in the v1 milestone.

I also started another project based on SimQLe that will be used to make sure development database environments are the same as production - but that's still a work in progress in how that is going to work.

I wonder if there a better forum for discussing the project? This chat should really be focused on this particular issue. I set up a gitter thing - thoughts on moving this convo there? https://gitter.im/SimQLe/community#

TomMalkin commented 5 years ago

Given this is covered in pandas I think this is out of scope and closing. Also using language specific offset statements etc would make it harder to maintain and be no longer sql application agnostic

TomMalkin / SimQLe

Add a streaming recordset generator #35