🔒 hi i need some help regarding fastapi | Python | Page 1

nocturne seal Apr 8, 2024, 5:30 PM

#

@asyncer.asyncify
@app.get("/list_users")
async def list_users():
    try:
        query = """
        SELECT username, email, status, roles
        FROM users_d
        WHERE username != 'superadmin'
        """
        data = await async_execute(query)

        if not data:
            return JSONResponse(status_code=404, content={"message": "No users found"})

        users = [{"username": row[0], "email": row[1], "status": row[2], "roles": row[3]} for row in data]
        return JSONResponse(content=users)

    except Exception as e:
        return JSONResponse(status_code=500, content={"message": str(e)})

@asyncer.asyncify
@app.put("/update_status/{username}")
async def update_status(username: str, new_status: Optional[bool] = None, new_role: Optional[str] = None):
    # Check if the user exists in the database
    query = 'SELECT username, status, roles FROM users WHERE username = %(username)s'
    user_data = await async_execute(query, {'username': username}, clickhouse_client)

    if not user_data:
        raise HTTPException(status_code=404, detail="User not found")

    user = user_data[0]  # Assuming username is unique

    if new_status is not None:
        # Convert True/False to 1/0 for status
        user['status'] = int(new_status)

    if new_role is not None:
        user['roles'] = new_role

    # Update the user's status and roles in the database
    update_query = 'UPDATE users SET status = %(status)s, roles = %(roles)s WHERE username = %(username)s'
    await async_execute(update_query, {'status': user['status'], 'roles': user['roles'], 'username': username}, clickhouse_client)

    return {"message": "User status and/or roles updated successfully"}

lean wagonBOT Apr 8, 2024, 5:30 PM

#

nocturne seal ```python @asyncer.asyncify @app.get("/list_users") async def list_users(): ...

#

@nocturne seal

Python help channel opened

Remember to:

Ask your Python question, not if you can ask or if there's an expert who can help.
Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

nocturne seal Apr 8, 2024, 5:31 PM

#

@oak bloom hello

dreamy pond Apr 8, 2024, 5:31 PM

#

is there a reason you're using asyncer here?

nocturne niche Apr 8, 2024, 5:31 PM

#

nocturne seal ```python @asyncer.asyncify @app.get("/list_users") async def list_users(): ...

You havn't explained what the problem is

nocturne seal Apr 8, 2024, 5:31 PM

#

 File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 72, in app
    response = await func(request)
  File "/usr/local/lib/python3.8/dist-packages/fastapi/routing.py", line 299, in app
    raise e
  File "/usr/local/lib/python3.8/dist-packages/fastapi/routing.py", line 294, in app
    raw_response = await run_endpoint_function(
  File "/usr/local/lib/python3.8/dist-packages/fastapi/routing.py", line 191, in run_endpoint_function
    return await dependant.call(**values)
  File "/root/testing4.py", line 271, in update_status
    user['status'] = int(new_status)
TypeError: list indices must be integers or slices, not str```

#

i want to update the user status or role or both based on username

#

@asyncer.asyncify
@app.put("/update_status/{username}")
async def update_status(username: str, new_status: Optional[bool] = None, new_role: Optional[str] = None):
    # Check if the user exists in the database
    query = 'SELECT username, status, roles FROM users WHERE username = %(username)s'
    user_data = await async_execute(query, {'username': username}, clickhouse_client)

    if not user_data:
        raise HTTPException(status_code=404, detail="User not found")

    user = user_data[0]  # Assuming username is unique

    if new_status is not None:
        # Convert True/False to 1/0 for status
        user['status'] = int(new_status)

    if new_role is not None:
        user['roles'] = new_role

    # Update the user's status and roles in the database
    update_query = 'UPDATE users SET status = %(status)s, roles = %(roles)s WHERE username = %(username)s'
    await async_execute(update_query, {'status': user['status'], 'roles': user['roles'], 'username': username}, clickhouse_client)

    return {"message": "User status and/or roles updated successfully"}```

this function throwing above error

nocturne niche Apr 8, 2024, 5:32 PM

#

nocturne seal ```python @asyncer.asyncify @app.put("/update_status/{username}") async def upda...

what is the value behind user_data?

nocturne seal Apr 8, 2024, 5:33 PM

#

CREATE TABLE users (
    id UUID DEFAULT generateUUIDv4(),
    username String,
    hashed_password String,
    email String,
    status UInt8 DEFAULT 0, -- Default status is 0 (False)
    roles Array(String) DEFAULT ['soc'], -- Default roles is ['soc']
    PRIMARY KEY (id)
) ENGINE = MergeTree()
ORDER BY (id);```

#

this is the table behind

nocturne niche Apr 8, 2024, 5:33 PM

#

if you print(user_data) what does it show?

nocturne seal Apr 8, 2024, 5:33 PM

#

ok wait let me show you

#

([('salman', 0, ['soc'])], [('username', 'String'), ('status', 'UInt8'), ('roles', 'Array(String)')])

nocturne niche Apr 8, 2024, 5:34 PM

#

I reckon its a string

dreamy pond Apr 8, 2024, 5:34 PM

#

that looks like a tuple containing different things

nocturne seal Apr 8, 2024, 5:35 PM

#

tuple is only in roles

nocturne niche Apr 8, 2024, 5:35 PM

#

strange result

nocturne seal Apr 8, 2024, 5:35 PM

#

like i want to update user status to 1 or 0 based on username

nocturne niche Apr 8, 2024, 5:36 PM

#

status is UInt8 tho?

#

ah wait, that's just the format

nocturne seal Apr 8, 2024, 5:36 PM

#

should i make boolean in database

#

like true or false

nocturne niche Apr 8, 2024, 5:36 PM

#

Ah ok. So its user_data[0][1] for the status value

nocturne seal Apr 8, 2024, 5:37 PM

#

where should i change that

nocturne niche Apr 8, 2024, 5:37 PM

#

So you were referencing user['status'] but the user is not a dictionary

#

Its a list of tuples

#

The first index of the list is a tuple of the user values

#

i.e. [('salman', 0, ['soc'])]

#

Oh and it seems to be in another list

#

Then to get the name, its basically the first index of the first index of the first index of user_data

nocturne seal Apr 8, 2024, 5:39 PM

#

oh wait

#

i think i do wrong

#

why would i need select in above function

nocturne niche Apr 8, 2024, 5:39 PM

#

basically 3 levels deep

nocturne seal Apr 8, 2024, 5:39 PM

#

i only need update query

#

@nocturne niche

@asyncer.asyncify
@app.put("/update_status/{username}")
async def update_status(username: str, new_status: Optional[bool] = None, new_role: Optional[str] = None):
    user = {'username': username, 'status': None, 'roles': None}

    if new_status is not None:
        # Convert True/False to 1/0 for status
        user['status'] = int(new_status)

    if new_role is not None:
        user['roles'] = new_role

    # Update the user's status and roles in the database
    update_query = 'UPDATE users SET status = %(status)s, roles = %(roles)s WHERE username = %(username)s'
    await async_execute(update_query, {'status': user['status'], 'roles': user['roles'], 'username': username}, clickhouse_client)

    return {"message": "User status and/or roles updated successfully"}```

is it ok ?

oak bloom Apr 8, 2024, 5:41 PM

#

Why are you passing clickhouse_client to async_execute?

#

And why do you have @asyncer.asyncify on update_statua?

dreamy pond Apr 8, 2024, 5:42 PM

#

you also don't need to use @asyncer.asyncify

#

^^

#

you don't need asyncer anywhere within fastapi basically.

nocturne seal Apr 8, 2024, 5:42 PM

#

@oak bloom hi so you told me told me to use when connect with databse?

#

@oak bloom

ALTER TABLE users UPDATE status = 1 , roles = ['admin'] WHERE username = 'salman';

i want to use this query in my api , i want to update status or roles or both at a same time

oak bloom Apr 8, 2024, 5:58 PM

#

nocturne seal <@172270232499388416> hi so you told me told me to use when connect with databse...

You don't need to pass clickhouse_client

#

I'm surprised it works

nocturne seal Apr 8, 2024, 5:58 PM

#

i changed that

#

but stuck on query

nocturne seal Apr 8, 2024, 6:32 PM

#

@oak bloom

#

@asyncer.asyncify
@app.get("/list_users")
async def list_users():
    try:
        query = """
        SELECT username, email, status, roles
        FROM users_d
        WHERE username != 'superadmin'
        """
        data = await async_execute(query)

        if not data:
            return JSONResponse(status_code=404, content={"message": "No users found"})

        users = [{"username": row[0], "email": row[1], "status": row[2], "roles": row[3]} for row in data]
        return JSONResponse(content=users)

    except Exception as e:
        return JSONResponse(status_code=500, content={"message": str(e)})

@app.put("/update_status/{username}")
async def update_status(username: str, new_status: Optional[bool] = None, new_role: Optional[str] = None):
    # Fetch user details from the database
    user_query = 'SELECT email, status, roles FROM users WHERE username = %(username)s'
    user_data = await async_execute(user_query, {'username': username})
    print(user_data)
    if not user_data:
        raise HTTPException(status_code=404, detail="User not found")

    # Convert fetched user details tuple to a dictionary
    user = {
        'email': user_data[0][0],
        'status': user_data[0][1],
        'roles': user_data[0][2],
    }
    print(user)

    # Update status and roles based on provided data or defaults
    if new_status is not None:
        # Convert True/False to 1/0 for status
        user['status'] = int(new_status)

    if new_role is not None:
        user['roles'] = new_role

    # Update the user's status and roles in the database
    update_query = 'ALTER TABLE users UPDATE status = %(status)s, roles = %(roles)s WHERE username = %(username)s'
    
    await async_execute(update_query, {'status': user['status'], 'roles': user['roles'], 'username': username}, clickhouse_client)

    return {"message": "User status and/or roles updated successfully"}

can you please check this both are working anything need to change

oak bloom Apr 8, 2024, 6:34 PM

#

You're still passing clickhouse_client where you shouldn't

#

And you still have a redundant asyncify decorator

nocturne seal Apr 8, 2024, 6:35 PM

#

can you please paste line

#

oh wait

#

@app.put("/update_status/{username}")
async def update_status(username: str, new_status: Optional[bool] = None, new_role: Optional[str] = None):
    # Fetch user details from the database
    user_query = 'SELECT email, status, roles FROM users WHERE username = %(username)s'
    user_data = await async_execute(user_query, {'username': username})
    print(user_data)
    if not user_data:
        raise HTTPException(status_code=404, detail="User not found")

    # Convert fetched user details tuple to a dictionary
    user = {
        'email': user_data[0][0],
        'status': user_data[0][1],
        'roles': user_data[0][2],
    }
    print(user)

    # Update status and roles based on provided data or defaults
    if new_status is not None:
        # Convert True/False to 1/0 for status
        user['status'] = int(new_status)

    if new_role is not None:
        user['roles'] = new_role

    # Update the user's status and roles in the database
    update_query = 'ALTER TABLE users UPDATE status = %(status)s, roles = %(roles)s WHERE username = %(username)s'
    
    await async_execute(update_query, {'status': user['status'], 'roles': user['roles'], 'username': username})

    return {"message": "User status and/or roles updated successfully"}``` now ?

#

@oak bloom need your help bro

dreamy pond Apr 8, 2024, 6:53 PM

#

nocturne seal <@172270232499388416> need your help bro

have a bit of patience.

nocturne seal Apr 8, 2024, 6:53 PM

#

alright

dreamy pond Apr 8, 2024, 6:55 PM

#

what's the current error?

nocturne seal Apr 8, 2024, 6:57 PM

#

@dreamy pond no error just need to verify that my implementaion is correct regarding async

@app.put("/update_status/{username}")
async def update_status(username: str, new_status: Optional[bool] = None, new_role: Optional[str] = None):
    # Fetch user details from the database
    user_query = 'SELECT email, status, roles FROM users WHERE username = %(username)s'
    user_data = await async_execute(user_query, {'username': username})
    print(user_data)
    if not user_data:
        raise HTTPException(status_code=404, detail="User not found")

    # Convert fetched user details tuple to a dictionary
    user = {
        'email': user_data[0][0],
        'status': user_data[0][1],
        'roles': user_data[0][2],
    }
    print(user)

    # Update status and roles based on provided data or defaults
    if new_status is not None:
        # Convert True/False to 1/0 for status
        user['status'] = int(new_status)

    if new_role is not None:
        user['roles'] = [new_role]
        print (user['roles'])

    # Update the user's status and roles in the database
    update_query = 'ALTER TABLE users UPDATE status = %(status)s, roles = %(roles)s WHERE username = %(username)s'
    print (update_query) 
    await async_execute(update_query, {'status': user['status'], 'roles': user['roles'], 'username': username})

    return {"message": "User status and/or roles updated successfully"}

#

or is there any better way to that
need to update status and roles column based on username

this is table structure

CREATE TABLE users (
    id UUID DEFAULT generateUUIDv4(),
    username String,
    hashed_password String,
    email String,
    status UInt8 DEFAULT 0, -- Default status is 0 (False)
    roles Array(String) DEFAULT ['soc'], -- Default roles is ['soc']
    PRIMARY KEY (id)
) ENGINE = MergeTree()
ORDER BY (id);```

#

this 1 is working code

dreamy pond Apr 8, 2024, 6:59 PM

#

only thing that pops out to the are the if cond is not None

#

you can just simplify to if cond:

nocturne seal Apr 8, 2024, 6:59 PM

#

where is cond?

#

oh you mean this if new_status :

dreamy pond Apr 8, 2024, 7:00 PM

#

correct

nocturne seal Apr 8, 2024, 7:00 PM

#

ok other than that

    # Convert fetched user details tuple to a dictionary
    user = {
        'email': user_data[0][0],
        'status': user_data[0][1],
        'roles': user_data[0][2],
    }```
this is needed /

dreamy pond Apr 8, 2024, 7:01 PM

#

sure, you would simplify it a bit further for readability

nocturne seal Apr 8, 2024, 7:01 PM

#

ok this is update func plesae check

@app.put("/update_status/{username}")
async def update_status(username: str, new_status: Optional[bool] = None, new_role: Optional[str] = None):
    # Fetch user details from the database
    user_query = 'SELECT email, status, roles FROM users WHERE username = %(username)s'
    user_data = await async_execute(user_query, {'username': username})
    print(user_data)
    if not user_data:
        raise HTTPException(status_code=404, detail="User not found")

    # Convert fetched user details tuple to a dictionary
    user = {
        'email': user_data[0][0],
        'status': user_data[0][1],
        'roles': user_data[0][2],
    }
    print(user)

    # Update status and roles based on provided data or defaults
    if new_status:
        # Convert True/False to 1/0 for status
        user['status'] = int(new_status)

    if new_role:
        user['roles'] = [new_role]
        print (user['roles'])

    # Update the user's status and roles in the database
    update_query = 'ALTER TABLE users UPDATE status = %(status)s, roles = %(roles)s WHERE username = %(username)s'
    print (update_query) 
    await async_execute(update_query, {'status': user['status'], 'roles': user['roles'], 'username': username})

    return {"message": "User status and/or roles updated successfully"}

dreamy pond Apr 8, 2024, 7:02 PM

#

like this

#

userdata_zero = user_data[0]
 
user = {
    'email': userdata_zero[0],
    'status': userdata_zero[1],
    'roles': userdata_zero[2],
}```

#

just for readibility though

#

yeah looks good

nocturne seal Apr 8, 2024, 7:03 PM

#

also

#

this one

@asyncer.asyncify
@app.get("/list_users")
async def list_users():
    try:
        query = """
        SELECT username, email, status, roles
        FROM users_d
        WHERE username != 'superadmin'
        """
        data = await async_execute(query)

        if not data:
            return JSONResponse(status_code=404, content={"message": "No users found"})

        users = [{"username": row[0], "email": row[1], "status": row[2], "roles": row[3]} for row in data]
        return JSONResponse(content=users)

    except Exception as e:
        return JSONResponse(status_code=500, content={"message": str(e)})

#

it basically list all users

dreamy pond Apr 8, 2024, 7:03 PM

#

you don't need asyncer.asyncify anywhere in your program basically.

#

just get rid of asyncer altogether

nocturne seal Apr 8, 2024, 7:03 PM

#

ah @oak bloom guide me really good but i forget why he said to use

#

yeah somekind of blocking IO?

dreamy pond Apr 8, 2024, 7:04 PM

#

he might've not known the context, it doesn't apply here because fastapi can already put things into a Threadpool if needed

nocturne seal Apr 8, 2024, 7:04 PM

#

can you please look this 1

#

1 second

#

!paste

lean wagonBOT Apr 8, 2024, 7:05 PM

#

Pasting large amounts of code

If your code is too long to fit in a codeblock in Discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the Paste! button in the bottom left, or by pressing CTRL + S. After doing that, you will be navigated to the new paste's page. Copy the URL and post it here so others can see it.

nocturne seal Apr 8, 2024, 7:05 PM

#

https://paste.pythondiscord.com/JL7A (updated)

can you please verify or check if its good like performacce wise

#

@oak bloom guide me each function and he told me to fix alot of things 🙂

oak bloom Apr 8, 2024, 7:08 PM

#

dreamy pond he might've not known the context, it doesn't apply here because fastapi can alr...

asyncer is made for FastAPI

#

It's used to mix blocking and async IO

dreamy pond Apr 8, 2024, 7:09 PM

#

most likely under the hood though

oak bloom Apr 8, 2024, 7:09 PM

#

Which is what's happening here, there's some code that accesses clickhouse and some code that accesses aioredis

nocturne seal Apr 8, 2024, 7:09 PM

#

yes

#

my all data access from clickhouse

#

and for next query in fetech same data from redis with in 1 minute

dreamy pond Apr 8, 2024, 7:09 PM

#

oak bloom Which is what's happening here, there's some code that accesses clickhouse and s...

yeah fair enough, for that one thing, it should be good, but for normal def functions, it's a bit redundant

nocturne seal Apr 8, 2024, 7:11 PM

#

@asyncer.asyncify
@app.get("/mitre_alerts")
async def mitre_alerts(duration: int = Query(...), unit: str = Query(...), token: str = Depends(oauth2_scheme), current_user: dict = Depends(get_current_user)):
    unit_mapping = {'minute': 60, 'hour': 3600, 'day': 86400, 'month': 2592000, 'year': 31536000}
    if unit not in unit_mapping:
        raise HTTPException(status_code=400, detail="Invalid unit specified. Valid units are 'minute', 'hour', 'day', 'month', 'year'.")

    try:
        # Calculate the duration in seconds based on the selected unit
        duration_seconds = duration * unit_mapping[unit]

        query = """
        SELECT 
            alert.metadata.mitre_technique_id AS mitre_technique_id,
            COUNT(*) AS score
        FROM 
            suricata_d2
        WHERE 
            arrayExists(x -> x != '', alert.metadata.mitre_tactic_id) 
            AND arrayExists(x -> x != '', alert.metadata.mitre_tactic_name)
            AND parseDateTimeBestEffort("timestamp") >= toUnixTimestamp(toDateTime(now()) - INTERVAL %(duration_seconds)s second)
        GROUP BY 
            alert.metadata.mitre_technique_id
        ORDER BY 
            mitre_technique_id ASC
        """

        # Execute ClickHouse query with parameters
        data = await async_execute(query, params={"duration_seconds": duration_seconds})

        if not data:
            # If no data is returned, return a custom message
            return {"message": "No TTP's found in the given time range."}

        # Process the ClickHouse data to get the list of techniques
        techniques = [{"techniqueID": row[0][0], "score": row[1]} for row in data]
        return techniques
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

#

can you please tell me where to use redis function in this

#

async def get_data_from_cache(query):
    cached_data = await redis_pool.get(query)
    if cached_data:
        cached_data = json.loads(cached_data)
        cache_timestamp = cached_data.get('timestamp')
        current_timestamp = time.time()
        if (current_timestamp - cache_timestamp) < CACHE_EXPIRATION_TIME:
            return cached_data.get('data')
    return None

async def cache_data(query, result):
    cache_entry = {
        'timestamp': time.time(),
        'data': result
    }
    await redis_pool.setex(query, CACHE_EXPIRATION_TIME, json.dumps(cache_entry))

this is my redis function

#

@asyncer.asyncify
@app.get("/mitre_alerts")
async def mitre_alerts(duration: int = Query(...), unit: str = Query(...), token: str = Depends(oauth2_scheme), current_user: dict = Depends(get_current_user)):
    unit_mapping = {'minute': 60, 'hour': 3600, 'day': 86400, 'month': 2592000, 'year': 31536000}
    if unit not in unit_mapping:
        raise HTTPException(status_code=400, detail="Invalid unit specified. Valid units are 'minute', 'hour', 'day', 'month', 'year'.")

    try:
        # Calculate the duration in seconds based on the selected unit
        duration_seconds = duration * unit_mapping[unit]

        # Construct a cache key based on the endpoint and query parameters
        cache_key = f"mitre_alerts:{duration}:{unit}"

        # Check if data is available in cache
        cached_data = await get_data_from_cache(cache_key)
        if cached_data:
            return cached_data

        query = """
        SELECT 
            alert.metadata.mitre_technique_id AS mitre_technique_id,
            COUNT(*) AS score
        FROM 
            suricata_d2
        WHERE 
            arrayExists(x -> x != '', alert.metadata.mitre_tactic_id) 
            AND arrayExists(x -> x != '', alert.metadata.mitre_tactic_name)
            AND parseDateTimeBestEffort("timestamp") >= toUnixTimestamp(toDateTime(now()) - INTERVAL %(duration_seconds)s second)
        GROUP BY 
            alert.metadata.mitre_technique_id
        ORDER BY 
            mitre_technique_id ASC
        """```

like this @dreamy pond @oak bloom

#

🙂

oak bloom Apr 8, 2024, 7:32 PM

#

You still have that redundant asyncify

#

Decorators on top of @app... don't do anything

nocturne seal Apr 8, 2024, 7:33 PM

#

dont do anything mean i remove that ?

#

async def mitre_alerts(duration: int = Query(...), unit: str = Query(...), token: str = Depends(oauth2_scheme), current_user: dict = Depends(get_current_user)):
    unit_mapping = {'minute': 60, 'hour': 3600, 'day': 86400, 'month': 2592000, 'year': 31536000}
    if unit not in unit_mapping:
        raise HTTPException(status_code=400, detail="Invalid unit specified. Valid units are 'minute', 'hour', 'day', 'month', 'year'.")

    try:
        # Calculate the duration in seconds based on the selected unit
        duration_seconds = duration * unit_mapping[unit]

        query = """
        SELECT 
            alert.metadata.mitre_technique_id AS mitre_technique_id,
            COUNT(*) AS score
        FROM 
            suricata_d2
        WHERE 
            arrayExists(x -> x != '', alert.metadata.mitre_tactic_id) 
            AND arrayExists(x -> x != '', alert.metadata.mitre_tactic_name)
            AND parseDateTimeBestEffort("timestamp") >= toUnixTimestamp(toDateTime(now()) - INTERVAL %(duration_seconds)s second)
        GROUP BY 
            alert.metadata.mitre_technique_id
        ORDER BY 
            mitre_technique_id ASC
        """

        # Execute ClickHouse query with parameters
        data = await async_execute(query, params={"duration_seconds": duration_seconds})
``` now ?

nocturne seal Apr 8, 2024, 8:00 PM

#

lean wagonBOT Apr 8, 2024, 9:00 PM

#

Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.

#🔒 hi i need some help regarding fastapi