[sqlalchemy] Re: sqlalchemy messes up names with "_1" suffix

['Jonathan Vanasco' via sqlalchemy]

> i have this litte flask-admin game running, now out of nowwhere 
sqlalchemy has begun to add strange "_1" suffixes to the column names. i 
know sqlalchemy does this to keep names unique, but in my case the queries 
are failing

SQLAlchemy does do this, for those reasons, and to the columns... but note 
those exact error:


sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown 
column 'attribs_1.ts' in 'field list'")


It's not finding the `.ts` on the `attribs` table, which was mapped to 
`attribs_1` in the query.

I think the best thing do to is what mike said - create a complete 
executable example you can share. the model + the query.  My first guess is 
that you have a typo on the column/table name in the model or query.  There 
could also be an inheritance issue because of a typo too.


 

-- 
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/c53dd18c-bc8a-42bd-819c-0b111e1a71a2o%40googlegroups.com.

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

[Mike Bayer]

hey there -

what you're doing is suited by an included feature of SQLAlchemy called "single 
table inheritance", which will return to you instances of "Mammal" or 
AnimalModel object based on the value of "type".

see: 
https://docs.sqlalchemy.org/en/13/orm/inheritance.html#single-table-inheritance



On Thu, Jul 9, 2020, at 6:53 PM, Raghav wrote:
> 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
>  
> .

-- 
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/2b829383-9330-4b41-a6c5-761d71117dbe%40www.fastmail.com.

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

[Mike Bayer]

the AES library has been updated a bit so I updated the recipes at:

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

regardless of what encrpytion you are using, for a WHERE clause to work, you 
need to ensure the encrypt() function produces the same string each time. I'm 
using a fixed "nonce" now to ensure that works for the current version of AES. 
you should be able to run the function repeatedly and get the same encrypted 
value each time:

(Pdb) aes_encrypt("some value")
b'7884f37e601994409b34618ca6a41606'
(Pdb) aes_encrypt("some value")
b'7884f37e601994409b34618ca6a41606'
(Pdb) aes_encrypt("some value")
b'7884f37e601994409b34618ca6a41606'

On Fri, Jul 10, 2020, at 11:16 AM, Mike Bayer wrote:
> The recipe encrypts the value in the WHERE clause, however it seems like the 
> usage of the libraries in the example is a bit out of date for Python 3. What 
> Python version are you using ?
> 
> 
> 
> On Thu, Jul 9, 2020, at 1:15 PM, 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 

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

[Mike Bayer]

The recipe encrypts the value in the WHERE clause, however it seems like the 
usage of the libraries in the example is a bit out of date for Python 3. What 
Python version are you using ?



On Thu, Jul 9, 2020, at 1:15 PM, 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 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 

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

[Simon King]

Not in the traditional sense, no. ORDER BY is implemented by the
database, and with client-side encryption, the database only ever sees
encrypted strings.

Simon

On Fri, Jul 10, 2020 at 8:41 AM Justin Van Vuuren  wrote:
>
> Also, regarding the client side approach, would one be able to do an order by 
> query?
>
> On Thu, 9 Jul 2020 at 21:28, Justvuur  wrote:
>>
>> 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.
>
> --
> 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/CAJK07SZwotU64v22pmRJn4SR6aV2cb%2B6U_tKMwJxgG9Pe0cUQA%40mail.gmail.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/CAFHwexdJquCGHv0B5aAOUgkFBXijxtEM48Fo0YoSFrSOsNo4Zw%40mail.gmail.com.

[sqlalchemy] Re: sqlalchemy messes up names with "_1" suffix

[Ben]

Not sure if this will help but are you using FlaskWTF?  If you have 
repeating fields on a form, some of its data structures will append a _1, 
_2... to each instance in your response to keep them unique. So, just a 
guess, but perhaps your problem is related to Flask / WTForms?
On Friday, July 10, 2020 at 8:50:13 AM UTC-4 christia...@itsv.at wrote:

> hi,
>
> i have this litte flask-admin game running, now out of nowwhere sqlalchemy 
> has begun to add strange "_1" suffixes to the column names. i know 
> sqlalchemy does this to keep names unique, but in my case the queries are 
> failing
> and my naming is unique.
>
>
> my models:
>
> ### DB models
>
> # Base model that for other models to inherit from
> class Base(db.Model):
> __abstract__ = True
>
> id = db.Column(db.Integer, primary_key=True, autoincrement=True)
> ts = db.Column(db.TIMESTAMP, default=db.func.current_timestamp(),
>   onupdate=db.func.current_timestamp())
>
> def __str__(self):
> attrs = db.class_mapper(self.__class__).attrs # show also 
> relationships
> if 'name' in attrs:
> return self.name
> elif 'parent' in attrs:
> return self.parent
> elif 'value' in attrs:
> return self.value
> else:
> return "<%s(%s)>" % (self.__class__.__name__,
>  ', '.join('%s=%r' % (k.key, getattr(self, 
> k.key))
>for k in sorted(attrs)
>   )
> )
>
> class Attrib(Base):
> __tablename__ = 'attribs'
> name = Column(String(256, u'utf8_unicode_ci'), nullable=False)
> persistent = Column(Integer, server_default=FetchedValue())
> parent = Column(String(256, u'utf8_unicode_ci'), server_default=
> FetchedValue())
>
> class Entry(Base):
> __tablename__ = 'entries'
> node_id = Column(ForeignKey(u'nodes.id', ondelete=u'CASCADE', onupdate
> =u'CASCADE'), nullable=False, index=True)
> attrib_id = Column(ForeignKey(u'attribs.id', ondelete=u'CASCADE', 
> onupdate=u'CASCADE'), nullable=False, index=True)
> value = Column(String(256, u'utf8_unicode_ci'), nullable=False)
> attrib = relationship(u'Attrib', primaryjoin='Entry.attrib_id == 
> Attrib.id', backref=u'entries')
> node = relationship(u'Node', primaryjoin='Entry.node_id == Node.id', 
> backref=u'entries')
>
> class Node(Base):
> __tablename__ = 'nodes'
> name = Column(String(256, u'utf8_unicode_ci'), nullable=False)
>
>
> error:
>
> sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown 
> column 'attribs_1.ts' in 'field list'")
> [SQL: SELECT entries.id AS entries_id, entries.ts AS entries_ts, 
> entries.node_id 
> AS entries_node_id, entries.attrib_id AS entries_attrib_id, entries.value 
> AS entries_value, attribs_1.id AS attribs_1_id, attribs_1.ts AS 
> attribs_1_ts, attribs_1.name AS attribs_1_name, attribs_1.persistent AS 
> attribs_1_persistent, attribs_1.parent AS attribs_1_parent, nodes_1.id AS 
> nodes_1_id, nodes_1.ts AS nodes_1_ts, nodes_1.name AS nodes_1_name
> FROM entries LEFT OUTER JOIN attribs AS attribs_1 ON entries.attrib_id = 
> attribs_1.id LEFT OUTER JOIN nodes AS nodes_1 ON entries.node_id = nodes_1
> .id
>  LIMIT %(param_1)s]
> [parameters: {'param_1': 20}]
>
> any idea whats going on here?
>

-- 
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/4b7dbdc1-8219-4e93-a928-7c0ae0efdeben%40googlegroups.com.

Re: [sqlalchemy] sqlalchemy messes up names with "_1" suffix

[Mike Bayer]

On Fri, Jul 10, 2020, at 8:50 AM, Xander Cage wrote:
> hi,
> 
> i have this litte flask-admin game running, now out of nowwhere sqlalchemy 
> has begun to add strange "_1" suffixes to the column names. i know sqlalchemy 
> does this to keep names unique, but in my case the queries are failing
> and my naming is unique.
> 
> 
> my models:
> 
> ### DB models
> 
> # Base model that for other models to inherit from
> class Base(db.Model):
>  __abstract__ = True
> 
>  id = db.Column(db.Integer, primary_key=True, autoincrement=True)
>  ts = db.Column(db.TIMESTAMP, default=db.func.current_timestamp(),
>  onupdate=db.func.current_timestamp())
> 
> def __str__(self):
>  attrs = db.class_mapper(self.__class__).attrs # show also relationships
> if 'name' in attrs:
> return self.name
> elif 'parent' in attrs:
> return self.parent
> elif 'value' in attrs:
> return self.value
> else:
> return "<%s(%s)>" % (self.__class__.__name__,
> ', '.join('%s=%r' % (k.key, getattr(self, k.key))
> for k in sorted(attrs)
> )
> )
> 
> class Attrib(Base):
>  __tablename__ = 'attribs'
>  name = Column(String(256, u'utf8_unicode_ci'), nullable=False)
>  persistent = Column(Integer, server_default=FetchedValue())
>  parent = Column(String(256, u'utf8_unicode_ci'), 
> server_default=FetchedValue())
> 
> class Entry(Base):
>  __tablename__ = 'entries'
>  node_id = Column(ForeignKey(u'nodes.id', ondelete=u'CASCADE', 
> onupdate=u'CASCADE'), nullable=False, index=True)
>  attrib_id = Column(ForeignKey(u'attribs.id', ondelete=u'CASCADE', 
> onupdate=u'CASCADE'), nullable=False, index=True)
>  value = Column(String(256, u'utf8_unicode_ci'), nullable=False)
>  attrib = relationship(u'Attrib', primaryjoin='Entry.attrib_id == Attrib.id', 
> backref=u'entries')
>  node = relationship(u'Node', primaryjoin='Entry.node_id == Node.id', 
> backref=u'entries')
> 
> class Node(Base):
>  __tablename__ = 'nodes'
>  name = Column(String(256, u'utf8_unicode_ci'), nullable=False)
> 
> 
> error:
> 
> sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown 
> column 'attribs_1.ts' in 'field list'")
> [SQL: SELECT entries.id AS entries_id, entries.ts AS entries_ts, 
> entries.node_id AS entries_node_id, entries.attrib_id AS entries_attrib_id, 
> entries.value AS entries_value, attribs_1.id AS attribs_1_id, attribs_1.ts AS 
> attribs_1_ts, attribs_1.name AS attribs_1_name, attribs_1.persistent AS 
> attribs_1_persistent, attribs_1.parent AS attribs_1_parent, nodes_1.id AS 
> nodes_1_id, nodes_1.ts AS nodes_1_ts, nodes_1.name AS nodes_1_name
> FROM entries LEFT OUTER JOIN attribs AS attribs_1 ON entries.attrib_id = 
> attribs_1.id LEFT OUTER JOIN nodes AS nodes_1 ON entries.node_id = nodes_1.id
>  LIMIT %(param_1)s]
> [parameters: {'param_1': 20}]
> 
> any idea whats going on here?


Hi there-

this depends on how you are emitting this query. Can you supply a complete 
minimal reproducing example? most notably, what code you are running which 
produces this SQL statement.






> 

> --
>  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/0983ac9b-0ecd-416e-bf6c-1ee5e73c95aeo%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/db9896c2-e1b5-4cd2-a60f-be83c9598311%40www.fastmail.com.

[sqlalchemy] sqlalchemy messes up names with "_1" suffix

[Xander Cage]

hi,

i have this litte flask-admin game running, now out of nowwhere sqlalchemy 
has begun to add strange "_1" suffixes to the column names. i know 
sqlalchemy does this to keep names unique, but in my case the queries are 
failing
and my naming is unique.


my models:

### DB models

# Base model that for other models to inherit from
class Base(db.Model):
__abstract__ = True

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
ts = db.Column(db.TIMESTAMP, default=db.func.current_timestamp(),
  onupdate=db.func.current_timestamp())

def __str__(self):
attrs = db.class_mapper(self.__class__).attrs # show also 
relationships
if 'name' in attrs:
return self.name
elif 'parent' in attrs:
return self.parent
elif 'value' in attrs:
return self.value
else:
return "<%s(%s)>" % (self.__class__.__name__,
 ', '.join('%s=%r' % (k.key, getattr(self, k
.key))
   for k in sorted(attrs)
  )
)

class Attrib(Base):
__tablename__ = 'attribs'
name = Column(String(256, u'utf8_unicode_ci'), nullable=False)
persistent = Column(Integer, server_default=FetchedValue())
parent = Column(String(256, u'utf8_unicode_ci'), server_default=
FetchedValue())

class Entry(Base):
__tablename__ = 'entries'
node_id = Column(ForeignKey(u'nodes.id', ondelete=u'CASCADE', onupdate=u
'CASCADE'), nullable=False, index=True)
attrib_id = Column(ForeignKey(u'attribs.id', ondelete=u'CASCADE', 
onupdate=u'CASCADE'), nullable=False, index=True)
value = Column(String(256, u'utf8_unicode_ci'), nullable=False)
attrib = relationship(u'Attrib', primaryjoin='Entry.attrib_id == 
Attrib.id', backref=u'entries')
node = relationship(u'Node', primaryjoin='Entry.node_id == Node.id', 
backref=u'entries')

class Node(Base):
__tablename__ = 'nodes'
name = Column(String(256, u'utf8_unicode_ci'), nullable=False)


error:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown 
column 'attribs_1.ts' in 'field list'")
[SQL: SELECT entries.id AS entries_id, entries.ts AS entries_ts, 
entries.node_id 
AS entries_node_id, entries.attrib_id AS entries_attrib_id, entries.value 
AS entries_value, attribs_1.id AS attribs_1_id, attribs_1.ts AS attribs_1_ts
, attribs_1.name AS attribs_1_name, attribs_1.persistent AS 
attribs_1_persistent, attribs_1.parent AS attribs_1_parent, nodes_1.id AS 
nodes_1_id, nodes_1.ts AS nodes_1_ts, nodes_1.name AS nodes_1_name
FROM entries LEFT OUTER JOIN attribs AS attribs_1 ON entries.attrib_id = 
attribs_1.id LEFT OUTER JOIN nodes AS nodes_1 ON entries.node_id = nodes_1.
id
 LIMIT %(param_1)s]
[parameters: {'param_1': 20}]

any idea whats going on here?

-- 
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/0983ac9b-0ecd-416e-bf6c-1ee5e73c95aeo%40googlegroups.com.

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

[Justin Van Vuuren]

Also, regarding the client side approach, would one be able to do an order
by query?

On Thu, 9 Jul 2020 at 21:28, Justvuur  wrote:

> 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
> 
> .
>

-- 
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/CAJK07SZwotU64v22pmRJn4SR6aV2cb%2B6U_tKMwJxgG9Pe0cUQA%40mail.gmail.com.