'Correctly set up an engine connection postgresql
I'm working with sqlalchemy and postgresql using python modules.
There is a database module which creates the objects that I need in order to work with postgresql tables. The init method is the following:
class Database:
is_instantiated = False
def __init__(self):
if not Database.is_instantiated:
self.engine = create_engine("postgresql+psycopg2://name:pwd@localhost:5432/udemy")
self.metadata = MetaData()
self.create_table()
Database.is_instantiated = True
print("Database connection success")
else:
print("Already connected")
The create_table() method just creates tables objects like:
def create_table(self):
self.tbl_employee = Table("employee", self.metadata,
Column("id", Integer(), primary_key=True),
Column("first_name", String(), nullable=False),
Column("last_name", String(), nullable=False),
Column("birthday", String(10), nullable=False),
Column("department_name", String(), nullable=False)
)
Now, there is a start module with a button that if pressed it opens another window using the following method:
def manage_employees(self):
self.hide()
self.employee_window = EmployeeMenu(self)
self.employee_window.show()
The employee module, when instanciated, populate the QTableWidget using this method:
def populate_tbl(self):
self.db = Database()
headers, data = self.db.get_data_for_tab()
tbl_nr_rows = len(data)
tbl_nr_columns = len(headers)
self.tbl_center.setRowCount(tbl_nr_rows)
self.tbl_center.setColumnCount(tbl_nr_columns)
self.tbl_center.setHorizontalHeaderLabels(tuple(headers))
self.tbl_center.setSelectionMode(qw.QAbstractItemView.SelectionMode.SingleSelection)
self.tbl_center.horizontalHeader().setSectionResizeMode(qw.QHeaderView.Stretch)
self.tbl_center.setSelectionBehavior(qw.QAbstractItemView.SelectRows)
for row in range(tbl_nr_rows):
for col in range(tbl_nr_columns):
self.tbl_center.setItem(row, col, qw.QTableWidgetItem(str(data[row][col])))
The self.db.get_data_for_tab() simply takes data from the postgresql in order to populate the table widget.
The first time that I open the employee window everything works fine, but I have also a button that can bring me back to the start menu:
def show_start(self):
self.hide()
self.start_menu.show()
If I use this method and then try to reopen the employee window (using the manage_employees), it will raise an error because the Database.is_instantiated attribute is True now and therefore it will not run the code for creating the engine, metadata, ...
My first question is if it is correct to try to create the engine and others database's objects only one time, and in that case how can I set up that properly in order to avoid this kind of issues.
If it is not necessary, I have seen that if I remove the initial check in the init method of the Database class, the employee table will be populated properly, but I don't know if this is the right way to work with sqlalchmey objects.
Many thanks in advance for your help.
Andrea
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
