pydal example

[sugizo]

successful implement blacksheep with pydal
could share or documented if you interest
posted some of code on here

thanks

[RobertoPrevato]

Thanks for the heads up, sugizo. Sure, if you share your code in a repository it might be interesting to have a link here. Thanks!

[sugizo]

share the code for RESTful simple CRUD for blacksheep with pydal in zip file, to run :
python server.py

or (first commenting the if condition in server.py)
uvicorn server:app --host 0.0.0.0 --port 8000 --log-level debug --reload

or (first commenting the if condition in server.py)
hypercorn server:app --bind '0.0.0.0:8000' --log-level debug --reload

desc

• server.py file to run the server
• test folder to store package files
• database subfolder to store database file if using sqlite, *.table (files generated by pydal) and sql log file
• exposed subfolder to store controllers (function to routing the request and response)
• models.py file is talk about define table, including it's validators if used to validate the data
• settings.py file is talk about settings for blacksheep and pydal

blacksheep_pydal.zip

pydal ref

will send with another orm if you want (pony, datasets, sqlalchemy, peewee)

[RobertoPrevato]

Thanks for sharing your work, @sugizo!
I downloaded the code and read it, pydal looks like an interesting ORM. I have a recommendation about the way it's used in the async app: since db operations are I/O bound, it would be best to wrap the synchronous functions into calls that use run_in_executor with a thread pool executor.

You wouldn't see the benefits of this when having a small load on the web application (not many concurrent users), but as the number of users increases then it would pay off. It is advised to do so because otherwise the event loop is blocked.

A common example of this approach is in files operations in the context of async apps (example in blacksheep).

[sugizo]

interested to learn about how to implement in blacksheep with pydal
is it just simply add async on the function
e.g.
exposed/install.py

@app.route('/install', methods = ['GET'] )
async def install(request):
    -- cutted --
    return responses.redirect('/api/get/test/1')

exposed/api.py

@app.route('/api/get/{tablename}/{rec_id}', methods = ['GET'] )
async def get(request, tablename, rec_id):
    -- cutted --
    return responses.text(rows)

@app.route('/api/delete/{tablename}/{rec_id}', methods = ['DELETE'] )
async def delete(request, tablename, rec_id):
    -- cutted --
    return responses.json({'Record Deleted' : row} )

the put and post function on example code above already use async, or perhaps it need to be revised ?

is this what you mean or could you give an example code above that must be change ?
thanks

[RobertoPrevato]

I mean the parts that interact with a database:

rows = db(query).select().as_json()

# ...
row = db(db[tablename]._id == rec_id).validate_and_update(**values)
db.commit()

# ...
row = db(db[tablename]._id == rec_id).delete()
db.commit()

Right now the methods are all synchronous. Since interacting with a db is a I/O bound operation, these methods should all be asynchronous. As a side note, I described this in my documentation:

BlackSheep supports both asynchronous and synchronous request handlers. Request handlers don't need to be asynchronous in those scenarios when the response is well-known and it can be produced without doing any I/O bound operation or any CPU intensive operation. This is the case for example of redirects

...
Request handlers that do I/O bound operations or CPU intensive operations should be instead async, to not impede the work of the web server's loop. For example, if information are fetched from a database or a remote API when handling a web request handler, it is a good practice to use asynchronous code to reduce RAM consumption and not impede the event loop of the web application.

I know little about pydal, so I don't know if it implements also an async API. If it doesn't, it would be a good PR for the project to submit a wrapper to use run_in_executor as I mentioned above. Essentially the same thing that aiofiles is for Files operations.

If you want to learn more about the async pattern, I recommend to watch Ryan Dahl's original presentation of Node.js.

Notes:

• some Python developers, including famous ones like the author of SQLAlchemy, Michael Bayer, criticize the use of async methods to interact with DBs. Mr. Bayer has a good point that, since you never have high concurrency on a DB (people usually use connection pools of 50 to 100 TCP/IP connections), it doesn't make sense to re-write all libraries that do DB operations to be completely asynchronous. Meaning that wrapping the sync methods in executors' calls (as I am recommending here) is sufficient, according to Bayer.
• However, don't be mistaken by the exact thing Bayer is saying. Async code makes sense for web applications to support higher loads and to reduce the RAM consumption considerably. Many hosting solutions today have a pay-as-you-go pricing model that charges depending on the amount of CPU and RAM (among other things) used by your apps - so reducing the RAM is also good under this point of view. If you build an async web app, then I/O and CPU bound operations should be wrapped in async coroutines.

---

For example, instead of:

@app.route('/api/get/{tablename}/{rec_id}', methods = ['GET'] )
def get(request, tablename, rec_id):
    query = (db[tablename]['id'] == rec_id)
    rows = db(query).select().as_json()
    return responses.text(rows)

Something like:

@app.route('/api/get/{tablename}/{rec_id}', methods = ['GET'] )
async def get(request, tablename, rec_id):
    query = (db[tablename]['id'] == rec_id)
    rows = await db(query).select()   # Note the await!
    return responses.text(rows.as_json())

[sugizo]

interesting, already tested (add async on def function and await on db IO operation), but got an error not sure it's on blacksheep or pydal side
exposed/install.py

@app.route('/install', methods = ['GET'] )
async def install(request):
    if db(db.test).isempty():
        test_id_1 = await db.test.insert(name = "test1")
        test_id_1 = await db.test.insert(name = "test2")
    db.commit()
    return responses.redirect('/api/get/test/1')

result

Internal Server Error.
While handling request: GET /install

Exception type:
TypeError
Exception message:
object Reference can't be used in 'await' expression
Stack trace:

Traceback (most recent call last):
  File "blacksheep/baseapp.pyx", line 72, in blacksheep.baseapp.BaseApplication.handle
  File "/Users/sugizo/Downloads/blacksheep_pydal/test/exposed/install.py", line 10, in install
    test_id_1 = await db.test.insert(name = "test1")
TypeError: object Reference can't be used in 'await' expression
Process ID: 1885.

exposed/api.py

@app.route('/api/get/{tablename}/{rec_id}', methods = ['GET'] )
async def get(request, tablename, rec_id):
    query = (db[tablename]['id'] == rec_id)
    rows = await db(query).select().as_json()
    return responses.text(rows)

result

Internal Server Error.
While handling request: GET /api/get/test/1

Exception type:
TypeError
Exception message:
object str can't be used in 'await' expression
Stack trace:

Traceback (most recent call last):
  File "blacksheep/baseapp.pyx", line 72, in blacksheep.baseapp.BaseApplication.handle
  File "/Users/sugizo/miniconda3/envs/python3_test/lib/python3.8/site-packages/blacksheep/server/normalization.py", line 438, in handler
    return await method(*values)
  File "/Users/sugizo/Downloads/blacksheep_pydal/test/exposed/api.py", line 18, in get
    rows = await db(query).select().as_json()
TypeError: object str can't be used in 'await' expression
Process ID: 1916.

[RobertoPrevato]

That's in how pydal is used in this specific case. I just made an example at the end of my previous message, to show how the thing would look like once DB operations are done asynchronously.

What the TypeError is saying, is that the object returned by the pydal method cannot be used in await expressions because it's not an object that can be awaited.

To write correct code, it would be necessary to do something like it's described here: awaitable loop.run_in_executor.

[felipebastosweb]

Since PyDAL has synchronous database connections and BlackSheep is an asynchronous framework, you need to put an asynchronous layer between them. This won't solve all the scalability issues of your application regarding concurrency and blocking I/O, but it can help minimize them. Ideally, you should use an asynchronous ORM like SQLAlchemy, Piccolo or TortoiseORM.

import asyncio
from concurrent.futures import ThreadPoolExecutor
from pydal import DAL, Field
from blacksheep import Application, Response

Database config

db = DAL('sqlite://storage.sqlite')
db.define_table('person', Field('name'))

Thread manager

executor = ThreadPoolExecutor(max_workers=5)

function async with Thread of database query

async def fetch_data(callback):
loop = asyncio.get_event_loop()
result = await loop.run_in_executor(executor, callback)
return result

def query_database():
rows = db(db.person).select()
return [row.name for row in rows]

Criação do aplicativo BlackSheep

app = Application()

Definição da rota

@app.route('/data')
async def get_data(request):
data = await fetch_data(query_database)
return Response(200, content=str(data))

[felipebastosweb]

https://github.com/Neoteroi/BlackSheep-SQLAlchemy/tree/main - Relational and NoSQL databases supported.