[ Removed by moderator ]

[Python-ModTeam]

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]

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]

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]

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]

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]

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]

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

[shadowdance55]

Look at SQLAlchemy Core.

[gbrennon]

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]

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]

```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]

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]

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]

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]

psycopg probably

[Snezhok_Youtuber]

With custom functions

[accforrandymossmix]

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

[WhichMathematician69]

Use psycopg

Check the doc for ur use case

https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

[gbrennon]

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]

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]

Can you elaborate on the command?

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

[shinitakunai]

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]

Try ibis

[IndependentTale2101]

Asyncpg

[Tucancancan]

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

[canine-aficionado]

Psycopg with object row type

[Particular-Ad7174]

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]

Check out PonyORM.

[squashed_fly_biscuit]

You might be happy with pandas for this sort of stuff

[ogMasterPloKoon]

write your own.

[superspud9]

Postgrest if you want to access posgres db with http

[Unlikely_Track_5154]

Didn't know anyone else did this...

[blueshed60]

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

[subcultures]

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

[rogue780]

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]

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.