Thread: [Sqlalchemy-tickets] [sqlalchemy] #2598: FetchedValue() doesn't seem to work how you'd expect
Brought to you by:
zzzeek
From: sqlalchemy <mi...@zz...> - 2012-11-02 15:04:55
|
#2598: FetchedValue() doesn't seem to work how you'd expect -----------------------------------------+-------------------- Reporter: tisdall | Type: defect Status: new | Priority: medium Milestone: | Component: access Severity: no triage selected yet | Keywords: Progress State: awaiting triage | -----------------------------------------+-------------------- I'm wanting to create a table which has a primary key that is automatically created by a trigger. Instead of it working I get an error saying "mysql.connector.errors.DatabaseError: 1364: Field 'patient_id' doesn't have a default value". If I explicitly give it a value then SQLAlchemy uses that value instead of fetching what the trigger ends up putting in as a replacement. The [http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=server_default #column-table-metadata-api server_default docs] say: "Use FetchedValue to indicate that an already-existing column will generate a default value on the database side which will be available to SQLAlchemy for post-fetch after inserts. This construct does not specify any DDL and the implementation is left to the database, such as via a trigger." here's some sample code that creates the situation: {{{ from sqlalchemy import (Column, BigInteger, Binary, create_engine) from sqlalchemy.schema import FetchedValue from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import (scoped_session, sessionmaker) DBSession = scoped_session(sessionmaker()) Base = declarative_base() class Patient(Base): __tablename__ = 'patient' patient_id = Column(BigInteger(unsigned=True), server_default=FetchedValue(), primary_key=True, autoincrement=False) #default=text("uuid_short()") details = Column(Binary(10000)) if __name__ == '__main__': engine = create_engine('mysql+mysqlconnector://root@127.0.0.1/mainserver?charset=utf8&use_unicode=0', echo=True) DBSession.configure(bind=engine) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) DBSession.execute("CREATE TRIGGER before_insert_"+Patient.__tablename__+" BEFORE INSERT ON `"+Patient.__tablename__+"` FOR EACH ROW SET new.`"+Patient.__tablename__+"_id` = UUID_SHORT();") session = DBSession() patient1 = Patient(patient_id=1, details = None) # works but outputs 1 instead of value set by trigger #patient1 = Patient(details = None) # throws error because it's expecting default session.add(patient1) session.flush() session.commit() print(patient1.patient_id) }}} this throws "mysql.connector.errors.DatabaseError: 1364: Field 'patient_id' doesn't have a default value" if you change it to {{{ patient1 = Patient(patient_id=1, details = None) }}} then you get {{{ 2012-11-02 15:01:24,439 INFO sqlalchemy.engine.base.Engine SELECT patient.patient_id AS patient_patient_id, patient.details AS patient_details FROM patient WHERE patient.patient_id = %(param_1)s 2012-11-02 15:01:24,439 INFO sqlalchemy.engine.base.Engine {'param_1': 1} Traceback (most recent call last): File "bug.py", line 28, in <module> print(patient1.patient_id) File "/sites/metrics_dev/lib/python3.3/site- packages/sqlalchemy/orm/attributes.py", line 168, in __get__ return self.impl.get(instance_state(instance),dict_) File "/sites/metrics_dev/lib/python3.3/site- packages/sqlalchemy/orm/attributes.py", line 451, in get value = callable_(passive) File "/sites/metrics_dev/lib/python3.3/site- packages/sqlalchemy/orm/state.py", line 285, in __call__ self.manager.deferred_scalar_loader(self, toload) File "/sites/metrics_dev/lib/python3.3/site- packages/sqlalchemy/orm/mapper.py", line 1719, in _load_scalar_attributes raise orm_exc.ObjectDeletedError(state) sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Patient at 0x7f9e79f4d750>' has been deleted, or its row is otherwise not present. }}} but mysql shows the following: {{{ mysql> select * from patient; +-------------------+---------+ | patient_id | details | +-------------------+---------+ | 94732327996882994 | NULL | +-------------------+---------+ 1 row in set (0.00 sec) }}} Obviously SQLAlchemy is trying to retrieve the object using a primary key of "1". -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 15:06:35
|
#2598: FetchedValue() doesn't seem to work how you'd expect ----------------------------------+------------------------------------ Reporter: tisdall | Owner: Type: defect | Status: new Priority: medium | Milestone: Component: access | Severity: no triage selected yet Resolution: | Keywords: Progress State: awaiting triage | ----------------------------------+------------------------------------ Comment (by tisdall): couldn't see where to mention this, but: {{{ >>> sqlalchemy.__version__ '0.7.9' }}} -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:1> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 16:30:52
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Changes (by zzzeek): * severity: no triage selected yet => major - 1-3 hours * component: access => sql * milestone: => 0.8.xx * owner: => zzzeek * status_field: awaiting triage => needs questions answered Comment: "this throws "mysql.connector.errors.DatabaseError?: 1364: Field 'patient_id' doesn't have a default value" is generated by your MySQL database, not SQLAlchemy, which suggests your trigger is not working as expected. So you first need to get the trigger working independently of everything else, using a plain DBAPI cursor in conjunction with cursor.execute("INSERT INTO table ..."). The trigger might not be working at all, or might not be working in conjunction with the MySQL-python DBAPI. On the SQLAlchemy side, there may or may not be issues using FetchedValue with a primary key column, as usually an integer primary key column is always "fetched" if not present, however that implies the autoincrement flag is True which I see you can't set here due to the CREATE TABLE. So FetchedValue compatibility with primary key here may be an issue at that level. However, there's yet another level that could still stop this feature in its tracks, which is that primary key columns can't be "fetched" in the usual way, they have to be delivered to us either via the DBAPI itself using the cursor.lastrowid value, or via special functions that tell us what the last inserted PK was. Either one of these methods needs to be available to us in order for the trigger-as-primary-key approach to work. The MySQL dialect currently uses cursor.lastrowid, so if MySQL-Python doesn't function correctly when a trigger is used, we might have to add additional dialect behaviors in order to detect this. But in any case the first step is to get your trigger working without SQLAlchemy being in use. -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:2> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 18:36:55
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Comment (by tisdall): Replying to [comment:2 zzzeek]: > "this throws "mysql.connector.errors.DatabaseError?: 1364: Field 'patient_id' doesn't have a default value" is generated by your MySQL database, not SQLAlchemy, which suggests your trigger is not working as expected. So you first need to get the trigger working independently of everything else, using a plain DBAPI cursor in conjunction with cursor.execute("INSERT INTO table ..."). The trigger might not be working at all, or might not be working in conjunction with the MySQL-python DBAPI. Actually, I thought that as well because it seemed to execute the trigger with no error, but there was no following COMMIT afterwards (perhaps creating a trigger is done outside the transaction?). However, right after the code crashes I go into mysql and am able to do "INSERT patient(details) VALUES (NULL)" and it successfully creates a new row with a UUID_SHORT for the primary key. So the actual mysql doesn't have a problem with the operation and the trigger is working properly. -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:3> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 18:40:02
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Comment (by tisdall): This probably doesn't make any difference, but I'm using mysqlconnector to interface with mysql as it seems to be the only one I could get working with Python3. That's not the default mysql connector. -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:4> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 19:11:13
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Comment (by zzzeek): I have lots of issues with mysqlconnector - each time I go to test it, it seems, I have to report a new bug on their bugtracker. Last I tried, the current release was non-functional due to an issue they are fixing now. -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:5> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 19:16:39
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Comment (by tisdall): Replying to [comment:5 zzzeek]: > I have lots of issues with mysqlconnector - each time I go to test it, it seems, I have to report a new bug on their bugtracker. Last I tried, the current release was non-functional due to an issue they are fixing now. > FUN! :) Okay, can you suggest a different connector that actually works with Py3? I think I tried several of the ones in the SQLAlchemy docs/wiki and that was the only one I got to work (using "work" losely here). -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:6> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 19:36:27
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Comment (by tisdall): Okay, did some investigating into the mysqlconnector and found out what was happening by entering some print() statements. It does the same as this in the client: {{{ mysql> INSERT INTO patient (details) VALUES (NULL); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1364 | Field 'patient_id' doesn't have a default value | +---------+------+-------------------------------------------------+ 1 row in set (0.00 sec) }}} Then in throws the warning as an exception. Is there a way in SQLAlchemy to switch off passing warnings into exceptions? -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:7> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 19:54:02
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Comment (by zzzeek): I'm not seeing the warning actually throwing an exception, however that solution here, as I indicated earlier, does not fix the problem because we aren't able to get at these new UUID values as generated inline. I only see the warning with MySQLdb, not mysql-connector-python. I'm running Python 2, because mysql-connector with Python 3 builds are failing, but Python version isn't terribly important here. In both cases, we aren't given any means of getting this newly generated value back, so this trigger cannot be used in conjunction with an ORM that needs primary key values. {{{ #!python #import MySQLdb #conn = MySQLdb.connect(host='localhost', user='scott', passwd='tiger', db='test') from mysql import connector conn = connector.connect(host='localhost', user='scott', passwd='tiger', db='test') cursor = conn.cursor() cursor.execute("drop table if exists patient") cursor.execute(""" create table patient ( patient_id BIGINT UNSIGNED primary key, details varchar(100) ) """) cursor.execute(""" CREATE TRIGGER before_insert_patient BEFORE INSERT ON `patient` FOR EACH ROW SET new.`patient_id` = UUID_SHORT() """) for i in range(3): cursor.execute("insert into patient (details) values ('some detail')") print cursor.lastrowid cursor.execute("Select last_insert_id()") print cursor.fetchall() cursor.execute("select patient_id from patient") print cursor.fetchall() }}} output here with either driver is: {{{ 0 [(0,)] [(94738459163361297,)] 0 [(0,)] [(94738459163361297,), (94738459163361298,)] 0 [(0,)] [(94738459163361297,), (94738459163361298,), (94738459163361299,)] }}} so we can confirm rows are being inserted, but there's no means of getting back that PK through this means. This is a big reason !FetchedValue() with primary keys is not a real priority, as it is not useful in most situations in any case. So here I'd forego, or just ignore, the trigger, and pass the SQL function directly as the "default" where it will be invoked separately as "SELECT UUID_SHORT()" before each INSERT: {{{ #!python from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Session Base = declarative_base() class Patient(Base): __tablename__ = 'patient' patient_id = Column(BigInteger(unsigned=True), default=func.uuid_short(), primary_key=True, autoincrement=False) details = Column(Binary(10000)) engine = create_engine("mysql://scott:tiger@localhost/test", echo=True) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) s = Session(engine) s.add(Patient(details='some details')) s.commit() }}} There's absolutely no downside to this, as we otherwise would need to invoke a function *after* each INSERT to get that value back, if there were such a way. So this method actually uses less overhead than the trigger. Alternatively, to skip the extra statement completely you can use a Python-side generation function. MySQL's implementation could be copied exactly, or this is a quick version that just uses a modulus of a built-in uuid4: {{{ #!python def new_id(): return uuid.uuid4().int % 10000000000000 class Patient(Base): __tablename__ = 'patient' patient_id = Column(BigInteger(unsigned=True), default=new_id, primary_key=True, autoincrement=False) details = Column(Binary(10000)) }}} -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:8> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-02 20:04:56
|
#2598: FetchedValue, or similar, support for PK columns -------------------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: | Keywords: Progress State: needs questions answered | -------------------------------------------+------------------------------- Comment (by tisdall): Okay, I think I understand now. Thank you! I ended up doing something like what you stated with a default=text("uuid_short()"). However, I'm concerned about other processes accessing the database and that's why I wanted to use a trigger. I modified the trigger to only insert a uuid_short() when no value is passed in during the insert. Please feel free to close this bug as I don't think I have the authorization to do that. -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:9> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-12 20:56:31
|
#2598: FetchedValue, or similar, support for PK columns -----------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: closed Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: worksforme | Keywords: Progress State: completed/closed | -----------------------------------+------------------------------- Changes (by zzzeek): * status: new => closed * resolution: => worksforme * status_field: needs questions answered => completed/closed -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:10> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-11-12 21:07:26
|
#2598: FetchedValue, or similar, support for PK columns -----------------------------------+------------------------------- Reporter: tisdall | Owner: zzzeek Type: defect | Status: closed Priority: medium | Milestone: 0.8.xx Component: sql | Severity: major - 1-3 hours Resolution: worksforme | Keywords: Progress State: completed/closed | -----------------------------------+------------------------------- Comment (by zzzeek): added new documentation regarding this in r4c1a3db74e53 -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2598#comment:11> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |