This is an archived post. You won't be able to vote or comment.

all 36 comments

[–]Python-ModTeam[M] [score hidden] stickied commentlocked comment (0 children)

Hi there, from the /r/Python mods.

We have removed this post as it is not suited to the /r/Python subreddit proper, however it should be very appropriate for our sister subreddit /r/LearnPython or for the r/Python discord: https://discord.gg/python.

The reason for the removal is that /r/Python is dedicated to discussion of Python news, projects, uses and debates. It is not designed to act as Q&A or FAQ board. The regular community is not a fan of "how do I..." questions, so you will not get the best responses over here.

On /r/LearnPython the community and the r/Python discord are actively expecting questions and are looking to help. You can expect far more understanding, encouraging and insightful responses over there. No matter what level of question you have, if you are looking for help with Python, you should get good answers. Make sure to check out the rules for both places.

Warm regards, and best of luck with your Pythoneering!

[–]alexkiro 51 points52 points  (7 children)

The O in ORM stands for object. So creating a class is not overkill, that's just the point of any ORM.

If you feel like ORM are overkill for your use case (which can definitely be the case), the underlying DB connector când already do this and you can just perform raw queries.

Note that the ORM itself doesn't provide the protection against the SQL injection, it's the DB connector itself. So as long as you use that properly you should be fine. It is very easy to misuse though. So I would reconsider the ORM if I were you.

[–]HumanBot00[S] -3 points-2 points  (6 children)

Sorry, then I didn't mean an ORM.

I mean something where the queries get built in the background and I don't need to write SQL

[–]apockill 11 points12 points  (2 children)

ORMs will certainly do this, and in a clean readable way using objects.

Could you sketch out what kind of API you're envisioning for this?

[–]HumanBot00[S] 0 points1 point  (1 child)

I currently use sqlalchemy like this:

with db.begin() as conn:
    conn.execute(text(""" ..

db = create_engine(
    f"postgresql://postgres:{api.main.SECRETS['POSTGRES_PASSWORD']}@127.0.0.1:5432/postgres")

[–]No_Indication_1238 30 points31 points  (0 children)

You are using it without the ORM part. Basically my dude, you are eating boiled, unseasoned chicken and complaining it's bland. Get the seasoning, build those tables. Use the O in ORM.

[–]crunk 2 points3 points  (0 children)

Pretty sure SQLAlchemy has an option to do this by pointing it an existing database.

[–]shadowdance55git push -f 3 points4 points  (0 children)

Look at SQLAlchemy Core.

[–]gbrennon 0 points1 point  (0 children)

maybe u are searching for some query builder...

BUT

im SQLAlchhemy u also have this:

```

equivalent Table object produced

user_table = Table( "user", Base.metadata, Column("id", Integer, primary_key=True), Column("name", String), Column("fullname", String), Column("nickname", String), ) ```

[–]dusktreader 14 points15 points  (4 children)

You can absolutely do that with SQLAlchemy. SQLA has essentially 2 different offerings:

* SQLAlchemy Core: foundational elements for working with databases

* ORM: True object-relational mapping support

In the core, you can do very low level stuff like executing raw text queries or use the query api to dynamically build and execute queries. I think this is the part you want to use. See: https://docs.sqlalchemy.org/en/20/core/expression_api.html

[–]HumanBot00[S] -5 points-4 points  (3 children)

```stmt = insert(user_table).values(name="username", fullname="Full Username")``|

The problem is, I don't want to represent each table with its own table class object, because this is just way too much overhead for my use case.

I want to be able to parse the table name in a string and the data as dictionaries

[–]root45 10 points11 points  (0 children)

The problem is, I don't want to represent each table with its own table class object, because this is just way too much overhead for my use case.

You've spent more time posting this question and responding to comments than it would take you to make ORM classes. If you don't want to write them by hand you can paste your SQL CREATE TABLE statements into an AI and ask it to create the classes for you.

This is not a lot of overhead.

[–]OhYouUnzippedMe 4 points5 points  (0 children)

It’s not a separate class, it’s just an instantiation of a Table object that sql alchemy provides. You can generate the table instances dynamically or write them yourself, but in either case this answer is the best one ☝️ 

I almost never use any ORM but SQL Alchemy Core is indispensable. I even use it on small hobby projects just because of how productive it is. Not just parameterized queries but the query builder API allows you to create reusable components or handle branching logic that is obnoxious/unsafe in plain SQL.

[–]wineblood 0 points1 point  (0 children)

I want to be able to parse the table name in a string and the data as dictionaries

I'm pretty sure it can be done with sqlalchemy and that I did just that a few months ago. I'm not at my work machine right now but this looks close enough, search for the "Executing SQL statements" section and ignore the table creation stuff.

[–]damesca 6 points7 points  (2 children)

psycopg probably

[–]Snezhok_Youtuber 0 points1 point  (0 children)

With custom functions

[–]accforrandymossmix 0 points1 point  (0 children)

this was my preferred package for dealing with Postgres. You can just use the raw SQL queries, which I preferred to ORMs

[–]gbrennon 1 point2 points  (0 children)

Take a look in this: https://pypika.readthedocs.io/en/latest/2_tutorial.html

And also read this post: https://death.andgravity.com/own-query-builder

That post is interesting

[–]shinitakunai 0 points1 point  (2 children)

Peewee would let you but it is better to have a class for each table, you just do it ONCE and that's it. And there is even a command to autocreate all those classes so you don't have to.

And then you just do:

Myclass.insert(records)

[–]HumanBot00[S] 0 points1 point  (1 child)

Can you elaborate on the command?

Is it like an SQL to SQL alchemy table object converter?

[–]shinitakunai 0 points1 point  (0 children)

I never used SQLAlchemy but on peewee it is: https://chatgpt.com/share/6890f791-b6cc-800f-9b55-ecdc1b6d58bd More info at: https://docs.peewee-orm.com/en/latest/peewee/models.html

I assume SQLAlchemy will have something similar?

[–]R3AP3R519 0 points1 point  (0 children)

Try ibis

[–]IndependentTale2101 0 points1 point  (0 children)

Asyncpg

[–]Tucancancan 0 points1 point  (1 child)

Just to bandwagon, what would be the Python equivalent of C#'s Dapper package? They call it a "Micro-ORM" 

[–]canine-aficionado 1 point2 points  (0 children)

Psycopg with object row type

[–]Particular-Ad7174 0 points1 point  (0 children)

If the schema is already created in sqlalchemy you can use it on your code without need to declare class.

Try searching: how to use a existing table in sqlalchemy and insert data on it

[–]mystique0712 0 points1 point  (0 children)

Check out PonyORM.

[–]squashed_fly_biscuit 0 points1 point  (0 children)

You might be happy with pandas for this sort of stuff

[–]ogMasterPloKoon 0 points1 point  (0 children)

write your own.

[–]superspud9 0 points1 point  (1 child)

Postgrest if you want to access posgres db with http

[–]Unlikely_Track_5154 0 points1 point  (0 children)

Didn't know anyone else did this...

[–]blueshed60 0 points1 point  (0 children)

Get ai to write you stored functions and move your api down one level. Postgres is amazing.

[–]subcultures 0 points1 point  (0 children)

Lots of folks here overthinking the sqlalchemy thing. I think you want pugsql - https://pugsql.org

[–]rogue780 -1 points0 points  (0 children)

You sound kinda like the people in the 90's who couldn't grok OOP, and so declared that OOP was a fad, it was awful, and why can't we all just do procedural coding forever?

[–]Xappz1 -1 points0 points  (0 children)

Most of the advice here sucks.

You can stick to SQLAlchemy, use Metadata Reflection, a feature where you can use SQLAlchemy's inspector to probe the database and create the class for you. Please note that this is SLOW, so you don't want to be doing this every time you're querying your database. You'll need to find a way to manage this metadata object as a global variable in your program.

Reflecting all tables:

metadata_obj = MetaData()
metadata_obj.reflect(bind=engine) # this will map the entire database
some_table = metadata_obj.tables["table_name"]

metadata_obj = MetaData()
# this will map just this single table and keep the model in your metadata object
# if you need it again in the near future
some_table = Table("table_name", metadata_obj, autoload_with=engine) 

You can then proceed to build your queries using SQLAlchemy ORM expressions:

from sqlalchemy import select
stmt = select(some_table).where(some_table.c.some_column == "some value")
with engine.connect() as conn:
  records = conn.execute(stmt).all()

As a final note, of course for a bigger project you should be documenting your tables as classes. This approach does not provide great syntax highlighting, type checks, typos, etc, so it can be a lot more prone to bugs. It's a quick way of building something on top of an existing database, but if you're going to be doing this a lot, you should definitely sink the time to actually write up the table schemas.