Re: [sqlalchemy] 'selectin' loading with composite keys on DB2 for i
This Worked!
@compiles(BinaryExpression, 'ibm_db_sa')
def _comp_binary(element, compiler, **kwargs):
text = compiler.visit_binary(element, **kwargs)
if element.operator is operators.in_op:
text = re.sub(r"\) IN \(", ") IN (VALUES ", text)
return text
Had to make a couple of changes 1) the dialect name is 'ibm_db_sa' and
2) (strangely!) DB2 gave me an error that it explicitly disallows parameter
markers in VALUES (but only in the single-column-key case). My other tests
seem to indicate that this was a misleading error message but I'm not going
to touch it again now that it is working.
Thanks!
On Thursday, 27 June 2019 15:03:01 UTC-4, Mike Bayer wrote:
>
>
>
> On Thu, Jun 27, 2019, at 2:11 PM, Steven James wrote:
>
> Currently, `selectin` loading with composite keys works for me on MySQL
> and SQLite. The documentation states that it also works with Postgres. I'm
> currently trying to get it working on DB2 (connecting to a DB2 for i, 7.2
> system.
>
> (the following assumes a table with the primary key consisting of two
> columns: a and b)
>
> selectin loading currently emits:
> `SELECT * FROM table WHERE (table.a, table.b) IN ((?, ?), (?, ?))`
>
> For this type of loading to work in DB2 (DB2 for i), the syntax needs to
> be:
> `SELECT * FROM table WHERE (table.a, table.b) IN VALUES (?, ?), (?,?)`
>
> Is there any way to implement this without a core change? I'm wondering if
> I can override the normal operation of in_() using a custom dialect or
> custom default comparator.
>
>
> funny thing is that I'm a Red Hat employee, so assuming RH's merger with
> IBM goes through I may eventually be an IBM employee, and maybe they'd like
> to give me DB2 things to work on :) However, that is not the case right
> now and I've done only very limited work with the DB2 driver as I'm sure
> you're aware the database itself is a beast.
>
> So this is something DB2's SQLAlchemy driver will have to add support for
> at some point, the selectinload thing is going to become more popular and
> also the internal mechanism for "IN" is going to be moving entirely to a
> newer architecture called "expanding". That's probably not important here
> though.
>
> For now, in order to get that "VALUES" in there, you don't need to
> "change" Core or work with custom datatypes, there's a variety of event
> hooks that can give you access to that part of the SQL more at the string
> level. I'm able to make this work also on Postgresql by intercepting
> BinaryExpression in the compiler, see the example below.
>
> import re
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKeyConstraint
> from sqlalchemy import Integer
> from sqlalchemy import String
> from sqlalchemy.ext.compiler import compiles
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import selectinload
> from sqlalchemy.orm import Session
> from sqlalchemy.sql import operators
> from sqlalchemy.sql.expression import BinaryExpression
>
>
> @compiles(BinaryExpression, "postgresql") # because I'm testing it here
> @compiles(BinaryExpression, "db2")
> def _comp_binary(element, compiler, **kw):
> text = compiler.visit_binary(element, **kw)
> if element.operator is operators.in_op:
> text = re.sub(r" IN \(", " IN (VALUES ", text)
> return text
>
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = "a"
>
> id = Column(Integer, primary_key=True)
> id2 = Column(Integer, primary_key=True)
> data = Column(String)
>
> bs = relationship("B")
>
>
> class B(Base):
> __tablename__ = "b"
> id = Column(Integer, primary_key=True)
>
> a_id = Column(Integer)
> a_id2 = Column(Integer)
>
> __table_args__ = (
> ForeignKeyConstraint(["a_id", "a_id2"], ["a.id", "a.id2"]),
> )
>
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> s.add(A(id=1, id2=1, bs=[B(), B()]))
> s.commit()
>
> s.query(A).options(selectinload(A.bs)).all()
>
>
>
>
>
>
> Thanks,
> Steven James
>
>
> --
> 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 sqlal...@googlegroups.com .
> To post to this group, send email to sqlal...@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/e7a3b368-d40e-4aba-84d5-10020fb05fe4%40googlegroups.com
>
>
Re: [sqlalchemy] 'selectin' loading with composite keys on DB2 for i
On Thu, Jun 27, 2019, at 2:11 PM, Steven James wrote:
> Currently, `selectin` loading with composite keys works for me on MySQL and
> SQLite. The documentation states that it also works with Postgres. I'm
> currently trying to get it working on DB2 (connecting to a DB2 for i, 7.2
> system.
>
> (the following assumes a table with the primary key consisting of two
> columns: a and b)
>
> selectin loading currently emits:
> `SELECT * FROM table WHERE (table.a, table.b) IN ((?, ?), (?, ?))`
>
> For this type of loading to work in DB2 (DB2 for i), the syntax needs to be:
> `SELECT * FROM table WHERE (table.a, table.b) IN VALUES (?, ?), (?,?)`
>
> Is there any way to implement this without a core change? I'm wondering if I
> can override the normal operation of in_() using a custom dialect or custom
> default comparator.
funny thing is that I'm a Red Hat employee, so assuming RH's merger with IBM
goes through I may eventually be an IBM employee, and maybe they'd like to give
me DB2 things to work on :) However, that is not the case right now and I've
done only very limited work with the DB2 driver as I'm sure you're aware the
database itself is a beast.
So this is something DB2's SQLAlchemy driver will have to add support for at
some point, the selectinload thing is going to become more popular and also the
internal mechanism for "IN" is going to be moving entirely to a newer
architecture called "expanding". That's probably not important here though.
For now, in order to get that "VALUES" in there, you don't need to "change"
Core or work with custom datatypes, there's a variety of event hooks that can
give you access to that part of the SQL more at the string level. I'm able to
make this work also on Postgresql by intercepting BinaryExpression in the
compiler, see the example below.
import re
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import selectinload
from sqlalchemy.orm import Session
from sqlalchemy.sql import operators
from sqlalchemy.sql.expression import BinaryExpression
@compiles(BinaryExpression, "postgresql") # because I'm testing it here
@compiles(BinaryExpression, "db2")
def _comp_binary(element, compiler, **kw):
text = compiler.visit_binary(element, **kw)
if element.operator is operators.in_op:
text = re.sub(r" IN \(", " IN (VALUES ", text)
return text
Base = declarative_base()
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
id2 = Column(Integer, primary_key=True)
data = Column(String)
bs = relationship("B")
class B(Base):
__tablename__ = "b"
id = Column(Integer, primary_key=True)
a_id = Column(Integer)
a_id2 = Column(Integer)
__table_args__ = (
ForeignKeyConstraint(["a_id", "a_id2"], ["a.id", "a.id2"]),
)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(id=1, id2=1, bs=[B(), B()]))
s.commit()
s.query(A).options(selectinload(A.bs)).all()
>
> Thanks,
> Steven James
>
>
> --
> 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/e7a3b368-d40e-4aba-84d5-10020fb05fe4%40googlegroups.com
>
> .
> 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 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
[sqlalchemy] Re: 'selectin' loading with composite keys on DB2 for i
Sorry... made a typo there... the desired syntax is: `SELECT * FROM table WHERE (table.a, table.b) IN (VALUES (?, ?), (?,?))` On Thursday, 27 June 2019 14:11:16 UTC-4, Steven James wrote: > > Currently, `selectin` loading with composite keys works for me on MySQL > and SQLite. The documentation states that it also works with Postgres. I'm > currently trying to get it working on DB2 (connecting to a DB2 for i, 7.2 > system. > > (the following assumes a table with the primary key consisting of two > columns: a and b) > > selectin loading currently emits: > `SELECT * FROM table WHERE (table.a, table.b) IN ((?, ?), (?, ?))` > > For this type of loading to work in DB2 (DB2 for i), the syntax needs to > be: > `SELECT * FROM table WHERE (table.a, table.b) IN VALUES (?, ?), (?,?)` > > Is there any way to implement this without a core change? I'm wondering if > I can override the normal operation of in_() using a custom dialect or > custom default comparator. > > Thanks, > Steven James > > -- 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/f318eb7e-82dc-4429-80e9-390406706eee%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] 'selectin' loading with composite keys on DB2 for i
Currently, `selectin` loading with composite keys works for me on MySQL and SQLite. The documentation states that it also works with Postgres. I'm currently trying to get it working on DB2 (connecting to a DB2 for i, 7.2 system. (the following assumes a table with the primary key consisting of two columns: a and b) selectin loading currently emits: `SELECT * FROM table WHERE (table.a, table.b) IN ((?, ?), (?, ?))` For this type of loading to work in DB2 (DB2 for i), the syntax needs to be: `SELECT * FROM table WHERE (table.a, table.b) IN VALUES (?, ?), (?,?)` Is there any way to implement this without a core change? I'm wondering if I can override the normal operation of in_() using a custom dialect or custom default comparator. Thanks, Steven James -- 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/e7a3b368-d40e-4aba-84d5-10020fb05fe4%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to have MySQL store TIMESTAMP but return timezone-aware datetime objects
On Thu, Jun 27, 2019, at 12:02 PM, Charles-Axel Dein wrote:
> Hi,
>
> I'm trying to have a deleted_at column on my records. I use MySQL and latest
> sqlalchemy as of writing. For historical reasons, I want to keep using the
> MySQL's TIMESTAMP columns.
>
> I would like to use timezone-aware datetime throughout my codebase.
> Everything in my DB is stored as UTC, so I don't really need to store the
> timezone. I just want to make sure the datetime is returned as a UTC-datetime.
>
> import pytz
> from sqlalchemy import Column, text, types
> from sqlalchemy.dialects.mysql import TIMESTAMP as M_TIMESTAMP
>
>
> # Fractional second precision
> FSP = 6
> TIMESTAMP = M_TIMESTAMP(fsp=FSP)
> CURRENT_TIMESTAMP = text("CURRENT_TIMESTAMP(%d)" % FSP)
>
>
> class TimezoneAwareTimestamp(types.TypeDecorator):
> """Ensure tz-aware timestamp are returned."""
>
>
> impl = TIMESTAMP
>
>
> def process_result_value(self, value, dialect):
> if not value:
> return None
> return value.replace(tzinfo=pytz.UTC)
>
>
> def DeletedAt():
> return Column("deleted_at", TimezoneAwareTimestamp, server_onupdate=text("0"),
> nullable=True)
>
>
> This is a pretty natural solution I came up with. Problem:
> TimezoneAwareTimestamp does not respect the `server_onupdate` attribute on
> creation. A table created with this DeletedAt column will show up as:
>
> deleted_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE
> CURRENT_TIMESTAMP(6)
So you need to instead be looking at the DDL that's being emitted and not the
"SHOW CREATE TABLE" which I assume is what's above.
"server_onupdate" does *not* generate any DDL, as there is no such thing as "ON
UPDATE" in SQL; this is a MySQL-specific extension that I believe only applies
to their TIMESTAMP datatype in the first place.
Support for MySQL's "ON UPDATE" phrase is not included as a first class feature
in SQLAlchemy right now and
https://github.com/sqlalchemy/sqlalchemy/issues/4652 seeks to add this
functionality. However a widely used workaround is to apply the "ON UPDATE"
inside the "server_default" field, which *is* part of standard SQL, e.g.
server_default=text("DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP") or whatever you
want it to be.
as for the nullable part, you've unfortunately found a bug, in that the
TypeDecorator is preventing it from detecting the special case nullability for
TIMESTAMP. It will be fixed in about 90 minutes
https://github.com/sqlalchemy/sqlalchemy/issues/4743 but a new SQLAlchemy
release isn't for a couple of weeks most likely. For now what I would do is add
an "ALTER TABLE" command in a textual way to your metadata, and you can make
whatever result you'd like occur here:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = DeletedAt()
event.listen(
A.__table__,
'after_create',
DDL(
"ALTER TABLE a MODIFY deleted_at TIMESTAMP NULL "
"DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP").execute_if(dialect="mysql")
)
the ALTER will fire off after the CREATE TABLE.
>
> instead of (replacing TimezoneAwareTimestamp with TIMESTAMP):
>
> deleted_at` timestamp(6) NULL DEFAULT NULL
>
> How can I have the custom type respect server_onupdate and nullable?
>
> Thanks,
>
> Charles
>
> --
> 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/983a49db-ae3f-422a-b996-b176f76c5aa7%40googlegroups.com
>
> .
> 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 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
Re: [sqlalchemy] How to inspect filters
Oh that's really interesting. Thank you for that. I'll definitely tuck that
away in my back pocket. My background is really heavy in raw SQL, and
meta-programming raw SQL is *awful.* Debugging sql that writes sql and
execs it is not fun. I'm not allowed to use sqlalchemy at work because no
one else on the team uses python, and we can't go around implementing stuff
in a way that only one person knows how to work on. But I really want to
get away from the SQL-only approach in my personal/side projects. For some
reason I often find myself really blocked when it comes to sqlalchemy.
Every time I approach my databases I just flip to sql mode and totally
forget that everything in sqla is just plain python, and I can treat it
that way. I see the obvious-level mapping between the two and just kind of
hit a block beyond that. I should probably sit down and read the source
code to try and get past the block, that way I'm not so surprised by answer
that Mike and people like you give me. Anyway, probably TMI. Cheers and
thanks!
On Thu, Jun 27, 2019 at 11:09 AM Jonathan Vanasco
wrote:
>
>
> On Wednesday, June 26, 2019 at 2:43:44 PM UTC-4, Andrew Martin wrote:
>>
>> That's very interesting, Jonathan. Could you show me a quick example of
>> that approach? I'm not sure I *need* to do that, but I think I would learn
>> about SQLAlchemy from such an example and trying to understand it.
>>
>
> One large project has an 'internal api' that tries to centralize the
> sqlalchemy interface.
>
> Let's say we're searching for a "user". I would create a dict for the
> data like this:
>
> query_metadata = {'requester': 'site-admin', # admin interface, user
> interface, user api, etc
> 'filters': {'Username=': 'foo', # just an
> internal notation
> },
>'query_data': {}, # information related to what is
> in the query as it is built
> }
>
>
> This payload is basically the same stuff you'd pass to as queryargs to one
> of your functions above. We essentially pass it to our version of your
> CRUD service which then acts on it to generate the query.
>
> We don't implement this approach on every query - just on a handful of
> queries that have intense logic with dozens of if/else statements and that
> connect to multiple "filters". Stashing this in an easily accessible
> manner has just been much easier than constantly trying to examine the
> query to act on it.
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/chGVkNwmKyQ/unsubscribe.
> To unsubscribe from this group and all its topics, 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/39a4a326-c384-4c91-909a-40d9c3acb323%40googlegroups.com
>
> .
> 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 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/CAOVGraLheEcKaoKuwZRUBRQEgGaQ5dG7UuvV2YuW5czhzFLvqg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to inspect filters
On Wednesday, June 26, 2019 at 2:43:44 PM UTC-4, Andrew Martin wrote:
>
> That's very interesting, Jonathan. Could you show me a quick example of
> that approach? I'm not sure I *need* to do that, but I think I would learn
> about SQLAlchemy from such an example and trying to understand it.
>
One large project has an 'internal api' that tries to centralize the
sqlalchemy interface.
Let's say we're searching for a "user". I would create a dict for the data
like this:
query_metadata = {'requester': 'site-admin', # admin interface, user
interface, user api, etc
'filters': {'Username=': 'foo', # just an internal
notation
},
'query_data': {}, # information related to what is
in the query as it is built
}
This payload is basically the same stuff you'd pass to as queryargs to one
of your functions above. We essentially pass it to our version of your
CRUD service which then acts on it to generate the query.
We don't implement this approach on every query - just on a handful of
queries that have intense logic with dozens of if/else statements and that
connect to multiple "filters". Stashing this in an easily accessible
manner has just been much easier than constantly trying to examine the
query to act on it.
--
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/39a4a326-c384-4c91-909a-40d9c3acb323%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How to have MySQL store TIMESTAMP but return timezone-aware datetime objects
Hi,
I'm trying to have a deleted_at column on my records. I use MySQL and
latest sqlalchemy as of writing. For historical reasons, I want to keep
using the MySQL's TIMESTAMP columns.
I would like to use timezone-aware datetime throughout my codebase.
Everything in my DB is stored as UTC, so I don't really need to store the
timezone. I just want to make sure the datetime is returned as a
UTC-datetime.
import pytz
from sqlalchemy import Column, text, types
from sqlalchemy.dialects.mysql import TIMESTAMP as M_TIMESTAMP
# Fractional second precision
FSP = 6
TIMESTAMP = M_TIMESTAMP(fsp=FSP)
CURRENT_TIMESTAMP = text("CURRENT_TIMESTAMP(%d)" % FSP)
class TimezoneAwareTimestamp(types.TypeDecorator):
"""Ensure tz-aware timestamp are returned."""
impl = TIMESTAMP
def process_result_value(self, value, dialect):
if not value:
return None
return value.replace(tzinfo=pytz.UTC)
def DeletedAt():
return Column("deleted_at", TimezoneAwareTimestamp, server_onupdate=text
("0"),
nullable=True)
This is a pretty natural solution I came up with. Problem:
TimezoneAwareTimestamp does not respect the `server_onupdate` attribute on
creation. A table created with this DeletedAt column will show up as:
deleted_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE
CURRENT_TIMESTAMP(6)
instead of (replacing TimezoneAwareTimestamp with TIMESTAMP):
deleted_at` timestamp(6) NULL DEFAULT NULL
How can I have the custom type respect server_onupdate and nullable?
Thanks,
Charles
--
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/983a49db-ae3f-422a-b996-b176f76c5aa7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Limiting Relationship Depths
On Wednesday, June 26, 2019 at 4:01:31 PM UTC-4, Michael P. McDonnell wrote: > > Awesome, thank you Jonathan. > > I know I've read that at least 3-4 times, but I think I've been staring at > the screen too much these days to actually read. I'll give it a go and let > you know how it goes! > SqlAlchemy doesn't fetch the relationship by default. You have to configure it to via 'lazy'; and tell it to fetch more things via dynamic loading options on the query. Staring at the docs a while is a good thing. Very shortly it will make sense -- i promise! And if you do things wrong, post your example here and we'll all help you figure it out. -- 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/8119016f-095d-49e2-8d8f-971d726ef6ca%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Where clause when using polymorphic_identity on base class
I see, thank you, Mike.
So, looks like I just wanted strange thing: having class to be distinct
from itself.
Thank you for clarifying!
Dmytro
вт, 25 черв. 2019 о 19:26 Mike Bayer пише:
>
>
> On Fri, Jun 21, 2019, at 7:22 AM, Dmytro Starosud wrote:
>
> Base class A1 contains polymorphic_identity (along with polymorphic_on),
> but Query(A1)doesn't produce where clause, whereas Query(A2) (where A2 is
> subclass of A1 with its own polymorphic_identity) does.
>
> Tried looking in docs with no success. I think I am just missing something.
>
> from sqlalchemy import Column, Integer, Stringfrom sqlalchemy.ext.declarative
> import declarative_basefrom sqlalchemy.orm import Query, configure_mappers
> Base = declarative_base()
>
> class A1(Base):
> __tablename__ = 'a1'
> id = Column(Integer, primary_key=True)
> poly_on = Column(String, nullable=False)
> __mapper_args__ = {
> 'polymorphic_on': poly_on,
> 'polymorphic_identity': 'a1',
> }
>
> class A2(A1):
> __mapper_args__ = {
> 'polymorphic_identity': 'a2',
> }
>
>
> configure_mappers()
> print(Query(A1))# SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on# FROM a1
> print(Query(A2))# SELECT a1.id AS a1_id, a1.poly_on AS a1_poly_on# FROM a1#
> WHERE a1.poly_on IN (:poly_on_1)
>
> I would expect WHERE clauses in both cases.
>
>
>
> what would the first WHERE clause be limiting on ?
>
> an A2 is an A1, so if you are querying for all A1 objects, you should get
> those that are A2 as well.
>
> if you want to affect how this works you can use the before_compile event
> to add whatever filters you'd like, see the example at
> https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery
>
>
>
> Originally posted here .
>
>
> --
> 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/941f0c3e-e541-4554-8b4a-570c28afec64%40googlegroups.com
>
> .
> 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 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/ce1721a8-0a31-497f-8969-c79b8f786541%40www.fastmail.com
>
> .
> 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 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/CANC2W%2BAdYyKrxxQ_V60ZC7m1wrS%2B-CFDq9vq2pVxRPM5Y_GakQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
