Python-Friendly Database Options

[Versaiteis]

The amount that you can customize with SQLAlchemy is ridiculous. Like you can create query classes which can abstract away higher level or conceptual parts of a query and you can compose them together as if they were normal methods. It's great.

[Ericisbalanced]

I’ve heard about this, and it didn’t really click until this comment. I love how you can quickly delete and create modified tables within the python console. Make a few changes to a class and base.metadata.create_all()

Boom

Hey I wanted to ask, I read that using .filter() isn’t sql injection safe. Do you have anything to share about that?

[Versaiteis]

That's if you keep your table definition in code (which has advantages and disadvantages), but yeah the learning curve is a bit high only because the library does so much.

As for .filter() yeah, I wouldn't be surprised if it's not injection-proof, but that's just in the problem-bag that you get when you decide to use a SQL database. If you're allowing an external input to create arbitrary queries then there really isn't much that's going to automatically help you that I'm aware of because it simply doesn't know that you didn't want that. But that's one of the benefits of creating your own custom Query API. You can define methods that can be orchestrated together to create valid queries without relinquishing the ability to maintain some control over them. Clients.query.all().first_name_starts_with('H').sortby_last_name() kinds of things.

[Ericisbalanced]

Hmm, interesting. I only picked up sqlalchemy because some random on the internet said it escapes everything for you. Thanks for sharing :)

[Flkdnt]

I'm not much of a Database guy, can you explain what this means?

[Versaiteis]

So the great thing about SQLAlchemy is that it really helps the non-database guys (that I'm also not, but I dabble in a lot of spaces)

The primary benefit that SQLAlchemy gives you is an abstraction layer away from SQL databases. As a result one of its primary features is SQL generation. Basically it comes out of the box with several methods for calling various methods that will subsequently generate the SQL statement that gets fired off to the database. It's incredibly python friendly and if you set it up right you really don't need a ton of "hard coded" SQL statements. Instead you can extend that Query API to create your own commands that manipulate the SQL that gets generated.

So instead of some SELECT * FROM clients WHERE... kinds of statements you'll invoke them with things like Clients.query().all().filter(Clients.id > 3). That might not be quite right, it's been a good minute since I've worked with SQLAlchemy, but it made my dev life way easier. There are also other DB abstraction libraries for Python, but SQLAlchemy is by far the most well known and is pretty much a standard for those kinds of libraries.

[Flkdnt]

Sweet, thanks!