On 05/15/2017 06:43 AM, mdob wrote:
Just curious. Let's say we have a complex primary key of user_id
(integer), project_id (integer) and date (timestamp). After adding and
committing we don't have the PK and we won't be able to update it. Is
that right?
if you were using Postgresql this would be no problem, because we use
RETURNING in the INSERT to get all the server default values back
directly. But MySQL does not support this, for primary key values that
are server-generated, we can only get the AUTO_INCREMENT integer back,
that's correct. We don't call last_inserted_id() directly, this is a
function of the driver which gives us the number via cursor.lastrowid.
For things like dates, for us to know the PK of the row we just
inserted, we need to generate it before we do the insert. So in your
Table def you need to use client side "default=func.utcnow()" or
whatever so that SQLAlchemy Core knows it can run that default
generation function as a separate SQL SELECT; this does not preclude
being able to have a server default on the column as well, it just would
not be used in the case of ORM insert.
If it was auto-increment integer then it would probably be fine. PK
would be fetched using last_inster_id() in mysql or similar method in
other dialects.
|
from sqlalchemy import create_engine, Column, Integer, TIMESTAMP,
FetchedValue
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine =
create_engine('mysql+mysqldb://root:****@192.168.1.12:3306/test?charset=utf8',
echo=True)
class Timesheet(Base):
__tablename__ = 'timesheet'
user_id = Column(Integer, primary_key=True)
project_id = Column(Integer, primary_key=True)
date = Column(TIMESTAMP(), primary_key=True, nullable=False,
server_default=FetchedValue())
session = sessionmaker(engine)()
t1 = Timesheet(user_id=1, project_id=1)
session.add(t1)
session.commit()
print t1.date
|
|
2017-05-15 11:46:09,411 INFO sqlalchemy.engine.base.Engine SHOW
VARIABLES LIKE 'sql_mode'
2017-05-15 11:46:09,411 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,412 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2017-05-15 11:46:09,412 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,412 INFO sqlalchemy.engine.base.Engine show
collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2017-05-15 11:46:09,413 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,413 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS CHAR(60)) AS anon_1
2017-05-15 11:46:09,413 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS CHAR(60)) AS anon_1
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE
utf8_bin AS anon_1
2017-05-15 11:46:09,414 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 11:46:09,415 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-05-15 11:46:09,416 INFO sqlalchemy.engine.base.Engine INSERT INTO
timesheet (user_id, project_id) VALUES (%s, %s)
2017-05-15 11:46:09,416 INFO sqlalchemy.engine.base.Engine (1, 1)
2017-05-15 11:46:09,416 INFO sqlalchemy.engine.base.Engine COMMIT
Traceback (most recent call last):
File "/home/mike/projects/sandbox/box7.py", line 21, in <module>
print t1.date
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py",
line 237, in __get__
return self.impl.get(instance_state(instance), dict_)
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py",
line 578, in get
value = state._load_expired(state, passive)
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/state.py",
line 474, in _load_expired
self.manager.deferred_scalar_loader(self, toload)
File
"/home/mike/envs/slashdb9/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
line 669, in load_scalar_attributes
raise orm_exc.ObjectDeletedError(state)
sqlalchemy.orm.exc.ObjectDeletedError: Instance '<Timesheet at
0x7f9ad5931d50>' has been deleted, or its row is otherwise not present.
2017-05-15 11:46:09,419 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-05-15 11:46:09,420 INFO sqlalchemy.engine.base.Engine SELECT
timesheet.user_id AS timesheet_user_id, timesheet.project_id AS
timesheet_project_id, timesheet.date AS timesheet_date
FROM timesheet
WHERE timesheet.user_id = %s AND timesheet.project_id = %s AND
timesheet.date IS NULL
2017-05-15 11:46:09,420 INFO sqlalchemy.engine.base.Engine (1, 1)
|
--
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
