Re: [sqlalchemy] orm.relationship() with dynamic part ?

[Mike Bayer]

you can do a bindparam with a callable, which I *think* should work here:

ContentTranslation.language_id == bindparam(None, callable_=lambda:
get_current_request().locale_name)

you want to set bake_queries=False on this relationship().

if it doesn't work, there's other ways to do this too.



On Thu, Apr 5, 2018 at 10:27 AM, Julien Cigar  wrote:
> Hello,
>
> I wondered what would be the "best" way to achieve something like:
> https://gist.github.com/silenius/f4f98acc372e228093298002c0736894#file-foo-py-L5
> ?
>
> With "get_current_request().locale_name" replaced by "en" I'm getting
> something like
> https://gist.githubusercontent.com/silenius/7f9f0e55cf8ea700222f67f88313e00f/raw/947151d597b8d95513580316ebb79f886be37b5d/sa.sql
> which is exactly what I want ..!
>
> Thanks :)
>
> Julien
>
>
> --
> Julien Cigar
> Belgian Biodiversity Platform (http://www.biodiversity.be)
> PGP fingerprint: EEF9 F697 4B68 D275 7B11  6A25 B2BB 3710 A204 23C0
> No trees were killed in the creation of this message.
> However, many electrons were terribly inconvenienced.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full 
> description.
> ---
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] orm.relationship() with dynamic part ?

[Julien Cigar]

Hello,

I wondered what would be the "best" way to achieve something like:
https://gist.github.com/silenius/f4f98acc372e228093298002c0736894#file-foo-py-L5
?

With "get_current_request().locale_name" replaced by "en" I'm getting
something like
https://gist.githubusercontent.com/silenius/7f9f0e55cf8ea700222f67f88313e00f/raw/947151d597b8d95513580316ebb79f886be37b5d/sa.sql
which is exactly what I want ..!

Thanks :)

Julien


-- 
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11  6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

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


signature.asc
Description: PGP signature

Re: [sqlalchemy] Inherit foreign key to a dynamic table based on inherited column value

[Johnathan Norton]

I wasnt looking to use the monster,race,foliage, and fauna classes as
mixins for the character ones. There's not a monster, or fauna stat that i
want to add on to a character object.

Didnt realize this is what I'm trying to do until you asked the right
questions.

I guess I'm trying to make it like this:
- Entity heirarcy is supposed to be used for mechanics of combat, and
information about specific characters/bosses/players.
- Species hierarcy is supposed to be more of a lore storage table.
Information pertaining to a specific bread of monster, fauna, race etc... I
could easily put all the species in one table and do a simple 1:M, but the
monsters will probably need more columns than the foilage table, and that
will waste space.
- The relationship i need is to basically hop straight from a specific
character to the lore entry about its species.

There currently is no base of those species classes. SpeciesLookup()was
performing that function until I changed it to just be a mapping of ids to
the table names. But making a base class for them is a good idea, so I'll
try it out next time I'm at my computer.

Thanks a bunch, man. Ur youtube vid and pdf handout with the tutorial
really kick started me into this. Im lovin it.

On Thu, Apr 5, 2018, 08:45 Mike Bayer  wrote:

> when you have CharacterEntity, would there also be
> MonsterCharacterEntity, FaunaCharacterEntity, etc. ?  if you had
> "Monster", "Fauna", "Race", "Foliage" mixins that are applied to the
> LivingEntity hierarchy that would be a place to hold your
> "species_lookup" relationship.  What is the base of the hierarchy of
> RaceLookup/MonsterLookup/FaunaLookup/FoliageLookup?  there should be
> some common term for that too like TaxonomyLookup or something.  since
> we are heavy with entity terminology here.
>
> On Thu, Apr 5, 2018 at 8:52 AM, Johnathan Norton
>  wrote:
> > Disclaimer: I'm very new at this. Only been learning databases for a
> couple
> > weeks, and been working with python for about a month and 1/2. I did do
> > several searches, phrasing things differently, but I can't seem to find
> my
> > answer.
> >
> > I'm trying to build a overly complex DnD database and cmdline program to
> GM
> > with.
> > Current issue is that I'm playing with a lot of mixin classes, creating
> new
> > tables for more and more complex entities so I'm not having simple
> > entities(less columns) waste space in the same table with complex
> ones(more
> > columns).
> >
> > For instance:
> > LivingEntity table has current_hp, max_hp, and base_ac columns.
> > StattedEntity would inherit those from LivingEntity and gain attributes
> that
> > ready it for combat: str, dex, con, etc...
> >
> > I'm trying to add a piece of functionality to the LivingEntity class that
> > gets passed down to its children. I want all children entity tables to
> have
> > two columns.
> > - One column is the entity's species type (Foliage, Fauna, Monster,
> > Race)
> > - The other column takes that type and goes to a table with that
> type in
> > the name, and uses the second column's entry as a foreign key for that
> > column.
> >
> > When I create an entity, I'd like to be able to give a function a
> string. It
> > searches through the fauna, foliage, monster, and race tables for that
> > string, and when it finds it, fills in the species_id, and species_type
> > columns appropriately.
> > Finally, I want to be able to query the foliage, fauna, monster, and race
> > tables to get general information about trees, deer, beholders, or
> humans.
> >
> > Code: (Bolded text is my main focus atm.)
> >
> > class Entity():
> > __tablename__ = 'Entity'
> >
> > id
> > name
> > description
> > parent_campaign
> >
> > class LivingEntity(Entity):
> > """Parent class for statted entities. Mainly basic metadata about
> > generic living creatures that won't be in combat."""
> > __tablename__ = 'LivingEntities'
> >
> > @declared_attr
> > def species_type(cls):
> > return Column('species_type', ForeignKey('SpeciesLookup.name'),
> > nullable=False)
> >
> > @declared_attr
> > def species_id(cls):
> > Column('species_id', ForeignKey(f'{species_type}Lookup.id'))
> > #species_type is out of scope here, yes?
> >
> > current_hp = Column(Integer, nullable=False)
> > max_hp = Column(Integer, nullable=False)
> > base_ac = Column(Integer, nullable=False)
> >
> > @declared_attr
> > def SpeciesLookup(cls):
> > return relationship('SpeciesLookup')
> >
> > @declared_attr
> > def I_don't_even_know_how_to_begin_to_define_this_relationship(cls):
> > return relationship(f'{species_type}Lookup')
> >
> >
> > class StattedEntity(LivingEntity):
> > """Parent class for monster, character, and player tables. Adds
> combat
> > and hex map functionality."""
> > __tablename__ = 'StattedEntity'
> >
> > level
> > armor_ac_bonus
> > 

Re: [sqlalchemy] Inherit foreign key to a dynamic table based on inherited column value

[Mike Bayer]

when you have CharacterEntity, would there also be
MonsterCharacterEntity, FaunaCharacterEntity, etc. ?  if you had
"Monster", "Fauna", "Race", "Foliage" mixins that are applied to the
LivingEntity hierarchy that would be a place to hold your
"species_lookup" relationship.  What is the base of the hierarchy of
RaceLookup/MonsterLookup/FaunaLookup/FoliageLookup?  there should be
some common term for that too like TaxonomyLookup or something.  since
we are heavy with entity terminology here.

On Thu, Apr 5, 2018 at 8:52 AM, Johnathan Norton
 wrote:
> Disclaimer: I'm very new at this. Only been learning databases for a couple
> weeks, and been working with python for about a month and 1/2. I did do
> several searches, phrasing things differently, but I can't seem to find my
> answer.
>
> I'm trying to build a overly complex DnD database and cmdline program to GM
> with.
> Current issue is that I'm playing with a lot of mixin classes, creating new
> tables for more and more complex entities so I'm not having simple
> entities(less columns) waste space in the same table with complex ones(more
> columns).
>
> For instance:
> LivingEntity table has current_hp, max_hp, and base_ac columns.
> StattedEntity would inherit those from LivingEntity and gain attributes that
> ready it for combat: str, dex, con, etc...
>
> I'm trying to add a piece of functionality to the LivingEntity class that
> gets passed down to its children. I want all children entity tables to have
> two columns.
> - One column is the entity's species type (Foliage, Fauna, Monster,
> Race)
> - The other column takes that type and goes to a table with that type in
> the name, and uses the second column's entry as a foreign key for that
> column.
>
> When I create an entity, I'd like to be able to give a function a string. It
> searches through the fauna, foliage, monster, and race tables for that
> string, and when it finds it, fills in the species_id, and species_type
> columns appropriately.
> Finally, I want to be able to query the foliage, fauna, monster, and race
> tables to get general information about trees, deer, beholders, or humans.
>
> Code: (Bolded text is my main focus atm.)
>
> class Entity():
> __tablename__ = 'Entity'
>
> id
> name
> description
> parent_campaign
>
> class LivingEntity(Entity):
> """Parent class for statted entities. Mainly basic metadata about
> generic living creatures that won't be in combat."""
> __tablename__ = 'LivingEntities'
>
> @declared_attr
> def species_type(cls):
> return Column('species_type', ForeignKey('SpeciesLookup.name'),
> nullable=False)
>
> @declared_attr
> def species_id(cls):
> Column('species_id', ForeignKey(f'{species_type}Lookup.id'))
> #species_type is out of scope here, yes?
>
> current_hp = Column(Integer, nullable=False)
> max_hp = Column(Integer, nullable=False)
> base_ac = Column(Integer, nullable=False)
>
> @declared_attr
> def SpeciesLookup(cls):
> return relationship('SpeciesLookup')
>
> @declared_attr
> def I_don't_even_know_how_to_begin_to_define_this_relationship(cls):
> return relationship(f'{species_type}Lookup')
>
>
> class StattedEntity(LivingEntity):
> """Parent class for monster, character, and player tables. Adds combat
> and hex map functionality."""
> __tablename__ = 'StattedEntity'
>
> level
> armor_ac_bonus
> init_bonus
> initiative
> speed
> etc...
>
> class SentientEntity():
> """Parent class for any entities needing sentient functionality."""
> __tablename__ = 'SentientEntity'
>
> alignment
> religion
> behaviors
> current_goal
> fears
>
> class CharacterEntity(StattedEntity, SentientEntity, Base):
> """Holds instances of simple NPCs."""
> profession
> talents
> brief_backstory
> party
> stress
> main_goal
>
> class PlayerEntity(StattedEntity, SentientEntity, Base):
> """Holds instances of player characters."""
> class_id
> backstory_file
> storybook_file
> wis_save
> cha_save
> athletics
> acrobatics
> sleight_of_hand
> stealth
> location
> etc...
>
> class SpeciesLookup(Entity, Base):
> """Lookup table for species types."""
> __tablename__ = 'SpeciesLookup'
>
> class FoliageLookup(Entity, Base):
> """lookup table for all foliage species"""
> __tablename__ = 'FoliageLookup'
> specific_attribute_1
> specific_attribute_3
>
> class FaunaLookup(Entity, Base):
> """Lookup table for all innocent fauna"""
> __tablename__ = 'FaunaLookup'
> specific_attribute_5
> specific_attribute_2
>
> class MonsterLookup(Entity, Base):
> """Lookup table for all monsters, beasts"""
> __tablename__ = 'MonsterLookup'
> specific_attribute_7
> specific_attribute_6
>
> class RaceLookup(Entity, Base):
> """lookup table for all races"""
> 

[sqlalchemy] Inherit foreign key to a dynamic table based on inherited column value

[Johnathan Norton]

Disclaimer: I'm very new at this. Only been learning databases for a couple 
weeks, and been working with python for about a month and 1/2. I did do 
several searches, phrasing things differently, but I can't seem to find my 
answer.

I'm trying to build a overly complex DnD database and cmdline program to GM 
with.
Current issue is that I'm playing with a lot of mixin classes, creating new 
tables for more and more complex entities so I'm not having simple 
entities(less columns) waste space in the same table with complex ones(more 
columns).

For instance:
LivingEntity table has current_hp, max_hp, and base_ac columns.
StattedEntity would inherit those from LivingEntity and gain attributes 
that ready it for combat: str, dex, con, etc...

I'm trying to add a piece of functionality to the LivingEntity class that 
gets passed down to its children. I want all children entity tables to have 
two columns.
- One column is the entity's species type (Foliage, Fauna, Monster, 
Race)
- The other column takes that type and goes to a table with that type 
in the name, and uses the second column's entry as a foreign key for that 
column.

When I create an entity, I'd like to be able to give a function a string. 
It searches through the fauna, foliage, monster, and race tables for that 
string, and when it finds it, fills in the species_id, and species_type 
columns appropriately.
Finally, I want to be able to query the foliage, fauna, monster, and race 
tables to get general information about trees, deer, beholders, or humans.

Code: (Bolded text is my main focus atm.)

class Entity():
__tablename__ = 'Entity'

id
name
description
parent_campaign

class LivingEntity(Entity):
"""Parent class for statted entities. Mainly basic metadata about 
generic living creatures that won't be in combat."""
__tablename__ = 'LivingEntities'

@declared_attr
def species_type(cls):
return Column('species_type', ForeignKey('SpeciesLookup.name'), 
nullable=False)



*@declared_attrdef species_id(cls):
Column('species_id', ForeignKey(f'{species_type}Lookup.id')) #species_type 
is out of scope here, yes?*

current_hp = Column(Integer, nullable=False)
max_hp = Column(Integer, nullable=False)
base_ac = Column(Integer, nullable=False)

@declared_attr
def SpeciesLookup(cls):
return relationship('SpeciesLookup')


*@declared_attrdef 
I_don't_even_know_how_to_begin_to_define_this_relationship(cls):*
*return relationship(f'{species_type}Lookup')*


class StattedEntity(LivingEntity):
"""Parent class for monster, character, and player tables. Adds combat 
and hex map functionality."""
__tablename__ = 'StattedEntity'

level
armor_ac_bonus
init_bonus
initiative
speed
etc...

class SentientEntity():
"""Parent class for any entities needing sentient functionality."""
__tablename__ = 'SentientEntity'

alignment
religion
behaviors
current_goal
fears

class CharacterEntity(StattedEntity, SentientEntity, Base):
"""Holds instances of simple NPCs."""
profession
talents
brief_backstory
party
stress
main_goal

class PlayerEntity(StattedEntity, SentientEntity, Base):
"""Holds instances of player characters."""
class_id
backstory_file
storybook_file
wis_save
cha_save
athletics
acrobatics
sleight_of_hand
stealth
location
etc...

class SpeciesLookup(Entity, Base):
"""Lookup table for species types."""
__tablename__ = 'SpeciesLookup'

class FoliageLookup(Entity, Base):
"""lookup table for all foliage species"""
__tablename__ = 'FoliageLookup'
specific_attribute_1
specific_attribute_3

class FaunaLookup(Entity, Base):
"""Lookup table for all innocent fauna"""
__tablename__ = 'FaunaLookup'
specific_attribute_5
specific_attribute_2

class MonsterLookup(Entity, Base):
"""Lookup table for all monsters, beasts"""
__tablename__ = 'MonsterLookup'
specific_attribute_7
specific_attribute_6

class RaceLookup(Entity, Base):
"""lookup table for all races"""
__tablename__ = 'RaceLookup'
specific_attribute_9
specific_attribute_11

-- 
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 it safe to re-use a closed session?

[Mike Bayer]

On Thu, Apr 5, 2018 at 7:12 AM, 'Brian Candler' via sqlalchemy
 wrote:
> The documentation doesn't seem to be 100% clear on this point: if you
> close() a session, can you continue to use it as if it were a fresh session?
> (Unlike say a closed filehandle)
>
> In other words, in a non-threaded application, is this a valid pattern?
>
> Session = sessionmaker(bind=engine)
> session = Session()
>
> def my_request():
> # Each request uses the same session object but cleans it up at the end
> try:
> 
> finally:
> session.close()
>
> As opposed to:
>
> def my_request():
> # Each request uses a separate session object
> session = Session()
> try:
> 
> finally:
> session.close()
>
> The reason for asking is that there are times where it would be more
> convenient to create a single session up-front and use it where required,
> closing at the end of each unit of work, than have to pass around a new
> session through multiple levels of function calls.

currently the Session will allow this, close() just closes out all of
its current state and leaves it available to be used again like a
brand new session.  It's always worked this way so there's not really
any way I could change this now.The only caveat to this is in the
case of failure modes in the Session, e.g. when some error has been
raised and the Session does not fully recover its state correctly,
meaning the Session might be unusable from that point forward...there
have probably been some issues like this in the past and I'd have to
search to find out what they were.

that said, you definitely do *not* want to share the same Session
through concurrent requests, I see you use the term "non-threaded", so
OK.

If this Session is a global object of some kind which is no longer
passed to functions, there's still not much difference between calling
.close() on it and just re-assigning that session global to a new
Session object.

>
> In particular, flask-sqlalchemy wants to create a single session (albeit a
> scoped session).

a scoped session is a whole different thing.   that's a registry of
many Session objects keyed to a threading.local object.   I'm not sure
what flask-sqlalchemy's request cleanup code is but I'd imagine they
are calling scoped_session.remove(), and not just
scoped_session.close(), which means you would get a new Session.



> Hence if I want to use the same models in non-Flask code,
> I need to reference that session and just close it at the end of each unit
> of work, rather than create a fresh session each time.

it shouldn't be a problem but I'm not sure what you are gaining by doing so.

>
> Thanks,
>
> Brian.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

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

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Is it safe to re-use a closed session?

['Brian Candler' via sqlalchemy]

The documentation  
doesn't seem to be 100% clear on this point: if you close() a session, can 
you continue to use it as if it were a fresh session?  (Unlike say a closed 
filehandle)

In other words, in a non-threaded application, is this a valid pattern?

Session = sessionmaker(bind=engine)
session = Session()

def my_request():
# Each request uses the same session object but cleans it up at the end
try:

finally:
session.close()

As opposed to:

def my_request():
# Each request uses a separate session object
session = Session()
try:

finally:
session.close()

The reason for asking is that there are times where it would be more 
convenient to create a single session up-front and use it where required, 
closing at the end of each unit of work, than have to pass around a new 
session through multiple levels of function calls.

In particular, flask-sqlalchemy wants to create a single session (albeit a 
scoped session).  Hence if I want to use the same models in non-Flask code, 
I need to reference that session and just close it at the end of each unit 
of work, rather than create a fresh session each time.

Thanks,

Brian.

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