The officially official Devuan Forum!

nahkhiirmees

Member

Registered: 2022-07-24

Posts: 261  

sqlalchemy

By the way, any Python nerds in this forum? Experienced in sqlalchemy?

(This is Devuan-related in the sense that the host OS is Devuan Chimaera. Inside and outside the vm. ')

First time in 10 years i try to write a simple app with Flask and SQLAlchemy. Should be a simple thing, but more the instructions i read, the more confused i become. A three day parade march to Kabul is turning into a 10-year-long-nightmare-in-the-mountains.

I mean, it should't bee too difficult to:

1. set up connection to a pre-existing db
2. query a table with user-provided value
3. show result back to user somehow

But i have been running into many kinds of errors today.

I think setting up the conn works

e=sqlalchemy.create_engine(<A_CONNECTION_STRING>, echo=True)
conn=e.connect()

at least i get no complaints from Flask.
I query the table with:

metadata=MetaData()
c=Table('table1', metadata, autoload_with=e)
q=c.select().where(c.id_field==user_provided_id)
res=conn.execute(q).fetchone()

... and couldn't get to phase #3 . There were all kinds of complaints. For example:"AttributeError 'Table' object has no attribute 'id_field' ".

I have confirmed, with psql that i can connect to the db with the connectionstring(to be more precise:login, password and other params) i have. Also i have confirmed that  name of the table and column are right. There also should be row with the id value i tried with.

Any working example code anaywhere which does what i'm looking for?
I mean i have read some of the sqlalchemy docs. Also tried https://www.datacamp.com/tutorial/sqlal … l-examples and https://www.blog.pythonlibrary.org/2010 … databases/ . To no avail.

One thing that can complicate simple things is Docker. I use bitnami/python:3.8.17. Tried also bitnami/python:latest. No difference, the Flask app is still not working.

Nothing surprising in Dockerfile: WORKDIR , COPY requirements.txt into container and RUN pip -r requirements.txt . And outside of Dockerfile, mount the app directory.

Inside vm, "dpkg -l docker*" says:

ii docker-compose 1.25.0-1
ii docker.io 20.10.5+dfsg10+deb11u2

And requirements.txt pip uses says, amongst other things:

Flask==3.0.0
psycopg2-binary==2.9.9
SQLAlchemy==2.0.22

There were also line: "flask-sqlalchemy==3.1.1". But i commented that out. To see if something changes. Nope. Not in a way that matters.

I suspect that those packages installed by pip also complicate a task that should be simple. New version of every package comes out 2 or 3 times a day and no one bothers to check if they work as intended or not. Which versions should work? Should i comment out flask-sqlalchemy or SQLAlchemy? Commenting out both will definitely prevent the db connection.

Somehow i'm not surprised that SQL injection is still a thing in 2020'ies. It seems that devs are forced to do queries in a very primitive and dangerous way.
Also it seems that it is mandatory to use scripting language abominations from the 1990'ies to get anything done at all.

Last edited by nahkhiirmees (2023-11-23 17:26:01)

nahkhiirmees

Member

Registered: 2022-07-24

Posts: 261  

Re: sqlalchemy

Now i think that i have to confirm that postgres gets the sql commands at all. Parameter "echo=True" results in Python echoing the commands sqlalchemy generates. So i know that something happens on the Python-side of things.

But i'm not sure that those commands end up in postgres-container. So i'll have to tail -f some_log_file . Postres-container should echo some parts of the commands it receives, into stdout. And i haven't noticed any from that Flask-container.

Another thing is commands trying to select from database pg_catalog. I know these are necessary in order to copy pre-existing database structure into code. 
The account i use to select from "table1" may not have necessary privileges to use that catalog. Have to do some gogling to find out which are the absolute minimal grants that i need.
IMO it is stupid to GRANT ALL PRIVILEGES ON ALL DATABASES just to select from 1 table.

But anyways, working example code would be very nice to find.
It was 2012 when i tried Flask before. I have the previous "hello_world"-attempt printed in paper. But it is half-finished and outdated. And then there is the principal problem that i don't need to define all the tables in code when i have already built the db, outside the Flask-container.

P.S. and then there is the question about psycopg2, which version actually works and can i download it from anywhere anymore? And is "no news is good news" still a valid principle?

Last edited by nahkhiirmees (2023-11-23 12:51:33)

nahkhiirmees

Member

Registered: 2022-07-24

Posts: 261  

Re: sqlalchemy

Maybe stackexchange.com would be a better place for this topic. But i don't have account on that site.

Anyways, changed logging settings in postgres-container. I started getting "LOG connection authetticated"-messages.
Entered into running python container with docker exec, started Python interpreter and started entering commands. Those "authenticated"-messages arrived into stderr. So now i know that sqlalchemy can connect into  postgres.

But "r=conn.execute(text("SELECT * FROM table1"))", which i tried instead of "c.select().where()" is still a problem. For some reason it doesn't return any rows. I think it should.
And it is very stupid if i have to enter commands that way.   I thought that with sqlalchemy, another way of doing queries would be possible.

Last edited by nahkhiirmees (2023-11-23 17:21:31)

nahkhiirmees

Member

Registered: 2022-07-24

Posts: 261  

Re: sqlalchemy

Managed to get rid of some complaints by changing "c.id_field==" to "c.columns.id_field==".
Maybe the software doesn't correspond to documentation or another way round.
Have to re-check that db-container works as it is supposed to. Tried with psql and got some errors.

nahkhiirmees

Member

Registered: 2022-07-24

Posts: 261  

Re: sqlalchemy

It seems that i can make sqlalchemy fetch some data from tables when the connectionstrings are right.
But another thing i noticed, with a new vm, docker-compose can't find the .yml-file with -f. I have to say it's absolute path, then d-c finds it.
Any ideas why? And how to fix that? I ask here, in the case this is a Devuan-specific problem. (Almost 20 years there were some kind of a RedHat-related problem with package gcc, that other distros did'nt have.)

Something may have gone wrong with thet new vm's install. Or there is some important package missing. I noticed also that in the new vm's xfce terminal, there is no colours with output of "ls".

That's not weird when using root-account. But using a normal user account...

Last edited by nahkhiirmees (2023-11-29 16:16:51)