Question 89241 in PostgreSQL Question posted in
The official documentation can be found here.

How to assign the url of the uploaded photo from AWS to database(postgresql) model in the same endpoint

Below that there is an endpoint that uploads a photo to aws-s3 and return the url of the photo. This works well.

session1 = boto3.Session(
    aws_access_key_id='XXXXXXXXXX',
    aws_secret_access_key='XXXXXXXXX'
)
S3_BUCKET_NAME = "XXXXX"
s3 = session1.resource("s3")
bucket = s3.Bucket(S3_BUCKET_NAME)

KB= 1024
MB = 1024 *KB


SUPPORTED_FILE_TYPES = {
    'image/png': 'png',
    'image/jpeg': 'jpg'
}

async def s3_upload(contents:bytes,key:str):
    logger.info(f'Uploading {key} to S3')
    bucket.put_object(Key=key, Body=contents)

async def s3_download(key=str):
    try :
        return s3.Object(bucket_name=S3_BUCKET_NAME,key=key).get()['Body'].read()
    except ClientError as err: 
        logger.error(str((err)))


router = APIRouter()



@router.post("/img-upload/")
async def upload(houseid:int,username:str,file: UploadFile = File(...)):
    if not file:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
                            detail='No file found')
    contents= await file.read()
    size= len(contents)

    if not 0 < size <= 1 * MB:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
                            detail='Suppoerted file size is 0-1MB')

    file_type = magic.from_buffer(buffer=contents,mime=True)

    if file_type not in SUPPORTED_FILE_TYPES:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST,
                           detail=f'Unsupported file type: {file_type}. Supported types are {SUPPORTED_FILE_TYPES}'
                           )

    await s3_upload(contents=contents, key=f'{username}-{houseid}.{SUPPORTED_FILE_TYPES[file_type]}')
    uploaded_file_url = f"https://{S3_BUCKET_NAME}.s3.amazonaws.com/{username}-{houseid}.{SUPPORTED_FILE_TYPES[file_type]}"
    return uploaded_file_url

What I want is that I want to save this url to database(Postgresql) inside of the /img-upload/ endpoint. I tried many methods but no solution yet.

I have a model as below. I want to assign the url from "/img-upload/" to img_url.How to do that?

class House(Base):
    id = Column(Integer,primary_key = True, index=True)
    title = Column(String,nullable= False)
    rent = Column(Integer,nullable = False)
    no_rooms = Column(Integer,nullable = False)
    size = Column(Integer,nullable = False)
    additional_cost = Column(Integer,nullable = False)
    total_rent = Column(Integer,nullable = False)
    max_flatmates = Column(Integer,nullable = False)
    exist_fmates = Column(Integer,nullable = False)
    heating_type = Column(String,nullable= False)
    heating_cost = Column(Integer,nullable = False)
    city = Column(String,nullable = False) 
    adress = Column(String,nullable = False)
    zipcode = Column(Integer,nullable = False)
    description = Column(String,nullable=False)
    min_duration = Column(Integer,nullable = False)
    smoking_allowed = Column(Boolean,nullable = False)
    parking = Column(String,nullable = False)
    postedby = Column(String,nullable = False)
    date_posted = Column(Date)
    img_url = Column(String,nullable = True)
    is_active = Column(Boolean(),default=True)
    owner_id =  Column(Integer,ForeignKey("user.id"))
    owner = relationship("User",back_populates="houses")
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you @Nuno.But I got an error when I used the code from you. I just changed a few things on your code and it works well now.

    Here are the changes;

    new_data.img_url = uploaded_file_url
db.add(new_data)
db.commit()
db.refresh(new_data)
return existing_house```

  2. 0
    1. Install the necessary drivers to connect to POSTGRESQL.

    In Debian OS: sudo apt-get install psycopg2

    1. SQLAlchemy engine is recommended.

    pip install sqlalchemy

    1. Use the SQLAlchemy to setup the database connection. Put this content inside a database.py file.
    from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create a SQLAlchemy engine and session factory
SQLALCHEMY_DATABASE_URL = "postgresql://your_username:your_password@your_host/your_database"
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Create a SQLAlchemy declarative base
Base = declarative_base()

# Dependency to get a database session for each request
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
    1. Ensure the tables are created when the application starts:
    from database import Base

@app.on_event("startup")
def startup():
    Base.metadata.create_all(bind=engine)
    1. Add this code to your endpoint:
    from sqlalchemy.orm import Session
from datetime import datetime

@router.post("/img-upload/")
async def upload(houseid:int,username:str,file: UploadFile = File(...), db: Session = Depends(get_db)):
    ### Your previous code
    uploaded_file_url = f"https://{S3_BUCKET_NAME}.s3.amazonaws.com/{username}-{houseid}.{SUPPORTED_FILE_TYPES[file_type]}"
    
    # Inserting data in POSTGRESQL
    new_data = House(img_url=uploaded_file_url, date_posted=datetime.now(), is_active=True, owner_id=1)
    db.add(new_data)
    db.commit()
    db.refresh(new_data)


    return uploaded_file_url
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search