search

honza / anosql

Easy SQL in Python
Other
207 stars 29 forks source link

I made a derived work based on anosql. #29

Closed nackjicholson closed 5 years ago

nackjicholson commented 6 years ago

Hey all,

@honza @alecbrooks @steveYeah (you are people I've seen active in the repo lately)

I worked this weekend on a proof of concept library based on anosql which handles some things I thought might be useful. Here is the link if you'd like to look through it https://gitlab.com/willvaughn/aiosql

@honza I copied the License, and as it truly is a derived work based on your library you are the top line on the copyright.

Chiefly, here are the things I had in mind while working on it:

  1. Out of the box support for asyncio based database drivers like aiosqlite and asyncpg. Without losing support for sync drivers like sqlite and postgresql. I tried to accomplish this by isolating the code specific to each driver into "QueryLoader" strategies.
  2. Ability to load a hierarchy of file from nested directories. To namespace the queries object as a tree, i.e. queries.contacts.get_all().
  3. Easy extension to accommodate custom database drivers. I wanted the ability to extend the library to mysql or mssql without necessarily having to add code to the mainline codebase.

I didn't feel like it was correct to do all of these things in mainline anosql because of compatibility. The asyncio stuff limits my library to supporting python3.6+ because of f-strings and async def functions. Time willing, please look through the library and feel free to ask me any questions you have.

Things to talk about:

Cheers!

honza commented 6 years ago

:heart:

honza commented 6 years ago

Honestly, I haven't had much time to work on this for a while now, and unfortunately, I don't see that changing. I'm happy to hand out the commit bit, and you can run with it.

nackjicholson commented 6 years ago

@honza I'm happy to take it over since I'm planning to rely on it more and more in projects at work. Thank you!

alysbrooks commented 6 years ago

I haven't had time to look at the source code in depth, but all three of your goals are valuable improvements. Number 3 in particular—I think the biggest limitation compared to Yesql and similar libraries is the limited database support.

If there's still some work to make your version have feature parity, I'd be happy to help.

steveYeah commented 6 years ago

Hey, just seen the thread. I'll be happy to help out, I can have a look through tomorrow night and give some feedback.

On Mon, 19 Nov 2018, 20:55 A Brooks <notifications@github.com wrote:

I haven't had time to look at the source code in depth, but all three of your goals are valuable improvements. Number 3 in particular—I think the biggest limitation compared to Yesql and similar libraries is the limited database support.

If there's still some work to make your version have feature parity, I'd be happy to help.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/honza/anosql/issues/29#issuecomment-440038409, or mute the thread https://github.com/notifications/unsubscribe-auth/ABiyySlNGRAfH5grEgmL5lNDc8hYLbd9ks5uwxqmgaJpZM4Ypiy3 .

nackjicholson commented 6 years ago

I've been thinking a bit about how to implement changes to anosql.

The main challenge I see to porting what I have into anosql, is the support for python2.7. There are still many folks out there using python2.7 and SQL databases, so I definitely don't want to drop them.

Hurdles when considering 2.7 support:

What I ideally want.

  1. An anosql version which supports python2/3 and has the following two features

    • loading directories of .sql files
    • extension for other database drivers via register_query_loader(name, loader)

  2. Another anosql version which can evolve beyond python2, only supports python3.6+, and enables the asyncio ecosystem.

How to get there.

Up for discussion, but here's a plan. Not necessarily the plan.

  1. Work toward a python 2/3 compatible version with the features above and release it as 0.3.0 without making any major breaking anosql API changes (we can add new things, just can't break existing things)
  2. Take a moment to reflect on the anosql API and discuss whether we want to make any changes.
    • For instance, I rather like what I did with aiosql.from_str(sql driver_type) and aiosql.from_path(path, driver_type) instead of anosql.load_queries_from_string(db_type, string) and anosql.load_queries(db_type, filename). There may be even better options than either.
    • Is a BaseQueryLoader class a good way to expose the ability for people to write new driver support? Is DriverTypeAdapter a better name. Is there another approach entirely?
    • etc.
  3. Release 1.0.0 with any thoughtful API changes. We then put this on a branch to maintain, improve, and bugfix it if necessary. But its more or less the finished version of anosql for python2.7/python3.4. We keep it there in the 1.x.x range of versions.
  4. Release 2.0.0, a major breaking version which has the same API changes, and additionally supports asyncio driver types, brings stdlib python3 improvements and is designed for 3.6+ only.
  5. Maintain 2.0.0 off of master. It is free to evolve with the world of python3, however it should also be a fairly complete and stable package able to stay in the 2.x.x version range for quite a while.

Next Steps

I can begin working on step 1. But, if you guys have any thoughts on the approach, or features you'd like for the lib I'm really interested to hear them.

EDIT

I think it might make more sense to keep the asyncio stuff in a separate project. Maintaining two branches and releases out of one repo which target different versions of python seems like quite a few hoops to jump through, and might be better kept separate. I'm happy we got a few changes incorporated with 0.3.0. Maybe there are API and documentation improvements to incorporate for a potential 1.0.

nackjicholson commented 5 years ago

https://nackjicholson.github.io/aiosql

I've been working on this as sort of a canary for anosql. I introduced some changes that I think I could apply to a 1.0.0 release here.

Got rid of the ABC QueryLoader

I got rid of the clunky abstract base class QueryLoader in favor of making database driver adapters duck-typed. I think writing adapters this way will be easier, and users don't have to depend on a base class from anosql.QueryLoader to make their classes. For more details there's some really sparse docs here.

Optional way to use the cursor objects directly.

All the queries with a SELECT type have a duplicate method suffixed by _cursor which is a context manager to the database cursor. So get_all_blogs(conn) can also be used as:

rows = queries.get_all_blogs(conn)
# [(1, "My Blog", "yadayada"), ...]

with queries.get_all_blogs_cursor(conn) as cur:
    # All the power of the underlying cursor object! Not limited to just a list of rows.
    for row in cur:
        print(row)

Got rid of $

I decided the use_col_description flag set via the dollar sign -- name: $get-all-blogs in order to control how rows are formatted is actually something better handled most of the time by the database drivers themselves. And almost all of them already have this feature baked in better than we can do it.

sqlite.Row And for Postgres there are many Connection and Cursor subclasses

An example in the tests to make sure it works as intended test_psycopg2.py

Next Step

I think I have the asyncio stuff pretty well isolated, and I could somewhat easily adapt this API to be 2.7 and 3.5 compatible as a 1.0.0 release over here. Would definitely appreciate feedback from anyone on it if you have time.

nackjicholson commented 5 years ago

Wasn't too difficult at all #34