social.coop is one of the many independent Mastodon servers you can use to participate in the fediverse.
A Fediverse instance for people interested in cooperative and collective projects. If you are interested in joining our community, please apply at https://join.social.coop/registration-form.html.

Administered by:

Server stats:

499
active users

#sqlalchemy

0 posts0 participants0 posts today

Dear #LazyWeb and friends,

I have a lovely normalised database and one column, when fetched back from the database, can have multiple values. I have the standard 3 table many-to-many setup: Table A, table B, and a third Table that has the matching IDs from A and B to do the multi-to-multi.

How do I fetch this back from the database and present it as a JSON array? Everything seems to suggest using the database to make a delimited string and let clients turn it into an array. Silly.

I love this magick:

```
In [1]: import model

In [2]: model.Tooter
Out[2]: model.Tooter

In [3]: model.Tooter.account
Out[3]: <sqlalchemy.orm.attributes.InstrumentedAttribute at 0x7fbf7bd31bc0>

In [4]: model.Tooter.account == 'foo'
Out[4]: <sqlalchemy.sql.elements.BinaryExpression object at 0x7fbf8183a960>
```

:) #python #SQLAlchemy #metaprogramming

Well this is messing with me:

SQLAlchemy column definition:
start_time = Column(DateTime, nullable=False, comment="Activity start date (DateTime)")

SQLAlchemy query:
activity_db = (db.query(activities_models.Activity).filter(activities_models.Activity.id == activity_id).first())

print(type(activity_db.start_time))
print(activity_db.start_time)

First print returns: <class 'str'>
Second print returns: 2024-11-25T16:49:52+0100

Boost welcomed 🙃

Why?! #Python #help #MySQL #SQLAlchemy #boost

As a DBA, I always want developers to use database transactions and appropriate levels of locking. Because I'm tired of seeing explicit locking of tables or autocommit enabled.

BUT, after two days of trying to patch a simple CRUD app to apply my own rules, failing miserably, I just want to remove all this ORM crap and use raw SQL.

That's it.

Tomorrow, I will rewrite this monstrosity.

Replied in thread

@veronica #sqlalchemy has been nothing but trouble in our team. Lazy loading makes you have more transactions that last longer and also leads to the n+1 query problem. And I hate the query constructor to me it’s harder to understand than plain old SQL. We’re stripping it off to get back down to the #psycopg2 layer.

What's your preferred alternative to #SQLAlchemy? Looking for something more lightweight and less complex.

I don't necessarily need an #ORM, I certainly don't need cross-DBMS abstraction.

In fact, all I basically need is a nice interface to #SQLite. If it comes with some kind of ORM, that's nice. Way more important would be if it helps me deal with migrations. The less I have to think about capturing / applying schema changes, the better.

Suggestions? What did you enjoy working with?

#Alembic, the migration tool for #SQLAlchemy, supports not only schema upgrades but also downgrades and branch merges, so I didn't have much trouble moving between different branches with slightly different schemas in my local development environment, which is something I don't think many database migration tools offer.

alembic.sqlalchemy.org/en/late

alembic.sqlalchemy.orgWorking with Branches — Alembic 1.13.3 documentation
Replied in thread

My learning style is to figure out what I need to get to the next step, then take that next step, then repeat. This has helped me get as far as I have in this project, but also means I'm learning just enough to keep making progress.

I'm not really going through tutorials or digging into fundamentals much, only enough to accomplish something.

I know just enough about how databases work but it's been a long while since I've really been into them and I've never designed one before, let alone with foreign keys and relationships. I know the basic idea of what I want, but finding examples is harder.

If anyone has any great examples of foreign key relationships in SQL Alchemy, especially with a Vue based front end, I'm all ears.

If not, I might need to dust off my old blog and get something up there.

Replied in thread

Well I've hit a bit of a wall on the foreign key situation in SQL Alchemy on the backend side of this project.

I want to have an inventory item with a deviceTypeId field that's an ID number pointing over to the Device Types table. Each Device Type row has an ID and a name. I want the Device Type name to get back over to the inventory item record as another field.

If that doesn't make sense, maybe this will in JSON:

Device Type row / example:
{ "id": 1, "name": "Synology DS414 NAS" }

Inventory Item row / example:
{ "id": 1, "name": "ds414", "deviceTypeId": 1, ...(other fields) }

The database relationship I want to have my SQL Alchemy ORM do is this:

{ "id": 1, "name": "ds414", "deviceTypeId": 1, **"deviceType": "Synology DS414 NAS",**
...(other fields) }

I've seen lots of docs with relationships(...), Mapped(...) calls, Lists, back reference, back populate, and other things.

For now I really just want to pull over the name field from another table, based on the foreign key ID.

trying #alembic (with #sqlalchemy) to migrate some sql schema, everything running with smooth except when I change constraint (unique=True).

alembic can't process this migrate because SQLite have limitation on modifying constraint schema using ALTER TABLE.

I have trying to use BATCH MODE but did not meet my expectations. 🥲

And then today: good lord, understanding the interplay between Postgres and #SQLAlchemy gets complicated once you want to do certain things like GIN indices for the JSON you‘re storing.

I think I understand things well enough now to try, but I may want to change my approach to this in the future - I already feel exhausted, and I have only started writing my classes specs.

#TIL a whole bunch of things about the things #sqlalchemy can do, and how it works under the surface.

I don't often have a good opportunity to go codebase-diving, but it's definitely also one of the cool possibilities of #opensource software - to peak under the hood and understand how things happen.

When you're still trying to understand how you might solve things, that's something like a guidepost. It gives orientation, or a place to return to if you're on very uncertain terrain.