[Python] Menu driven database management system

I am developing a menu-based system free of charge to computerize some manual tasks in order to help a friend better manage a small business.

I am new to Python and trying to use a top-down, menu-based submodule approach. I have the menu program working in that I can display the various processes I want to computerize. I also have the signals and slots set up and working.

I am focusing on the customer change and add menu options. I have a module that I wrote which will read and display the information in the database. It functions as a stand-alone module. I am working on developing it to use as a customer maintenance module and add or change a record in the customer database based on variable called “action” (global) which is set to an “A” for add or “C” for change in the the menu module.

When I try import the module from the menu program I get an error saying you cannot initiate an application when one is already running. I changed the program to eliminate the QApplication and it runs, but nothing is displayed.

Thanks in advance for your assistance.

Below is the menu code. I will follow that up with the customer maintenance code.

import os
import sys
import sqlite3
from PySide6.QtGui import QPixmap
from PySide6.QtUiTools import (QUiLoader)
from PySide6.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel
from PySide6.QtWidgets import (
QApplication,
QMainWindow,
QMenu,
QWidget
)

loader = QUiLoader()
basedir = os.path.dirname(__file__)
action = ""
class menu(QMainWindow):
def __init__(self):
super().__init__()
self.main_menu = loader.load(
os.path.join(basedir,"windows/main_menu.ui"), None
)
self.cci = loader.load(
os.path.join(basedir, "windows/cust_input.ui"), None
)

pixmap = QPixmap('graphics/logo.png')
# set logo file
self.main_menu.logo.setPixmap(pixmap)
self.main_menu.show()
#Create customer menu

menu_cust = QMenu(self)

# Create addAction instances for the customer menu
add_cust = menu_cust.addAction("Add Customer")
change_cust = menu_cust.addAction("Change Customer")
locate_cust = menu_cust.addAction("Locate Customer")

# Create part menu
menu_part = QMenu(self)
# Create addAction instances for the parts menu
add_part = menu_part.addAction("Add Part")
change_part = menu_part.addAction("Change Part")
locate_part = menu_part.addAction("Locate Part")

# Create model menu
menu_modl = QMenu(self)
# Create addAction instances for the model menu
add_modl = menu_modl.addAction("Add Model")
change_modl = menu_modl.addAction("Change Model")
locate_modl = menu_modl.addAction("Locate Model")

# Create order menu
menu_ordr = QMenu(self)
# Create addAction instances for the order menu
add_ordr = menu_ordr.addAction("Add Order")
change_ordr = menu_ordr.addAction("Change Order")
locate_ordr = menu_ordr.addAction("Locate Order")

# Attach menu options to QtoolButton Menus in .ui file
self.main_menu.cust_op.setMenu(menu_cust)
self.main_menu.part_op.setMenu(menu_part)
self.main_menu.modl_op.setMenu(menu_modl)
self.main_menu.ordr_op.setMenu(menu_ordr)

# Connect the QActions to functions
add_cust.triggered.connect(self.cust_add)
change_cust.triggered.connect(self.cust_chg)
locate_cust.triggered.connect(self.cust_loc)

add_part.triggered.connect(self.part_add)
change_part.triggered.connect(self.part_chg)
locate_part.triggered.connect(self.part_loc)

add_modl.triggered.connect(self.modl_add)
change_modl.triggered.connect(self.modl_chg)
locate_modl.triggered.connect(self.modl_loc)

add_ordr.triggered.connect(self.ordr_add)
change_ordr.triggered.connect(self.ordr_chg)
locate_ordr.triggered.connect(self.ordr_loc)
self.main_menu.show()

def check_customer(self):
self.sys_cust_code = self.cci.ci_input.text() # Updating the customer code
print("Customer Code Entered:", self.sys_cust_code)

db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(os.path.join(basedir, "databases/integrity.db"))

if not db.open():
print("Can't open database")
self.cci.ci_error.setText("Can't Open Customer Database")
self.cci.ci_error.setStyleSheet("background-color : red; color : white")
return

query = QSqlQuery()
query.prepare('SELECT * FROM customer WHERE cust_code = ?')
query.addBindValue(self.sys_cust_code) #

if not query.exec():
print("Query execution error: ", query.lastError().text())
self.cci.ci_error.setText("Query execution error: "+query.lastError().text())
self.cci.ci_error.setStyleSheet("background-color : red; color : white")
return

customer_rec = query.next() # Function to retrieve data from a query
if (not customer_rec) and (action == "C"): #pick up here
self.cci.ci_error.setText("No Customer Found to Change.")
self.cci.ci_error.setStyleSheet("background-color : red; color : white")
return
#ok
sys_cust_found = "N"
if (customer_rec) and (action == "A"):
self.cci.ci_error.setText("Customer Exists. Cannot Add.")
self.cci.ci_error.setStyleSheet("background-color : red; color : white")
return

self.cci.ci_error.setText(" ")
self.cci.ci_error.setStyleSheet("background-color : normal; color : normal")
import customer_maint

print("sys_cust_found =", sys_cust_found)
print("sys_cust_code =", self.sys_cust_code)

def cust_add(self):
print('Add Customer Triggered')
self.cci.setWindowTitle("Input Customer Code")
self.cci.ci_input.returnPressed.connect(self.check_customer)
action = "A"
self.cci.ci_error.setText(" ")
self.cci.show()

def cust_chg(self):
print('Change Customer Trigger')
self.cci.setWindowTitle("Input Customer Code")
self.cci.ci_input.editingFinished.connect(self.check_customer)
action = "C"
self.cci.ci_error.setText(" ")
self.cci.show()

def cust_loc(self):
print('Locate Customer Triggered')
def part_add(self):
print('Add Part Triggered')
def part_chg(self):
print('Change Part Trigger')
def part_loc(self):
print('Locate Part Triggered')
def modl_add(self):
print('Add Model Triggered')
def modl_chg(self):
print('Change Model Trigger')
def modl_loc(self):
print('Locate Model Triggered')
def ordr_add(self):
print('Add Order Triggered')
def ordr_chg(self):
print('Change Order Trigger')
def ordr_loc(self):
print('Locate Order Triggered')

app = QApplication(sys.argv)
ex = menu()
app.exec()


Here is the customer maintenance code which is still a work in process.

import os
import sys
import sqlite3
import PySide6
print("Customer Maint Execution has begun")
#QUiLoader allows screen created in QT Designer to be loaded
from PySide6.QtUiTools import QUiLoader
from PySide6.QtCore import QSize
from PySide6.QtSql import QSqlDatabase, QSqlQuery, QSqlQueryModel
from PySide6.QtWidgets import (
QApplication,
QComboBox,
QDataWidgetMapper,
QDoubleSpinBox,
QFormLayout,
QLabel,
QLineEdit,
QMainWindow,
QSpinBox,
QWidget,
)
print("Action Code Passed = ", main_menu.action)
loader = QUiLoader()
basedir = os.path.dirname(__file__)
class customer(QWidget):
def __init__(self):
super().__init__()
self.customer = loader.load(
os.path.join(basedir, "windows/customer.ui"), None
)
print("ui.show about to be executed")
self.ui.show()
print("ui.show executed")
self.ui.setWindowTitle("Integrity Customer Maintenance")
self.db = QSqlDatabase("QSQLITE")
self.db.setDatabaseName(os.path.join(basedir, "databases/integrity.db"))
self.db.open()
self.connection_obj = sqlite3.connect('databases/integrity.db')
self.cursor_obj = self.connection_obj.cursor()
self.sys_cust_code = "mayhew"
#self.sys_cust_code = "123456"
self.cursor_obj.execute('SELECT * FROM customer WHERE cust_code = ?', (self.sys_cust_code,))
self.customer_rec = self.cursor_obj.fetchone() # holds data from customer database
self.code = self.customer_rec[0]
self.name = self.customer_rec[1]
self.bill_street1 = self.customer_rec[2]
self.bill_street2 = self.customer_rec[3]
self.bill_city = self.customer_rec[4]
self.bill_state = self.customer_rec[5]
self.bill_zip = self.customer_rec[6]
print ("Key = ",self.sys_cust_code)
print(self.customer_rec)
print("")
print("Printing Individual Fields")
x=0
while x < 16:
print("Field", x,"Code = ", self.customer_rec[x])
x=x+1

self.ui.code
#self.ui.code.setText(f'{self.code}: {self.name}')
self.ui.code.setText(f'{self.customer_rec [0]}')
self.ui.name.setText(f'{self.customer_rec[1]}')
self.ui.bill_street_1.setText(f'{self.customer_rec[2]}')
self.ui.bill_street_2.setText(f'{self.customer_rec[3]}')
self.ui.bill_city.setText(f'{self.customer_rec[4]}')
self.ui.bill_state.setText(f'{self.customer_rec[5]}')
self.ui.bill_zip.setText(f'{self.customer_rec[6]}')
self.ui.bill_zip_plus.setText(f'{self.customer_rec[7]}')
self.ui.ship_street_1.setText(f'{self.customer_rec[8]}')
self.ui.ship_street_2.setText(f'{self.customer_rec[9]}')
self.ui.ship_city.setText(f'{self.customer_rec[10]}')
self.ui.ship_state.setText(f'{self.customer_rec[11]}')
self.ui.ship_zip.setText(f'{self.customer_rec[12]}')
self.ui.ship_zip_plus.setText(f'{self.customer_rec[13]}')
self.ui.last_order.setText(f'{self.customer_rec[14]}')
self.ui.terms.setText(f'{self.customer_rec[15]}')
mu = int(self.customer_rec[16])
print('Type = ',type(mu))
self.ui.mark_up.setValue(mu)
self.ui.show()


Thanks in advance for your assistance

Continue reading...