# CRUD via SQLAlchemy

## Get All

* We will have to import the <mark style="color:green;">`database.py`</mark> file and the <mark style="color:green;">`models.py`</mark> file&#x20;

```python
from . import models
from app.database import engine, get_db
```

* Next we will create the function with that calls the <mark style="color:orange;">`get_db`</mark> dependency and creates the query&#x20;

```python
@app.get("/sqlalchemy")
# We are saving the dependency and session in a variable (db)
def test_post(db: Session = Depends(get_db)):
    
    # We call that variable
    # Set to query
    # Add the class name from the models.py Table
    # Fetch all rows with .all()
    posts = db.query(models.Post).all()

    return { "data": posts }
```

{% hint style="info" %}
Note: Syntax for all queries is:

* db variable
* query to interrogate&#x20;
* models.\<CLASS\_NAME> - table that we want to use
* .add() - Fetches all rows
  {% endhint %}

## Get by ID

* We will fetch the post by ID by using the query and filtering for the ID in the models that is equal to the ID passed in

```python
def get_post(id: int, db: Session = Depends(get_db)):

    post = db.query(models.Post).filter(models.Post.id == id).first()


    if not post:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"post with id {id} was not found")

    return {"post_details": post}
```

{% hint style="info" %}
Note: If you know that you only have a single post with said ID, use the method <mark style="color:orange;">`.first()`</mark> instead of <mark style="color:orange;">`.all()`</mark>

* This is more efficient as <mark style="color:orange;">`.all()`</mark> will look further through all posts, even tho it has found the post with the ID specified
* Meaning that it uses resources that it doesn't need
  {% endhint %}

## Create

* We're basically doing the same thing as RAW SQL but we are passing in the variables directly

```python
@app.post("/posts", status_code=status.HTTP_201_CREATED)
def create_posts(post: Post, db: Session = Depends(get_db)):

    # Map table fields to data to add
    new_post = models.Post(title=post.title, content=post.content, published=post.published)

    # Add post to database
    db.add(new_post)

    # Commit it to the database
    db.commit()

    # Returning - Basically refresh table and retrieve the post in order to get the
    db.refresh(new_post)

    return { "data": new_post }
```

## Delete

* The first part of the query stays the same as the Get ID one

```python
@app.delete("/posts/{id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_post(id: int, db: Session = Depends(get_db))

    post = db.query(models.Post).filter(models.Post.id == id)



    if post.first() == None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"post with {id} does not exist")

    post.delete(synchronize_session=False)
    db.commit()

    return Response(status_code=status.HTTP_204_NO_CONTENT)
```

* We do call the <mark style="color:orange;">`.first()`</mark> method to check if there is no ID
* If there isn't it will raise an exception
* Outside the IF statement we will delete the post with the ID
* Then we have to commit the changes to the database

## Update

```python
@app.put("/posts/{id}")
def update_posts(id: int, post_schema: Post, db: Session = Depends(get_db)):

    post_query = db.query(models.Post).filter(models.Post.id == id)

    post = post_query.first()

    # Raising a 404 if post index position of post is not found
    if post == None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"post with {id} does not exist")

    post_query.update(post_schema.dict(), synchronize_session=False)

    db.commit()

    return {"data": post_query.first() }  # Return new post post
```

* We are saving the query in the <mark style="color:orange;">`post_query`</mark> variable&#x20;
* Then we are getting the first post
* If that post does not exist, it raises an error
* If it does it updates the database entry with the <mark style="color:orange;">`post_schema`</mark>that already is passed in
* Commits this to the database
* To return it to the user, we just run another query


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.arkannis.net/programming/python/frameworks/sqlalchemy/crud-via-sqlalchemy.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.