How to insert record using ORM in python postgresql
What is ORM : without query using inbuilt functions we can connect to db for fetching, inserting records
Sqlalchemy is ORM in Python
How to use ORM in Python
- Install Python: use below link for python installation
2. After that you have to Install fastapi or other python framework: You can follow the below link for fast api installation
3. Then install Sqlalchemy : Using pip command you can install Sqlalchemy
pip install sqlalchemy
from sqlalchemy.orm import Session
4.Then create instance in python file
app = FastAPI()
5. Create database file and import sqlchem engine and sessionmaker
6. Create Database Connection
7 Create URL for access ORM
Complete Code:
main.py
from typing import Optional
from fastapi import FastAPI, Response, status, HTTPException, Depends
from fastapi.params import Body
from pydantic import BaseModel
from random import randrange
import psycopg2
from psycopg2.extras import RealDictCursor
import time
from sqlalchemy.orm import Session
from .import models
from .database import engine, SessionLocal, get_db
models.Base.metadata.create_all(bind=engine)
app = FastAPI()
class Post(BaseModel):
title:str
content:str
published : bool =True
@app.get("/test")
async def get_method():
return {"MSG": "Get Method"}
#add post data
@app.post("/createpost", status_code=status.HTTP_201_CREATED)
async def create_post(new_post:Post, db: Session = Depends(get_db)):
# postData = models.Post(title=new_post.title, content=new_post.content, published=new_post.published )
postData = models.Post(**new_post.dict())
db.add(postData)
db.commit()
db.refresh(postData)
return {"Post": postData}
database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# SQLALCHEMY_DATABASE_URL = 'postgresql://<username>:<password>@<ip-address/hostname>/<database_name>'
SQLALCHEMY_DATABASE_URL = 'postgresql://postgres:123456789@localhost/fastapi_ORM_DB'
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
# Dependency
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
Models.py
from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy.sql.expression import null
from .database import Base
class Post(Base):
__tablename__ = "post"
id = Column(Integer, primary_key=True, nullable=False)
title = Column(String, nullable=False)
content = Column(String, nullable=False)
published = Column(Boolean, default=True)
Run your application
uvicorn main:app --reload
Execute your URL using postman
Check database:
Happy Learning 🙂