I need help about initialize the database table, thank you!

Questions and Help
#1

That’s my code about create db engine and init the database table

@app.before_server_start
async def create_db_engine(app, loop):
    _config = app.config
    app.ctx.bind = create_async_engine(
        f"mysql+aiomysql://{_config.MYSQL_USER}:{_config.MYSQL_PASSWORD}@{_config.MYSQL_HOST}/{_config.MYSQL_DBNAME}",
        pool_pre_ping=True,
        echo=True,
    )
    metadata = MetaData(app.ctx.bind)
    metadata.create_all()

But it tell me:

AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'
sys:1: RuntimeWarning: coroutine 'BaseEventLoop.create_server' was never awaited

So, how can I await ‘BaseEventLoop.create_server’? :sweat_smile:
I really need your help !!! :smiling_face_with_three_hearts:

#2

Please provide a link to your code or provide the full stack trace - there’s not enough here to work with to answer your question.

#3

That’s about my code

  • model/ __ init__.py
from sqlalchemy import INTEGER, Column
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class BaseModel(Base):
    __abstract__ = True
    id = Column(INTEGER(), primary_key=True, autoincrement=True, index=True, unique=True)
  • model/user.py
from sqlalchemy import Column, String, ForeignKey

from .model import BaseModel


class User(BaseModel):
    __tablename__ = "users"
    name = Column(String(), ForeignKey('users.id'))

    def to_dict(self):
        return {
            "id": self.id,
            "name": self.name
        }

    def __str__(self):
        return "{}<{}>".format(self.name, self.id)

    def __repr__(self):
        return self.__str__()
  • middleware/__ init__.py
from contextvars import ContextVar

from sanic import Sanic
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import sessionmaker


_base_model_session_ctx = ContextVar("session")
app = Sanic.get_app()


@app.before_server_start
async def create_db_engine(app, loop):
    _config = app.config
    app.ctx.bind = create_async_engine(
        f"mysql+aiomysql://{_config.MYSQL_USER_1}:{_config.MYSQL_PASSWORD_1}@{_config.MYSQL_HOST}/"
        f"{_config.MYSQL_DBNAME_1}",
        pool_pre_ping=True,
        echo=True,
    )

    BaseModel.metadata.create_all(app.ctx.bind)


@app.on_request
async def inject_session(request):
    request.ctx.session = sessionmaker(app.ctx.bind, AsyncSession, expire_on_commit=False)()
    request.ctx.session_ctx_token = _base_model_session_ctx.set(request.ctx.session)


@app.on_response
async def close_session(request, response):
    if hasattr(request.ctx, "session_ctx_token"):
        _base_model_session_ctx.reset(request.ctx.session_ctx_token)
        await request.ctx.session.close()
  • blueprint/user.py
from sanic import Blueprint
from sanic.response import json, text

from .model.user import User

user = Blueprint(name="user", url_prefix="/user")


@user.get("/l")
async def get_l(request):
    return text("1")


@user.get("/<user_name>")
async def get_user(request, user_name: str):
    session = request.ctx.session
    async with session.begin():
        # car = Car(brand=user_name)
        user = User(name=user_name)
        print(user, user.to_dict())
        session.add_all([user])
    return json(user.to_dict())
  • run.py
from sanic import Sanic
from sanic.response import json

from zgweb.util import setup_rate_limiter
from zgweb.util import utility

app = Sanic(__name__)
limiter = setup_rate_limiter(app)

from zgweb import blueprint, middleware

utility.autodiscover(
    app,
    blueprint,
    middleware,
    recursive=True
)

@app.route("/")
async def default(request):
    return json({"message": "hello Sanic!"})

And that’s the Traceback

Traceback (most recent call last):
  File "F:\1Code\Zgweb\run.py", line 14, in <module>
    app.run(
  File "F:\1Code\Zgweb\venv\lib\site-packages\sanic\app.py", line 1051, in run
    serve_single(server_settings)
  File "F:\1Code\Zgweb\venv\lib\site-packages\sanic\server\runners.py", line 199, in serve_single
    serve(**server_settings)
  File "F:\1Code\Zgweb\venv\lib\site-packages\sanic\server\runners.py", line 126, in serve
    loop.run_until_complete(app._server_event("init", "before"))
  File "D:\Python\3.9.5\lib\asyncio\base_events.py", line 642, in run_until_complete
    return future.result()
  File "F:\1Code\Zgweb\venv\lib\site-packages\sanic\app.py", line 1496, in _server_event
    await self.dispatch(
  File "F:\1Code\Zgweb\venv\lib\site-packages\sanic\signals.py", line 163, in dispatch
    return await dispatch
  File "F:\1Code\Zgweb\venv\lib\site-packages\sanic\signals.py", line 133, in _dispatch
    retval = await maybe_coroutine
  File "F:\1Code\Zgweb\venv\lib\site-packages\sanic\app.py", line 1382, in _listener
    await maybe_coro
  File "F:\1Code\Zgweb\zgweb\middleware\__init__.py", line 24, in create_db_engine
    BaseModel.metadata.create_all(app.ctx.bind)
  File "F:\1Code\Zgweb\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 4779, in create_all
    bind._run_ddl_visitor(
AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'
sys:1: RuntimeWarning: coroutine 'BaseEventLoop.create_server' was never awaited
#4

Are you running this as sanic run:app?

#5

Yeah, I’ve tried it, but it didn’t works.

#6

Sorry for answering so lately.

It seems you call the sync method create_all on async driver for mysql (aiomysql).

the middleware should be modify to this:

@app.before_server_start
async def create_db_engine(app, loop):
    _config = app.config
    app.ctx.bind = create_async_engine(
        f"mysql+aiomysql://{_config.MYSQL_USER_1}:{_config.MYSQL_PASSWORD_1}@{_config.MYSQL_HOST}/"
        f"{_config.MYSQL_DBNAME_1}",
        pool_pre_ping=True,
        echo=True,
    )

    async with app.ctx.bind.begin() as conn:
        await conn.run_sync(BaseModel.metadata.create_all)

here is the sqlalchemy documents

1 Like
#7

I meant to put a run_sync example in sanic how-to docs.

It seems I missed these part.