Re: [sqlalchemy] Primary keys mandatory sortability
One of the many reasons not to implement __lt__, is that the types are used with different databases. Those have had very different stories, especially enum labels may not have been added in the same order. Hence, defining and order may lead people to rely on it. But, on either side (postgresql or python) that may yield different behavior depending on the underlying db. We could sort this out (but may not really be worth the pain, on a system that is in production 24/7). But we also have had bugs in the past, that boiled down to ill defined or non-sensical orders (e.g. inadvertently sorting objects based on their memory addresses [thank Python3 for fixing that]) That can work for suprisingly long periods of time, until it doesn't. So, though it may sound a bit too phisolophical or just silly, I'd rather not implement orders that don't exist. As Python3 chose to do (e.g. objects no longer have an implicit order) Our use case is more about partitioning tables. The fact that an entity belongs to one partition or another doesn't make the entity greater or lower than any other. Not being able to add the enum in the primary key often means we have to handle unique constraints manually, add surrogate keys, which are on some large tables uselessly space and time consuming. I've read the issue. Our code base having been started quite a while back, we're using your enum recipe. We have implemented a few other behavior on top of that, that has delayed our migration to plain Python enums. Not sure if it would be worth doing in that setting or be a valid motivation to do the change. I may have some time, later in the summer (end of July, August). No promises. Anyhow, thank you for your answer. Regards, Nicolas. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e4fdb93a-3db5-4e27-bcb4-31cc61863d65%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Primaire keys mandatory sortability
Hi there, Where I work, in our data model, we'd have several the opportunity to have primary keys defined has a composite of something + an enum value. Though because sqlalchemy requises that primary keys can be sorted, it is not possible to define such primary keys. Indeed, in Python, enums are not sortablle. Which is fine by us and, though we could implemented some kind of ordre, to keep the behavior of enum types consistent between those use in a table and those that are not, we'd rater not do that. And yes, some might argue that this is a de-normalization and hence is evil, but we find it handy enough to accept corrupting our souls... If we do define such primary keys we end-up with the following error: [2019-01-29T18:56:24.936Z app.py:35986 MainProcess session.py:400 ] ERR APIException during a database transaction: . The database transaction will be roll backed Traceback (most recent call last): File "~/.repositories/project/static_api/repository/zone.py", line 175, in delete_zone zone_to_delete.delete() File "~/.repositories/project/models/zone.py", line 1851, in delete super(Zone, self).delete() File "~/.repositories/project/orm/declarative_base.py", line 325, in delete return get_session().delete(self, **kwargs) File "~/.repositories/project/orm/session.py", line 115, in delete self.flush() File "~/.repositories/project/orm/utils.py", line 289, in retry_on_deadlock_decorator return wrapped(*args, **kwargs) File "~/.repositories/project/orm/session.py", line 188, in flush return super(EtlSession, self).flush(objects) File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2139, in flush self._flush(objects) File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush transaction.rollback(_capture_exception=True) File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush flush_context.execute() File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute rec.execute(self) File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 577, in execute uow File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 243, in delete_obj uowtransaction)) File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 357, in _organize_states_for_delete states): File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1108, in _connections_for_states for state in _sort_states(states): File "~/.virtualenvs/project-py35/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 1130, in _sort_states sorted(persistent, key=lambda q: q.key[1]) TypeError: unorderable types: _EnumSymbol() < _EnumSymbol() This is not à New issue, see reference below. It is mentioned there that this may be made optional. So what would be the status of this ? I understand this is not an arbitrary choice (a post mentions preventing deadlocks). What would be the trade offs of such an options ? https://groups.google.com/forum/#!searchin/sqlalchemy/primary$20key$20sortable%7Csort:date/sqlalchemy/mWbr-Tw4wvU/QXFYH2rRgFsJ Thanks for enlightening us. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7a749928-b3e7-42e2-b428-f8d76000a881%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: exception message encoded in utf8
I think I can help reproduce this, but one has to configure the base system
in a non C or english locale.
E.g. the system I work with is in french: it's default locale is
fr_FR.UTF-8, hence the postgresql server
I installed on it runs with that locale too, by default. One can check with
the `SHOW lc_messages;` SQL query.
Hence, with any error I trigger on the server side, like an IntegrityError
or a ProgrammingError, comes
with a message from the server, which is encoded in that locale. For
example many of messages translated
in french, come with «» (french *guillemets*) instead of double quotes
which have psycopg or SQLAlchemy
choke on it. I would agree that is looks more like psycopg issue. It also
rarely impacts production environments,
which are generally not set with fancy locale (at least I don't do that).
For reference, some ways to mitigate this are:
- reconfigure your postgresql serveur and have it use another default
(requires "administrative" access to reconfigure the server);
- reconfigure your session so it use another session (cf. code snippet
below, requires to be superuser which is rarely convenient);
Regarding the required priveleges cf.
https://www.postgresql.org/docs/current/static/runtime-config-client.html.
@listens_for(_engine, 'connect')
def first_connect_callback(dbapi_connection, connection_record):
# Registers composite types so psycopg2 know how to
# handle them when they appear in resultsets.
cursor = dbapi_connection.cursor()
cursor.execute("SET lc_messages TO 'C';")
cursor.execute("COMMIT;")
cursor.execute("BEGIN;") # Make sure you start a new transaction (may
not always be appropriate)
Le jeudi 20 décembre 2012 18:35:13 UTC+1, Michael Bayer a écrit :
>
> OK as I said earlier, I'm not able to reproduce this.So I'd need that
> reproduction case in order to do anything. To be honest it sounds more
> like a psycopg2 bug, since psycopg2 does the decoding in most cases
> nowadays and even works with Python 3, so for it to be raising an exception
> with the "bytes" type for the message is certainly a bug. But would need
> to see a real world example to get a feel for it.
>
>
> On Dec 20, 2012, at 7:03 AM, Sylvain Prat wrote:
>
> Sorry to ressurrect this thread but the problem is still there. Since
> SQLAlchemy knows the encoding used to communicate with the database, it can
> properly decode the error strings returned by the database to unicode. So,
> I think it should be SQLAlchemy's responsibility to convert the error
> strings to unicode, not the user's responsibility. Could we open a bug for
> that in the tracker?
>
> Sylvain
>
>
> Le vendredi 7 mars 2008 02:39:56 UTC+1, jean-philippe dutreve a écrit :
>>
>> On 7 mar, 02:29, Michael Bayer wrote:
>>
>> > logging module itself throws UnicodeDecodeError ?
>> yes, in logging.format: ... = "%s" % msg
>> with msg the exception message encoded in utf8 and the default
>> encoding is ascii.
>>
>> > are you sending exception messages using logging.debug() or similar ?
>> exactly: log.error("... : %s", e.message)
>>
>> my impression
>> > is that you'd want to decode those manually doing something like
>> > string.decode('utf-8').
>> yes, it works fine, but it's pain to do this in each try/except.
>> Another solution is setting utf8 as the default encoding in
>> sitecustomize.py.
>> It's better centralized, but has sitepackage effect.
>>
>> But a better way IMHO is that the DB driver or SA returns unicode
>> exception message.
>>
>
> --
> 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/-/OOEvbKoo63cJ.
> To post to this group, send email to sqlal...@googlegroups.com
> .
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
