In SQL Alchemy / FastApi, how to insert in database, a list of a BaseModel class?

ailauli69 0
Viewed 22

I want to insert dynamically into a table named "computers" some rows for some of its columns (these columns are not known in advance this is why I use the term dynamically).

To do so, I created a BaseModel class called "ColumnIn" that has two parameters: "name" (describe the name of the column), "value" (describes the value we want to affect for this column):

from fastapi import FastAPI
from sqlalchemy import MetaData, Table, insert, Column, Integer, String, create_engine
from pydantic import BaseModel
from typing import List, Union, Set

engine = create_engine("postgresql://postgres:f7Af&JhyuqdD@localhost/collector", pool_pre_ping=True, echo=True)
metadata_obj = MetaData()
computers = Table("computers", metadata_obj, autoload_with=engine)

class ColumnIn(BaseModel):
    name: Union[str, None] = None
    value : Union[str, None] = None

class ItemIn(BaseModel):
    db_name: Union[str, None] = None
    table_name: Union[str, None] = None
    other: List[ColumnIn]

app = FastAPI()

@app.post("/computers/")
async def create_computer(item: ItemIn):
    try:
        with engine.connect() as conn:
            result = conn.execute(
                insert(computers),
                [ val for val in item.other])
            conn.commit()
            return item
    except Exception as e:
        print(e)
        return item

When I perform a POST, no error on the FastAPI side, but an error occurs on the database side (SQL Alchemy):

SET/VALUES column expression or string key expected, got ('name', 'thename').

Do you understand this error ?

Thank you

[EDIT]:

To be mode precise, in place of :

[ val for val in item.other]

which clearly does not work, I need to have something like this:

{"name": "thename", "ip": "theip"}

Where I can specify all of the values of this dict dynamically (because depending on the case, names and value of columns will change)

sqlalchemy fastapi python
1 Answers

Ok, I just put here the solution that worked for me

dico = {}
for it in item.other:
    dico[it.name] = it.value


try:
    with engine.connect() as conn:
        result = conn.execute(

            insert(computers),
            [   
                dico
            ]
        )
        conn.commit()
        return item
ailauli690
Related