[Bug] ProgrammingError on Insert in a mysql database

[sralloza]

I'm trying to following the tutorial, but it keeps throwing SQL errors. I don't think I'm missing anything, but I apologize in advanced if that's the case.

This is the code (extracted from the tutorial but using mysql as a backend)

from uuid import UUID, uuid4

from ormdantic import Ormdantic
from pydantic import BaseModel, Field

database = Ormdantic("mysql+aiomysql://root:root@localhost:3306/test")

@database.table(pk="id", indexed=["name"])
class Flavor(BaseModel):
    """A coffee flavor."""

    id: UUID = Field(default_factory=uuid4)
    name: str = Field(max_length=63)


async def main():
    async with database._engine.begin() as conn:
        await database.init()
        await conn.run_sync(database._metadata.drop_all)
        await conn.run_sync(database._metadata.create_all)

    f1 = Flavor(name="Vanilla")
    await database[Flavor].insert(f1)
    f2 = await database[Flavor].find_one(f1.id)
    print(f2)


if __name__ == "__main__":
    import asyncio

    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())

I am using a mysql database with docker-compose:

version: "3.8"

services:
  database:
    image: mysql:oracle
    container_name: database
    environment:
      - MYSQL_ROOT_PASSWORD=root
    ports:
      - "3306:3306"

I am getting the following exception:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"flavor" ("id","name") VALUES (\'418b6b61-c73b-44bd-a1dc-a315cab4ddaa\',\'Vanilla\')\' at line 1')
[SQL: INSERT INTO "flavor" ("id","name") VALUES ('418b6b61-c73b-44bd-a1dc-a315cab4ddaa','Vanilla')]
(Background on this error at: https://sqlalche.me/e/14/f405)

Removing the .insert line, the .find_one line throws a ProgrammingError too:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"flavor" WHERE "id"=\'b4ff9b8f-aebd-4ed2-a8c9-b310058a7aeb\'\' at line 1')
[SQL: SELECT "id" "flavor\id","name" "flavor\name" FROM "flavor" WHERE "id"='b4ff9b8f-aebd-4ed2-a8c9-b310058a7aeb']
(Background on this error at: https://sqlalche.me/e/14/f405)

I'm using ormdantic==1.5.1, aiomysql==0.1.1 and sqalchemy==1.4.45.

[yezz123]

Hello @sralloza

This is literally weird I don't know what can cause this, but in PostgreSQL works fine as usual for me, I will add some integration tests for MySQL and we can see if its from the code level of ormdantic, cause at the end I depend on the usage of SQLAlchemy and Pypika to build the queries and both support MySQL and asyncio MySQL so this is a bit weird but I will double check it