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('hello@gmail.com', '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('hello@gmail.com', 'secret_key'), 'gender': 'male'})
It didn't work. I also tried
query = f"""
insert into customers (email, gender) values
(pgm_sys_encrypt('hello@gmail.com', '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.
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': 'hello@gmail.com', 'secret_key': 's3cr37', 'gender': 'male'})