Re: [sqlalchemy] convert subset to dictionary
Are you eager-loading the "student.subjects" relationship? If not,
that will give you the biggest performance increase. Without that, you
will be issuing a separate DB query for each of the students, to load
that student's subjects. Eager-loading allows you to preload the
subjects for every student in a single query:
https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#joined-eager-loading
Simon
On Fri, Jul 3, 2020 at 1:36 PM Justvuur wrote:
>
> Hi Simon, thanks for the help! I've never used that before, it's quite handy.
>
> I'm looping through all the students and printing them and their subject
> details to a CSV file.
> What makes things a tad complicated is the subjects must appear in a specific
> order.
> There is a table that has the subject code and order number (ordered_subjects
> used below is the resultset from it).
> I printed out the timing and found the problem to be with a nested for loop.
>
> I was hoping to reduce that process time by using a map that automatically
> gets populated instead of having to create it on the fly.
>
> Before - subjects_collection "attribute_mapped_collection":
>
> for row in students:
> row_no += 1
>
> for subject in row.subjects:
> student_subjects[subject.code] = subject.value
>
> csv_row = [row_no]
> csv_row += [student_subjects.get(x.code, '') for x in ordered_subjects]
> csv_row += [row.created_on, row.updated_on]
>
> writer.writerow([x.encode('utf-8') if type(x) == unicode else x for x in
> csv_row])
>
>
> After adding the subjects_collection "attribute_mapped_collection", I
> unfortunately did not see a change in performance.
>
> After - subjects_collection "attribute_mapped_collection":
>
> for row in students:
> row_no += 1
> csv_row = [row_no]
> csv_row += [row.subjects_collection.get(x.code, '').value for x in
> ordered_subjects]
> csv_row += [row.created_on, row.updated_on]
>
> writer.writerow([x.encode('utf-8') if type(x) == unicode else x for x in
> csv_row])
>
>
> class Subject(db.Model):
> __tablename__ = 'subjects'
>
> student_id = db.Column(db.Integer, db.ForeignKey('students.id'),
> primary_key=True)
>
> code = db.Column(db.String(50), primary_key=True)
>
> value= db.Column(db.String)
>
> def __init__(self, code , value):
> self.code = code
> self.value = value
>
>
> class Student(ResourceMixin, db.Model):
> __tablename__ = 'students'
>
> subjects= db.relationship('Subject', backref='student')
>
> id = db.Column(db.Integer, primary_key=True)
>
> subjects_collection = relationship("Subject",
> collection_class=attribute_mapped_collection('code'))
>
> Can you see a way I can optimize this? Any ideas?
>
>
> On Friday, 3 July 2020 12:31:03 UTC+2, Simon King wrote:
>>
>> Are you trying to optimise the database access (ie. minimize the
>> number of queries), or provide a nice dictionary-style API for your
>> Student objects? What do you mean when you say that looping over
>> student.subjects is quite heavy?
>>
>> An association proxy can be used to get dict-style access to a relationship:
>>
>>
>> https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#proxying-to-dictionary-based-collections
>>
>> There are also a couple of examples in the SQLAlchemy docs that
>> provide a dictionary-style API:
>>
>>
>> https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.dynamic_dict
>>
>>
>> https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.vertical
>>
>> Hope that helps,
>>
>> Simon
>>
>> On Thu, Jul 2, 2020 at 8:46 PM Justvuur wrote:
>> >
>> > Hi there,
>> >
>> > I'm struggling to find an efficient way to get a two columned subset into
>> > dictionary form.
>> >
>> > I have an entity that has a subset of data. The subset is linked to the
>> > entity via Id. The order of the subset of data is defined in another table.
>> >
>> > Example:
>> > Student - Id, firstname, lastname
>> > Subjects - StudentId, SubjectCode, SubjectName
>> >
>> > At the moment I'm looping through the SqlAlchemy result of
>> > "student.subjects" in python and creating a dictionary from that. It's
>> > quite heavy, especially when there are 2000+ students with a potential of
>> > 100+ subjects each.
>> >
>> > For each student, how do I get the subjects as a dictionary for a student
>> > where the key is the SubjectCode and the value is the SubjectName?
>> > Better yet, how can I get a result set: Id, firstname, lastname
>> > SubjectCode x, SubjectCode y, etc etc (where the SubjectName becomes the
>> > value and the SubjectCode becomes the column)?
>> >
>> > Regards,
>> > Justin
>> >
>> > --
>> > SQLAlchemy -
>> > The Python SQL Toolkit and Object Relational Mapper
>> >
>> > http://www.sqlalchemy.org/
>> >
>> >
Re: [sqlalchemy] sequence in sqlalchemy
hi there -
SQLite does not support SEQUENCE objects.
On Fri, Jul 3, 2020, at 1:14 AM, Anoop Thiparala wrote:
> id = db.Column(db.Integer, Sequence('patients_seq', start=1,
> increment=1) , primary_key=True)
>
> any reason why this is not working with sqlite3
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/0357a1ac-7409-414a-b65a-006360c383feo%40googlegroups.com
>
> .
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/33faaac6-293a-446c-9d88-00acd6b4b33d%40www.fastmail.com.
Re: [sqlalchemy] "Virtual models" for JSONB data?
On Fri, Jul 3, 2020, at 9:21 AM, Pedro Ferreira wrote:
> Hi,
>
> I was wondering if there's some way to create some sort of a "virtual"
> model which is not based on an actual table?
>
> Use case: I have a model (e.g. `Room`) which contains a `data` JSONB
> attribute/column which is supposed to store plugin-specific data. I
> would like plugin code to be able to create a "virtual" model which
> references `Room` 1-to-1 and injects a `plugin_data` backref into it.
> That means I would be able to do `room.plugin_data.user`, for instance,
> which would use `room.data['user_id']` to fetch a `User` object.
>
> Right now, our plugins do this by means of an additional table. E.g., in
> this case we would have `PluginRoom`, which would store `user_id` and
> have a 1-to-1 relationship with `Room`, with a backref.
>
> I was wondering if there's some way we can have a similar pattern based
> on unstructured JSONB data.
Python can do this using patterns like the `__getattr__` method. here's a 30
second proof of concept. note the use of flag_modified() to ensure that an
attribute set is flagged for unit of work update.
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.orm.attributes import flag_modified
Base = declarative_base()
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
_data = Column(JSONB, default={})
def __init__(self, **kw):
self._data = {}
self._data.update(kw)
def __getattr__(self, key):
if key.startswith("_"):
raise AttributeError(key)
return self._data[key]
def __setattr__(self, key, value):
if key.startswith("_"):
object.__setattr__(self, key, value)
else:
self._data[key] = value
flag_modified(self, "_data")
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
a1 = A(foo="bar", bar="bat")
s.add(a1)
s.commit()
assert s.query(A._data).first() == ({"foo": "bar", "bar": "bat"},)
a1.bar = "new bat"
s.commit()
assert s.query(A._data).first() == ({"foo": "bar", "bar": "new bat"},)
>
> Cheers,
>
> Pedro
>
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/5581edb1-3cbe-2179-f925-dbeaaa7523b8%40cern.ch.
>
>
> *Attachments:*
> * signature.asc
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c7e0b7f2-49fd-4fce-8cd6-b4b4ecca530b%40www.fastmail.com.
[sqlalchemy] "Virtual models" for JSONB data?
Hi, I was wondering if there's some way to create some sort of a "virtual" model which is not based on an actual table? Use case: I have a model (e.g. `Room`) which contains a `data` JSONB attribute/column which is supposed to store plugin-specific data. I would like plugin code to be able to create a "virtual" model which references `Room` 1-to-1 and injects a `plugin_data` backref into it. That means I would be able to do `room.plugin_data.user`, for instance, which would use `room.data['user_id']` to fetch a `User` object. Right now, our plugins do this by means of an additional table. E.g., in this case we would have `PluginRoom`, which would store `user_id` and have a 1-to-1 relationship with `Room`, with a backref. I was wondering if there's some way we can have a similar pattern based on unstructured JSONB data. Cheers, Pedro -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5581edb1-3cbe-2179-f925-dbeaaa7523b8%40cern.ch. signature.asc Description: OpenPGP digital signature
Re: [sqlalchemy] convert subset to dictionary
Hi Simon, thanks for the help! I've never used that before, it's quite
handy.
I'm looping through all the students and printing them and their subject
details to a CSV file.
What makes things a tad complicated is the subjects must appear in a
specific order.
There is a table that has the subject code and order number
(ordered_subjects used below is the resultset from it).
I printed out the timing and found the problem to be with a nested for
loop.
I was hoping to reduce that process time by using a map that automatically
gets populated instead of having to create it on the fly.
Before - subjects_collection "attribute_mapped_collection":
for row in students:
row_no += 1
for subject in row.subjects:
student_subjects[subject.code] = subject.value
csv_row = [row_no]
csv_row += [student_subjects.get(x.code, '') for x in ordered_subjects]
csv_row += [row.created_on, row.updated_on]
writer.writerow([x.encode('utf-8') if type(x) == unicode else x for x
in csv_row])
After adding the subjects_collection "attribute_mapped_collection", I
unfortunately did not see a change in performance.
After - subjects_collection "attribute_mapped_collection":
for row in students:
row_no += 1
csv_row = [row_no]
csv_row += [row.subjects_collection.get(x.code, '').value for x in
ordered_subjects]
csv_row += [row.created_on, row.updated_on]
writer.writerow([x.encode('utf-8') if type(x) == unicode else x for x
in csv_row])
class Subject(db.Model):
__tablename__ = 'subjects'
student_id = db.Column(db.Integer, db.ForeignKey('students.id'),
primary_key=True)
code = db.Column(db.String(50), primary_key=True)
value= db.Column(db.String)
def __init__(self, code , value):
self.code = code
self.value = value
class Student(ResourceMixin, db.Model):
__tablename__ = 'students'
subjects= db.relationship('Subject', backref='student')
id = db.Column(db.Integer, primary_key=True)
subjects_collection = relationship("Subject",
collection_class=attribute_mapped_collection('code'))
Can you see a way I can optimize this? Any ideas?
On Friday, 3 July 2020 12:31:03 UTC+2, Simon King wrote:
>
> Are you trying to optimise the database access (ie. minimize the
> number of queries), or provide a nice dictionary-style API for your
> Student objects? What do you mean when you say that looping over
> student.subjects is quite heavy?
>
> An association proxy can be used to get dict-style access to a
> relationship:
>
>
> https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#proxying-to-dictionary-based-collections
>
>
> There are also a couple of examples in the SQLAlchemy docs that
> provide a dictionary-style API:
>
>
> https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.dynamic_dict
>
>
>
> https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.vertical
>
> Hope that helps,
>
> Simon
>
> On Thu, Jul 2, 2020 at 8:46 PM Justvuur >
> wrote:
> >
> > Hi there,
> >
> > I'm struggling to find an efficient way to get a two columned subset
> into dictionary form.
> >
> > I have an entity that has a subset of data. The subset is linked to the
> entity via Id. The order of the subset of data is defined in another table.
> >
> > Example:
> > Student - Id, firstname, lastname
> > Subjects - StudentId, SubjectCode, SubjectName
> >
> > At the moment I'm looping through the SqlAlchemy result of
> "student.subjects" in python and creating a dictionary from that. It's
> quite heavy, especially when there are 2000+ students with a potential of
> 100+ subjects each.
> >
> > For each student, how do I get the subjects as a dictionary for a
> student where the key is the SubjectCode and the value is the SubjectName?
> > Better yet, how can I get a result set: Id, firstname, lastname
> SubjectCode x, SubjectCode y, etc etc (where the SubjectName becomes the
> value and the SubjectCode becomes the column)?
> >
> > Regards,
> > Justin
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlal...@googlegroups.com .
> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/57b74c9a-a6e5-494b-b468-d0bdcbcce60co%40googlegroups.com.
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit
Re: [sqlalchemy] convert subset to dictionary
Are you trying to optimise the database access (ie. minimize the number of queries), or provide a nice dictionary-style API for your Student objects? What do you mean when you say that looping over student.subjects is quite heavy? An association proxy can be used to get dict-style access to a relationship: https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#proxying-to-dictionary-based-collections There are also a couple of examples in the SQLAlchemy docs that provide a dictionary-style API: https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.dynamic_dict https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.vertical Hope that helps, Simon On Thu, Jul 2, 2020 at 8:46 PM Justvuur wrote: > > Hi there, > > I'm struggling to find an efficient way to get a two columned subset into > dictionary form. > > I have an entity that has a subset of data. The subset is linked to the > entity via Id. The order of the subset of data is defined in another table. > > Example: > Student - Id, firstname, lastname > Subjects - StudentId, SubjectCode, SubjectName > > At the moment I'm looping through the SqlAlchemy result of "student.subjects" > in python and creating a dictionary from that. It's quite heavy, especially > when there are 2000+ students with a potential of 100+ subjects each. > > For each student, how do I get the subjects as a dictionary for a student > where the key is the SubjectCode and the value is the SubjectName? > Better yet, how can I get a result set: Id, firstname, lastname SubjectCode > x, SubjectCode y, etc etc (where the SubjectName becomes the value and the > SubjectCode becomes the column)? > > Regards, > Justin > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/57b74c9a-a6e5-494b-b468-d0bdcbcce60co%40googlegroups.com. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeFF0TvOMhapyPgFeZsPnwgsbOUWgiO%2B_YAtyPrd2JF6A%40mail.gmail.com.
