SQL Alchemy versus psycopg2

[Tony S]

Just looking for some input as I am in the beginning process of moving an application from an MS base using SQL Server, SSRS and SSIS over to Postgres / Python (for now).

I am not looking at using the ORM for various reasons:

• Python will not be the only language or tool accessing and updating the DB over time
• Stored procedures (functions) are used almost exclusively for reasons of security (execute only access, SQL injection protection) and performance (each individual device (mostly GPS based) reports in at least every 30 seconds and we have to produce both near-real time state / position data and fairly complex analytical processing involving processing through thousands of records per tracked object using geospatial functions and data that exist in PostGIS.  Just one of the reports I run can involve comparing 60,000 geo records sequentially looking for changes, to produce a report of about 100 lines for just 1 vehicle out of the hundreds (soon thousands) that we track: it seems inefficient to pull all that data back through the ORM to process in Python, when most of the data will be discarded anyway.
• Complex data structures where objects of various types are related to other objects in one-to-many and many-to-many relationships that change over time and are reported over time, and where most data changes are also tracked and reported over time: easier to do this using views and functions to simplify development
• CTEs that are used for hierarchical processing such as company and departmental data structures (time variant)
• The database has already been designed and created using pgmodeler, which allows for graphical DB design and implementation
• The existing load procedures from the current source system (Oracle based) into the new PostgreSQL db are already written in Python using psycopg2, passing JSON data into stored functions and seems to work well, usually faster than the previous SQL Server /  SSIS solution

So what will sqlachemy get me over using psycopg2, considering that we will be using stored functions (procedures in Postgres 11) to process data for all reports / analytic extracts for the foreseeable future, and that over time we will be implementing a formal analytical / reporting toolset, web / mobile front ends using React, and so on?

TIA

Tony

[Mike Bayer]

Can't help but make the comment that ORMs can work with CTEs and you
can write them semantically the same as you'd do in straight SQL, as
well as that they are capable of working with database schemas that
are already designed. Does your program emit SELECT statements
(perhaps against views) and wish to handle result rows in Python in
some fashion? Will these rows eventually make their way into Python
objects? These are all things that high quality ORMs are meant to
help automate and save you the time of reinventing those layers
yourself. Beyond that, SQLAlchemy's ORM is just one optional
component on top of the entire Core system which is generally
considered to be helpful at many levels, including as a highly capable
base for any arbitrary database-row-to-python-object marshalling
system if you do have the need to create this, as would likely be the
case for a stored-procedure based system. I'd recommend looking
through the talks and tutorials at
http://www.sqlalchemy.org/library.html as well as
http://docs.sqlalchemy.org/en/latest/intro.html if you are curious
what SQLAlchemy offers.

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