Python Forum
advice needed to construct queries in flask-sqlalchemy from mysql select
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
advice needed to construct queries in flask-sqlalchemy from mysql select
#1
Hi,
I have a libre office database for my accounts and I am trying to create a flask app on a local net to reproduce the database making it easier to access the database from different pc's on the network, I have created the flask app and I have created the routes to import the data into the new database, but I need advise on the best setup for the queries.
the queries can be very large and are often nested (querying queries). I do not know the correct technical terms.
With accounts there are about 100 different accounts, passive and active, and each needs to be queried to see the transactions in and out, and their balance. My app has several different modules, "User access login", "Books", "DATA", "Sales", "VAT returns", "Balance sheet" and "Stock". Each needs to be able to access the different queries, to avoid repetition of the queries I thought I would put all the queries into a seperate module and then import them. The queries are built up from a base query. example:MONGENCRED
Quote:SELECT "Date", "Crebit", "Amount" FROM "DATA" WHERE "PID" = :AN
From this query "DATA" is the table representing the ledger with all the different movements, "Date", "Amount" are obvious, "Credit" is the movement of the account in and "PID" is the accounting year. from this query I build up other queries to interogate each account.
ie to see all movement in "Credit" to the bank '512000' I use the following query: "512000"
Quote:SELECT * FROM ( SELECT "ID", "DEBIT", "CREDIT", CASE WHEN "CREDIT" IS NULL THEN 0 ELSE "CREDIT" END - CASE WHEN "DEBIT" IS NULL THEN 0 ELSE "DEBIT" END "Solde" FROM "GEN" LEFT JOIN ( SELECT "Dedit", SUM( "MONGENDEB"."Amount" ) "DEBIT" FROM "MONGENDEB" GROUP BY "Dedit" ) "A" ON "GEN"."ID" = "A"."Dedit" LEFT JOIN ( SELECT "Crebit", SUM( "MONGENCREB"."Amount" ) "CREDIT" FROM "MONGENCREB" GROUP BY "Crebit" ) "B" ON "GEN"."ID" = "B"."Crebit" ) WHERE "ID" = '512000'
In my existing database I have a total of about 100 queries, in my app these will need to be access on different pages. I know how to query the whole table ie:
Quote:books = Book.query.all()
But as there are so many queries I dont know if they should be in classes, or each should be a function; ie
def mongencred():
    books = Book.query.all()
    return foo bar
I dont know how to translate the mysql query into a flask-sqlalchemy query. I need advise on:
How to construct a basic query in flask-sqlalchemy, after i can look at the documentation in sqlalchemy to construct joins etc. And how to set up a "query" module I can import easily. I have no technical training in python or data base, I am english but live in France so all the words I use are French, for the French Tax system, and even though I am strugling to understand I am addicted to python and especially Flask.
Any Help greatly appreciated.
Paul
Attached is my "models.py"

Attached Files

.py   models.py (Size: 6.71 KB / Downloads: 2)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Array to construct matrix Felipe 1 3,870 Jan-16-2017, 06:11 PM
Last Post: micseydel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020