[sqlalchemy] Re: Exists/Correlate newbie question
Michael, I removed the correlate and it still works. I would have sworn until I added the correlation I kept getting an error in the subquery. Thanks again! On Mar 28, 12:25 pm, Michael Bayer mike...@zzzcomputing.com wrote: right so, the correlate() can be against multi_obs directly, not just __table__, and also shouldn't be required at all since auto-correlation is sufficient here. On Mar 28, 2011, at 12:23 PM, dan wrote: Thanks for the feedback. I found an example in google groups that got me on the right track. For those interested, my sqlalchemy looks like: multi_obs2 = aliased(multi_obs) subQ = qaqc.db.session.query(multi_obs2).\ filter(multi_obs2.m_date = beginDate).\ filter(multi_obs2.m_date endDate).\ filter(multi_obs2.sensor_id == nnSensorId).\ filter(multi_obs2.d_top_of_hour == 1).\ filter(multi_obs2.d_report_hour == multi_obs.d_report_hour).\ correlate(multi_obs.__table__).\ statement recs = qaqc.db.session.query(multi_obs).\ filter(multi_obs.m_date = beginDate).\ filter(multi_obs.m_date endDate).\ filter(multi_obs.sensor_id == sensorId).\ filter(multi_obs.d_top_of_hour == 1).\ filter(exists(subQ)).\ order_by(multi_obs.m_date.asc()).all() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Query filter like with wildcards not working with custom type
Hello,
I'm using a custom type (TypeDecorator) to handle encrypting data on
write and decrypting data on read. Everything works fine for inserting
and reading from the database with one exception. Using the like
operator with '%' syntax is not returning any results.
For the purposes of this post, lets suppose the data manipulation is
simple base 64 encoding:
#code block:
class EncryptedString(TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
if not value:
return value
return base64.b64encode(value)
def process_result_value(self, value, dialect):
if not value:
return value
decrypted_sub_string = base64.b64decode(value)
#and a simple model:
Base = declarative.declarative_base()
class MyTable(Base):
__tablename__ = mytable
name = EncryptedString(128)
#and the query that does not work:
Session().query(MyTable).filter(MyTable.name.like('%foo%')
#end code block
Let's say there's a row in the table with name = 'foobar' (actually
its whatever 'foobar' encodes to) the above query will return nothing.
If I query for .like('foobar') then I will get the row returned as
expected.
Lastly, I'm on v0.5.1 (way behind I know!).
Any thoughts? Thanks so much,
Stephen
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Query filter like with wildcards not working with custom type
On Mar 29, 2011, at 4:12 AM, Stephen wrote:
Hello,
I'm using a custom type (TypeDecorator) to handle encrypting data on
write and decrypting data on read. Everything works fine for inserting
and reading from the database with one exception. Using the like
operator with '%' syntax is not returning any results.
For the purposes of this post, lets suppose the data manipulation is
simple base 64 encoding:
#code block:
class EncryptedString(TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
if not value:
return value
return base64.b64encode(value)
def process_result_value(self, value, dialect):
if not value:
return value
decrypted_sub_string = base64.b64decode(value)
#and a simple model:
Base = declarative.declarative_base()
class MyTable(Base):
__tablename__ = mytable
name = EncryptedString(128)
#and the query that does not work:
Session().query(MyTable).filter(MyTable.name.like('%foo%')
#end code block
Let's say there's a row in the table with name = 'foobar' (actually
its whatever 'foobar' encodes to) the above query will return nothing.
If I query for .like('foobar') then I will get the row returned as
expected.
Lastly, I'm on v0.5.1 (way behind I know!).
Any thoughts? Thanks so much,
It would appear that your encrypted type is also encrypting the % signs.It
depends highly on the style of encryption you are using what path to take here.
If the translation is one-to-one on a per character basis then you might want
to do a comparison column LIKE '%' + 'encrypted' + '%', which would involve
working around MyTable.name's forced coercion of the right hand side, probably
using col.like(literal('%', type_=String) + my_encryption('foo') + literal('%',
type_=String)). Or modifying your type to allow '%' signs through, perhaps if
they are escaped in some special way.
The more robust method is to allow the % operator to work naturally by
performing the decryption on the database side:
func.decrypt(MyTable.name).like('%foo%')
where decrypt would be a function or custom procedure on the DB side that
does the same decryption.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Query filter like with wildcards not working with custom type
I don't think I'm able to support '%' signs in the type because 'foo'
encrypted will not be a substring contained within 'foobar' encrypted.
Doing the translation by character would be too costly for introducing
load on our encryption service.
The actual encryption/decryption is handled by hitting a remote
RESTful service so I'm not sure I could do that on the DB side as you
mention. This functionality isn't mission critical so I may just have
to remove it from my application.
Thanks for your input though!
On Mar 29, 7:55 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Mar 29, 2011, at 4:12 AM, Stephen wrote:
Hello,
I'm using a custom type (TypeDecorator) to handle encrypting data on
write and decrypting data on read. Everything works fine for inserting
and reading from the database with one exception. Using the like
operator with '%' syntax is not returning any results.
For the purposes of this post, lets suppose the data manipulation is
simple base 64 encoding:
#code block:
class EncryptedString(TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
if not value:
return value
return base64.b64encode(value)
def process_result_value(self, value, dialect):
if not value:
return value
decrypted_sub_string = base64.b64decode(value)
#and a simple model:
Base = declarative.declarative_base()
class MyTable(Base):
__tablename__ = mytable
name = EncryptedString(128)
#and the query that does not work:
Session().query(MyTable).filter(MyTable.name.like('%foo%')
#end code block
Let's say there's a row in the table with name = 'foobar' (actually
its whatever 'foobar' encodes to) the above query will return nothing.
If I query for .like('foobar') then I will get the row returned as
expected.
Lastly, I'm on v0.5.1 (way behind I know!).
Any thoughts? Thanks so much,
It would appear that your encrypted type is also encrypting the % signs.
It depends highly on the style of encryption you are using what path to take
here.
If the translation is one-to-one on a per character basis then you might want
to do a comparison column LIKE '%' + 'encrypted' + '%', which would involve
working around MyTable.name's forced coercion of the right hand side,
probably using col.like(literal('%', type_=String) + my_encryption('foo') +
literal('%', type_=String)). Or modifying your type to allow '%' signs
through, perhaps if they are escaped in some special way.
The more robust method is to allow the % operator to work naturally by
performing the decryption on the database side:
func.decrypt(MyTable.name).like('%foo%')
where decrypt would be a function or custom procedure on the DB side that
does the same decryption.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Combine Textual query with a filter
I posted this to Stack Overflow without any luck. Seeing that MB hangs around here, this should be the first place I try. I'm using SA 0.6.6, Python 2.66 and Postgres 8.3. I have certain queries which require somewhat complex security check that can be handled with a WITH RECURSIVE query. What I'm trying to do is combine a textual query with a query object so I can apply filters as necessary. My original thought was was to create my text query as a subquery and then combine that with the user's query and filters. Unfortunately, this isn't working. subquery = session.query(sharedFilterAlias).\ from_statement(sharedFilterQuery).subquery() This results in this error: AttributeError: 'Annotated_TextClause' object has no attribute 'alias' Is there anyway to combine a textual query with SQLAlchemy's query object? I just need to apply the Query and filter to my text query with something like: where Entity.id in (textQueryResult). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Combine Textual query with a filter
On Mar 29, 2011, at 3:33 PM, Corey Coogan wrote: I posted this to Stack Overflow without any luck. Seeing that MB hangs around here, this should be the first place I try. I'm using SA 0.6.6, Python 2.66 and Postgres 8.3. I have certain queries which require somewhat complex security check that can be handled with a WITH RECURSIVE query. What I'm trying to do is combine a textual query with a query object so I can apply filters as necessary. My original thought was was to create my text query as a subquery and then combine that with the user's query and filters. Unfortunately, this isn't working. subquery = session.query(sharedFilterAlias).\ from_statement(sharedFilterQuery).subquery() This results in this error: AttributeError: 'Annotated_TextClause' object has no attribute 'alias' Is there anyway to combine a textual query with SQLAlchemy's query object? I just need to apply the Query and filter to my text query with something like: where Entity.id in (textQueryResult). Its not clear what SQL you'd be looking for when you say from_statement().subquery().Anything can be in text(), so SQLA can't generically select from it - theres no SQL parser so it doesn't know what columns would be present inside of it to attach to the .c. collection on an Alias object. Typically its better to not use pure text(), if you're looking to have a .c. collection, and to just put your string based stuff in the WHERE clause of a select() which is usually where the elaborate stuff is, that is select().where(string stuff) or query.filter(string stuff). select() is a little more open ended than Query here in that you can send strings to select_from() and others too. from sqlalchemy import * s = select([a, b]).select_from(hoho).where(x=5) print s print select([s.c.a, s.c.b]) SELECT a, b FROM hoho WHERE x=5 SELECT a, b FROM (SELECT a, b FROM hoho WHERE x=5) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Combine Textual query with a filter
Thanks MB, that helps me out. I hadn't considered the text portion of the WHERE. The thing is, I'm using WITH RECURSIVE, which gets me a Common Table Expression (CTE) that represents a folder hierarchy. I want to then query my entity, apply any filters and make sure my ID appears in the CTE. Does that make sense? Here's the pseudo code of what I want to do and you can tell me if it's possible. sharedFolder = WITH RECURSIVE q (.) entity = session.query(Entity).filter(Entity.value 100).filter(in_(Entity.id,sharedFolder)).first() Does that make any sense at all? Basically, I just need to apply the CTE to my query and filters so I can return the hydrated entity if it passes the security check done in the WITH RECURSIVE statement. On Mar 29, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 29, 2011, at 3:33 PM, Corey Coogan wrote: I posted this to Stack Overflow without any luck. Seeing that MB hangs around here, this should be the first place I try. I'm using SA 0.6.6, Python 2.66 and Postgres 8.3. I have certain queries which require somewhat complex security check that can be handled with a WITH RECURSIVE query. What I'm trying to do is combine a textual query with a query object so I can apply filters as necessary. My original thought was was to create my text query as a subquery and then combine that with the user's query and filters. Unfortunately, this isn't working. subquery = session.query(sharedFilterAlias).\ from_statement(sharedFilterQuery).subquery() This results in this error: AttributeError: 'Annotated_TextClause' object has no attribute 'alias' Is there anyway to combine a textual query with SQLAlchemy's query object? I just need to apply the Query and filter to my text query with something like: where Entity.id in (textQueryResult). Its not clear what SQL you'd be looking for when you say from_statement().subquery(). Anything can be in text(), so SQLA can't generically select from it - theres no SQL parser so it doesn't know what columns would be present inside of it to attach to the .c. collection on an Alias object. Typically its better to not use pure text(), if you're looking to have a .c. collection, and to just put your string based stuff in the WHERE clause of a select() which is usually where the elaborate stuff is, that is select().where(string stuff) or query.filter(string stuff). select() is a little more open ended than Query here in that you can send strings to select_from() and others too. from sqlalchemy import * s = select([a, b]).select_from(hoho).where(x=5) print s print select([s.c.a, s.c.b]) SELECT a, b FROM hoho WHERE x=5 SELECT a, b FROM (SELECT a, b FROM hoho WHERE x=5) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
