SQL Database Introduction and base files for coding

 SQL Database Introduction

Database

  • Organized collection of structured information of data, which is stored in a computer system.
  • The data can be easily accessed
  • The data can be modified
  • The data can be controlled and organized
  • Many databases use a structured query language(SQL) to modify and write data
  • Data can be related to just about any object.
  • For example, a user on an application may have:
    • Name
    • Age
    • Email
    • Password
  • A database is a collection of data
  • Since data, on its own, is just data. A database allows management of this data
  • Databases are organized in how data can be retrieved, stored and modified
  • There are many types of Database Management Systems (DBMS)
  • Some of the databases are:
    • SQLite
    • MySQL
    • Postgres
    • There are many more.

SQL

  • Pronounced either as S-Q-L or "See Quel"
  • Standard language for dealing with relational databases
  • SQL can be used to do different things with the database records: (CRUD)
    • Create
    • Read
    • Update
    • Delete

Create and Install Database File

We need to download SQLALCHEMY for database connection as it is an ORM which will help us to switch between multiple databases without making too much change to any SQL query.

Code: database.py
Here we have base code for database setup not function but will become once completed.

# This file is to make the connection with database for FastAPI framework project.

from sqlalchemy import create_engine
# It will help to create local sessions for each thread to interact with database seperately.
from sqlalchemy.orm import sessionmaker
# importing declarative to create the object of database to control all database activity.
from sqlalchemy.ext.declarative import declarative_base

# Below command will create database in our project folder.
SQLALCHEMY_DATABASE_URL='sqlite:///./todos.db'
# database engine setup as it will let us know about the database which is going to be used.
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={'check_same_thread':False})
# check_same_thread:False will ensure that no two process can share
# same connection and instance and we not need to check everytime the same thread.

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# object of database.
Base = declarative_base()

Create Database Models

Models:

It help sqlalchemy to understand what type of database are we connecting and accessing for tables performing all CRUD operations over it.

example of table:


Boolean is represented as 0 for False and 1 for True in a table.

Code: models.py
# This will help sqlalchemy to understand the database table and work on it.
from database import Base
from sqlalchemy import Column, Integer, String, Boolean

class Todos(Base):
    __tablename__ = 'todos'

    id = Column(Integer, primary_key = True, index = True)
    title = Column(String)
    description = Column(String)
    priority = Column(Integer)
    complete = Column(Boolean, default = False)


Create our Todos Database

Here we will create the database for storing data in table.

code: main.py
# This file it to perform all the activity which we required for Todo application.
from fastapi import FastAPI
import models
from database import engine

app = FastAPI()

models.Base.metadata.create_all(bind=engine)

Once we will run this main file then it will create a todo.db file in the folder where main is present and then you need to install sqlite to play with this database.


Comments

Post a Comment

Popular posts from this blog

Post Request with Pydantic usage (input validator)

Image
Post Request by using Pydantic Pydantic Python library that is used for data modeling, data parsing and has efficient error handling. Pydantics is commonly used as a resource for data validation and how to handle data coming to our FastAPI application. Pydantic implementation mechanism Create a different request model for data validation Field data validation on each variable/element BaseModel is the Pydantics BaseModel class which will provide facility to validate data. Field is the keyword to do it. As it will help to provide constraints for data validation. sample class BookRequest(BaseModel):     id: int = Field(gt=0)     title: str = Field(min_length=3)     author: str = Field(min_length=1)     description: str = Field(min_length=1, max_length=100)     rating: int = Field(gt=0, lt=5) Book Request and Book Conversion We will convert the Pydantic Request into a Book Example for post request method @app.post("/create_book") async def c...
Read more

Code Scalability and Routing

Image
 Creating Authentication file As we need to keep authentication part separate from our main application working so that we will not mess-up with our main application logic and it's security settings and authentication criteria as well as logics. auth.py - Basic authentication code stub. # This file is to define authenticator and authorization for the Todo application. from fastapi import FastAPI , status app = FastAPI () @ app . get ( "/auth" , status_code = status . HTTP_200_OK ) async def get_user ():     return { 'user' : 'authenticated_user' } Create Routers Package Here we want to use both and auth.py and main.py file to run together and to allow routers to run smoothly and want to scale the project code. To make this possible we need to use router facility or FastAPI. Here we are going to have a main file is a route of our application and routes will sit on this main file and will help in performing the tasks on call from main file. To make i...
Read more

CRUD Assignment

Image
 CRUD Assignment 1. Create a new API Endpoint that can fetch all books from a specific author using either Path Parameters or Query Parameters. Code and Result: As async is implemented by FastAPI itself hence ignored it. from fastapi import FastAPI app = FastAPI () BOOKS = [         { "title" : "Book One" , "author" : "Author A" , "category" : "Fiction" },         { "title" : "Book Two" , "author" : "Author B" , "category" : "Non-Fiction" },         { "title" : "Book Three" , "author" : "Author A" , "category" : "Biography" },         { "title" : "Book Four" , "author" : "Author A" , "category" : "Historical" },         { "title" : "Book Five" , "author" : "Author A" , "category" : "Romanc...
Read more