1

I'm building a webapp for a small business, which is supposed to be some sort of mini ERP. The webapp is built with AngularJS, so I'm using the server side mostly to manage the database and provide a nice REST api.

I really like working with ORMs, and it really fits my needs. I chose SQLAlchemy for my database layer since I find it very easy and convenient to work with (and I also love Python). However, when using SQLAlchemy's ORM layer, and you want to query some entities for results (including its children when necessary), it executes the query requested (which is quite fast), but then, whenever it gets the results, it creates a Python object for each result, and also (to my understanding), does a lot of work maintaining those result objects, allowing the user to work with them and keep them in sync with the database. However, wrapping the results and creating SQLAlchemy entities (I think it's called unit-of-work in SQLAlchemy), is very very slow (few thousands results take few seconds to finish), as opposed to just getting the results (in tuples) which is pretty fast.

Thing is, since I'm using AngularJS, all I want is that those results will be converted to JSON, instead of converting them first to Python objects and doing tons of unnecessary work. I really like working with the ORM, since it helps you manage nested queries and relationships, however if I won't have a choice, I will probably fall back to SQLAlchemy Core, but it's going to be very hard for me to parse the results into JSON objects (and managing all those queries for the REST apis, which some of them are going to be custom queries constructed by the user... it's a management system after all...). SQLAlchemy's ORM already does the results parsing and generating the queries, I just want to it create JSON objects (which can actually be just Python's dictionaries and lists), rather than creating pure Python objects.

Ideas how to accomplish what I want?

TL;DR:

Read title.

rboy
  • 750
  • 9
  • 20

1 Answers1

2

If you are fortunate enough to use the most recentish PostgreSQL, you can use the recent JSON functions such as row_to_json() function on server side already:

 select row_to_json(mytable) from mytable

Which returns each line as a string of the format '{"column":"value","column2":"value2"}'. Then you can iterate the resulting lines using SQLAlchemy Core straight to the client side without touching them in Python.

Antti Haapala -- Слава Україні
  • 129,958
  • 22
  • 279
  • 321
  • This is a very nice feature. Unfortunately my db of choice is MySQL, which I have some experience with. I'm interested in how this feature handles joins? I mean, if you have a Posts table, and a Comments table, and you would like to get all the posts and for each post all its comments, the expected JSON supposed to be nested, does PostgreSQL knows how to handle this, or will it just return a flat JSON object with all the joined columns as keys? – rboy Sep 02 '14 at 03:59
  • Yeah, so far a flat object. All in all, if you need to support object graphs, you will not pay much extra for the ORM in SA - notice that say if your comments have authors that are User, then SA needs to ORM each user only once and then map by PK. – Antti Haapala -- Слава Україні Sep 02 '14 at 04:15
  • I didn't say that it's slow without testing. I know that SQLAlchemy stores recent fetched objects in a cache (session), and retrieves them from there if needed, without sending another query. Anyway, for objects not in the session, the process of creating them and maintaining them is very very slow. I did a test an retrieved 20 Posts, with each post having 50 comments, and each comment having 25 sub-comment (25000 results), and it took 2.6 seconds wrapping those results, while only getting the results (query execution and passing over the the socket took 0.02 seconds). – rboy Sep 02 '14 at 04:29
  • How many queries were issued? If you use lazy loading (default), then additional queries are issued for each relation fetched, these are ofc not part of the initial query time. – Antti Haapala -- Слава Україні Sep 02 '14 at 06:08
  • See the relevant [documentation](http://docs.sqlalchemy.org/en/latest/orm/loading.html) – Antti Haapala -- Слава Україні Sep 02 '14 at 06:09
  • I know that my friend. I tried different combinations - the longest time is of course `lazy` loading, but I'm really not talking about this.. it can take many seconds. I tried both `joined` and `subquery` in different combinations, but the results were very close (difference of ~0.3 seconds in favor of `subquery`). ORM is slow, that is a known fact... maybe it's useful if the content is rendered on the server side and the queries are known in advanced, maybe it will give chance to optimizations then.. but not when queries are issued in short intervals and can be customized. – rboy Sep 02 '14 at 06:32