FastAPI Python Database connectivity

Question

I am new to the FastAPI world, I am creating an API to fetch and post the data to the MySQL database. I followed few link on internet and developed below code

DatabaseConnection.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

sql_database_url="mysql://root:root@localhost:3306/first_db"

engine=create_engine(sql_database_url)

sessionLocal=sessionmaker(autocommit=False,bind=engine)

base=declarative_base()

MainClass.py

from fastapi import FastAPI,Query,Depends
from sqlalchemy import Column,String,Integer
from typing import Optional,List
from pydantic import BaseModel
from sqlalchemy.orm import Session

from DatabaseConnection import engine,sessionLocal,base

app=FastAPI()

class User(base):
    __tablename__="users"
    id=Column(Integer,primary_key=True,index=True)
    name=Column(String(255),unique=True,index=True)

class UserSchema(BaseModel):
    id:int
    name:str

    class Config:
        orm_model=True


base.metadata.create_all(bind=engine)

@app.post("/create-user")
def createUser(userSchema:UserSchema):
    user=User(id=userSchema.id,name=userSchema.name)
    sessionLocal().add(user)
    sessionLocal().commit()
    return user

When i try to run this API using UVICorn i was running successful and the table also created successfully but the data that i am sending through the body is not added in the table.
The table is showing null value is added \

I have referred link

Any help will be appreciated. Thanks in advance

Piyush Jiwane · Accepted Answer · 2022-01-10 05:53:22Z

1

Thanks @anjaneyulubatta505 for help
but we need to do below changes to post data in database

@app.post("/create-user")
def createUser(userSchema:UserSchema):
    user = User(id=userSchema.id, name=userSchema.name)
    with Session(bind=engine) as session:
        session.add(user)
        session.commit()
    return user

referred links

Link1
Link2 from sqlAlChemy

answered Jan 10, 2022 at 5:53

Piyush Jiwane

1791 gold badge3 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anjaneyulubatta505 · Accepted Answer · 2022-01-10 05:38:51Z

0

You are not committing the change to the database. Just change your code like below to make it work.

# ... imports and other stuff
from DatabaseConnection import sessionLocal

@app.post("/create-user")
def createUser(userSchema:UserSchema):
    user = User(id=userSchema.id, name=userSchema.name)
    with sessionLocal() as session:
        session.add(user)
        session.commit()

    return user

edited Jan 10, 2022 at 5:38

answered Jan 9, 2022 at 7:17

anjaneyulubatta505

11.9k1 gold badge61 silver badges68 bronze badges

5 Comments

Piyush Jiwane Over a year ago

Thanks for respond, but with the above code I am getting 500:Internal Server Error

Piyush Jiwane Over a year ago

It giving me sqlalchemy.exc.UnboundExecutionError: Could not locate a bind configured on mapper mapped class User->users, SQL expression or this Session. error msg

anjaneyulubatta505 Over a year ago

@PiyushJiwane updated the code. you can check it now. make sure you imoprt localSession correctly.

Piyush Jiwane Over a year ago

Hi, still getting same error

Piyush Jiwane Over a year ago

I found a solution and we need to do below changes with Session(bind=engine) as session:

Collectives™ on Stack Overflow

FastAPI Python Database connectivity

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related