Clash Royale CLAN TAG#URR8PPP

Populate QTableView with SQLAlchemy

I'm building a simple CRUD Python app using SQLAlchemy ORM, PyQT5 and SQLite3. I'd like to follow Model/View pattern and so I'm following several articles from QT documentation:

I also found this repository upon which I'm building my own app (I'm following it just for the reference, not copying it).

This is what I have so far:

a database mapping

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, Boolean, ForeignKey, UniqueConstraint, Sequence
from sqlalchemy.orm import relationship

Base = declarative_base()

class Customer(Base):
__tablename__ = "customers"
id = Column(Integer, Sequence('customer_id_seq'), primary_key=True)
alias = Column(String(20), nullable=False, unique=True)
first_name = Column(String(30))
last_name = Column(String(80))
firm_name = Column(String(70))
tax_id = Column(String(20), nullable=False, unique=True)
address = Column(String(100), nullable=False)
postal_code = Column(String(6), nullable=False)
city = Column(String(30), nullable=False)
payment = Column(Integer, nullable=False)

templates = relationship("Template", back_populates="customer", cascade='all, delete-orphan', passive_deletes=True)


class Product(Base):
__tablename__ = "products"
id = Column(Integer, Sequence('product_id_seq'),primary_key=True)
product_name = Column(String(80), nullable=False, unique=True)
symbol = Column(String(10), nullable=False, unique=True)
unit = Column(String(10), nullable=False)

# there is no native "currency" type in SQLite3
# common practice is to store scaled integer values
# e.g. 123,45 becomes 12345
unit_net_price = Column(Integer,nullable=False)
vat_rate = Column(Integer, nullable=False)
billed_per_month = Column(Boolean, nullable=False)

templates = relationship("Template", back_populates="product", cascade='all, delete-orphan', passive_deletes=True)


class Template(Base):
__tablename__ = "templates"
id = Column(Integer, Sequence('template_id_seq'), primary_key=True)
customer_id = Column(Integer, ForeignKey("customers.id", ondelete='CASCADE'))
product_id = Column(Integer, ForeignKey("products.id", ondelete='CASCADE'))

customer = relationship("Customer", back_populates="templates")
product = relationship("Product", back_populates="templates")


and a main window

import sys

from PyQt5 import uic
from PyQt5.QtSql import QSqlTableModel
from PyQt5.QtWidgets import QMainWindow, QApplication

engine = create_engine("sqlite:///invoices.db", echo=False)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

class CustomersWindow(QMainWindow):
def __init__(self):
super(CustomersWindow, self).__init__()
uic.loadUi("UI/customers.ui", self)
self.show()
self.init_ui()

def init_ui(self):
self.init_table()

def init_table(self):
self.model = QSqlTableModel()
self.customers_table.setModel(self.model)

if __name__ == '__main__':
app = QApplication(sys.argv)
home_window = CustomersWindow()
sys.exit(app.exec_())


Now, following the aforementioned SQL Model Classes article in order to populate the table I ought to do something like this

self.model = QSqlTableModel()
self.customers_table.setModel(self.model)
model.setQuery("SELECT * FROM customers");


but this is totally against the whole idea of using SQLAlchemy ORM as I'd have to write each SQL command otherwise. The author of the mentioned GitHub repository defined the query command like this and then he's manually populating the table like, for example, in here.

Right now I'm confused as to how can I make SQLAlchemy and QSqlQueryModel work together since I can't find anything interesting on the Internet and I'd like to avoid having to manually populate the table or writing SQL commands by hand.

I'm well aware of the Camelot project but as I have just started learning, I'm afraid that the Camelot will do all the heavy lifting for me and I will miss quite a lot of the knowledge. On the other hand, I have an experience with Java's Hibernate and Spring so I'm not starting from the scratch.

My question is: is there currently a way (except the Camelot) to make SQLAlchemy and QSqlQueryModel work together, and if not, how much does the Camelot hide?

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.