[sqlalchemy] Re: A question about the Hybrid Value pattern for encrypted values
In my case, I want to enforce case-insensitive comparison for
encrypted email addresses.
The hybrid can be made to work for encryption very nicely if you use a
comparator that coerces the value to its encrypted form before passing to the
database. It's worth publishing so I created an example at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SymmetricEncryption.
The issue the approach has however is if you're dealing with salted
encryption and attempting to compare crypted values in the database - you
can't encrypt on the Python side since each comparison would require the salt
of each value to be compared. Ants' older recipe at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/HashPropertyproposes using
DB side functions for this scenario.
Thanks so much for your advice. I took your SymmetricEncryption
recipe and modified it to support case-insensitive comparison on the
encrypted values. I created a working example below as a reference
for others.
from sqlalchemy import create_engine, func, Column, Integer,
LargeBinary
from sqlalchemy.ext.hybrid import hybrid_property, Comparator
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
EMAIL_LEN_MAX = 256
encrypt = lambda x: x.encode('utf-8').replace('---', '===') # Dummy
function
decrypt = lambda x: x.decode('utf-8').replace('===', '---') # Dummy
function
db = scoped_session(sessionmaker())
Base = declarative_base()
class CaseInsensitiveUnicode(unicode): # !!!
def __lt__(self, other):
return self.lower() other.lower()
def __le__(self, other):
return self.lower() = other.lower()
def __eq__(self, other):
return self.lower() == other.lower()
def __ne__(self, other):
return self.lower() != other.lower()
def __gt__(self, other):
return self.lower() other.lower()
def __ge__(self, other):
return self.lower() = other.lower()
class CaseInsensitiveEncryptComparator(Comparator):
def operate(self, op, other, **kwargs):
return op(self.__clause_element__(), encrypt(other.lower()),
**kwargs) # !!!
class EmailMixin(object):
'Mixin class for a case-insensitive encrypted email address'
email_ = Column(LargeBinary(EMAIL_LEN_MAX * 2)) # Doubled for
unicode addresses
@hybrid_property
def email(self):
return CaseInsensitiveUnicode(decrypt(self.email_)) # !!!
@email.setter
def email(self, email):
self.email_ = encrypt(email.lower())
@email.comparator
def email(cls):
return CaseInsensitiveEncryptComparator(cls.email_)
class User(Base, EmailMixin):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
engine = create_engine('sqlite:///', echo=True)
db.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
db.merge(User(email='x...@example.com'))
db.merge(User(email='x...@example.com'))
user = db.query(User).filter_by(email='x...@example.com').first()
assert user.email == 'x...@example.com'
assert user.email == 'x...@example.com'
emails = [x.email for x in db.query(User)]
assert sorted(emails) == ['x...@example.com', 'x...@example.com']
assert 'x...@example.com' in emails
#
https://github.com/invisibleroads/invisibleroads-templates/blob/master/invisibleroads/paster_templates/core/+package+/models.py_tmpl
--
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] A question about the Hybrid Value pattern for encrypted values
Hi Michael, In the documentation you state that the Hybrid Value pattern is useful for encrypted values. http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html Is it possible for you to provide an example of the Hybrid Value pattern as applied to encrypted values? In my case, I want to enforce case-insensitive comparison for encrypted email addresses. The email addresses are currently encrypted model-side by overriding TypeDecorator to encrypt on the way in and decrypt on the way out. I have tried applying a column_property(comparator_factory=CaseInsensitiveComparator) but that applies lower() to the encrypted value instead of the decrypted email address. I thought the Hybrid Value pattern might apply in this case, but the encrypt() and decrypt() functions I am using are strictly Python without SQL equivalents. Is there a way to enforce case-insensitive encrypted comparison from the model? Thanks, RHH -- 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: A question about the Hybrid Value pattern for encrypted values
On Jun 21, 5:55 pm, Roy Hyunjin Han starsareblueandfara...@gmail.com wrote: In the documentation you state that the Hybrid Value pattern is useful for encrypted values.http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html Is it possible for you to provide an example of the Hybrid Value pattern as applied to encrypted values? In my case, I want to enforce case-insensitive comparison for encrypted email addresses. The email addresses are currently encrypted model-side by overriding TypeDecorator to encrypt on the way in and decrypt on the way out. I have tried applying a column_property(comparator_factory=CaseInsensitiveComparator) but that applies lower() to the encrypted value instead of the decrypted email address. I thought the Hybrid Value pattern might apply in this case, but the encrypt() and decrypt() functions I am using are strictly Python without SQL equivalents. Is there a way to enforce case-insensitive encrypted comparison from the model? To answer my own question, I am going to guess that the Hybrid Value pattern for encrypted values refers specifically to database-provided encryption functions such as pgcrypto. If the encryption functions are pure Python, I don't think the Hybrid Value pattern applies. -- 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] How do we retrieve the database dialect that a specific session is using?
Is there any way to retrieve the database dialect that a specific session is using? e.g. MySQL, PostGIS, etc. I am trying to modify Sanjiv Singh's GeoAlchemy extension so that a person can retrieve the appropriate database procedure for a given dialect, so that geospatial queries can be both direct (performed in a single database call) as well as dialect-neutral. For example, in PostGIS, getting the WKT of a geometry requires func.ST_AsText() session.query(func.ST_AsText(Road.geom)).first() However, in MySQL, the same procedure requires func.AsText() session.query(func.AsText(Road.geom)).first() I would like to be able to create a function called wkt() in both geoalchemy/mysql.py and geoalchemy/postgis.py that enables me to the following: session.query(session.dialect.wkt(Road.geom)).first() Thanks, Roy Hyunjin Han Modi Research Group Earth Institute at Columbia University http://modi.mech.columbia.edu -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: How to get inserted ids after row insert?
Hi Michael, Does the behavior of result.lastrowid change after SQLAlchemy version 5.5? It seems that in older versions lastrowid is the first id inserted and in later versions lastrowid is the last id inserted. RHH On Fri, Apr 10, 2009 at 11:57 AM, Michael Bayermike...@zzzcomputing.com wrote: call result.lastrowid. you'll get whatever the DBAPI deems worthy of sending. Andrija Frincic aka BobRock wrote: Hi all I use insert statement as text to insert record into MySQL DB. I chose this method because I use INSERT in combination with SELECT, so there is no way to use SQL Expression language. My problem is that there is no inserted ids available after inserting data using raw SQL statement. When I call last_inserted_ids on ResultProxy returned by session.execute I get following exception: AttributeError: 'MySQLExecutionContext' object has no attribute '_last_inserted_ids'. Here is a sample code: ins = insert into visitor(queueid, firstname, uid, added, description, queue_pos select :queueid, :firstname, :uid, :added, :description, max(queue_pos) +1 FROM visitor WHERE DATE(added)=DATE(:added) AND queueid=:queueid res = meta.Session.execute(ins, self.form_result); meta.Session.commit() ids = res.last_inserted_ids(); I use SQL alchemy 0.5.2 with Pylons and with MySQLDb 1.2.3b1 and Python 2.6. Could someone point me how to get id of inserted record. Best regards Andrija --~--~-~--~~~---~--~~ 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: connectionless queries with Spatial data (PostGIS)
Geospatial support is definitely an external project in any case. As soon as something goes in the core, it's now linked to our release process, I'm getting the tickets and complaints personally, etc., theres no way a huge subject like geo support belongs there. I agree with you that it has to be an external project. I do not want to bog down SQLAlchemy's release process. I'm just looking for smooth integration with PostGIS and other geospatial SQL extensions.If we decide my observation that GeoDjango has done a lot of work that needs to be re-done isn't really valid, and everything they've done is only useful for Django web applications, then that idea should be scrapped, and a simple library which builds upon SQLAlchemy expression constructs should be created. We need the SQLAlchemy extension to Spatialite in the lab now, so we'll get to work on that first. 1. As you suggested, we'll first try looking into making GeoDjango work as a plugin, in which case we'll get a lot of stuff for free. 2. Otherwise, we'll write our own stripped-down version for Spatialite and PostGIS. I'm aiming for a release of the plugin or extension by the end of May. --~--~-~--~~~---~--~~ 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: connectionless queries with Spatial data (PostGIS)
Michael, Do you know if anyone is working on a PostGIS extension for SQLAlchemy? If not, I'd like to take a crack at it. RHH On Mon, Mar 16, 2009 at 1:47 PM, Michael Bayer mike...@zzzcomputing.com wrote: Roy H. Han wrote: On Feb 20, 6:52 pm, Michael Bayer mike...@zzzcomputing.com wrote: Also I've built an ORM extension forpostgiswhich is incomplete but demonstrates how to round trip and createPostGISexpressions in a clean way, thats in the distribution in examples/postgis/postgis.py . Thanks for this contribution, Michael. Will you be at the PyCon SQLAlchemy sprint? I would like to work on getting PostGIS support hardcoded in the trunk. ill be there monday and tuesday. PostGIS is a PG extension though so support for it should remain separateid most like an external tool along the lines of geodjango. --~--~-~--~~~---~--~~ 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: connectionless queries with Spatial data (PostGIS)
Do you know if anyone is working on a PostGIS extension for SQLAlchemy? If not, I'd like to take a crack at it. My suggestions regarding this are of course building off of the postgis.py example, and also perhaps, if its feasable (not sure if it is), building the whole thing as a plugin to GeoDjango, which works with many different databases besides Postgres, and seems to have a lot of non-django-specific functionality. If the Django ORM bindings in GeoDjango could be replaced with SQLAlchemy bindings in an efficient way (where effiicient means, we're not taking Django ORM constructs and then translating to SQLA), we could leverage all the work they've done. Hmm. I was hoping I could add geospatial support to SQLAlchemy itself rather than for a specific web framework. I think GeoDjango wraps the geospatial C++ libraries (GDAL, GEOS, PROJ) with PostGIS but otherwise most of the core functionality seems to be in the geospatial C++ libraries. I have also been tinkering with Alessandro Furieri's Spatialite for SQLite3 and I think it would be a real boon if one could add geospatial support to SQLAlchemy via Spatialite as well as PostGIS. You are saying that we can save work by writing it as a plugin to GeoDjango, but I really like Pylons. Certainly, I can start with the postgis.py example and use the GeoDjango API and MapFish API as references. The MapFish community seems to have integrated PostGIS with Pylons already and maybe I can ask them how they feel about a geospatial extension for SQLAlchemy? We have a need for this at the lab because most of our custom desktop GIS applications use SQLite and SQLAlchemy seems like a great way to glue them all together. --~--~-~--~~~---~--~~ 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] PostgreSQL executemany and result.last_inserted_ids()
I'm trying to use SQLAlchemy's executemany syntax in PostgreSQL and
get the last_inserted_ids().
What is the recommended way to get these last_inserted_ids()?
I know that I can access the id attribute for each model instance but
there are hundreds of inserted rows and I would rather retrieve them
all at once.
Thanks,
RHH
names = ['ted', 'steve', 'mike']
result = meta.Session.execute(model.names_table.insert(), [{'name': x} for x
in names])
result.last_inserted_ids()
Traceback (most recent call last):
File
/usr/lib/python2.5/site-packages/WebError-0.10.1-py2.5.egg/weberror/evalcontext.py,
line 35, in exec_expr
exec code in self.namespace, self.globs
File web, line 1, in module
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py,
line 1560, in last_inserted_ids
return self.context.last_inserted_ids()
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/default.py,
line 284, in last_inserted_ids
return self._last_inserted_ids
AttributeError: 'PGExecutionContext' object has no attribute
'_last_inserted_ids'
--~--~-~--~~~---~--~~
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: PostgreSQL executemany and result.last_inserted_ids()
Yay! I solved it. Thanks for making such a great module.
result =
meta.Session.execute(model.names_table.insert(postgres_returning=[model.names_table.c.id]),
[{'name': x} for x in names])
result.fetchall()
[(40,), (41,), (42,)]
On Thu, Apr 9, 2009 at 6:05 PM, Roy Hyunjin Han
starsareblueandfara...@gmail.com wrote:
I'm trying to use SQLAlchemy's executemany syntax in PostgreSQL and
get the last_inserted_ids().
What is the recommended way to get these last_inserted_ids()?
I know that I can access the id attribute for each model instance but
there are hundreds of inserted rows and I would rather retrieve them
all at once.
Thanks,
RHH
names = ['ted', 'steve', 'mike']
result = meta.Session.execute(model.names_table.insert(), [{'name': x} for
x in names])
result.last_inserted_ids()
Traceback (most recent call last):
File
/usr/lib/python2.5/site-packages/WebError-0.10.1-py2.5.egg/weberror/evalcontext.py,
line 35, in exec_expr
exec code in self.namespace, self.globs
File web, line 1, in module
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py,
line 1560, in last_inserted_ids
return self.context.last_inserted_ids()
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/default.py,
line 284, in last_inserted_ids
return self._last_inserted_ids
AttributeError: 'PGExecutionContext' object has no attribute
'_last_inserted_ids'
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
