3
\$\begingroup\$

I don't like the way my code is written, but at the moment, I don't see, how I should do it better.

I have a class (let's say MyInvoiceType), which needs to save data to preexisting database. The database is now mapped with SqlAlchemy (initially was just created via SQL interface).

I would have certain Table classes mapped via reflection and for one I need to specify PKs myself, therefore (as I understand) I had to create custom class:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine,Table

Base=automap_base()
class Batches(Base):
    __tablename__="batches"
    __table__ = Table(__tablename__, Base.metadata, autoload=True, autoload_with=engine) 
    __mapper_args__ = {'primary_key': [__table__.c.composite_pk1,__table__.c.composite_pk2]} 

sql_conn='my://db@connection'
engine=create_engine(sql_conn)
Base.prepare(engine, reflect=True)

Clients=Base.classes.clients
Invoices=Base.classes.invoices
InvoiceLines=Base.classes.invoicelines
Session=sessionmaker(bind=engine)

class MyInvoiceType(object):
    def __init__(self):
        self.session=Session() # actual DB session
    # here go functions which read data from pandas dataframe, check it
    # and push it out into respective DB tables for invoice data, invoice lines data, 
    # and batches for each invoice line.
...

Now, MyInvoiceType is the only place where I use the mapped table classes (Clients,Invoices,InvoiceLines and Batches). What happens is, I get invoice data, check it, and feed it into the 3 invoice data tables, which make up the real invoice.

I feel it is ugly to have so much preparatory things happen outside the class, which would be executed whenever I load this module file, even if I did it by mistake and never instantiated MyInvoiceType. In particular, I don't like sql_conn variable dangling outside any class/function.

Is there a better way to organize this code so that database interaction happens as much as possible only when I try to create a new MyInvoiceType() object? Would it be good or on the contrary, bad? In particular, can (and should I) move all the engine creation and Invoices,etc class creation to inside MyInvoiceType? Can I/Should I do something like self.Clients=Base.classes.clients? What about Batches?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.