Re: [sqlalchemy] SQLAlchemy Many-to-Many Users, Groups, Roles

[Leslie Luyt]

This is fantastic, I didn't realise you could define a table using a sql 
expression. I am always amazed by how amazing and configurable SQLAlchemy 
is.

Thank you so much for the help with this.

PS. I am a huge fan. Please give Dilly a pat for me =).

On Saturday, October 21, 2017 at 5:14:28 AM UTC+2, Mike Bayer wrote:
>
> note I inadvertently used a previous form for the first GroupProfile 
> example.   The two forms are: 
>
>
> class GroupProfile(Base): 
> __table__ = ( 
> select([Group.group_id, Profile]). 
> select_from(join(Group, ProfileGroupRole).join(Profile)). 
> distinct().alias() 
> ) 
> ... 
>
> and the potentially more efficient one: 
>
> distinct_profile_groups = select( 
> [ProfileGroupRole.group_id, ProfileGroupRole.profile_id]).\ 
> distinct().alias() 
>
>
> class GroupProfile(Base): 
> # the GroupProfile itself is then based on Group/Profile rows 
> # joined to our distinct group_id/profile_id pairs 
> __table__ = ( 
> select([Group.group_id, Profile]). 
> select_from(join(Group, distinct_profile_groups).join(Profile)). 
> alias() 
> ) 
>
>
>
> The second form applies DISTINCT to only two columns, whereas the 
> first applies DISTINCT to all columns in Profile, which can be 
> wasteful.   A third form could use Postgresql DISTINCT ON to limit the 
> scope of the DISTINCT without using a second subquery. 
>
>
>

-- 
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] SQLAlchemy Many-to-Many Users, Groups, Roles

[Mike Bayer]

note I inadvertently used a previous form for the first GroupProfile
example.   The two forms are:


class GroupProfile(Base):
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, ProfileGroupRole).join(Profile)).
distinct().alias()
)
...

and the potentially more efficient one:

distinct_profile_groups = select(
[ProfileGroupRole.group_id, ProfileGroupRole.profile_id]).\
distinct().alias()


class GroupProfile(Base):
# the GroupProfile itself is then based on Group/Profile rows
# joined to our distinct group_id/profile_id pairs
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, distinct_profile_groups).join(Profile)).
alias()
)



The second form applies DISTINCT to only two columns, whereas the
first applies DISTINCT to all columns in Profile, which can be
wasteful.   A third form could use Postgresql DISTINCT ON to limit the
scope of the DISTINCT without using a second subquery.



On Fri, Oct 20, 2017 at 11:11 PM, Mike Bayer  wrote:
> On Fri, Oct 20, 2017 at 4:52 PM, Leslie Luyt  
> wrote:
>> Here is my example:
>>
>> class Role(Base):
>> __tablename__ = 'role'
>> role_id = Column(BigInteger, primary_key=True)
>> role_name = Column(String, nullable=False)
>> role_description = Column(String)
>>
>>
>> class Group(Base):
>> __tablename__ = 'group'
>> group_id = Column(BigInteger, primary_key=True)
>> group_name = Column(String, nullable=False)
>> group_description = Column(String)
>>
>> profiles = association_proxy('_profile_group_roles', 'profile')
>>
>>
>> class Profile(Base):
>> __tablename__ = 'profile'
>> profile_id = Column(BigInteger, primary_key=True)
>> profile_username = Column(String, nullable=False, unique=True)
>>
>> groups = association_proxy('_profile_group_roles', 'group')
>> roles = association_proxy('_profile_group_roles', 'role')
>>
>>
>> class ProfileGroupRole(Base):
>> __tablename__ = 'profile_group_role_assoc'
>> profile_id = Column(BigInteger, ForeignKey('profile.profile_id'),
>> primary_key=True)
>> group_id = Column(BigInteger, ForeignKey('group.group_id'),
>> primary_key=True)
>> role_id = Column(BigInteger, ForeignKey('role.role_id'),
>> primary_key=True)
>>
>> group = relationship(Group, backref=backref('_profile_group_roles',
>> cascade='all, delete-orphan'))
>> role = relationship(Role)
>> profile = relationship(Profile, backref=backref('_profile_group_roles',
>> cascade='all, delete-orphan'))
>>
>>
>> Example data:
>> s = db.connect(engine_config)
>> profile1 = db.Profile(profile_username='test_user')
>> group1 = db.Group(group_name='group1', group_description='')
>> role1 = db.Role(role_name='role1')
>> prg1 = db.ProfileGroupRole(profile=profile1, group=group1,
>> role=role1)
>> group2 = db.Group(group_name='group2', group_description='')
>> role2 = db.Role(role_name='role2')
>> prg2 = db.ProfileGroupRole(profile=profile1, group=group2,
>> role=role1)
>> prg3 = db.ProfileGroupRole(profile=profile1, group=group2,
>> role=role2)
>> s.add_all([prg1, prg2, prg3])
>> s.commit()
>>
>> Querying:
>> db.Group.query.filter(db.Group.group_name ==
>> 'group1').one().profiles[0].roles
>>
>> produces:
>> [Role{'role_id': 4, 'role_name': 'role1', 'role_description': None},
>> Role{'role_id': 4, 'role_name': 'role1', 'role_description': None},
>> Role{'role_id': 5, 'role_name': 'role2', 'role_description': None}]
>>
>> and now what I expected to get back was just one the one role associated
>> with 'group1':
>> [Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}]
>
> OK so this is a little awkward if you truly want
> "group.profiles[0].roles" to be local to the group. It means you don't
> really want Group.profiles to be a list of Profile objects, which is
> an object that can be associated with multiple groups.  You want a
> "view" object that is a GroupProfile, a Profile that's associated with
> a specific group, which then has one or more Roles set up for that
> Group/Profile association.   So implied here is that the join across
> the ProfileGroupRole association is between GroupProfile and Role - as
> far as how to link Group and Profile together we will need to produce
> distinct pairs by joining against a DISTINCT of ProfileGroupRole.
> This is a more complicated technique.
>
> I'm going off your original email that indicates you can't change the
> table structure at all here.   So to work all these requirements
> together we can create a GroupProfile "view" object as:
>
> class GroupProfile(Base):
> __table__ = (
> select([Group.group_id, Profile]).
> select_from(join(Group, ProfileGroupRole).join(Profile)).
> distinct().alias()
> )
>
> group = relationship(
> Group,
> 

Re: [sqlalchemy] SQLAlchemy Many-to-Many Users, Groups, Roles

[Mike Bayer]

On Fri, Oct 20, 2017 at 4:52 PM, Leslie Luyt  wrote:
> Here is my example:
>
> class Role(Base):
> __tablename__ = 'role'
> role_id = Column(BigInteger, primary_key=True)
> role_name = Column(String, nullable=False)
> role_description = Column(String)
>
>
> class Group(Base):
> __tablename__ = 'group'
> group_id = Column(BigInteger, primary_key=True)
> group_name = Column(String, nullable=False)
> group_description = Column(String)
>
> profiles = association_proxy('_profile_group_roles', 'profile')
>
>
> class Profile(Base):
> __tablename__ = 'profile'
> profile_id = Column(BigInteger, primary_key=True)
> profile_username = Column(String, nullable=False, unique=True)
>
> groups = association_proxy('_profile_group_roles', 'group')
> roles = association_proxy('_profile_group_roles', 'role')
>
>
> class ProfileGroupRole(Base):
> __tablename__ = 'profile_group_role_assoc'
> profile_id = Column(BigInteger, ForeignKey('profile.profile_id'),
> primary_key=True)
> group_id = Column(BigInteger, ForeignKey('group.group_id'),
> primary_key=True)
> role_id = Column(BigInteger, ForeignKey('role.role_id'),
> primary_key=True)
>
> group = relationship(Group, backref=backref('_profile_group_roles',
> cascade='all, delete-orphan'))
> role = relationship(Role)
> profile = relationship(Profile, backref=backref('_profile_group_roles',
> cascade='all, delete-orphan'))
>
>
> Example data:
> s = db.connect(engine_config)
> profile1 = db.Profile(profile_username='test_user')
> group1 = db.Group(group_name='group1', group_description='')
> role1 = db.Role(role_name='role1')
> prg1 = db.ProfileGroupRole(profile=profile1, group=group1,
> role=role1)
> group2 = db.Group(group_name='group2', group_description='')
> role2 = db.Role(role_name='role2')
> prg2 = db.ProfileGroupRole(profile=profile1, group=group2,
> role=role1)
> prg3 = db.ProfileGroupRole(profile=profile1, group=group2,
> role=role2)
> s.add_all([prg1, prg2, prg3])
> s.commit()
>
> Querying:
> db.Group.query.filter(db.Group.group_name ==
> 'group1').one().profiles[0].roles
>
> produces:
> [Role{'role_id': 4, 'role_name': 'role1', 'role_description': None},
> Role{'role_id': 4, 'role_name': 'role1', 'role_description': None},
> Role{'role_id': 5, 'role_name': 'role2', 'role_description': None}]
>
> and now what I expected to get back was just one the one role associated
> with 'group1':
> [Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}]

OK so this is a little awkward if you truly want
"group.profiles[0].roles" to be local to the group. It means you don't
really want Group.profiles to be a list of Profile objects, which is
an object that can be associated with multiple groups.  You want a
"view" object that is a GroupProfile, a Profile that's associated with
a specific group, which then has one or more Roles set up for that
Group/Profile association.   So implied here is that the join across
the ProfileGroupRole association is between GroupProfile and Role - as
far as how to link Group and Profile together we will need to produce
distinct pairs by joining against a DISTINCT of ProfileGroupRole.
This is a more complicated technique.

I'm going off your original email that indicates you can't change the
table structure at all here.   So to work all these requirements
together we can create a GroupProfile "view" object as:

class GroupProfile(Base):
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, ProfileGroupRole).join(Profile)).
distinct().alias()
)

group = relationship(
Group,
backref=backref("profiles", viewonly=True,
order_by=__table__.c.profile_id),
primaryjoin=foreign(__table__.c.group_id) == Group.group_id,
viewonly=True)

roles = relationship(
Role,
secondary=ProfileGroupRole.__table__,
primaryjoin=and_(
__table__.c.group_id == ProfileGroupRole.group_id,
__table__.c.profile_id == ProfileGroupRole.profile_id),
viewonly=True,
collection_class=set,
lazy="joined", innerjoin=True)

Just so you know, that mapping is using several advanced techniques to
work the three-way association table into a structure that behaves
more like individual two-way associations (however note this is
strictly read-only stuff, it won't persist data as designed).  The map
to a SELECT sets up that we can get unique Group/Profile pairs
regardless of how many ProfileGroupRole rows link them together.   The
GroupProfile.role relationship brings ProfileGroupRole in as an
association table a second time to produce individual rows per Role,
and it also uses joined eager loading with innerjoin=True to emit
fewer queries; we can use innerjoin because there are definitely Role
rows present.


Below 

Re: [sqlalchemy] SQLAlchemy Many-to-Many Users, Groups, Roles

[Leslie Luyt]

Here is my example:

class Role(Base):
__tablename__ = 'role'
role_id = Column(BigInteger, primary_key=True)
role_name = Column(String, nullable=False)
role_description = Column(String)


class Group(Base):
__tablename__ = 'group'
group_id = Column(BigInteger, primary_key=True)
group_name = Column(String, nullable=False)
group_description = Column(String)

profiles = association_proxy('_profile_group_roles', 'profile')


class Profile(Base):
__tablename__ = 'profile'
profile_id = Column(BigInteger, primary_key=True)
profile_username = Column(String, nullable=False, unique=True)

groups = association_proxy('_profile_group_roles', 'group')
roles = association_proxy('_profile_group_roles', 'role')


class ProfileGroupRole(Base):
__tablename__ = 'profile_group_role_assoc'
profile_id = Column(BigInteger, ForeignKey('profile.profile_id'), 
primary_key=True)
group_id = Column(BigInteger, ForeignKey('group.group_id'), 
primary_key=True)
role_id = Column(BigInteger, ForeignKey('role.role_id'), 
primary_key=True)

group = relationship(Group, backref=backref('_profile_group_roles', 
cascade='all, delete-orphan'))
role = relationship(Role)
profile = relationship(Profile, backref=backref('_profile_group_roles', 
cascade='all, delete-orphan'))


Example data:
s = db.connect(engine_config)
profile1 = db.Profile(profile_username='test_user')
group1 = db.Group(group_name='group1', group_description='')
role1 = db.Role(role_name='role1')
prg1 = db.ProfileGroupRole(profile=profile1, group=group1, 
role=role1)
group2 = db.Group(group_name='group2', group_description='')
role2 = db.Role(role_name='role2')
prg2 = db.ProfileGroupRole(profile=profile1, group=group2, 
role=role1)
prg3 = db.ProfileGroupRole(profile=profile1, group=group2, 
role=role2)
s.add_all([prg1, prg2, prg3])
s.commit()

Querying:
db.Group.query.filter(db.Group.group_name == 
'group1').one().profiles[0].roles

produces:
[Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}, 
Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}, 
Role{'role_id': 5, 'role_name': 'role2', 'role_description': None}]

and now what I expected to get back was just one the one role associated 
with 'group1':
[Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}]

-- 
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: SQLAlchemy : declarative_base and metaclass conflict

[Sven]

Exactly what I was looking for and it works (even applied to my project).

I tried so many things these last days and the solution now looks so simple.

Thank you very much !

-- 
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] SQLAlchemy : declarative_base and metaclass conflict

[Mike Bayer]

On Fri, Oct 20, 2017 at 3:15 PM, Mike Bayer  wrote:
> On Fri, Oct 20, 2017 at 2:40 PM, Sven  wrote:
>> Thank you for your answer.
>>
>> I'm sorry, I have not been clear enough regarding the "__abstract__ = True".
>>
>> I suppose that I will have to add this to a lot of classes for the following
>> reasons :
>> 1) because it will allow me to persist the classes one by one and still be
>> able to run and test the project (i will not have all the errors because
>> there is no table name and no primary key). So, I would have to delete the
>> "__abstract__" when I begin to work on the persistence of a new class.
>> 2) because I thought that the best solution in this case is to map only the
>> concrete classes. So, in my example, I would have to map "Player" and "NPC",
>> but not "Character". So only the classes at the bottom of the hierarchy
>> would have to be mapped. That's still a lot of classes but probably easier
>> to implement.
>
> OK, so I see you are looking to add something like "Mappable" or
> similar only to those classes that are concrete.
>
> how about this?
>
> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
> from sqlalchemy import Column, Integer
>
>
> class MetaBase(type):
>
> def __init__(cls, nom, bases, contenu):
> type.__init__(cls, nom, bases, contenu)
> print("Init MetaBase")
> pass
>
>
> class DeclMetaBase(MetaBase, DeclarativeMeta):
>
> def __init__(cls, nom, bases, contenu):
> super(MetaBase, cls).__init__(nom, bases, contenu)
> print("Init DeclMetaBase")

actually like this:

class DeclMetaBase(MetaBase, DeclarativeMeta):

def __init__(cls, nom, bases, contenu):
MetaBase.__init__(cls, nom, bases, contenu)
DeclarativeMeta.__init__(cls, nom, bases, contenu)
print("Init DeclMetaBase")





>
> Base = declarative_base(metaclass=DeclMetaBase)
>
>
> class Stockable(metaclass=MetaBase):
>
> def __init__(self):
> print("Init Stockable")
>
>
> class Character(Stockable, Base):
>
> __tablename__ = 'characters'
> id = Column(Integer, primary_key=True)
>
> def __init__(self, name):
> self.name = name
> print("Init character")
>
> print(repr(Character.__table__))
> jean = Character("Jean")
> print(jean.name)
>
>
>
>>
>> But I have to say that this is not absolutely clear for me for now. This is
>> the first time I use SQLAlchemy. Do you think that this method is possible
>> and is the right way to proceed ?
>
>
>
> Whether or not it "works" involves mostly the database schema that
> gets created and how reasonable this schema is from a relational /
> database performance / complexity perspective.   This depends a lot on
> how these class hierarchies are organized and how they interact with
> each other, so it's difficult to say.
>
>
>
>>
>> Le vendredi 20 octobre 2017 20:02:40 UTC+2, Mike Bayer a écrit :
>>>
>>>
>>> CONTINUING !  sorry
>>>
>>>
>>> On Fri, Oct 20, 2017 at 11:55 AM, Sven Dumay  wrote:


 I tried other things and I found the following solution :

 from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
 from sqlalchemy import Column, Integer

 class MetaBase(DeclarativeMeta):

 def __init__(cls, nom, bases, contenu):
 super(MetaBase, cls).__init__(nom, bases, contenu)
 print("Init MetaBase")

 Base = declarative_base(metaclass = MetaBase)

 class Stockable(Base):

 __abstract__ = True

 def __init__(self):
 print("Init Stockable")

 class Character(Stockable):

 __tablename__ = 'characters'
 id = Column(Integer, primary_key=True)

 def __init__(self, name):
 self.name = name
 print("Init character")


 jean = Character("Jean")
 print(jean.name)


>>>
>>> this seems like roughly the correct approach.
>>>

 It seems to work. I get the following result :

 >>>
 Init MetaBase
 Init MetaBase
 Init MetaBase
 Init compte
 Jean
 >>>

 However, the problem with this method is that I have to add "__abstract__
 = True" to every class which is inherited by Stockable... so, about 400
 classes.
>>>
>>>
>>> I don't see why that is.  If these classes are mapped to tables (which, if
>>> they are persisted, they are), then there is no reason to add
>>> "__abstract__".As in my previous email, how these 400 classes link
>>> to tables is what needs to be answered and then we can formulate the correct
>>> calling style.
>>>
>>>

 It is not very clean. Is it possible to avoid that by using something
 similar to my first code ? It would be great !

 Thank you very much.

 Sven

 --
 SQLAlchemy -
 The Python SQL Toolkit and Object Relational Mapper

 

Re: [sqlalchemy] SQLAlchemy : declarative_base and metaclass conflict

[Mike Bayer]

On Fri, Oct 20, 2017 at 2:40 PM, Sven  wrote:
> Thank you for your answer.
>
> I'm sorry, I have not been clear enough regarding the "__abstract__ = True".
>
> I suppose that I will have to add this to a lot of classes for the following
> reasons :
> 1) because it will allow me to persist the classes one by one and still be
> able to run and test the project (i will not have all the errors because
> there is no table name and no primary key). So, I would have to delete the
> "__abstract__" when I begin to work on the persistence of a new class.
> 2) because I thought that the best solution in this case is to map only the
> concrete classes. So, in my example, I would have to map "Player" and "NPC",
> but not "Character". So only the classes at the bottom of the hierarchy
> would have to be mapped. That's still a lot of classes but probably easier
> to implement.

OK, so I see you are looking to add something like "Mappable" or
similar only to those classes that are concrete.

how about this?

from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy import Column, Integer


class MetaBase(type):

def __init__(cls, nom, bases, contenu):
type.__init__(cls, nom, bases, contenu)
print("Init MetaBase")
pass


class DeclMetaBase(MetaBase, DeclarativeMeta):

def __init__(cls, nom, bases, contenu):
super(MetaBase, cls).__init__(nom, bases, contenu)
print("Init DeclMetaBase")

Base = declarative_base(metaclass=DeclMetaBase)


class Stockable(metaclass=MetaBase):

def __init__(self):
print("Init Stockable")


class Character(Stockable, Base):

__tablename__ = 'characters'
id = Column(Integer, primary_key=True)

def __init__(self, name):
self.name = name
print("Init character")

print(repr(Character.__table__))
jean = Character("Jean")
print(jean.name)



>
> But I have to say that this is not absolutely clear for me for now. This is
> the first time I use SQLAlchemy. Do you think that this method is possible
> and is the right way to proceed ?



Whether or not it "works" involves mostly the database schema that
gets created and how reasonable this schema is from a relational /
database performance / complexity perspective.   This depends a lot on
how these class hierarchies are organized and how they interact with
each other, so it's difficult to say.



>
> Le vendredi 20 octobre 2017 20:02:40 UTC+2, Mike Bayer a écrit :
>>
>>
>> CONTINUING !  sorry
>>
>>
>> On Fri, Oct 20, 2017 at 11:55 AM, Sven Dumay  wrote:
>>>
>>>
>>> I tried other things and I found the following solution :
>>>
>>> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
>>> from sqlalchemy import Column, Integer
>>>
>>> class MetaBase(DeclarativeMeta):
>>>
>>> def __init__(cls, nom, bases, contenu):
>>> super(MetaBase, cls).__init__(nom, bases, contenu)
>>> print("Init MetaBase")
>>>
>>> Base = declarative_base(metaclass = MetaBase)
>>>
>>> class Stockable(Base):
>>>
>>> __abstract__ = True
>>>
>>> def __init__(self):
>>> print("Init Stockable")
>>>
>>> class Character(Stockable):
>>>
>>> __tablename__ = 'characters'
>>> id = Column(Integer, primary_key=True)
>>>
>>> def __init__(self, name):
>>> self.name = name
>>> print("Init character")
>>>
>>>
>>> jean = Character("Jean")
>>> print(jean.name)
>>>
>>>
>>
>> this seems like roughly the correct approach.
>>
>>>
>>> It seems to work. I get the following result :
>>>
>>> >>>
>>> Init MetaBase
>>> Init MetaBase
>>> Init MetaBase
>>> Init compte
>>> Jean
>>> >>>
>>>
>>> However, the problem with this method is that I have to add "__abstract__
>>> = True" to every class which is inherited by Stockable... so, about 400
>>> classes.
>>
>>
>> I don't see why that is.  If these classes are mapped to tables (which, if
>> they are persisted, they are), then there is no reason to add
>> "__abstract__".As in my previous email, how these 400 classes link
>> to tables is what needs to be answered and then we can formulate the correct
>> calling style.
>>
>>
>>>
>>> It is not very clean. Is it possible to avoid that by using something
>>> similar to my first code ? It would be great !
>>>
>>> Thank you very much.
>>>
>>> Sven
>>>
>>> --
>>> 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+...@googlegroups.com.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at 

Re: [sqlalchemy] SQLAlchemy : declarative_base and metaclass conflict

[Sven]

Thank you for your answer.

I'm sorry, I have not been clear enough regarding the "__abstract__ = True".

I suppose that I will have to add this to a lot of classes for the 
following reasons :
1) because it will allow me to persist the classes one by one and still be 
able to run and test the project (i will not have all the errors because 
there is no table name and no primary key). So, I would have to delete the 
"__abstract__" when I begin to work on the persistence of a new class.
2) because I thought that the best solution in this case is to map only the 
concrete classes. So, in my example, I would have to map "Player" and 
"NPC", but not "Character". So only the classes at the bottom of the 
hierarchy would have to be mapped. That's still a lot of classes but 
probably easier to implement.

But I have to say that this is not absolutely clear for me for now. This is 
the first time I use SQLAlchemy. Do you think that this method is possible 
and is the right way to proceed ?

Le vendredi 20 octobre 2017 20:02:40 UTC+2, Mike Bayer a écrit :
>
>
> CONTINUING !  sorry
>
>
> On Fri, Oct 20, 2017 at 11:55 AM, Sven Dumay  > wrote:
>
>>
>> *I tried other things and I found the following solution :*
>>
>> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
>> from sqlalchemy import Column, Integer
>>
>> class MetaBase(DeclarativeMeta):
>>
>> def __init__(cls, nom, bases, contenu):
>> super(MetaBase, cls).__init__(nom, bases, contenu)
>> print("Init MetaBase")
>>
>> Base = declarative_base(metaclass = MetaBase)
>>
>> class Stockable(Base):
>>
>> __abstract__ = True
>>
>> def __init__(self):
>> print("Init Stockable")
>>
>> class Character(Stockable):
>>
>> __tablename__ = 'characters'
>> id = Column(Integer, primary_key=True)
>>  
>> def __init__(self, name):
>> self.name = name
>> print("Init character")
>>
>>
>> jean = Character("Jean")
>> print(jean.name)
>>
>>
>>
> this seems like roughly the correct approach.
>  
>
>> It seems to work. I get the following result :
>>
>> >>> 
>> Init MetaBase
>> Init MetaBase
>> Init MetaBase
>> Init compte
>> Jean
>> >>>
>>
>> However, the problem with this method is that I have to add *"__abstract__ 
>> = True" *to every class which is inherited by Stockable... so, about 400 
>> classes. 
>>
>
> I don't see why that is.  If these classes are mapped to tables (which, if 
> they are persisted, they are), then there is no reason to add 
> "__abstract__".As in my previous email, how these 400 classes link 
> to tables is what needs to be answered and then we can formulate the 
> correct calling style.
>
>  
>
>> It is not very clean. Is it possible to avoid that by using something 
>> similar to my first code ? It would be great !
>>
>> Thank you very much.
>>
>> Sven
>>
>> -- 
>> 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+...@googlegroups.com .
>> To post to this group, send email to sqlal...@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] Is ARRAY supported in MySQL?

[Mike Bayer]

On Fri, Oct 20, 2017 at 1:35 PM, sqlalchemy_mysql  wrote:
> That's correct. MySQL doesn't support. I see people workaround by
> serializing it to string/text and store and retrieve so was wondering
> SQLAlchemy has some support like that. Seems like NO.

You use a TypeDecorator for that kind of thing:

http://docs.sqlalchemy.org/en/latest/core/custom_types.html?highlight=typedecorator#typedecorator-recipes




>
>
> On Friday, October 20, 2017 at 10:25:33 AM UTC-7, Mike Bayer wrote:
>>
>> On Thu, Oct 19, 2017 at 6:38 PM, sqlalchemy_mysql 
>> wrote:
>> > Sorry I don't see any references in doc. I tried using array type but
>> > got
>> > this error
>> >
>> > can't render element of type 
>> >
>> > Compiler > > 0x110949e90> can't render element of type > > 'sqlalchemy.sql.sqltypes.ARRAY'>
>> >
>> > Here is my column defintion
>> >
>> > sqla.Column(sqla_types.ARRAY(sqla_types.String), default=[],
>> > nullable=False)
>>
>> to my knowledge, MySQL has no ARRAY datatype available so this would
>> not work (a google search just now also doesn't turn up anything new).
>> If either MySQL or MariaDB implements ARRAY, SQLAlchemy's dialect
>> would require enhancements in order for this to be supported.
>>
>>
>> >
>> > --
>> > 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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@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 - 
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] SQLAlchemy : declarative_base and metaclass conflict

[Mike Bayer]

CONTINUING !  sorry


On Fri, Oct 20, 2017 at 11:55 AM, Sven Dumay  wrote:

>
> *I tried other things and I found the following solution :*
>
> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
> from sqlalchemy import Column, Integer
>
> class MetaBase(DeclarativeMeta):
>
> def __init__(cls, nom, bases, contenu):
> super(MetaBase, cls).__init__(nom, bases, contenu)
> print("Init MetaBase")
>
> Base = declarative_base(metaclass = MetaBase)
>
> class Stockable(Base):
>
> __abstract__ = True
>
> def __init__(self):
> print("Init Stockable")
>
> class Character(Stockable):
>
> __tablename__ = 'characters'
> id = Column(Integer, primary_key=True)
>
> def __init__(self, name):
> self.name = name
> print("Init character")
>
>
> jean = Character("Jean")
> print(jean.name)
>
>
>
this seems like roughly the correct approach.


> It seems to work. I get the following result :
>
> >>>
> Init MetaBase
> Init MetaBase
> Init MetaBase
> Init compte
> Jean
> >>>
>
> However, the problem with this method is that I have to add *"__abstract__
> = True" *to every class which is inherited by Stockable... so, about 400
> classes.
>

I don't see why that is.  If these classes are mapped to tables (which, if
they are persisted, they are), then there is no reason to add
"__abstract__".As in my previous email, how these 400 classes link
to tables is what needs to be answered and then we can formulate the
correct calling style.



> It is not very clean. Is it possible to avoid that by using something
> similar to my first code ? It would be great !
>
> Thank you very much.
>
> Sven
>
> --
> 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] SQLAlchemy : declarative_base and metaclass conflict

[Mike Bayer]

On Fri, Oct 20, 2017 at 11:55 AM, Sven Dumay  wrote:

> Hello,
>
> I am actually working on an existing project (an online-game) which is
> already advanced and pretty consequent.
>
> My goal is to replace the save system actually based on Pickle by
> SQLAlchemy. Not so easy because I have to deal with the existing classes
> and there is a lot of work to do (about 400 classes to persist).
>
> I'm not sure to know what is the best way to proceed and I think that I
> require some help.
>
> *Let's look at the classes organization of the project :*
>
>
> 
>
>
> Every class which should be persistent has to be inherited from Stockable.
> It is already designed this way and I think that it would be too
> complicated to change that. Below Stockable, there is hundred of classes
> with their own hierarchy. For example, Character is inherited from
> Stockable and Player and NPC (Non-player Character) are inherited from
> Character.
>


this is going to work very poorly.   SQLAlchemy supports class inheritance
mapped to databases but it's not really something that scales well to deep
hierarchies. You can actually stretch this a lot by emphasizing
single-table inheritance so that you aren't hobbled with dozens of joins,
but this seems like it is still a very deep hierarchy even for that
approach.

What you need to do here is forget about your whole class hierarchy, and
first design the database schema.   You want to persist this data in a
relational database.   How?   What do the tables look like?   For any
non-trivial application, this is where you need to design things from.


>
> My problem today is that I don't know how to proceed regarding the
> metaclass "MetaBase". I am not able to use declarative_base() and MetaBase
> at the same time. There is a metabase conflict. I found some other topics
> about this problem on the internet and I tried several solutions, but
> still, it never works for me.
>
> *To resume, here is how it basically works without SQLAlchemy :*
>
> class MetaBase(type):
>
> def __init__(cls, nom, bases, contenu):
> type.__init__(cls, nom, bases, contenu)
> pass
>
> class Stockable(metaclass = MetaBase):
>
> def __init__(self):
> pass
>
>
> class Character(Stockable):
>
> def __init__(self):
> pass
>
>
> *Here is what I would like to do with SQLAlchemy:*
>
> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
> from sqlalchemy import Column, Integer
>
> Base = declarative_base()
>
> class MetaBase(DeclarativeMeta):
>
> def __init__(cls, nom, bases, contenu):
> super(MetaBase, cls).__init__(nom, bases, contenu)
> print("Init MetaBase")
>
>
> class Stockable(metaclass = MetaBase):
>
> def __init__(self):
> print("Init Stockable")
>
> class Character(Stockable, Base):
>
> __tablename__ = 'characters'
> id = Column(Integer, primary_key=True)
>
> def __init__(self, name):
> self.name = name
> print("Init character")
>
>
> jean = Character("Jean")
> print(jean.name)
>
>
>


you need to pass your metaclass to declarative_base() so that the
_decl_class_registry and other things are established:

http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html?highlight=declarative_base#sqlalchemy.ext.declarative.declarative_base.params.metaclass

 Stockable then just descends from Base normally.



Here is what I get :
>
> >>>
> Traceback (most recent call last):
>   File "C:\Users\Sven\Desktop\SQL Alchemy Tests\test2.py", line 10, in
> 
> class Stockable(metaclass = MetaBase):
>   File "C:\Users\Sven\Desktop\SQL Alchemy Tests\test2.py", line 7, in
> __init__
> super(MetaBase, cls).__init__(nom, bases, contenu)
>   File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\api.py",
> line 64, in __init__
> _as_declarative(cls, classname, cls.__dict__)
>   File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\base.py",
> line 88, in _as_declarative
> _MapperConfig.setup_mapping(cls, classname, dict_)
>   File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\base.py",
> line 103, in setup_mapping
> cfg_cls(cls_, classname, dict_)
>   File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\base.py",
> line 125, in __init__
> clsregistry.add_class(self.classname, self.cls)
>   File 
> "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\clsregistry.py",
> line 34, in add_class
> if classname in cls._decl_class_registry:
> AttributeError: type object 'Stockable' has no attribute
> '_decl_class_registry'
> >>>
>
> Does someone knows what it means and how it can be resolved ?
>
> *I tried other things and I found the following solution :*
>
> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
> from sqlalchemy import Column, Integer
>
> class 

Re: [sqlalchemy] mapper existance checks possibly wrong

[Mike Bayer]

On Fri, Oct 20, 2017 at 11:05 AM, Simon King  wrote:
> The "is not None" is important when checking a variable that may
> contain a ClauseElement, precisely because ClauseElement defines that
> __bool__ method.
>
> However, in Session.get_bind(), "mapper" is not supposed to contain a
> ClauseElement. It should either be an instance of
> sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:" is a
> valid and concise way to write it. Comparing to None might be strictly
> more accurate, but it shouldn't be necessary.

agree, "mapper" means "mapper" and it is not supposed to be a
ClauseElement.   This sounds like arguments are not being passed
correctly somewhere.   Would need a demonstration script if something
in SQLAlchemy itself is claimed to be making the mistake.



>
> Simon
>
>
>
> On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан  wrote:
>> Really, `mapper` contains this: > 0x7fe673fb0a50; group_getter>. But does it changes something? I already
>> encountered this problem in my code, when I checked sqla-objects existance
>> without "is not None", project was broken. Is it normal to omit that
>> condition in sqlalchemy code?
>>
>> I use:
> sqlalchemy.__version__
>> '1.0.19'
>>
>> пятница, 20 октября 2017 г., 16:42:23 UTC+3 пользователь Simon King написал:
>>>
>>> On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан  wrote:
>>> > Hi.
>>> > I use my own `RoutingSession` and `RoutingQuery` implementation, most of
>>> > it
>>> > inspired by `sqlalchemy.ext.horizontal_shard`:
>>> >
>>> > class RoutingSession(Session):
>>> > def get_bind(self, mapper=None, clause=None, shard_id=None,
>>> > **kwargs):
>>> > original_bind = None
>>> > try:
>>> > original_bind = super(RoutingSession, self).get_bind(mapper,
>>> > clause)
>>> > except UnboundExecutionError:
>>> > # may not be bound
>>> > pass
>>> > if shard_id is None:
>>> > shard_id = TenantIDStorage.get_shard_id()  # just global
>>> > storage
>>> > bind_for_shard = self.__binds[shard_id]
>>> > if original_bind is not None and original_bind.url ==
>>> > bind_for_shard.url:
>>> > return original_bind
>>> > else:
>>> > return bind_for_shard
>>> >
>>> > def __init__(self, shards=None, query_cls=CachingQuery,
>>> > engines_factory=None, **kwargs):
>>> > super(RoutingSession, self).__init__(query_cls=query_cls,
>>> > **kwargs)
>>> > self.__binds = {}
>>> > self.engines_factory = engines_factory
>>> > if shards is not None:
>>> > self.update_shards(**shards)
>>> >
>>> > def _add_bind(self, key, bind):
>>> > self.__binds[key] = bind
>>> >
>>> >
>>> >
>>> >
>>> > class RoutingQuery(Query):
>>> > def __init__(self, *args, **kwargs):
>>> > super(RoutingQuery, self).__init__(*args, **kwargs)
>>> > self._shard_id = TenantIDStorage.get_shard_id()
>>> >
>>> > def get(self, ident):
>>> > self._check_bound_to_shard()
>>> > return super(CachingQuery, self).get(ident)
>>> >
>>> > def _check_bound_to_shard(self):
>>> > if self._shard_id is None:
>>> > raise ValueError('query not bound to any shard')
>>> >
>>> > def _execute_and_instances(self, querycontext):
>>> > self._check_bound_to_shard()
>>> > querycontext.attributes['shard_id'] = self._shard_id
>>> > result = self._connection_from_session(
>>> > mapper=self._mapper_zero(),
>>> > shard_id=self._shard_id).execute(
>>> > querycontext.statement,
>>> > self._params
>>> > )
>>> > return self.instances(result, querycontext)
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > Sometimes I got this error:
>>> >
>>> >   File "/home/anton/Projects/proj/admin/proj/admin/views/stats.py", line
>>> > 898, in _get_filters_from_request
>>> > control_groups = [g.id for g in
>>> > User.get_own_groups_query(current_user.id)]
>>> >   File
>>> >
>>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>>> > line 2802, in __iter__
>>> > return self._execute_and_instances(context)
>>> >   File
>>> >
>>> > "/home/anton/Projects/proj/core/proj/core/orm_extensions/rouing_session.py",
>>> > line 105, in _execute_and_instances
>>> > shard_id=self._shard_id).execute(
>>> >   File
>>> >
>>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>>> > line 2806, in _connection_from_session
>>> > **kw)
>>> >   File
>>> >
>>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>>> > line 984, in connection
>>> > bind = self.get_bind(mapper, clause=clause, **kw)
>>> >   File
>>> >
>>> > "/home/anton/Projects/proj/core/proj/core/orm_extensions/cachingquery.py",
>>> > line 279, in get_bind
>>> > original_bind = 

Re: [sqlalchemy] DESC index in test

[Mike Bayer]

On Fri, Oct 20, 2017 at 9:39 AM, Gijs Molenaar  wrote:
> Hi!
>
> I don't think this is standard SQL? At least MonetDB doesn't support it, and
> I can't find a way to disable this apart from overriding the test:
>
> https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/testing/suite/test_reflection.py#L128


not finding it in the particular SQL 2003 standard doc I have (that
is, CREATE INDEX overall, is not mentioned)   but all the DBs I
test on support it, including sqlite, potsgresql, mysql, oracle, MS
SQL serverand that's a lot :)

Of course a column store is totally different so we can add exclusion
rules for these if you need.



>
> greetings,
>
>  - Gijs
>
> --
> 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] Is ARRAY supported in MySQL?

[sqlalchemy_mysql]

That's correct. MySQL doesn't support. I see people workaround by 
serializing it to string/text and store and retrieve so was wondering 
SQLAlchemy has some support like that. Seems like NO.


On Friday, October 20, 2017 at 10:25:33 AM UTC-7, Mike Bayer wrote:
>
> On Thu, Oct 19, 2017 at 6:38 PM, sqlalchemy_mysql  > wrote: 
> > Sorry I don't see any references in doc. I tried using array type but 
> got 
> > this error 
> > 
> > can't render element of type  
> > 
> > Compiler  > 0x110949e90> can't render element of type  > 'sqlalchemy.sql.sqltypes.ARRAY'> 
> > 
> > Here is my column defintion 
> > 
> > sqla.Column(sqla_types.ARRAY(sqla_types.String), default=[], 
> nullable=False) 
>
> to my knowledge, MySQL has no ARRAY datatype available so this would 
> not work (a google search just now also doesn't turn up anything new). 
> If either MySQL or MariaDB implements ARRAY, SQLAlchemy's dialect 
> would require enhancements in order for this to be supported. 
>
>
> > 
> > -- 
> > 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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@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] Some sqlalchemy unittests running twice while testing with an external dialect

[Mike Bayer]

On Fri, Oct 20, 2017 at 9:32 AM, su-sa  wrote:
> I ran the unittests of SQLAlchemy with an external dialect and observed that
> some Tests can 2 times and also had different results.
>
> Does somebody know what the reason behing it could be ?


we've communicated offlist on this and I would need to see the full
stdout of the run to describe what may be happening.

>
> --
> 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] Different results if the tests are ran multiple number of times

[Mike Bayer]

On Fri, Oct 20, 2017 at 9:27 AM, su-sa  wrote:
> I am trying to test sqlalchemy with an external dialect. To do the Tests
> with the requirements specified by this external dialect, I used the
> --requirements Parameter. The Test results of some Tests varied when I ran
> the Tests multiple number of times. I am´doing the testing currently on the
> 1.2.0b3.dev0 beta Version until the stable 1.2 Version is released. Can this
> be a Problem of the external dialect or some bug in sqlalchemy?

I believe we have communicated offline, so in this case I would
reiterate that the main SQLAlchemy test suite isn't intended to be run
with external dialects in general, however if we do find shortcomings
in the test suite in general they can be corrected.   There are many
reasons why successive runs of the test suite may be different,
including:

- queries that don't specify ORDER BY may return a different result
- Python dictionary ordering is "random" per test run, and many
behaviors, particularly in the ORM, derive from dictionary ordering
such as the order in which objects of unrelated tables are flushed,
and others
- A failed test suite run will often not be able to DROP the objects
that it's created.   A successive test run may fail if these objects
get in the way of the new run.A first-level mitigation here is to
run the test suite with --dropfirst, however if this database
generates additional schema objects that make this more complicated
then they may need to be dropped manually (an example would be
Postgresql's ENUM datatype 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 - 
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] SQLAlchemy Many-to-Many Users, Groups, Roles

[Mike Bayer]

On Fri, Oct 20, 2017 at 8:53 AM, Leslie Luyt  wrote:
> I am trying to integrate with an existing user-group-role table structure
> where a user can belong to many groups and have multiple roles on each
> group.
>
>
> I found a similar question to this, however it does not allow for multiple
> roles: Many-to-many declarative SQLAlchemy definition for users, groups, and
> roles
>
>
> I have the following table structure and would like to be able to access the
> roles in the following sort of manner: group.users[0].roles
>
> It would also be nice to be able to access it from the other directions but
> is not required i.e. user.groups[0].roles or role.groups[0].users


OK so, the first step would be to build out linkages between the
classes using relationship().  The documentation for this feature
starts at: http://docs.sqlalchemy.org/en/latest/orm/relationships.html

>From there, you'd be using the association proxy pattern so you'd want
to focus on the association pattern:
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object
 and then to remove the "hop" you'd look into using association proxy:
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects


So those are the areas you'd be looking to use.   Now if you've tried
all that and it's not working, provide examples of what you've tried
and we can see if we can find where it's going wrong.



>
>
> class Role(Base):
> __tablename__ = 'roles'
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode(16), unique=True)
>
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode(16), unique=True)
>
> class Group(Base):
> __tablename__ = 'groups'
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode(16), unique=True)
>
> class UserGroupRole(Base):
> __tablename__ = 'user_group_role'
> id = Column(Integer, primary_key=True, autoincrement=True)
> user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'),
> nullable=False)
> group_id = Column(Integer, ForeignKey('groups.id', ondelete='CASCADE'),
> nullable=False)
> role_id = Column(Integer, ForeignKey('roles.id', ondelete='CASCADE'),
> nullable=False)
>
>
> --
> 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] Is ARRAY supported in MySQL?

[Mike Bayer]

On Thu, Oct 19, 2017 at 6:38 PM, sqlalchemy_mysql  wrote:
> Sorry I don't see any references in doc. I tried using array type but got
> this error
>
> can't render element of type 
>
> Compiler  0x110949e90> can't render element of type  'sqlalchemy.sql.sqltypes.ARRAY'>
>
> Here is my column defintion
>
> sqla.Column(sqla_types.ARRAY(sqla_types.String), default=[], nullable=False)

to my knowledge, MySQL has no ARRAY datatype available so this would
not work (a google search just now also doesn't turn up anything new).
If either MySQL or MariaDB implements ARRAY, SQLAlchemy's dialect
would require enhancements in order for this to be supported.


>
> --
> 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] sqlalchemy.select().group_by(expr) doesn't use label of expression, while .order_by(expr) does

[Mike Bayer]

On Thu, Oct 19, 2017 at 4:25 AM, Gijs Molenaar  wrote:
>
>
> Op donderdag 19 oktober 2017 03:10:21 UTC+2 schreef Mike Bayer:
>>
>> On Wed, Oct 18, 2017 at 7:38 AM, Gijs Molenaar 
>> wrote:
>> > hi!
>> >
>> >
>> > Not sure if this a bug or something I should in my SQLAlchemy dialect,
>> > but
>> > currently
>> >
>> >
>> > expr = (table.c.x + table.c.y).label('lx')
>> > select([func.count(table.c.id), expr]).group_by(expr).order_by(expr)
>> >
>> > compiles to:
>> >
>> > SELECT count(some_table.id) AS count_1, some_table.x + some_table.y AS
>> > lx
>> > \nFROM some_table GROUP BY some_table.x + some_table.y ORDER BY lx;
>> >
>> >
>> > which works fine for for example sqlite, but MonetDB requires the use of
>> > the
>> > lx label in the GROUP BY, which I think makes sense? Should this be
>> > addressed on the SQLalchemy side or on the MonetDB dialect side?
>>
>> so this was overhauled in
>>
>> http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#label-constructs-can-now-render-as-their-name-alone-in-an-order-by
>> where we changed ORDER BY to use the label name when the expression is
>> passed.
>>
>> so the immediate answer would be to not actually order by the label().
>
>
> I think i didn't formulate my e-mail correctly. The ORDER BY is not the
> problem, it is the GROUP BY that requires a label. If the GROUP BY doesn't
> use a label (just like ORDER BY), MonetDB doesn't want to eat the query.

this is my fault because I read/reply to these emails very fast and
focus mainly on the code I see.If you want it the other way
around, there is the feature described at:

https://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#order-by-and-group-by-are-special-cases

that is, say group_by("somelabel").

If you need this to be more automatic for this backend, we can look
into seeing how the "ORDER BY" version of the feature can be more
generalized on a backend-specific basis but this would be longer-term.



>
>>
>> At the compiler level, this behavior can be disabled across the board like
>> this:
>>
>> diff --git a/lib/sqlalchemy/dialects/sqlite/base.py
>> b/lib/sqlalchemy/dialects/sqlite/base.py
>> index d8ce7f394..389d9bb02 100644
>> --- a/lib/sqlalchemy/dialects/sqlite/base.py
>> +++ b/lib/sqlalchemy/dialects/sqlite/base.py
>> @@ -800,6 +800,10 @@ class SQLiteCompiler(compiler.SQLCompiler):
>>  'week': '%W',
>>  })
>>
>> +def visit_label(self, elem, **kw):
>> +kw.pop('render_label_as_label', None)
>> +return super(SQLiteCompiler, self).visit_label(elem, **kw)
>> +
>>  def visit_now_func(self, fn, **kw):
>>  return "CURRENT_TIMESTAMP"
>>
>>
>>
>> however no dialect does that right now, you'd need to watch this
>> behavior closely across SQLAlchemy releases to make sure it doesn't
>> break, unless we make this behavior official.
>>
>>
>>
>>
>> >
>> >
>> > related issue:
>> >
>> >
>> > https://github.com/gijzelaerr/sqlalchemy-monetdb/issues/21
>> >
>> > greetings,
>> >
>> >  - Gijs
>> >
>> > --
>> > 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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@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 - 
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, 

[sqlalchemy] Re: SQLAlchemy : declarative_base and metaclass conflict

[Sven Dumay]

Version of Python : 3.4.0 
Version of SQLAlchemy : 1.2.0b2

-- 
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] SQLAlchemy : declarative_base and metaclass conflict

[Sven Dumay]

Hello,

I am actually working on an existing project (an online-game) which is 
already advanced and pretty consequent.

My goal is to replace the save system actually based on Pickle by 
SQLAlchemy. Not so easy because I have to deal with the existing classes 
and there is a lot of work to do (about 400 classes to persist).

I'm not sure to know what is the best way to proceed and I think that I 
require some help.

*Let's look at the classes organization of the project :*




Every class which should be persistent has to be inherited from Stockable. 
It is already designed this way and I think that it would be too 
complicated to change that. Below Stockable, there is hundred of classes 
with their own hierarchy. For example, Character is inherited from 
Stockable and Player and NPC (Non-player Character) are inherited from 
Character.

My problem today is that I don't know how to proceed regarding the 
metaclass "MetaBase". I am not able to use declarative_base() and MetaBase 
at the same time. There is a metabase conflict. I found some other topics 
about this problem on the internet and I tried several solutions, but 
still, it never works for me. 

*To resume, here is how it basically works without SQLAlchemy :*

class MetaBase(type):

def __init__(cls, nom, bases, contenu):
type.__init__(cls, nom, bases, contenu)
pass

class Stockable(metaclass = MetaBase):

def __init__(self):
pass


class Character(Stockable):
 
def __init__(self):
pass


*Here is what I would like to do with SQLAlchemy:*

from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy import Column, Integer

Base = declarative_base()

class MetaBase(DeclarativeMeta):

def __init__(cls, nom, bases, contenu):
super(MetaBase, cls).__init__(nom, bases, contenu)
print("Init MetaBase")


class Stockable(metaclass = MetaBase):

def __init__(self):
print("Init Stockable")

class Character(Stockable, Base):

__tablename__ = 'characters'
id = Column(Integer, primary_key=True)
 
def __init__(self, name):
self.name = name
print("Init character")


jean = Character("Jean")
print(jean.name)


Here is what I get :

>>> 
Traceback (most recent call last):
  File "C:\Users\Sven\Desktop\SQL Alchemy Tests\test2.py", line 10, in 

class Stockable(metaclass = MetaBase):
  File "C:\Users\Sven\Desktop\SQL Alchemy Tests\test2.py", line 7, in 
__init__
super(MetaBase, cls).__init__(nom, bases, contenu)
  File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\api.py", 
line 64, in __init__
_as_declarative(cls, classname, cls.__dict__)
  File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\base.py", 
line 88, in _as_declarative
_MapperConfig.setup_mapping(cls, classname, dict_)
  File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\base.py", 
line 103, in setup_mapping
cfg_cls(cls_, classname, dict_)
  File "C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\base.py", 
line 125, in __init__
clsregistry.add_class(self.classname, self.cls)
  File 
"C:\Python34\lib\site-packages\sqlalchemy\ext\declarative\clsregistry.py", 
line 34, in add_class
if classname in cls._decl_class_registry:
AttributeError: type object 'Stockable' has no attribute 
'_decl_class_registry'
>>>

Does someone knows what it means and how it can be resolved ?

*I tried other things and I found the following solution :*

from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy import Column, Integer

class MetaBase(DeclarativeMeta):

def __init__(cls, nom, bases, contenu):
super(MetaBase, cls).__init__(nom, bases, contenu)
print("Init MetaBase")

Base = declarative_base(metaclass = MetaBase)

class Stockable(Base):

__abstract__ = True

def __init__(self):
print("Init Stockable")

class Character(Stockable):

__tablename__ = 'characters'
id = Column(Integer, primary_key=True)
 
def __init__(self, name):
self.name = name
print("Init character")


jean = Character("Jean")
print(jean.name)


It seems to work. I get the following result :

>>> 
Init MetaBase
Init MetaBase
Init MetaBase
Init compte
Jean
>>>

However, the problem with this method is that I have to add *"__abstract__ 
= True" *to every class which is inherited by Stockable... so, about 400 
classes. It is not very clean. Is it possible to avoid that by using 
something similar to my first code ? It would be great !

Thank you very much.

Sven

-- 
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 

Re: [sqlalchemy] SQLAlchemy enum in external file

[Jonathan Vanasco]

sorry. i'm getting over a code and in my fever-dream state thought I was 
making something clear with an analogy.

looking at this, and literally everything else I wrote yesterday, I'm 
amazed it's at least in english.

-- 
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] mapper existance checks possibly wrong

[Simon King]

The "is not None" is important when checking a variable that may
contain a ClauseElement, precisely because ClauseElement defines that
__bool__ method.

However, in Session.get_bind(), "mapper" is not supposed to contain a
ClauseElement. It should either be an instance of
sqlalchemy.orm.mapper.Mapper, or None, in which case "if mapper:" is a
valid and concise way to write it. Comparing to None might be strictly
more accurate, but it shouldn't be necessary.

Simon



On Fri, Oct 20, 2017 at 3:17 PM, Антонио Антуан  wrote:
> Really, `mapper` contains this:  0x7fe673fb0a50; group_getter>. But does it changes something? I already
> encountered this problem in my code, when I checked sqla-objects existance
> without "is not None", project was broken. Is it normal to omit that
> condition in sqlalchemy code?
>
> I use:
 sqlalchemy.__version__
> '1.0.19'
>
> пятница, 20 октября 2017 г., 16:42:23 UTC+3 пользователь Simon King написал:
>>
>> On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан  wrote:
>> > Hi.
>> > I use my own `RoutingSession` and `RoutingQuery` implementation, most of
>> > it
>> > inspired by `sqlalchemy.ext.horizontal_shard`:
>> >
>> > class RoutingSession(Session):
>> > def get_bind(self, mapper=None, clause=None, shard_id=None,
>> > **kwargs):
>> > original_bind = None
>> > try:
>> > original_bind = super(RoutingSession, self).get_bind(mapper,
>> > clause)
>> > except UnboundExecutionError:
>> > # may not be bound
>> > pass
>> > if shard_id is None:
>> > shard_id = TenantIDStorage.get_shard_id()  # just global
>> > storage
>> > bind_for_shard = self.__binds[shard_id]
>> > if original_bind is not None and original_bind.url ==
>> > bind_for_shard.url:
>> > return original_bind
>> > else:
>> > return bind_for_shard
>> >
>> > def __init__(self, shards=None, query_cls=CachingQuery,
>> > engines_factory=None, **kwargs):
>> > super(RoutingSession, self).__init__(query_cls=query_cls,
>> > **kwargs)
>> > self.__binds = {}
>> > self.engines_factory = engines_factory
>> > if shards is not None:
>> > self.update_shards(**shards)
>> >
>> > def _add_bind(self, key, bind):
>> > self.__binds[key] = bind
>> >
>> >
>> >
>> >
>> > class RoutingQuery(Query):
>> > def __init__(self, *args, **kwargs):
>> > super(RoutingQuery, self).__init__(*args, **kwargs)
>> > self._shard_id = TenantIDStorage.get_shard_id()
>> >
>> > def get(self, ident):
>> > self._check_bound_to_shard()
>> > return super(CachingQuery, self).get(ident)
>> >
>> > def _check_bound_to_shard(self):
>> > if self._shard_id is None:
>> > raise ValueError('query not bound to any shard')
>> >
>> > def _execute_and_instances(self, querycontext):
>> > self._check_bound_to_shard()
>> > querycontext.attributes['shard_id'] = self._shard_id
>> > result = self._connection_from_session(
>> > mapper=self._mapper_zero(),
>> > shard_id=self._shard_id).execute(
>> > querycontext.statement,
>> > self._params
>> > )
>> > return self.instances(result, querycontext)
>> >
>> >
>> >
>> >
>> >
>> >
>> > Sometimes I got this error:
>> >
>> >   File "/home/anton/Projects/proj/admin/proj/admin/views/stats.py", line
>> > 898, in _get_filters_from_request
>> > control_groups = [g.id for g in
>> > User.get_own_groups_query(current_user.id)]
>> >   File
>> >
>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>> > line 2802, in __iter__
>> > return self._execute_and_instances(context)
>> >   File
>> >
>> > "/home/anton/Projects/proj/core/proj/core/orm_extensions/rouing_session.py",
>> > line 105, in _execute_and_instances
>> > shard_id=self._shard_id).execute(
>> >   File
>> >
>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>> > line 2806, in _connection_from_session
>> > **kw)
>> >   File
>> >
>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> > line 984, in connection
>> > bind = self.get_bind(mapper, clause=clause, **kw)
>> >   File
>> >
>> > "/home/anton/Projects/proj/core/proj/core/orm_extensions/cachingquery.py",
>> > line 279, in get_bind
>> > original_bind = super(RoutingSession, self).get_bind(mapper, clause)
>> >   File
>> >
>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>> > line 1336, in get_bind
>> > if mapper and mapper.mapped_table.bind:
>> >   File
>> >
>> > "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
>> > line 539, in __bool__
>> > raise TypeError("Boolean value of this clause is not defined")
>> > TypeError: Boolean value of this 

Re: [sqlalchemy] mapper existance checks possibly wrong

[Антонио Антуан]

Really, `mapper` contains this: . But does it changes something? I already 
encountered this problem in my code, when I checked sqla-objects existance 
without "is not None", project was broken. Is it normal to omit that 
condition in sqlalchemy code?

I use:
>>> sqlalchemy.__version__
'1.0.19'

пятница, 20 октября 2017 г., 16:42:23 UTC+3 пользователь Simon King написал:
>
> On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан  > wrote: 
> > Hi. 
> > I use my own `RoutingSession` and `RoutingQuery` implementation, most of 
> it 
> > inspired by `sqlalchemy.ext.horizontal_shard`: 
> > 
> > class RoutingSession(Session): 
> > def get_bind(self, mapper=None, clause=None, shard_id=None, 
> **kwargs): 
> > original_bind = None 
> > try: 
> > original_bind = super(RoutingSession, self).get_bind(mapper, 
> > clause) 
> > except UnboundExecutionError: 
> > # may not be bound 
> > pass 
> > if shard_id is None: 
> > shard_id = TenantIDStorage.get_shard_id()  # just global 
> storage 
> > bind_for_shard = self.__binds[shard_id] 
> > if original_bind is not None and original_bind.url == 
> > bind_for_shard.url: 
> > return original_bind 
> > else: 
> > return bind_for_shard 
> > 
> > def __init__(self, shards=None, query_cls=CachingQuery, 
> > engines_factory=None, **kwargs): 
> > super(RoutingSession, self).__init__(query_cls=query_cls, 
> **kwargs) 
> > self.__binds = {} 
> > self.engines_factory = engines_factory 
> > if shards is not None: 
> > self.update_shards(**shards) 
> > 
> > def _add_bind(self, key, bind): 
> > self.__binds[key] = bind 
> > 
> > 
> > 
> > 
> > class RoutingQuery(Query): 
> > def __init__(self, *args, **kwargs): 
> > super(RoutingQuery, self).__init__(*args, **kwargs) 
> > self._shard_id = TenantIDStorage.get_shard_id() 
> > 
> > def get(self, ident): 
> > self._check_bound_to_shard() 
> > return super(CachingQuery, self).get(ident) 
> > 
> > def _check_bound_to_shard(self): 
> > if self._shard_id is None: 
> > raise ValueError('query not bound to any shard') 
> > 
> > def _execute_and_instances(self, querycontext): 
> > self._check_bound_to_shard() 
> > querycontext.attributes['shard_id'] = self._shard_id 
> > result = self._connection_from_session( 
> > mapper=self._mapper_zero(), 
> > shard_id=self._shard_id).execute( 
> > querycontext.statement, 
> > self._params 
> > ) 
> > return self.instances(result, querycontext) 
> > 
> > 
> > 
> > 
> > 
> > 
> > Sometimes I got this error: 
> > 
> >   File "/home/anton/Projects/proj/admin/proj/admin/views/stats.py", line 
> > 898, in _get_filters_from_request 
> > control_groups = [g.id for g in 
> > User.get_own_groups_query(current_user.id)] 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>  
>
> > line 2802, in __iter__ 
> > return self._execute_and_instances(context) 
> >   File 
> > 
> "/home/anton/Projects/proj/core/proj/core/orm_extensions/rouing_session.py", 
>
> > line 105, in _execute_and_instances 
> > shard_id=self._shard_id).execute( 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
>  
>
> > line 2806, in _connection_from_session 
> > **kw) 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>  
>
> > line 984, in connection 
> > bind = self.get_bind(mapper, clause=clause, **kw) 
> >   File 
> > 
> "/home/anton/Projects/proj/core/proj/core/orm_extensions/cachingquery.py", 
> > line 279, in get_bind 
> > original_bind = super(RoutingSession, self).get_bind(mapper, clause) 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
>  
>
> > line 1336, in get_bind 
> > if mapper and mapper.mapped_table.bind: 
> >   File 
> > 
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
>  
>
> > line 539, in __bool__ 
> > raise TypeError("Boolean value of this clause is not defined") 
> > TypeError: Boolean value of this clause is not defined 
> > 
> > 
> > 
> > 
> > I found that `mapper` in original `get_bind` always checks with `is not 
> > None` condition. There is only one place where condition omitted: `if 
> mapper 
> > and mapper.mapped_table.bind...`. 
> > Possibly it is not correct? Or my code does not do something important? 
>
> Are you able to catch this error in a debugger? 
>
> I'm going to make a wild guess that "mapper" at this point is some 
> sort of SQL construct (such as a column object or mapped property), 
> rather than an actual mapper. 
>
> You don't say which version of 

Re: [sqlalchemy] SQLAlchemy enum in external file

[TazMainiac]

On Fri, Oct 20, 2017 at 3:58 AM, Simon King  wrote:

> This is a subtle python gotcha based on how the import system works.
> It doesn't have anything to do with SQLAlchemy.
>
>  latest/python_concepts/import_traps.html#executing-the-main-module-twice>
>
> When you import a module, it gets cached in sys.modules under its
> "fully qualified module name", so it doesn't matter how many times you
> run "import foo", the module is only loaded once.
>
> However, the script that you initially *execute* gets the special name
> "__main__". If it gets subsequently imported via an "import"
> statement, it is not found in the cache, so it is executed again,
> meaning you get duplicate definitions of everything in the module.
>
> Hope that helps,
>
> Simon
>

Thank you.  This was the explanation I'd been looking for.

So - trying to work around this - the following works (after moving exiting
__main__ code into main() function):

$ python -c "from myoper import main; import sys; main(*sys.argv[1:])"
ext_check 1

But it seems like the best solution to this is to probably move any test
code that calls out to an external module OUT of myoper (probably into
myoper_test itself, which is what I'm going to end up doing).  Are there
any other common solutions?

Thanks again,
Taz

-- 
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] mapper existance checks possibly wrong

[Simon King]

On Fri, Oct 20, 2017 at 2:15 PM, Антонио Антуан  wrote:
> Hi.
> I use my own `RoutingSession` and `RoutingQuery` implementation, most of it
> inspired by `sqlalchemy.ext.horizontal_shard`:
>
> class RoutingSession(Session):
> def get_bind(self, mapper=None, clause=None, shard_id=None, **kwargs):
> original_bind = None
> try:
> original_bind = super(RoutingSession, self).get_bind(mapper,
> clause)
> except UnboundExecutionError:
> # may not be bound
> pass
> if shard_id is None:
> shard_id = TenantIDStorage.get_shard_id()  # just global storage
> bind_for_shard = self.__binds[shard_id]
> if original_bind is not None and original_bind.url ==
> bind_for_shard.url:
> return original_bind
> else:
> return bind_for_shard
>
> def __init__(self, shards=None, query_cls=CachingQuery,
> engines_factory=None, **kwargs):
> super(RoutingSession, self).__init__(query_cls=query_cls, **kwargs)
> self.__binds = {}
> self.engines_factory = engines_factory
> if shards is not None:
> self.update_shards(**shards)
>
> def _add_bind(self, key, bind):
> self.__binds[key] = bind
>
>
>
>
> class RoutingQuery(Query):
> def __init__(self, *args, **kwargs):
> super(RoutingQuery, self).__init__(*args, **kwargs)
> self._shard_id = TenantIDStorage.get_shard_id()
>
> def get(self, ident):
> self._check_bound_to_shard()
> return super(CachingQuery, self).get(ident)
>
> def _check_bound_to_shard(self):
> if self._shard_id is None:
> raise ValueError('query not bound to any shard')
>
> def _execute_and_instances(self, querycontext):
> self._check_bound_to_shard()
> querycontext.attributes['shard_id'] = self._shard_id
> result = self._connection_from_session(
> mapper=self._mapper_zero(),
> shard_id=self._shard_id).execute(
> querycontext.statement,
> self._params
> )
> return self.instances(result, querycontext)
>
>
>
>
>
>
> Sometimes I got this error:
>
>   File "/home/anton/Projects/proj/admin/proj/admin/views/stats.py", line
> 898, in _get_filters_from_request
> control_groups = [g.id for g in
> User.get_own_groups_query(current_user.id)]
>   File
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
> line 2802, in __iter__
> return self._execute_and_instances(context)
>   File
> "/home/anton/Projects/proj/core/proj/core/orm_extensions/rouing_session.py",
> line 105, in _execute_and_instances
> shard_id=self._shard_id).execute(
>   File
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
> line 2806, in _connection_from_session
> **kw)
>   File
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
> line 984, in connection
> bind = self.get_bind(mapper, clause=clause, **kw)
>   File
> "/home/anton/Projects/proj/core/proj/core/orm_extensions/cachingquery.py",
> line 279, in get_bind
> original_bind = super(RoutingSession, self).get_bind(mapper, clause)
>   File
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
> line 1336, in get_bind
> if mapper and mapper.mapped_table.bind:
>   File
> "/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
> line 539, in __bool__
> raise TypeError("Boolean value of this clause is not defined")
> TypeError: Boolean value of this clause is not defined
>
>
>
>
> I found that `mapper` in original `get_bind` always checks with `is not
> None` condition. There is only one place where condition omitted: `if mapper
> and mapper.mapped_table.bind...`.
> Possibly it is not correct? Or my code does not do something important?

Are you able to catch this error in a debugger?

I'm going to make a wild guess that "mapper" at this point is some
sort of SQL construct (such as a column object or mapped property),
rather than an actual mapper.

You don't say which version of SQLAlchemy you are using, but since
you've overridden _execute_and_instances, I wonder if you need to use
something like query._bind_mapper() as used by _get_bind_args:



Hope that helps,

Simon

-- 
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 

[sqlalchemy] DESC index in test

[Gijs Molenaar]

Hi!

I don't think this is standard SQL? At least MonetDB doesn't support it, 
and I can't find a way to disable this apart from overriding the test:

https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/testing/suite/test_reflection.py#L128

greetings,

 - Gijs

-- 
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] Some sqlalchemy unittests running twice while testing with an external dialect

[su-sa]

I ran the unittests of SQLAlchemy with an external dialect and observed 
that some Tests can 2 times and also had different results. 

Does somebody know what the reason behing it could be ?

-- 
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] Different results if the tests are ran multiple number of times

[su-sa]

I am trying to test sqlalchemy with an external dialect. To do the Tests 
with the requirements specified by this external dialect, I used the 
--requirements Parameter. The Test results of some Tests varied when I ran 
the Tests multiple number of times. I am´doing the testing currently on the 
1.2.0b3.dev0 beta Version until the stable 1.2 Version is released. Can 
this be a Problem of the external dialect or some bug in sqlalchemy?

-- 
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] mapper existance checks possibly wrong

[Антонио Антуан]

Hi.
I use my own `RoutingSession` and `RoutingQuery` implementation, most of it 
inspired by `sqlalchemy.ext.horizontal_shard`:

class RoutingSession(Session):
def get_bind(self, mapper=None, clause=None, shard_id=None, **kwargs):
original_bind = None
try:
original_bind = super(RoutingSession, self).get_bind(mapper, clause)
except UnboundExecutionError:
# may not be bound
pass
if shard_id is None:
shard_id = TenantIDStorage.get_shard_id()  # just global storage
bind_for_shard = self.__binds[shard_id]
if original_bind is not None and original_bind.url == 
bind_for_shard.url:
return original_bind
else:
return bind_for_shard

def __init__(self, shards=None, query_cls=CachingQuery, 
engines_factory=None, **kwargs):
super(RoutingSession, self).__init__(query_cls=query_cls, **kwargs)
self.__binds = {}
self.engines_factory = engines_factory
if shards is not None:
self.update_shards(**shards)

def _add_bind(self, key, bind):
self.__binds[key] = bind




class RoutingQuery(Query):
def __init__(self, *args, **kwargs):
super(RoutingQuery, self).__init__(*args, **kwargs)
self._shard_id = TenantIDStorage.get_shard_id()

def get(self, ident):
self._check_bound_to_shard()
return super(CachingQuery, self).get(ident)

def _check_bound_to_shard(self):
if self._shard_id is None:
raise ValueError('query not bound to any shard')

def _execute_and_instances(self, querycontext):
self._check_bound_to_shard()
querycontext.attributes['shard_id'] = self._shard_id
result = self._connection_from_session(
mapper=self._mapper_zero(),
shard_id=self._shard_id).execute(
querycontext.statement,
self._params
)
return self.instances(result, querycontext)






Sometimes I got this error:

  File "/home/anton/Projects/proj/admin/proj/admin/views/stats.py", line 898, 
in _get_filters_from_request
control_groups = [g.id for g in User.get_own_groups_query(current_user.id)]
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
 line 2802, in __iter__
return self._execute_and_instances(context)
  File 
"/home/anton/Projects/proj/core/proj/core/orm_extensions/rouing_session.py", 
line 105, in _execute_and_instances
shard_id=self._shard_id).execute(
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
 line 2806, in _connection_from_session
**kw)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
 line 984, in connection
bind = self.get_bind(mapper, clause=clause, **kw)
  File 
"/home/anton/Projects/proj/core/proj/core/orm_extensions/cachingquery.py", line 
279, in get_bind
original_bind = super(RoutingSession, self).get_bind(mapper, clause)
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
 line 1336, in get_bind
if mapper and mapper.mapped_table.bind:
  File 
"/home/anton/Projects/proj/.venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py",
 line 539, in __bool__
raise TypeError("Boolean value of this clause is not defined")
TypeError: Boolean value of this clause is not defined




I found that `mapper` in original `get_bind` always checks with `is not 
None` condition. There is only one place where condition omitted: `if 
mapper and mapper.mapped_table.bind...`. 
Possibly it is not correct? Or my code does not do something important? 

-- 
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] SQLAlchemy Many-to-Many Users, Groups, Roles

[Leslie Luyt]

 

I am trying to integrate with an existing user-group-role table structure 
where a user can belong to many groups and have multiple roles on each 
group.


I found a similar question to this, however it does not allow for multiple 
roles: Many-to-many declarative SQLAlchemy definition for users, groups, 
and roles 





I have the following table structure and would like to be able to access 
the roles in the following sort of manner: group.users[0].roles

It would also be nice to be able to access it from the other directions but 
is not required i.e. user.groups[0].roles or role.groups[0].users


class Role(Base):
__tablename__ = 'roles'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(16), unique=True)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(16), unique=True)
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(16), unique=True)
class UserGroupRole(Base):
__tablename__ = 'user_group_role'
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), 
nullable=False)
group_id = Column(Integer, ForeignKey('groups.id', ondelete='CASCADE'), 
nullable=False)
role_id = Column(Integer, ForeignKey('roles.id', ondelete='CASCADE'), 
nullable=False)


-- 
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] generate raw insert statement

[Антонио Антуан]

Excuse me, missed your response.
Found this soultiuon:

>>> print(compile_query(Foo.__table__.insert().values(bar='zoo').compile(
Session.bind.dialect))
INSERT INTO foos (bar) VALUES ('zoo')





пятница, 13 октября 2017 г., 19:41:29 UTC+3 пользователь Mike Bayer написал:
>
> On Fri, Oct 13, 2017 at 11:21 AM, Антонио Антуан  > wrote: 
> > I've seen it and thought that some ways available... 
>
>
> you can maybe "capture" it via a logger but that's only as it runs. 
> The INSERT statement has to also return data to the ORM so it's not 
> tailored for "offline" use. 
>
> if you can give me a clue what you want to do I might have suggestions. 
>
>
>
> > пт, 13 окт. 2017 г. в 17:30, Mike Bayer  >: 
> >> 
> >> On Fri, Oct 13, 2017 at 8:43 AM, Антонио Антуан  > 
> >> wrote: 
> >> > I use such code to generate raw SQL from SQLA-queries: 
> >> > 
> >> > def compile_statement(statement): 
> >> > comp = statement.compile(dialect=postgresql.dialect()) 
> >> > params = {} 
> >> > for k, v in comp.params.items(): 
> >> > if isinstance(v, unicode): 
> >> > v = v.encode(cls.encoding) 
> >> > params[k] = adapt(v) 
> >> > return (comp.string.encode(cls.encoding) % 
> >> > params).decode(cls.encoding) 
> >> > 
> >> > 
> >> > def compile_query(query): 
> >> > return cls.compile_statement(query.statement) 
> >> > 
> >>  print(compile_query(Foo.query.filter(Foo.bar == 
> >>  'zoo').with_entities(Foo.bar)) 
> >> > SELECT foos.bar as foos_bar 
> >> > FROM foos 
> >> > WHERE foos.bar = 'zoo' 
> >> > 
> >> > 
> >> > Is there any way to generate raw SQL insert statement, generated by 
> this 
> >> > code? 
> >> > Foo(bar='zoo').save() 
> >> 
> >> if you mean that save() is the flask-sqlalchemy thing that calls 
> >> flush(), and you want to get the INSERT that the ORM uses directly, 
> >> there's not really a way to get that on the fly.   The insert is 
> >> generated internally based on a scan of what's in the object and it 
> >> occurs across several private functions which pass lots of internal 
> >> state around. 
> >> 
> >> 
> >> 
> >> > 
> >> > -- 
> >> > 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+...@googlegroups.com . 
> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com . 
> >> To post to this group, send email to sqlal...@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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@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 

Re: [sqlalchemy] SQLAlchemy enum in external file

[Simon King]

On Thu, Oct 19, 2017 at 9:10 PM, TazMainiac  wrote:
>
>
> On Thu, Oct 19, 2017 at 2:25 PM, Jonathan Vanasco 
> wrote:
>>
>> On Thursday, October 19, 2017 at 11:55:49 AM UTC-4, Taz Mainiac wrote:
>>>
>>> So - a morning spent googling does not turn up any information about
>>> Python classes having a different id depending on context (script vs
>>> module).  I'm probably not googling for the right thing?  Can anyone point
>>> me to information?
>>>
>>
>> The Python classes have a different id on each run not because of the
>> context (script vs module), but because they are run in different processes.
>> The "id" , like the object, is only local to the process.
>>
>> The attributes of your custom enum class are instances of enum objects.
>> which have attributes like name and value.
>
>
> I understand this.  The id is the address of the object under CPython (so it
> says in the docs).
>
>> If you ran two scripts or two modules, it would also almost always fail
>> (IIRC, it is remotely possible but highly unlikely, to generate the same id
>> in another process).
>
>
> I am not doing that.
>
>>
>> You should be storing/comparing the enum's 'name' or 'value' attributes --
>> not the enum object itself.  The enum object will always be different.
>
>
> I'm following the established documentation on how to use an enum with
> SQLAlchemy (with Postgres DB backend) - almost exactly as described in the
> doc under the Enum type:
>
>   http://docs.sqlalchemy.org/en/rel_1_1/core/type_basics.html
>
> (except I'm using declarative style).
>
> The problem is apparently that the enumerated type gets a different id
> depending on the context in which it is used (from a script vs from a module
> import).  This is very surprising to me, and I have not seen anything like
> it before (hence my question above asking for any references to this
> behavior).
>
> In any case, this causes SQL Alchemy to not recognize that the enumerated
> value assigned to a field (in another file) is not the same type as what is
> expected.  Clearly the ' is ' relationship is failing (since the id's are
> different).
>
> So - need to reorganize code a bit to avoid this...
>
> Taz

This is a subtle python gotcha based on how the import system works.
It doesn't have anything to do with SQLAlchemy.



When you import a module, it gets cached in sys.modules under its
"fully qualified module name", so it doesn't matter how many times you
run "import foo", the module is only loaded once.

However, the script that you initially *execute* gets the special name
"__main__". If it gets subsequently imported via an "import"
statement, it is not found in the cache, so it is executed again,
meaning you get duplicate definitions of everything in the module.

Hope that helps,

Simon

-- 
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.