date:20200709

[sqlalchemy] Remap an SQLAlchemy instance to a different non-SQLAlchemy class

2020-07-09 Thread Raghav

Hello everyone, hope you're all doing well.

I have a table which has a type column. Based on that value I need to 
instantiate a new class. I'm looking for a way to operate my SQLAlchemy 
object through this new class.

Here's my model class: 
```
class AnimalModel(Base):
__tablename__ = "animal"
id = Column(Integer, primary_key=True)
type = Column(String)
name = Column(String)
...
```

Here is my python class.
```
class Mammal(BaseAnimal):
def __init__(self, animal_model):
self.__dict__.update(animal_model.__dict__)

def has_fur(self):
return True
```

Once I query the animal table and receive the `AnimalModel` object. I 
create a new `BaseAnimal` class based on the type.

I need a way to merge or remap the `AnimalModel` object to the new 
`BaseAnimal` object so that I can get my methods from `BaseAnimal` and I 
also get to do table updates.

```
mammal = new Mammal(animal_model)
mammal.has_fur()
mammal.name = 'Whale'  # SQL row update
```

What are some ways in which this can be achieved?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d429d258-27c2-4ebc-b469-681fedb2ce17n%40googlegroups.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

2020-07-09 Thread Justvuur

A, ok ye, I understand, then I prefer client side.
I just need to sort out the encryption then... I'm not too familiar with 
encryption algorithms but at the moment its using a text key from a config 
file and the mode is CBC which I guess is what is generating a different 
value each time right?
In Mike's example he's using ECB mode which is the default.
What would be the best mode to use for my scenario? A good balance between 
secure and performance that works for client side?

On Thursday, 9 July 2020 20:18:52 UTC+2, Jonathan Vanasco wrote:
>
>
>
> On Thursday, July 9, 2020 at 2:12:36 PM UTC-4, Justvuur wrote:
>>
>> I've done some more digging... It seems when I did the search for 
>> "secrets", the text is encrypted and compared to the value in the columns, 
>>
>
> That is how client-side encryption works.  If you want to search for 
> "secrets", you need to use server-side encryption (which depends on the 
> database). In those systems, the server will decrypt the column in every 
> row when searching - which can be a performance issue.
>
> The thing is this type of comparison wont work, the algorithm generates a 
>> different string each encryption for the same string.
>>
>
>  What are you using for your encryption key? The key should be persistent, 
> and should always generate the same output for a given input.  In the 
> example from Michael Bayer, a random uuid is used as a placeholder.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/58a78976-3ebe-428b-824f-165b15f61cb4o%40googlegroups.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

2020-07-09 Thread 'Jonathan Vanasco' via sqlalchemy

On Thursday, July 9, 2020 at 2:12:36 PM UTC-4, Justvuur wrote:
>
> I've done some more digging... It seems when I did the search for 
> "secrets", the text is encrypted and compared to the value in the columns, 
>

That is how client-side encryption works.  If you want to search for 
"secrets", you need to use server-side encryption (which depends on the 
database). In those systems, the server will decrypt the column in every 
row when searching - which can be a performance issue.

The thing is this type of comparison wont work, the algorithm generates a 
> different string each encryption for the same string.
>

 What are you using for your encryption key? The key should be persistent, 
and should always generate the same output for a given input.  In the 
example from Michael Bayer, a random uuid is used as a placeholder.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2506c7ef-7a66-4662-a40b-db6e70b93347o%40googlegroups.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

2020-07-09 Thread Justvuur

I've done some more digging... It seems when I did the search for 
"secrets", the text is encrypted and compared to the value in the columns, 
see below:
SELECT 
student_form_fields.field_value AS student_form_fields_field_value, 
student_form_fields.student_id  AS student_form_fields_student_id, 
student_form_fields.field_id AS student_form_fields_field_id 
FROM 
student_form_fields 
WHERE 
student_form_fields.field_value = %(field_value_1)s 
LIMIT %(param_1)s
{'param_1': 1, 'field_value_1': 
'G1/jmJvsZLCOHcWRGUetNyF1388aJ4E9ocn3FBwuIOk='}

The thing is this type of comparison wont work, the algorithm generates a 
different string each encryption for the same string.
Should it not decrypt the value(s) in the column and compare it to the 
provided text/query param which in this case is secrets and not 
"G1/jmJvsZLCOHcWRGUetNyF1388aJ4E9ocn3FBwuIOk="?

On Thursday, 9 July 2020 19:15:32 UTC+2, Justvuur wrote:
>
> So there is good news and bad news hehe.
> Good news is if I query all the other columns (no encrypted) columns it 
> works. All results are returned correctly and decrypted.
> Bad news, if I query the encrypted column, nothing is "found".
>
> This is my model:
> class StudentFormField(db.Model):
> __tablename__ = 'student_form_fields'
>
> student_id = db.Column(Integer, ForeignKey('students.id'), 
> primary_key=True)
>
> field_id = db.Column(String(100), primary_key=True)
>
> decrypted_field_value = db.Column("field_value", EncryptedValue(), 
> nullable=False)
>
> #field_value = db.Column(db.String)
>
> def __init__(self, student_id, field_id, field_value):
> self.student_id = student_id
> self.field_id = field_id
> self.decrypted_field_value = field_value
>
> Query Example:
> StudentFormField.query.filter_by(decrypted_field_value = 'secrets').first()
>
> Returns None even though I have an encrypted value of "secrets" in that 
> column.
> Any idea what I could be doing wrong?
>
> On Thursday, 9 July 2020 16:37:17 UTC+2, Mike Bayer wrote:
>>
>>
>>
>> On Thu, Jul 9, 2020, at 4:53 AM, Justvuur wrote:
>>
>> Hi Mike,
>>
>> This is awesome! Yes, I like the client side approach, especially the "Use 
>> TypeDecorator" method.
>> Is there a specific version of SQLAlchemy I need?
>>
>>
>> TypeDecorator is pretty universal so it should work for any modern 
>> version in the past five years at least
>>
>>
>>
>> So with this, I would be able to query/filter an encrypted column and it 
>> will automatically perform the decryption to test the column?
>>
>>
>> should work, sure, try it out
>>
>>
>>
>>
>>
>> On Wednesday, 8 July 2020 at 16:10:22 UTC+2 Mike Bayer wrote:
>>
>>
>> I had just created all-new revised encryption examples on the wiki and 
>> apparently I forgot to link them from the index, fixed.
>>
>> Here's two examples showing the general idea of how this can be done:
>>
>>
>> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionClientSide
>>
>>
>> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionServerSide
>>
>> the "client side" version is more general purpose and you can use any 
>> encryption system you'd like.
>>
>>
>>
>> On Wed, Jul 8, 2020, at 6:55 AM, Justvuur wrote:
>>
>> Hi All,
>>
>> In one or two database tables I need to encrypt/decrypt a couple of 
>> columns. These tables/columns already exist in the DB and they already have 
>> data in them.
>>
>> What is the best way using SQL Alchemy to add encryption to these 
>> tables/columns?
>> It needs to be efficient and the columns need to be able to work with 
>> querying/filtering.
>>
>> Regards,
>> Justin
>>
>>
>>
>> --
>> SQLAlchemy - 
>> The Python SQL Toolkit and Object Relational Mapper
>>  
>> http://www.sqlalchemy.org/
>>  
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> --- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlalchemy+...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/583d5772-50e4-484a-93e2-fcab29532116n%40googlegroups.com
>>  
>> 
>> .
>>
>>
>>
>> --
>> SQLAlchemy - 
>> The Python SQL Toolkit and Object Relational Mapper
>>  
>> http://www.sqlalchemy.org/
>>  
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> --- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlal...@googlegroups.com.
>> To view this discussion on

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

2020-07-09 Thread Justvuur

So there is good news and bad news hehe.
Good news is if I query all the other columns (no encrypted) columns it 
works. All results are returned correctly and decrypted.
Bad news, if I query the encrypted column, nothing is "found".

This is my model:
class StudentFormField(db.Model):
__tablename__ = 'student_form_fields'

student_id = db.Column(Integer, ForeignKey('students.id'), 
primary_key=True)

field_id = db.Column(String(100), primary_key=True)

decrypted_field_value = db.Column("field_value", EncryptedValue(), 
nullable=False)

#field_value = db.Column(db.String)

def __init__(self, student_id, field_id, field_value):
self.student_id = student_id
self.field_id = field_id
self.decrypted_field_value = field_value

Query Example:
StudentFormField.query.filter_by(decrypted_field_value = 'secrets').first()

Returns None even though I have an encrypted value of "secrets" in that 
column.
Any idea what I could be doing wrong?

On Thursday, 9 July 2020 16:37:17 UTC+2, Mike Bayer wrote:
>
>
>
> On Thu, Jul 9, 2020, at 4:53 AM, Justvuur wrote:
>
> Hi Mike,
>
> This is awesome! Yes, I like the client side approach, especially the "Use 
> TypeDecorator" method.
> Is there a specific version of SQLAlchemy I need?
>
>
> TypeDecorator is pretty universal so it should work for any modern version 
> in the past five years at least
>
>
>
> So with this, I would be able to query/filter an encrypted column and it 
> will automatically perform the decryption to test the column?
>
>
> should work, sure, try it out
>
>
>
>
>
> On Wednesday, 8 July 2020 at 16:10:22 UTC+2 Mike Bayer wrote:
>
>
> I had just created all-new revised encryption examples on the wiki and 
> apparently I forgot to link them from the index, fixed.
>
> Here's two examples showing the general idea of how this can be done:
>
> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionClientSide
>
> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionServerSide
>
> the "client side" version is more general purpose and you can use any 
> encryption system you'd like.
>
>
>
> On Wed, Jul 8, 2020, at 6:55 AM, Justvuur wrote:
>
> Hi All,
>
> In one or two database tables I need to encrypt/decrypt a couple of 
> columns. These tables/columns already exist in the DB and they already have 
> data in them.
>
> What is the best way using SQL Alchemy to add encryption to these 
> tables/columns?
> It needs to be efficient and the columns need to be able to work with 
> querying/filtering.
>
> Regards,
> Justin
>
>
>
> --
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/583d5772-50e4-484a-93e2-fcab29532116n%40googlegroups.com
>  
> 
> .
>
>
>
> --
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/1164ebf3-4ee3-4434-a21c-fe12ce57f556n%40googlegroups.com
>  
> 
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0cb3753d-8f5e-41af-8770-ec15dd538fa2o%40googlegroups.com.

Re: [sqlalchemy] postgresql geometry types

2020-07-09 Thread Mike Bayer

Hi -

I think you want to be using geoalchemy2:

https://geoalchemy-2.readthedocs.io/en/latest/



On Wed, Jul 8, 2020, at 3:37 PM, Роберт Шотланд wrote:
> We are beginning to use the PostgreSQL (12) geometric types (initially the 
> 'point' datatype) in our SA model, and was disappointed to find that the 
> sqlalchemy postgresql dialect modules appear not to have these. 
> 
> So I wrote a function using schema.CreateColumn to generate the postgresql 
> DDL according to instructions in the info={} dictionary
> 
> *@compiles(schema.CreateColumn, 'postgresql')*
> *def compile_cc(element, compiler, **kw):*
> * """*
> * Replace the type specified in the Column specification with the one 
> supplied in info={'type': 'OTHER TYPE'}*
> * :param element: # information about the Column specification*
> * :param compiler: # dialect*
> * :param kw: # keywords*
> * :return: # replacement string*
> * """*
> * col = element.element*
> **
> * if "type" not in col.info:*
> * return compiler.visit_create_column(element, **kw) # if no 'type' key in 
> col.info, return unchanged*
> * else:*
> * return compiler.visit_create_column(element, **kw).replace(str(col.type), 
> col.info['type']) # return new type*
> 
> In my model the column is defined as below:
> 
> *point_ra_dec = Column(VARCHAR, Computed('point(ra, "dec")'), info={'type': 
> 'point'})*
> 
> However, alembic appears to be unaware of this, and generates 
> 
> *ALTER TABLE ephemeris ADD COLUMN point_ra_dec VARCHAR GENERATED ALWAYS AS 
> (point(ra, "dec")) STORED;*
> **
> What I want, of course is:
> 
> *ALTER TABLE ephemeris ADD COLUMN point_ra_dec **point** GENERATED ALWAYS AS 
> (point(ra, "dec")) STORED;*
> **
> What furtther do I need to do to get this to work?
> 
> Oh, and are the postgreSQL geometric types planned for the 1.4 SA release?
> 
> Rob
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/0a8021ac-e373-4875-a82a-ec065240615fo%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0e5b6282-ad3d-42a0-940b-c358814f5629%40www.fastmail.com.

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

2020-07-09 Thread Mike Bayer



On Thu, Jul 9, 2020, at 4:53 AM, Justvuur wrote:
> Hi Mike,
> 
> This is awesome! Yes, I like the client side approach, especially the "Use 
> TypeDecorator" method.
> Is there a specific version of SQLAlchemy I need?

TypeDecorator is pretty universal so it should work for any modern version in 
the past five years at least



> So with this, I would be able to query/filter an encrypted column and it will 
> automatically perform the decryption to test the column?

should work, sure, try it out



> 
> 
> On Wednesday, 8 July 2020 at 16:10:22 UTC+2 Mike Bayer wrote:
>> __
>> I had just created all-new revised encryption examples on the wiki and 
>> apparently I forgot to link them from the index, fixed.
>> 
>> Here's two examples showing the general idea of how this can be done:
>> 
>> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionClientSide
>> 
>> https://github.com/sqlalchemy/sqlalchemy/wiki/SymmetricEncryptionServerSide
>> 
>> the "client side" version is more general purpose and you can use any 
>> encryption system you'd like.
>> 
>> 
>> 
>> On Wed, Jul 8, 2020, at 6:55 AM, Justvuur wrote:
>>> Hi All,
>>> 
>>> In one or two database tables I need to encrypt/decrypt a couple of 
>>> columns. These tables/columns already exist in the DB and they already have 
>>> data in them.
>>> 
>>> What is the best way using SQL Alchemy to add encryption to these 
>>> tables/columns?
>>> It needs to be efficient and the columns need to be able to work with 
>>> querying/filtering.
>>> 
>>> Regards,
>>> Justin
>>> 
>>> 
>>> 

>>> --
>>> SQLAlchemy - 
>>> The Python SQL Toolkit and Object Relational Mapper
>>> 
>>> http://www.sqlalchemy.org/
>>> 
>>> To post example code, please provide an MCVE: Minimal, Complete, and 
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>>> description.
>>> --- 
>>> You received this message because you are subscribed to the Google Groups 
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an 
>>> email to sqlalchemy+...@googlegroups.com.
>>> To view this discussion on the web visit 
>>> https://groups.google.com/d/msgid/sqlalchemy/583d5772-50e4-484a-93e2-fcab29532116n%40googlegroups.com
>>>  
>>> .
>> 
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/1164ebf3-4ee3-4434-a21c-fe12ce57f556n%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/0b4b7fa0-0cfb-466c-be0f-8f48d6a56eef%40www.fastmail.com.

[sqlalchemy] Remap an SQLAlchemy instance to a different non-SQLAlchemy class

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

Re: [sqlalchemy] postgresql geometry types

Re: [sqlalchemy] Encrypt/Decrypt specific column(s)

7 matches

Site Navigation

Mail list logo

Footer information