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.
Just added SQLAlchemy from @zzzeek to our list! From raw SQL nightmares to elegant Python ORM - you've transformed how an entire generation of developers works with databases.
Thank you for this masterpiece of engineering!
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>
```
SQLite vs PostgreSQL: A Performance Showdown in Real-World Applications
In a battle between SQLite and PostgreSQL, one developer's quest for optimal performance reveals surprising insights about database capabilities. As applications scale, understanding the nuances of th...
https://news.lavx.hu/article/sqlite-vs-postgresql-a-performance-showdown-in-real-world-applications
Holy s*it, I just tried #tortoise-orm for #Python and my mind is blown away. After using #SQLAlchemy for a long time this is like night and day!
Kdybych #juniorguru dělal znova, možná bych místo #Peewee použil #SQLAlchemy. Možná spolu s #PyDantic. A možná bych na to použil https://sqlmodel.tiangolo.com/ Možná. Kdo ví.
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.
@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.
@veronica Give a try to #peewee - much more simple than #SQLAlchemy !
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.
FastAPI と Strawberry で作る GraphQL Server ~SQLAlchemy を添えて~
https://qiita.com/zoetaka38/items/a4c269d316009a66c3af?utm_campaign=popular_items&utm_medium=feed&utm_source=popular_items
I'm pretty impressed, I manage to get a cte construct working without much difficulty. The SQLAlchemy documentation wasn't too helpful, BUT, SQLite's was good to get the concept down, and I just assumed it'd behave a certain way, that is:
```
cteq = <initial select>.cte(recursive=True)
cteq = cteq.union_all(<recursive lookup query using cteq.c.field>)
```
as I used this in an in expression, I had to wrap it:
```
cteq = select(cteq.c.field)
```
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.
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.
a little bit confusing with #sqlalchemy, the database is there and contain tables with fine column but I can't insert data to the table because the table is not found.
weird.
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.
Qiita - 人気の記事 
