How to convert python class to table postgres-CodePudding

I study python and databases. I have been looking for information on how to make a table in a database from a Python class for a very long time, but I have not found a good explanation

I was looking for examples, but the description is too weak and the examples are very difficult to understand

I tried using orm, I want to implement without ready-made solutions like sqlalchemy

I mean.

class User:
   tablename = "user"
   name = CharField(..)



def create_table(
    sql_query: str, 
    conn: psycopg2.extensions.connection, 
    cur: psycopg2.extensions.cursor
) -> None:
    try:
        # Execute the table creation query
        cur.execute(sql_query)
    except Exception as e:
        print(f"{type(e).__name__}: {e}")
        print(f"Query: {cur.query}")
        conn.rollback()
        cur.close()
    else:
        # To take effect, changes need be committed to the database
        conn.commit()

class User -> postgres table

CodePudding user response：

You will have to connect to the database and use postgresSQL to tell the database how to structure the table(s). Simply make the table attributes the same as your class attributes.

Your table SQL will look something like:

 CREATE TABLE [IF NOT EXISTS] table_name (
       class_attribute datatype(length) column_constraint,
       class_attribute datatype(length) column_constraint,
       class_attribute datatype(length) column_constraint,
       table_constraints
    );

If you want to write your own interface to the postgresSQL DBMS you will need a very good understanding of relation databases. If you decide you want a ready-made solution try psycopg out.

CodePudding user response：

If you want to go down this route take a look at attrs:

@attr.s
class C(object):
    x = attr.ib(type=int)
    y = attr.ib(type=str)

flds = attr.fields(C)

flds
(Attribute(name='x', default=NOTHING, validator=None, repr=True, eq=True, order=True, hash=None, init=True, metadata=mappingproxy({}), type=<class 'int'>, converter=None, kw_only=False),
 Attribute(name='y', default=NOTHING, validator=None, repr=True, eq=True, order=True, hash=None, init=True, metadata=mappingproxy({}), type=<class 'str'>, converter=None, kw_only=False))

flds[0].name
'x'
flds[0].type 
int

For adding metadata e.g. table name see Metadata.

You would have to create a conversion code that takes Python types and maps to SQL type strings.

Honestly my recommendation is to not go this route with your own home brewed solution. There are a lot of ORMs out there that will do this for you, without the headache of keeping your code up to date. If you want something smaller then SQLAlchemy take a look at peewee.

My own preference is to keep my DDL as SQL scripts using Sqitch.