CODEWITHSUNDEEP
python-3.xpostgresqlormsqlalchemy
Cathy
Cathy

Reputation: 377

encrypt data in PostgreSQL using ORM

I want to encrypt the data in the PostgreSQL. I am using the below two methods to insert the data, one using ORM, other without ORM

db = sql.create_engine(connection_string)
metadata = sql.schema.MetaData(bind=db, reflect=True)
inputStringtable = sql.Table('person_info', metadata, autoload=True)

######Using ORM########
class RowInputString(object):
    pass
orm.Mapper(RowInputString, inputStringtable)
Sess = orm.sessionmaker(bind=db)
session = Sess()

inputTable = RowInputString()
inputTable.person_id = personId
inputTable.person_name = personName
session.add(inputTable)
session.commit()
################################

######not using ORM
def inserting_data(personId, personName):
    insertData = inputStringtable.insert().values(person_id=personId, person_name=personName)
    conn = db.connect()
    conn.execute(ins)
inserting_data(personId, personName)

I came across the below snippet to the encrypt and send it to database:

INSERT INTO users(login, passwd)
VALUES('my_login', crypt('my_password', gen_salt('md5')));

I find it little difficult how I can use this snippet in my code?

Upvotes: 0

Views: 1703

Answers (1)

jspcal
jspcal

Reputation: 51904

For general encryption, you can use the EncryptedType SQLAlchemy type.

For password hashing you can define a custom type in SQLAlchemy:

https://github.com/sqlalchemy/sqlalchemy/wiki/DatabaseCrypt

This uses bind_expression of the TypeDecorator API to map the passed-in column value to an expression involving built-in database functions (gen_salt and crypt).

Upvotes: 1

Related Questions