FastAPI + sqlite Inserting data to a database

Question

I am new to FastAPI and am playing around to get to know the framework. I am trying to insert data into my database but I am missing something.

Here's my model:

from datetime import datetime
from pydantic import BaseModel

class Article(BaseModel):
    id: int
    title: str
    slug: str
    content: str
    author: str
    date: datetime

class InArticle(BaseModel):
    title: str
    slug: str
    content: str
    author: str

and here's the logic

@app.post("/articles", response_model=InArticle)
async def create_article(article: InArticle):
    cursor = connexion.cursor()
    article_obj = (InArticle(
        title=article.title,
        slug=article.slug,
        content=article.content,
        author=article.author
    ))
    db_query = """INSERT INTO Article (title, slug, content, author) 
                    VALUES (?, ?, ?, ?)"""
    cursor.execute(db_query, article_obj)
    connexion.close()
    return article

I get this error:

cursor.execute(db_query, article_obj)
ValueError: parameters are of unsupported type

What am I missing ?

Does this answer your question? [parameter unsupported when inserting int in sqlite](https://stackoverflow.com/questions/11853167/parameter-unsupported-when-inserting-int-in-sqlite) — noninertialframe, Oct 15 '21 at 21:20

MatsLindh · Accepted Answer · 2021-10-16T08:55:01.923

0

SQLAlchemy is complaing about you giving it a single article_obj - which it has no idea what to do with. If you plan to issue the queries manually (you might want to look at SQLModel or using SQLAlchemy's ORM), you're going to have to give the parameters separately.

To use the syntax you've given you should either give the values manually:

cursor.execute(db_query, (article.title, article.slug, article.content, article.author))

.. or with named parameters instead:

db_query = """INSERT INTO Article (title, slug, content, author) 
                VALUES (:title, :slug, :content, :author)"""

# .. which you can then expand automagically
cursor.execute(db_query, **article_obj.dict())
# **article_obj.dict() expands it to title=article_obj.title, ..etc

edited Oct 16 '21 at 08:55

answered Oct 15 '21 at 21:30

MatsLindh

49,529
4
53
84

Thanks a lot for the time, the reactivity and the sources. The first method is working and I have now different ways to explore in order to implement it. Thanks again – GroovyHooked Oct 15 '21 at 22:00
After a little understanding I can pass it as a parameter like I did if it's an object; ***article_obj = { "title": article.title, "slug": article.slug, "content": article.content, "author": article.author }*** or with the cursors.execute method but it takes 2 arguments. So it is ***cursor.execute(db_query, (article.title, article.slug, article.content, article.author))*** Thanks again for your help. Tom – GroovyHooked Oct 15 '21 at 22:24

FastAPI + sqlite Inserting data to a database

1 Answers1