Re: [sqlalchemy] Count aggregation function with distinct over multiple columns
anything that looks like WORDS(, , , ...) that goes where SQL expressions go you can do with func. just use func.DISTINCT(col1, col2). On Fri, Dec 14, 2018 at 10:47 PM Pavel Pristupa wrote: > > I wonder if there's a way to build up the following SQL query for PostgreSQL > in SQLAlchemy using Session.query(): > > SELECT group_id, COUNT(DISTINCT(column1, column2)) FROM entities GROUP BY > group_id; > > I tried this one: > > Session.query(Entity.group_id, sa.func.count(sa.distinct(Entity.column1, > Entity.column2))).group_by(Entity.group_id) > > But sa.distinct doesn't support multiple columns. The Query's method > .distinct() supports multiple columns, but I have no idea how to use it for > this particular case. > > -- > 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. -- 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.
[sqlalchemy] Count aggregation function with distinct over multiple columns
I wonder if there's a way to build up the following SQL query for PostgreSQL in SQLAlchemy using Session.query(): SELECT group_id, COUNT(DISTINCT(column1, column2)) FROM entities GROUP BY group_id; I tried this one: Session.query(Entity.group_id, sa.func.count(sa.distinct(Entity.column1, Entity.column2))).group_by(Entity.group_id) But sa.distinct doesn't support multiple columns. The Query's method .distinct() supports multiple columns, but I have no idea how to use it for this particular case. -- 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.
Re: [sqlalchemy] Automapping tables with composite primary key
the classes will have many-to-many relationships set up that use this
table, see
https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#many-to-many-relationships
On Fri, Dec 14, 2018 at 5:28 PM 'irphinx' via sqlalchemy
wrote:
>
>
> I am having problems getting a list of table classes
> using automap_base when the table has a composite primary key.
> Not sure if this is the intended behaviour.
>
> When I try to get the list tables classes using automap_base,
> I only get tables 'a' and 'b' below and not table 'ab'.
>
> This is using sqlalchemy 1.2.15.
> This example was tested with sqlite3 but I get the same issue with Postgres
>
> Here is the schema:
>
> CREATE TABLE a (
> id INTEGER,
> PRIMARY KEY(id)
> );
>
>
> CREATE TABLE b (
> id INTEGER,
> PRIMARY KEY(id)
> );
>
>
> CREATE TABLE ab (
> a_id INTEGER NOT NULL,
> b_id INTEGER NOT NULL,
> FOREIGN KEY(a_id) REFERENCES a(id),
> FOREIGN KEY(b_id) REFERENCES b(id),
> PRIMARY KEY (a_id, b_id)
> );
>
>
>
> Base = automap_base()
> engine = create_engine("sqlite:///mydb.db")
> Base.prepare(engine, reflect=True)
> print ('classes {}'.format(Base.classes.keys()))
>
>
> --
> 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.
--
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.
[sqlalchemy] Automapping tables with composite primary key
I am having problems getting a list of table classes
using automap_base when the table has a composite primary key.
Not sure if this is the intended behaviour.
When I try to get the list tables classes using automap_base,
I only get tables 'a' and 'b' below and not table 'ab'.
This is using sqlalchemy 1.2.15.
This example was tested with sqlite3 but I get the same issue with Postgres
Here is the schema:
CREATE TABLE a (
id INTEGER,
PRIMARY KEY(id)
);
CREATE TABLE b (
id INTEGER,
PRIMARY KEY(id)
);
CREATE TABLE ab (
a_id INTEGER NOT NULL,
b_id INTEGER NOT NULL,
FOREIGN KEY(a_id) REFERENCES a(id),
FOREIGN KEY(b_id) REFERENCES b(id),
PRIMARY KEY (a_id, b_id)
);
Base = automap_base()
engine = create_engine("sqlite:///mydb.db")
Base.prepare(engine, reflect=True)
print ('classes {}'.format(Base.classes.keys()))
--
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.
Re: [sqlalchemy] General on set event handler
On Fri, Dec 14, 2018 at 4:58 PM Daniel Leon wrote:
>
> I want to be able to refresh the object immediately before setting its
> attribute and commit immediately after. I notice that the set event is before
> set. Is there an after set event?
there's not an "after" event but you can skip all these events
entirely and override __setattr__() if you wanted to, that's still
"normal" here:
from sqlalchemy import Integer, Column, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import object_session, Session
Base = declarative_base()
class SetHandler(object):
def __setattr__(self, key, value):
print("before set: %s" % value)
super(SetHandler, self).__setattr__(key, value)
print("after set: %s" % value)
sess = object_session(self)
if sess is not None:
print("flushing...")
sess.flush() # this would be a little nutty to do
# for every attribute change but it's possible
class A(SetHandler, Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
a = Column(Integer)
b = Column(Integer)
c = Column(Integer)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
a1 = A(a=1, b=2, c=3)
s = Session(e)
s.add(a1)
a1.b = 5
a1.c = 10
s.commit()
>
> --
> 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.
--
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.
Re: [sqlalchemy] General on set event handler
I want to be able to refresh the object immediately before setting its attribute and commit immediately after. I notice that the set event is before set. Is there an after set event? -- 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.
Re: many to many relation with foreign key to composite primary key
Hi Mike,
Thank you for the time you gave me.
The other problem I had to do was that my models were distributed in
different files. Alembic could not access their class and therefore could
not auto-generate his file.
Best regards,
Le jeu. 13 déc. 2018 à 19:48, Mike Bayer a
écrit :
> I can't reproduce any problem.
>
> Filling out your mappings from your first email, these look like:
>
> from sqlalchemy import *
> from sqlalchemy.orm import relationship
> from datetime import datetime
>
> from sqlalchemy.ext.declarative import declarative_base as db
> target_metadata = MetaData()
>
> DeclarativeBase = db(metadata=target_metadata)
>
> users_roles = Table(
> 'users_roles', target_metadata,
> Column('users_id', ForeignKey('users.id'), primary_key=True),
> Column('roles_id', ForeignKey('roles.id'), primary_key=True),
> )
>
> roles_permissions = Table(
> 'roles_permissions', target_metadata,
> Column('permissions_id', ForeignKey('permissions.id'),
> primary_key=True),
> Column('roles_id', ForeignKey('roles.id'), primary_key=True),
> )
> users_permissions = Table(
> 'users_permissions', target_metadata,
> Column('users_id', ForeignKey('users.id'), primary_key=True),
> Column('permissions_id', ForeignKey('permissions.id'),
> primary_key=True),
> )
>
>
> class User(DeclarativeBase):
> __tablename__ = 'users'
>
> id = Column(BigInteger, primary_key=True)
> email = Column(String(100), unique=True, nullable=False)
> name = Column(String(100), nullable=False)
> hashed_password = Column(String(100), nullable=False)
> is_admin = Column(BOOLEAN, default=False)
> is_active = Column(BOOLEAN, default=True)
> created = Column(DateTime, default=datetime.now)
> modified = Column(DateTime, default=datetime.now,
> onupdate=datetime.now)
> roles = relationship('Role', secondary=users_roles,
> back_populates='users', cascade="all, delete-orphan")
> permissions = relationship('Permission',
> secondary=users_permissions, back_populates='users',
>cascade="all, delete-orphan")
>
> def __repr__(self):
> return "" %
> (self.name, self.email, self.hashed_password)
>
>
> class Role(DeclarativeBase):
> __tablename__ = 'roles'
>
> id = Column(BigInteger, primary_key=True)
> name = Column(String(100), unique=True, nullable=False)
> users = relationship('User', secondary=users_roles,
> back_populates='roles',cascade="all, delete-orphan")
> permissions = relationship('Permission',
> secondary=roles_permissions, back_populates='roles',
>cascade="all, delete-orphan")
>
> def __repr__(self):
> return "" % self.name
>
>
> class Permission(DeclarativeBase):
> __tablename__ = 'permissions'
>
> id = Column(BigInteger, primary_key=True)
> name = Column(String(100), unique=True, nullable=False)
> description = Column(String(255))
> users = relationship('User', secondary=users_permissions,
> back_populates='permissions',
> cascade="all, delete-orphan")
> roles = relationship('Role', secondary=roles_permissions,
> back_populates='permissions',
> cascade="all, delete-orphan")
>
> def __repr__(self):
> return "" %
> (self.name, self.description)
>
>
> then i run autogenerate, migrations are generated as:
>
> def upgrade():
> # ### commands auto generated by Alembic - please adjust! ###
> op.create_table('permissions',
> sa.Column('id', sa.BigInteger(), nullable=False),
> sa.Column('name', sa.String(length=100), nullable=False),
> sa.Column('description', sa.String(length=255), nullable=True),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('name')
> )
> op.create_table('roles',
> sa.Column('id', sa.BigInteger(), nullable=False),
> sa.Column('name', sa.String(length=100), nullable=False),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('name')
> )
> op.create_table('users',
> sa.Column('id', sa.BigInteger(), nullable=False),
> sa.Column('email', sa.String(length=100), nullable=False),
> sa.Column('name', sa.String(length=100), nullable=False),
> sa.Column('hashed_password', sa.String(length=100), nullable=False),
> sa.Column('is_admin', sa.BOOLEAN(), nullable=True),
> sa.Column('is_active', sa.BOOLEAN(), nullable=True),
> sa.Column('created', sa.DateTime(), nullable=True),
> sa.Column('modified', sa.DateTime(), nullable=True),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('email')
> )
> op.create_table('roles_permissions',
> sa.Column('permissions_id', sa.BigInteger(), nullable=False),
> sa.Column('roles_id', sa.BigInteger(), nullable=False),
> sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
> sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ),
> sa.PrimaryKeyConstraint('permissions_id', 'roles_id')
> )
>
Re: [sqlalchemy] General on set event handler
On Thu, Dec 13, 2018 at 2:59 PM Daniel Leon wrote: > > I know that you can define an on set event handler for an attribute using the > decorator @event.listens_for(Class.Attribute, 'set'). I'd like a single event > handler to handle every attribute on every class. How can I do this? > > In the event handler I want to refresh the object before setting the value > and commit after. I know this is strange, but I'm converting a project from > SQLObject and to make the conversion as seamless as possible I want to make > SQLAlchemy mimic SQLObject's behaviour. the attribute event handlers don't have a "global" hook like this, so there is a recipe at https://docs.sqlalchemy.org/en/latest/_modules/examples/custom_attributes/listen_for_events.html where another event, "attribute_instrument", is used to intercept where an ORM attribute is set up, so then you can apply listeners to 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. > 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. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: __init__ not called for session.query items
My bad, answer on here https://docs.sqlalchemy.org/en/latest/orm/constructors.html#constructors-and-object-initialization. Delete this theme, please! -- 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.
[sqlalchemy] __init__ not called for session.query items
Is it normal? How i can write methods to loading objects? -- 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.
[sqlalchemy] Re: can't read the change with mysql in time
Did you close and open connection before check on second service? https://www.postgresql.org/docs/10/explicit-locking.html -- 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.
