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