[sqlalchemy] postgresql emulating app engines list property
Hello,
I'm hoping that some of the functionality I actually like in appengine
datastore can be duplicated in postgresql via sqlalchemy. However I'm not
quite grokking how all the moving pieces for a dialect(if that's the right
term) fit together.
On appengine there are list properties or repeated properties. This is
simply an array of values that has a custom behavior for the = in a
query. In that an object with a property named my_list who's value is
[1,2,3] will be true in the following where clauses...
where my_list=1
where my_list=2
where my_list=3
I feel like it is possible to get the same kind of behavior on postgresql
with the ARRAY column type, but I'm having trouble finding examples of how
one might do this.
Here's the code I have so far.
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.orm.properties import ColumnProperty
from sqlalchemy.orm import column_property
class ListComparator(ColumnProperty.ColumnComparator):
other operators as they make sense
optimization: override set operators for one filter instead of
multiples and'd
todo: non-string types?
def __eq__(self, other):
return self.__clause_element__().op()(u{%s} % other)
def ListColumn(*args, **kw):
makes a column of an array of types args[0]
and uses ListComparator to emulate appengine list property
if not isinstance(args[0], ARRAY):
largs = list(args)
largs[0] = ARRAY(args[0])
args = tuple(largs)
else:
raise ValueError(%s is an array which is not allowed % args[0])
return column_property(Column(*args, **kw),
comparator_factory=ListComparator)
example usage
class T(B):
__tablename__ = t
id = Column(Integer, primary_key=True)
v = ListColumn(Integer)
t = T()
t.v = [1, 2]
S.add(t)
S.commit()
S.query(T).count()
S.query(T).filter(T.v==1).count()
S.query(T).filter_by(v =1).count()
An array of strings or ints works just fine for this, but any other data
types I would have to convert to strings and in some cases escape for the
array literal syntax in postgresql. It seems like I'm missing some piece of
the puzzle in regards to how all the types in sqla relate to each other for
queries. Is it the dialect the drives that, is there behavior there for
ARRAY that I'm somehow missing or trying to duplicate?
Thanks in advance for any information you have.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/9ZJ_qS0UuQkJ.
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] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
I posted this on StackOverflow: We have a MySQL database and we're switching from DB API to SQLAlchemy. From the documentation, the ResultProxy.rowcount is supposed to report the number of affected rows by an UPDATE statement. If I execute this query with SQLAlchemy: UPDATE table SET field = IF(field 10, 10, field) WHERE id = 1 It will return a rowcount of 1 if there's a matching row, but for any value of field (even when greater or equal than 10). When I executed this query with DB API, it returned the correct number of affected rows (0 when field was greater or equal than 10 and 1 if lower). That's causing us some troubles because we'll have to first execute a select query to determine if there was a change or not. Is it a bug in SQLAlchemy? The documentation clearly states that if should return the number of affected rows, not the number of matching rows. I got an answer saying that if CLIENT_FOUND_ROWS is set, the number of affected rows becomes the number of matching rows. I've checked and SQLAlchemy sets this flag. This is clearly in contradiction with the documentation, so I think that it's a bug that should be fixed. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/rA953M70mu4J. 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: strange attributeerror module object has no attribute exc when using sqlalchemy from a mod_wsgi handle
On Tuesday, 27 March 2012 23:39:11 UTC+2, alonn wrote: this is what I got from tailing the mod_wsgi error stack: [Tue Mar 27 23:14:16 2012] [error] [client 127.0.0.1] from sqlalchemy import create_engine,String,Unicode,Integer, Column, func,distinct, desc [Tue Mar 27 23:14:16 2012] [error] [client 127.0.0.1] File /path/to/virtualenv/app/data/virtenv/lib/python2.6/site-packages/sqlalchemy/__init__.py, line 10, in module [Tue Mar 27 23:35:50 2012] [error] [client 127.0.0.1] AttributeError: 'module' object has no attribute 'exc' actually when I run the file directly from python without mod_wsgi the error doesn' t show up.. strange I'll be glas any help/exprience with this strange problem? I have the exact same problem... Did you find a solution yet on your end? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mohP4_bTXnYJ. 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: strange attributeerror module object has no attribute exc when using sqlalchemy from a mod_wsgi handle
On Tuesday, 12 June 2012 15:31:26 UTC+2, Michel Albert wrote: On Tuesday, 27 March 2012 23:39:11 UTC+2, alonn wrote: this is what I got from tailing the mod_wsgi error stack: [Tue Mar 27 23:14:16 2012] [error] [client 127.0.0.1] from sqlalchemy import create_engine,String,Unicode,Integer, Column, func,distinct, desc [Tue Mar 27 23:14:16 2012] [error] [client 127.0.0.1] File /path/to/virtualenv/app/data/virtenv/lib/python2.6/site-packages/sqlalchemy/__init__.py, line 10, in module [Tue Mar 27 23:35:50 2012] [error] [client 127.0.0.1] AttributeError: 'module' object has no attribute 'exc' actually when I run the file directly from python without mod_wsgi the error doesn' t show up.. strange I'll be glas any help/exprience with this strange problem? I have the exact same problem... Did you find a solution yet on your end? Upon closer inspection it was an access rights problem. mod_wsgi could not write into it's egg-cache. The following section of the docs explains how to fix this: http://code.google.com/p/modwsgi/wiki/ApplicationIssues#Access_Rights_Of_Apache_User After that I had to restart the server. Not only touch the WSGI file! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/TRdHQW89m0IJ. 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] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
On Jun 12, 2012, at 7:07 AM, Francis wrote: I posted this on StackOverflow: We have a MySQL database and we're switching from DB API to SQLAlchemy. From the documentation, the ResultProxy.rowcount is supposed to report the number of affected rows by an UPDATE statement. If I execute this query with SQLAlchemy: UPDATE table SET field = IF(field 10, 10, field) WHERE id = 1 It will return a rowcount of 1 if there's a matching row, but for any value of field (even when greater or equal than 10). When I executed this query with DB API, it returned the correct number of affected rows (0 when field was greater or equal than 10 and 1 if lower). That's causing us some troubles because we'll have to first execute a select query to determine if there was a change or not. Is it a bug in SQLAlchemy? The documentation clearly states that if should return the number of affected rows, not the number of matching rows. I got an answer saying that if CLIENT_FOUND_ROWS is set, the number of affected rows becomes the number of matching rows. I've checked and SQLAlchemy sets this flag. This is clearly in contradiction with the documentation, so I think that it's a bug that should be fixed. First off, let's note the new documentation which reorganizes the many misunderstandings this attribute has caused over the years, and now includes a removal of the word affected: http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy.rowcount MySQL-specific notes are also added at: http://docs.sqlalchemy.org/en/rel_0_7/dialects/mysql.html#rowcount-support these new notes are in versions 0.6, 0.7, 0.8. Next, here's why affected is not clearly a contradiction based on where you're coming from. The DBAPI spec says this: This read-only attribute specifies the number of rows that the last .execute*() produced (for DQL statements like 'select') or affected (for DML statements like 'update' or 'insert'). First note that the rowcount on select is not something anyone uses consistently since this is not a common feature of database client libraries. For UPDATE and DELETE, someone familiar with a wide array of databases would read this paragraph and link it to the knowledge they have of what database client libraries usually offer in this regard. The client APIs of: SQLite, Postgresql, Oracle, Sybase, Microsoft SQL Server, Firebird, and all the rest of them, *except* for MySQL, consider the rowcount to be the number of rows *matched* by the WHERE criterion. So most people who read this paragraph link it to a broader field of knowledge about client APIs and based on established convention know that affected here means number of rows matched, where MySQL is, as they so often are, the one backend that re-interprets the meaning/behavior of standard features to be something different (but as always, with flags/switches added at some point to make it act the normal way). It was in this way that the word affected found its way directly in the SQLAlchemy docs for rowcount. However, taking the word affected literally without the benefit of context, yes this is totally unclear. I've emailed DB-SIG to get their thoughts on if/how they'd like to clarify this. -- 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] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
Thank you for this very instructive answer! As I've never really used anything else than MySQL, I didn't know this. But as you said, there should probably be a note about this in the documentation, at least to define what affected means in the context of SQLAlchemy. On Tuesday, June 12, 2012 10:49:53 AM UTC-4, Michael Bayer wrote: On Jun 12, 2012, at 7:07 AM, Francis wrote: I posted this on StackOverflow: We have a MySQL database and we're switching from DB API to SQLAlchemy. From the documentation, the ResultProxy.rowcount is supposed to report the number of affected rows by an UPDATE statement. If I execute this query with SQLAlchemy: UPDATE table SET field = IF(field 10, 10, field) WHERE id = 1 It will return a rowcount of 1 if there's a matching row, but for any value of field (even when greater or equal than 10). When I executed this query with DB API, it returned the correct number of affected rows (0 when field was greater or equal than 10 and 1 if lower). That's causing us some troubles because we'll have to first execute a select query to determine if there was a change or not. Is it a bug in SQLAlchemy? The documentation clearly states that if should return the number of affected rows, not the number of matching rows. I got an answer saying that if CLIENT_FOUND_ROWS is set, the number of affected rows becomes the number of matching rows. I've checked and SQLAlchemy sets this flag. This is clearly in contradiction with the documentation, so I think that it's a bug that should be fixed. First off, let's note the new documentation which reorganizes the many misunderstandings this attribute has caused over the years, and now includes a removal of the word affected: http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy.rowcount MySQL-specific notes are also added at: http://docs.sqlalchemy.org/en/rel_0_7/dialects/mysql.html#rowcount-support these new notes are in versions 0.6, 0.7, 0.8. Next, here's why affected is not clearly a contradiction based on where you're coming from. The DBAPI spec says this: This read-only attribute specifies the number of rows that the last .execute*() produced (for DQL statements like 'select') or affected (for DML statements like 'update' or 'insert'). First note that the rowcount on select is not something anyone uses consistently since this is not a common feature of database client libraries. For UPDATE and DELETE, someone familiar with a wide array of databases would read this paragraph and link it to the knowledge they have of what database client libraries usually offer in this regard. The client APIs of: SQLite, Postgresql, Oracle, Sybase, Microsoft SQL Server, Firebird, and all the rest of them, *except* for MySQL, consider the rowcount to be the number of rows *matched* by the WHERE criterion. So most people who read this paragraph link it to a broader field of knowledge about client APIs and based on established convention know that affected here means number of rows matched, where MySQL is, as they so often are, the one backend that re-interprets the meaning/behavior of standard features to be something different (but as always, with flags/switches added at some point to make it act the normal way). It was in this way that the word affected found its way directly in the SQLAlchemy docs for rowcount. However, taking the word affected literally without the benefit of context, yes this is totally unclear. I've emailed DB-SIG to get their thoughts on if/how they'd like to clarify this. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/M7PyI_DGh0oJ. 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] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
Well, my reply doesn't really make sense as the note is there. I mean that it should be accessible from the rowcount description. That would be nice if affected row was an hyperlink to it's definition in the context of SQLAlchemy. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4-7klHGBkUsJ. 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: Reconnect while loosing Mysql connection
Too obvious or too complicated? On Monday, June 4, 2012 7:10:14 PM UTC+4, Pavel Skvazh wrote: Hi, everyone! Due to an obviously lacking skills of an admin, the connection with Mysql sever is really spotty. And I keep getting OperationalError 'Can't connect to MySQL server' error every once in a while. What's the best practice approach to handle this kind of errors. Logical way would be to try to reconnect N times every N seconds and if it fails - throw this error. Doing it manually doesn't seem appropriate and natural. Code won't be pretty either. Connection pool looks like the way to go. i.e. try to get another connection if this one dies for some reason. It doesn't look like it's the way it is. Any help would be highly appreciated. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/QKPokWMzPbkJ. 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] Re: Reconnect while loosing Mysql connection
turn off pooling is the quickest, use NullPool. otherwise set pool_recycle very low, or add a ping event. Docs for all of this at: http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html On Jun 12, 2012, at 2:07 PM, Pavel Skvazh wrote: Too obvious or too complicated? On Monday, June 4, 2012 7:10:14 PM UTC+4, Pavel Skvazh wrote: Hi, everyone! Due to an obviously lacking skills of an admin, the connection with Mysql sever is really spotty. And I keep getting OperationalError 'Can't connect to MySQL server' error every once in a while. What's the best practice approach to handle this kind of errors. Logical way would be to try to reconnect N times every N seconds and if it fails - throw this error. Doing it manually doesn't seem appropriate and natural. Code won't be pretty either. Connection pool looks like the way to go. i.e. try to get another connection if this one dies for some reason. It doesn't look like it's the way it is. Any help would be highly appreciated. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/QKPokWMzPbkJ. 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.
