Re: [sqlalchemy] session.add() neglecting some of my objects to be added

[David Laredo Razo]

i did as you instructed me but the error persists. 

This is the example code im talking about

Session = sessionmaker()
session = Session() 

mapper = inspect(ThermafuserReading)
readings = list()

header = ["hex(id(object))", "is transient", "is pending", "is persistent", 
"is detached", "is deleted", "is in session"]

#Open the csv file
csvFilePath = "/Users/davidlaredorazo/Box Sync/Data/Zone4/1C1A/1C1A 
2016-12-31.csv"
with open(csvFilePath, 'r') as csvfile:

reader = csv.reader(csvfile)
componentId = 1
count = 0

reading = ThermafuserReading(None, componentId)

for row in reader:

if count == 0:
count += 1
continue

#print(row)
timestamp = parse(row[0], None, ignoretz = True)

reading.timestamp = timestamp
new_object = copy.copy(reading)
new_object.timestamp = timestamp

readings.append(new_object)

#print(new_object, mapper.identity_key_from_instance(new_object))
#session.add(new_object)

row_format = "{:>15}" * (len(header) + 1)

print("Before adding to the session")
print(row_format.format("", *header))
for reading in readings:
insp = inspect(reading)
row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
insp.detached, insp.deleted, reading in session]
print(row_format.format("", *row))

session.add_all(readings)

print("\n#Elements in the session")
print(session)
for element in session:
print(element)

print("\nAfter adding to the session")
print(row_format.format("", *header))
for reading in readings:
insp = inspect(reading)
row = [hex(id(reading)), insp.transient, insp.pending, insp.persistent, 
insp.detached, insp.deleted, reading in session]
print(row_format.format("", *row))

These are some results I obtained by comparing wheter the objects in my 
list are in the session or not







As you can observe, according to the results above the objects are indeed 
inside the session but for some reason when I try to print whats contained 
in the session by doing

for element in session:
   print(element)

I just get a None, what am I doing wrong? I dont see anything wrong in my 
code, I hope you can help me clarify this. Thanks in advance.

I will attach both my code and the tests data in case you want to try it by 
yourself.


 


On Thursday, July 13, 2017 at 8:27:04 AM UTC-5, Mike Bayer wrote:
>
> On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo 
>  wrote: 
> > Hello, I am using SQLAlchemy version 1.2.0b1 
> > 
> > 
> > 
> > So far so go, the problem arises when I add readings to the session via 
> > session.add_all(readings). I only get the last element in my list added, 
> > e.g. 
>
> there's no reason at all that would happen, other than what's in 
> "readings" is not what you'd expect. 
>
> try iterating through every element in "readings" after the add_all(), 
> and do "obj in session". 
>
> If some of these objects were from a different session, then they may 
> be "detached" as you put them in in which case they'd go into 
> session.identity_map, not session.new. 
>
>
>
>
> > 
> > for new in session.new: 
> >print(new, mapper.identity_key_from_instance(new_object)) 
> > 
> >  
> > (, (datetime.datetime(2017, 1, 1, 
> 0, 
> > 0), 1)) 
> > 
> > 
> > Why is this behavior? I have a test code and the test data in case its 
> > needed to reproduce this behavior 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > -- 
> > 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 

[sqlalchemy] Re: What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

I just tried a revised version of the cache consumer method as follows:


def Record_entries_count(self):
# import pdb; pdb.set_trace()
db = cherrypy.request.db_session
query_subset = db.query(MyClass).merge_result(self.
search_result_cache)
result = query_subset.count()
return result

However, this gives me the following error:

AttributeError: 'list_iterator' object has no attribute 'count'


Why does `merge_result` return a list_iterator instead of the Query object 
itself? How can I perform a .count() method in this case? Thanks.



On Thursday, July 13, 2017 at 4:38:19 PM UTC-7, Jinghui Niu wrote:
>
> I have a web application served by cherrypy (, which is multi-threaded. ) 
>
> I'm trying to cache a set of rows queried from database using 
> `self.search_result_cache` variable on the GUI_Server object. On my 
> front-end, the web first request `list_entries` to prepare the rows and 
> stores them on `self.search_result_cache`. After that, on user's mouse 
> click the front-end initiats another request calling 
> `Record_entries_count`, which is expected to revive the Query from 
> `self.search_result_cache` and continue on to do some data refining, e.g. 
> summing up the count in this case.
>
> class GUI_Server:
>
>
> def __init__(self):
> self.search_result_cache = None
>
>
> @cherrypy.expose
> def list_entries(self, **criteriaDICT):
> # always store the result to self cache
> 
> ...
>
>
> db = cherrypy.request.db_session
>
>
> filter_func = getattr(self, 'filterCriteria_' + classmodel_obj.
> __name__)
> queryOBJ = filter_func(criteriaDICT, queryOBJ)
> self.search_result_cache = queryOBJ
> db.expunge_all()
>
> 
>
> def Record_entries_count(self):
> db = cherrypy.request.db_session
> query_subset = self.search_result_cache
> result = query_subset.count()
> return result
>
>
> But this doesn't work. It always give me an error:
>
> sqlite3.ProgrammingError: SQLite objects created in a thread can only be used 
> in that same thread.The object was created in thread id 139937752020736 and 
> this is thread id 139938238535424 
>
> I am already using `scoped_session` for each request session. I don't 
> understand why I got this error.
>
>
> What is the best pratice to cache queried result across different request 
> sessions like this? Thanks a lot.
>
>

-- 
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] What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

Hi Mike, I've read the example of dogpile caching. For my case
dogpile.cache seems to be a overkill, could you please provide a thinner
example of using Query.merge_result without involving another library?
Thanks.

On Thu, Jul 13, 2017 at 8:07 PM, Jinghui Niu  wrote:

> Thanks Mike. Just to clarify, so instead of caching a Query object, I
> should cache all those queried instance in my `self.search_result_cache`,
> is this the idea? Is there a way to just relay Query object from one method
> to another? Which seems a little simpler to me.
>
> On Thu, Jul 13, 2017 at 7:20 PM, Mike Bayer 
> wrote:
>
>> you need to use Session.merge and/or Query.merge_result so that a
>> *copy* of the detached object is placed into the Session.In
>> particular, Query.merge_result was created for the use case of caching
>> result sets.
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html?highligh
>> t=merge_result#sqlalchemy.orm.query.Query.merge_result
>>
>>
>> this links to the dogpile cache example at
>> http://docs.sqlalchemy.org/en/latest/orm/examples.html#modul
>> e-examples.dogpile_caching
>> illustrates a method that I've used in production successfully.
>>
>>
>>
>>
>>
>> On Thu, Jul 13, 2017 at 7:38 PM, Jinghui Niu 
>> wrote:
>> > I have a web application served by cherrypy (, which is multi-threaded.
>> )
>> >
>> > I'm trying to cache a set of rows queried from database using
>> > `self.search_result_cache` variable on the GUI_Server object. On my
>> > front-end, the web first request `list_entries` to prepare the rows and
>> > stores them on `self.search_result_cache`. After that, on user's mouse
>> click
>> > the front-end initiats another request calling `Record_entries_count`,
>> which
>> > is expected to revive the Query from `self.search_result_cache` and
>> continue
>> > on to do some data refining, e.g. summing up the count in this case.
>> >
>> > class GUI_Server:
>> >
>> >
>> > def __init__(self):
>> > self.search_result_cache = None
>> >
>> >
>> > @cherrypy.expose
>> > def list_entries(self, **criteriaDICT):
>> > # always store the result to self cache
>> >
>> > ...
>> >
>> >
>> > db = cherrypy.request.db_session
>> >
>> >
>> > filter_func = getattr(self, 'filterCriteria_' +
>> > classmodel_obj.__name__)
>> > queryOBJ = filter_func(criteriaDICT, queryOBJ)
>> > self.search_result_cache = queryOBJ
>> > db.expunge_all()
>> >
>> > 
>> >
>> > def Record_entries_count(self):
>> > db = cherrypy.request.db_session
>> > query_subset = self.search_result_cache
>> > result = query_subset.count()
>> > return result
>> >
>> >
>> > But this doesn't work. It always give me an error:
>> >
>> > sqlite3.ProgrammingError: SQLite objects created in a thread can only be
>> > used in that same thread.The object was created in thread id
>> 139937752020736
>> > and this is thread id 139938238535424
>> >
>> >
>> > I am already using `scoped_session` for each request session. I don't
>> > understand why I got this error.
>> >
>> >
>> > What is the best pratice to cache queried result across different
>> request
>> > sessions like this? Thanks a lot.
>> >
>> > --
>> > 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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit https://groups.google.com/d/to
>> pic/sqlalchemy/CWvLr8GPra4/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> 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 

Re: [sqlalchemy] What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

Thanks Mike. Just to clarify, so instead of caching a Query object, I
should cache all those queried instance in my `self.search_result_cache`,
is this the idea? Is there a way to just relay Query object from one method
to another? Which seems a little simpler to me.

On Thu, Jul 13, 2017 at 7:20 PM, Mike Bayer 
wrote:

> you need to use Session.merge and/or Query.merge_result so that a
> *copy* of the detached object is placed into the Session.In
> particular, Query.merge_result was created for the use case of caching
> result sets.
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html?
> highlight=merge_result#sqlalchemy.orm.query.Query.merge_result
>
>
> this links to the dogpile cache example at
> http://docs.sqlalchemy.org/en/latest/orm/examples.html#
> module-examples.dogpile_caching
> illustrates a method that I've used in production successfully.
>
>
>
>
>
> On Thu, Jul 13, 2017 at 7:38 PM, Jinghui Niu  wrote:
> > I have a web application served by cherrypy (, which is multi-threaded. )
> >
> > I'm trying to cache a set of rows queried from database using
> > `self.search_result_cache` variable on the GUI_Server object. On my
> > front-end, the web first request `list_entries` to prepare the rows and
> > stores them on `self.search_result_cache`. After that, on user's mouse
> click
> > the front-end initiats another request calling `Record_entries_count`,
> which
> > is expected to revive the Query from `self.search_result_cache` and
> continue
> > on to do some data refining, e.g. summing up the count in this case.
> >
> > class GUI_Server:
> >
> >
> > def __init__(self):
> > self.search_result_cache = None
> >
> >
> > @cherrypy.expose
> > def list_entries(self, **criteriaDICT):
> > # always store the result to self cache
> >
> > ...
> >
> >
> > db = cherrypy.request.db_session
> >
> >
> > filter_func = getattr(self, 'filterCriteria_' +
> > classmodel_obj.__name__)
> > queryOBJ = filter_func(criteriaDICT, queryOBJ)
> > self.search_result_cache = queryOBJ
> > db.expunge_all()
> >
> > 
> >
> > def Record_entries_count(self):
> > db = cherrypy.request.db_session
> > query_subset = self.search_result_cache
> > result = query_subset.count()
> > return result
> >
> >
> > But this doesn't work. It always give me an error:
> >
> > sqlite3.ProgrammingError: SQLite objects created in a thread can only be
> > used in that same thread.The object was created in thread id
> 139937752020736
> > and this is thread id 139938238535424
> >
> >
> > I am already using `scoped_session` for each request session. I don't
> > understand why I got this error.
> >
> >
> > What is the best pratice to cache queried result across different request
> > sessions like this? Thanks a lot.
> >
> > --
> > 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/CWvLr8GPra4/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> 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 

Re: [sqlalchemy] What is a best practice model for cache instances using their detached state?

[Mike Bayer]

you need to use Session.merge and/or Query.merge_result so that a
*copy* of the detached object is placed into the Session.In
particular, Query.merge_result was created for the use case of caching
result sets.

http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=merge_result#sqlalchemy.orm.query.Query.merge_result


this links to the dogpile cache example at
http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.dogpile_caching
illustrates a method that I've used in production successfully.





On Thu, Jul 13, 2017 at 7:38 PM, Jinghui Niu  wrote:
> I have a web application served by cherrypy (, which is multi-threaded. )
>
> I'm trying to cache a set of rows queried from database using
> `self.search_result_cache` variable on the GUI_Server object. On my
> front-end, the web first request `list_entries` to prepare the rows and
> stores them on `self.search_result_cache`. After that, on user's mouse click
> the front-end initiats another request calling `Record_entries_count`, which
> is expected to revive the Query from `self.search_result_cache` and continue
> on to do some data refining, e.g. summing up the count in this case.
>
> class GUI_Server:
>
>
> def __init__(self):
> self.search_result_cache = None
>
>
> @cherrypy.expose
> def list_entries(self, **criteriaDICT):
> # always store the result to self cache
>
> ...
>
>
> db = cherrypy.request.db_session
>
>
> filter_func = getattr(self, 'filterCriteria_' +
> classmodel_obj.__name__)
> queryOBJ = filter_func(criteriaDICT, queryOBJ)
> self.search_result_cache = queryOBJ
> db.expunge_all()
>
> 
>
> def Record_entries_count(self):
> db = cherrypy.request.db_session
> query_subset = self.search_result_cache
> result = query_subset.count()
> return result
>
>
> But this doesn't work. It always give me an error:
>
> sqlite3.ProgrammingError: SQLite objects created in a thread can only be
> used in that same thread.The object was created in thread id 139937752020736
> and this is thread id 139938238535424
>
>
> I am already using `scoped_session` for each request session. I don't
> understand why I got this error.
>
>
> What is the best pratice to cache queried result across different request
> sessions like this? Thanks a lot.
>
> --
> 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] inline polymorphic_load for single table inheritance

[Mike Bayer]

On Thu, Jul 13, 2017 at 4:37 PM, Shane Carey  wrote:
> When I have single table inheritance, how can I use the new 'inline'
> polymorphic_load feature to only query a subset of child classes?

'inline' polymorphic load is only about using fewer SQL queries to
load attributes on the objects you've already queried for.

>
> for c in s.query(Parent).all():
> print(c)
>
> I've tried adding and removing 'with_polymorphic': '*' from the parent
> class, but it always queries ChildTwo as well
>
> I feel like I need to set something on ChildTwo to tell it not to load, but
> nothing like this is documented

you'd query for ChildOne:

for c in s.query(ChildOne):
  # ...

docs:

http://docs.sqlalchemy.org/en/latest/orm/inheritance_loading.html#loading-single-inheritance



>
> --
> 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] What is a best practice model for cache instances using their detached state?

[Jinghui Niu]

I have a web application served by cherrypy (, which is multi-threaded. ) 

I'm trying to cache a set of rows queried from database using 
`self.search_result_cache` variable on the GUI_Server object. On my 
front-end, the web first request `list_entries` to prepare the rows and 
stores them on `self.search_result_cache`. After that, on user's mouse 
click the front-end initiats another request calling 
`Record_entries_count`, which is expected to revive the Query from 
`self.search_result_cache` and continue on to do some data refining, e.g. 
summing up the count in this case.

class GUI_Server:


def __init__(self):
self.search_result_cache = None


@cherrypy.expose
def list_entries(self, **criteriaDICT):
# always store the result to self cache

...


db = cherrypy.request.db_session


filter_func = getattr(self, 'filterCriteria_' + classmodel_obj.
__name__)
queryOBJ = filter_func(criteriaDICT, queryOBJ)
self.search_result_cache = queryOBJ
db.expunge_all()



def Record_entries_count(self):
db = cherrypy.request.db_session
query_subset = self.search_result_cache
result = query_subset.count()
return result


But this doesn't work. It always give me an error:

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used 
in that same thread.The object was created in thread id 139937752020736 and 
this is thread id 139938238535424 

I am already using `scoped_session` for each request session. I don't 
understand why I got this error.


What is the best pratice to cache queried result across different request 
sessions like this? Thanks a lot.

-- 
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: inline polymorphic_load for single table inheritance

[Shane Carey]

After reading the documentation more thoroughly, I realized that 
with_polymorphic does not filter the result set by subclasses included, it 
only eagerly loads the attributes of those subclasses.

In order to filter on certain subclasses, am I forced to user 
Query.filter() for that purpose?

-- 
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] inline polymorphic_load for single table inheritance

[Shane Carey]

When I have single table inheritance, how can I use the new 'inline' 
polymorphic_load feature to only query a subset of child classes?

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
__tablename__ = 'parent'

id = Column(Integer, primary_key=True)
type = Column(String(8), nullable=False)

__mapper_args__ = {
'polymorphic_on': type,
'with_polymorphic': '*'
}

class ChildOne(Parent):
one = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'one',
'polymorphic_load': 'inline'
}

class ChildTwo(Parent):
two = Column(String(8))

__mapper_args__ = {
'polymorphic_identity': 'two'
}

if __name__ == '__main__':
e = create_engine('sqlite:///inline.db', echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

s.add_all([ChildOne(one='hahaha'), ChildTwo(two='lololol')])

s.commit()

for c in s.query(Parent).all():
print(c)

I've tried adding and removing 'with_polymorphic': '*' from the parent 
class, but it always queries ChildTwo as well

I feel like I need to set something on ChildTwo to tell it not to load, but 
nothing like this is documented

-- 
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] session.add() neglecting some of my objects to be added

[Mike Bayer]

On Thu, Jul 13, 2017 at 12:31 AM, David Laredo Razo
 wrote:
> Hello, I am using SQLAlchemy version 1.2.0b1
>
>
>
> So far so go, the problem arises when I add readings to the session via
> session.add_all(readings). I only get the last element in my list added,
> e.g.

there's no reason at all that would happen, other than what's in
"readings" is not what you'd expect.

try iterating through every element in "readings" after the add_all(),
and do "obj in session".

If some of these objects were from a different session, then they may
be "detached" as you put them in in which case they'd go into
session.identity_map, not session.new.




>
> for new in session.new:
>print(new, mapper.identity_key_from_instance(new_object))
>
> 
> (, (datetime.datetime(2017, 1, 1, 0,
> 0), 1))
>
>
> Why is this behavior? I have a test code and the test data in case its
> needed to reproduce this behavior
>
>
>
>
>
>
>
>
>
>
> --
> 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] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

OK, thank a lot !

Le jeudi 13 juillet 2017 06:01:45 UTC+3, Mike Bayer a écrit :
>
> this is how that would have to be mapped, hypothetically: 
>
> class EngineerBase(Person): 
> __tablename__ = 'engineer' 
>
> id = Column(ForeignKey('person.id'), primary_key=True) 
> engineer_name = Column(String(30)) 
>
> __mapper_args__ = { 
> 'polymorphic_load': 'selectin' 
> } 
>
>
> class EngineerType1(EngineerBase): 
> __mapper_args__ = { 
> 'polymorphic_identity': 'engineer_t1', 
> 'polymorphic_load': 'inline' 
> } 
>
>
> class EngineerType2(EngineerBase): 
> __mapper_args__ = { 
> 'polymorphic_identity': 'engineer_t2', 
> 'polymorphic_load': 'inline' 
> } 
>
>
> doesn't work of course since the polymorphic selectin load is a new 
> feature trying to work its way into a very intricate workflow.  I 
> threw up 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4026/mixing-selectin-load-on-top-of-inline
>  
> to look into it.  not sure how easy / hard it would be. 
>
> On Wed, Jul 12, 2017 at 7:09 PM,   
> wrote: 
> > Here a MCWE : 
> > 
> > from sqlalchemy import Table, Column, Integer, String, ForeignKey, 
> > create_engine 
> > from sqlalchemy.orm import Session 
> > from sqlalchemy.ext.declarative import declarative_base 
> > 
> > 
> > Base = declarative_base() 
> > 
> > 
> > class Person(Base): 
> > __tablename__ = 'person' 
> > 
> > id = Column(Integer, primary_key=True) 
> > type = Column(String(50), nullable=False) 
> > name = Column(String(50)) 
> > 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'person', 
> > 'polymorphic_on': type 
> > } 
> > 
> > 
> > class Manager(Person): 
> > __tablename__ = 'manager' 
> > 
> > id = Column(ForeignKey('person.id'), primary_key=True) 
> > manager_name = Column(String(30)) 
> > 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'manager', 
> > 'polymorphic_load': 'selectin' 
> > } 
> > 
> > 
> > class EngineerBase(Person): 
> > __tablename__ = 'engineer' 
> > 
> > id = Column(ForeignKey('person.id'), primary_key=True) 
> > engineer_name = Column(String(30)) 
> > 
> > 
> > class EngineerType1(EngineerBase): 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'engineer_t1', 
> > 'polymorphic_load': 'selectin' 
> > } 
> > 
> > 
> > class EngineerType2(EngineerBase): 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'engineer_t2', 
> > 'polymorphic_load': 'selectin' 
> > } 
> > 
> > 
> > engine = create_engine('sqlite://') 
> > Base.metadata.create_all(engine) 
> > 
> > engine.echo = True 
> > 
> > session = Session(engine) 
> > 
> > eng1 = EngineerType1() 
> > eng2 = EngineerType2() 
> > 
> > session.add_all([eng1, eng2]) 
> > session.commit() 
> > 
> > session.query(Person).all() 
> > 
> > produces the following three queries : 
> > 
> > 2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine SELECT 
> person.id 
> > AS person_id, person.type AS person_type, person.name AS person_name 
> > FROM person 
> > 2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine () 
> > 2017-07-13 00:59:07,248 INFO sqlalchemy.engine.base.Engine SELECT 
> > engineer.id AS engineer_id, person.id AS person_id, person.type AS 
> > person_type 
> > FROM person JOIN engineer ON person.id = engineer.id 
> > WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id 
> > 2017-07-13 00:59:07,249 INFO sqlalchemy.engine.base.Engine (2, 
> > 'engineer_t2') 
> > 2017-07-13 00:59:07,253 INFO sqlalchemy.engine.base.Engine SELECT 
> > engineer.id AS engineer_id, person.id AS person_id, person.type AS 
> > person_type 
> > FROM person JOIN engineer ON person.id = engineer.id 
> > WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id 
> > 2017-07-13 00:59:07,254 INFO sqlalchemy.engine.base.Engine (1, 
> > 'engineer_t1') 
> > 
> > The last two queries can be grouped together. 
> > 
> > Le jeudi 13 juillet 2017 01:45:46 UTC+3, yoch@gmail.com a écrit : 
> >> 
> >> I have a mixed configuration with both joined and single table 
> subclasses 
> >> in a two-levels inheritance (like that), so selectin seems to be the 
> right 
> >> choice for me. 
> >> 
> >> Le jeudi 13 juillet 2017 01:09:50 UTC+3, Mike Bayer a écrit : 
> >>> 
> >>> On Wed, Jul 12, 2017 at 4:54 PM,   wrote: 
> >>> > I noticed that {'polymorphic_load': 'selectin'} on single table 
> >>> > inheritance 
> >>> > can make several SQL queries unnecessarily. 
> >>> 
> >>> well "selectin" loading would be inappropriate for single table 
> >>> inheritance because you are telling it to emit additional queries for 
> >>> additional subclasses, when in reality you'd like the original query 
> >>> just to include all columns. For this reason the traditional 
> >>> "with_polymorphic" approach is more appropriate for eager loading of 
> >>> single table subclasses. 
> >>>