Raw Query with SQL function in SQLAlchemy/encode/databases

Question

I'm a complete beginner at Python and FastAPI. I'm using FastAPI and I have a table where the requirement is to encrypt the personal information of the user using pgcrypto module of PostgreSQL. The raw query would be something like this which can be executed into any database client and it executes without any error

insert into customers (email, gender) values (pgm_sym_encrypt('[email protected]', 'some_secret_key'), 'male')

How to execute this query using SQLAlchemy core or encode/databases? I've tried this

from sqlalchemy import func
query = f"""
    insert into customers (email, gender) values 
    (:email, :gender)
    """
await database.execute(query=query, values={'email': func.pgm_sys_encrypt('[email protected]', 'secret_key'), 'gender': 'male'})

It didn't work. I also tried

query = f"""
    insert into customers (email, gender) values 
    (pgm_sys_encrypt('[email protected]', 'secret_key'), :gender)
    """
await database.execute(query=query, values={'gender': 'male'})

This didn't work either. I've no idea how to execute a function in the raw query. Please help. I've tried so much but I'm totally clueless on this one still now. Thank you in advance for your help.

snakecharmerb · Accepted Answer · 2022-01-13 09:12:46Z

2

As it's a raw query you should be able specify it as you would in raw SQL, so this should work:

from sqlalchemy.sql import text
query = """
    insert into customers (email, gender) values 
    (pgm_sys_encrypt(:email, :secret_key), :gender)
    """
await database.execute(query=text(query), values={'email': '[email protected]', 'secret_key': 's3cr37', 'gender': 'male'})

answered Jan 13, 2022 at 9:12

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Raw Query with SQL function in SQLAlchemy/encode/databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related