🔒 sqlalchemy MySQL migration

[hardy zenith]

Hey guys
Im new to the sqlalchemy...
the way I used to do setup my db is first writing the migration and then autogenerate the models directly from db
but seems like thats not the case for sqlalchemy (or it is and I don't know) although I do like how I can write my models manually as well, but the thing is how can I apply the models and tables that I created to my MySQL database?

[quartz hare]

@hardy zenith

Python help channel opened

Remember to:

• Ask your Python question, not if you can ask or if there's an expert who can help.
• Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
• Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.

[timber nexus]

when you have the models, you have a MetaData object which has a create_all() method

[hardy zenith]

couold you explain more?

[hardy zenith]

I mean where can I get the metadata from? I have multiple models

should I call the create_all on each of them?

[timber nexus]

you have a declarative base, right?

https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.DeclarativeBase.metadata

[hardy zenith]

I have this as base

from datetime import datetime, timezone

from sqlalchemy import Column, DateTime, Integer
from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    id = Column(Integer, primary_key=True, index=True)
    created_at = Column(DateTime, default=datetime.now(timezone.utc), nullable=False)
    updated_at = Column(
        DateTime,
        default=datetime.now(timezone.utc),
        onupdate=datetime.now(timezone.utc),
        nullable=False,
    )

[timber nexus]

yeah so Base.metadata.create_all(engine)

[hardy zenith]

and this will create the tables for all the models?

[timber nexus]

all the models from modules you've imported so far, yes

[hardy zenith]

what kind of dark magic is this
but could you say what you mean by "imported"? I have to import all modules before calling this? and does this also do IF NOT EXIST or should I call it just once

[timber nexus]

how would it know about models you haven't imported yet?

do you think it scans your codebase and imports everything automagically, or something?

[hardy zenith]

no, I wanted to be sure I understand you right
so something like this

from .base import Base
from .users import Users
from .products import Products

def create_tables(engine: AsyncEngine):
    Base.metadata.create_all(engine)

(just a example)

[timber nexus]

why would you create a function for that

just call that directly

also if you're doing that with an async engine, you need a different way of calling that

async with engine.begin() as conn:
    await conn.run_sync(Base.metadata.create_all)

[hardy zenith]

my currnet db setup look like this

from core.config import settings
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

engine = create_async_engine(ASYNC_DATABASE_URL, echo=True, future=True)

AsyncSessionLocal = async_sessionmaker(
    engine,
    expire_on_commit=False,
    autocommit=False,
    autoflush=False,
)


async def get_db():
    async with AsyncSessionLocal() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()

should I add a init function of some sort?

[timber nexus]

how are you using get_db()?

[hardy zenith]

and also is it ok to call the create_all on every run? or should I just call it once?

[hardy zenith]

with fastapi Depends

[timber nexus]

you can, but that won't do migrations; it just creates any missing tables

this is a deployment issue which you need to think of separately

[hardy zenith]

as I expect it to, so basically it only create tables that don't exist

[timber nexus]

yeah

btw future=True is redundant with sqlalchemy 2

[hardy zenith]

ok yeah thank you
I think what I'm doing currently is messy...
I mean I'm not used to ORMs, the above database will be used in two place, one in fastapi, and another in my actual program, is the current setup ok for that?

[timber nexus]

I recommend you set up a single top level package which houses all your code

the models would be shared between your fastapi web interface and the "real app"

one more thing: if you're not committed to mysql, I recommend you use postgresql instead

it's far superior in virtually every way

including the quality of the db drivers

[hardy zenith]

I would've loved to use Postgresql by any mean... but sadly it have to be MySQL...

[timber nexus]

I'm curious about the reason

[hardy zenith]

the reason is the person that Im doing the project in behalf of decided to write MySQL as database in the contract... so my hands are closed

[timber nexus]

right-o

c'est la vie

[hardy zenith]

btw, in my actuall project should I also call get_db to get a new session whenever I want to do something? or is there any better way? I did like this in first place because this is how fastapi doc recommend it

[timber nexus]

you can make an async context manager version of that

and then async with get_db_context() as db: ...

or something

[hardy zenith]

good idea, do you have any recommendation on the context manager? I guess I can use contextlib.asynccontextmanager with it? or is there better ways?

[timber nexus]

that's what you'd use

I see no reason for any alternatives

[hardy zenith]

thank you for the helps

[quartz hare]

Python help channel closed for inactivity

This help channel has been closed. Feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.