Python DBAccess.DBAccess Examples

Example #1

File: ui.py Project: KevinDeLaTorre/Grocer

	def addItemButtons( self ):
		sender = self.sender()
		if (sender.text() == "Next"):
			if (self.itemNameEdit.text() == "" or self.itemBrandEdit.text() == "" or self.storeNameEdit.text() == ""):
				self.displayMessage( "Fill out all forms", "Error" )
			else:
				self.itemName = self.itemNameEdit.text()
				self.itemBrand = self.itemBrandEdit.text()
				self.storeName = self.storeNameEdit.text()
				self.addItemInfoScreen()
		elif (sender.text() == "Add"):
			if (self.itemPrice.text() == "" or self.itemQuantity.text() == "" or \
					self.itemQType.text() == "" or self.itemDate.text() == ""):
				self.displayMessage( "Fill out all forms", "Error" )
			else:
				try:
					db = DBAccess.DBAccess()
					db.addStore( self.storeName )
					db.addItem( self.itemName, self.itemBrand )
					db.addPrice( self.itemName, self.itemBrand, float( self.itemPrice.text() ),
								 self.itemQType.text(), float( self.itemQuantity.text() ),
								 int( self.itemCouponUsed.isChecked() ), self.itemDate.text() )
					self.lastDate = self.itemDate.text()
					self.setStatus( "Item Added Successfully" )
				except:
					self.displayMessage( str( sys.exc_info()[ 0 ] ), "Database Error" )
				finally:
					self.addItemScreen()
		else:
			self.status.clearMessage()
			self.setCentralText( "Check menu for options" )

Example #2

File: ui.py Project: KevinDeLaTorre/Grocer

	def showMostOnSale( self ):
		db = DBAccess.DBAccess()
		mostRecorded = pd.read_sql( '''SELECT ITEMNAME, COUNT( ITEMNAME) AS CNT FROM PRICES
										GROUP BY ITEMNAME ORDER BY COUNT( * ) DESC
										LIMIT 3;
										''', db.getDBConnection() )
		pyplot.plot( mostRecorded[ 'ITEMNAME' ], mostRecorded[ 'CNT' ] )
		pyplot.xlabel( "Item Name" )
		pyplot.ylabel( "# of ads item was found in" )
		pyplot.show()

Example #3

File: ui.py Project: KevinDeLaTorre/Grocer

	def showMostValue( self ):
		db = DBAccess.DBAccess()
		mostValue = pd.read_sql( '''SELECT DISTINCT ITEMNAME AS ITEM, (PRICES.PRICE/PRICES.QUANTITY) AS VAL
		FROM PRICES
									ORDER BY (PRICES.PRICE/PRICES.QUANTITY)
									LIMIT 5;
									''', db.getDBConnection() )
		pyplot.plot( mostValue[ 'ITEM' ], mostValue[ 'VAL' ] )
		pyplot.ylabel( "Cost per unit in $" )
		pyplot.xlabel( "Item Name" )
		pyplot.show()

Example #4

File: ui.py Project: KevinDeLaTorre/Grocer

	def showMostDiverseStore( self ):
		db = DBAccess.DBAccess()
		mostDiverseStore = pd.read_sql( '''
										SELECT BRAND, COUNT( * ) AS C FROM ( STORES INNER jOIN ITEMS ON
										BRAND=STORENAME )
										GROUP BY BRAND
										ORDER BY COUNT( * ) DESC
										limit 4;
										''', db.getDBConnection() )
		pyplot.plot( mostDiverseStore[ 'BRAND' ], mostDiverseStore[ 'C' ] )
		pyplot.ylabel( "# of unique items sold by store" )
		pyplot.xlabel( "Store Name" )
		pyplot.show()

Example #5

File: ui.py Project: KevinDeLaTorre/Grocer

	def summaryScreen( self ):
		widget = QWidget()
		self.setCentralWidget( widget )
		
		db = DBAccess.DBAccess()
		numItems = pd.read_sql( "SELECT DISTINCT ITEMNAME FROM PRICES;", db.getDBConnection() )
		numPrices = pd.read_sql( "SELECT ITEMNAME FROM PRICES;", db.getDBConnection() )
		mostValue = pd.read_sql( '''SELECT * FROM 'PRICES'
									ORDER BY (PRICES.PRICE/PRICES.QUANTITY)
									limit 1;''', db.getDBConnection() )
		mostRecorded = pd.read_sql( '''SELECT ITEMNAME, COUNT( ITEMNAME) AS CNT FROM PRICES
										GROUP BY ITEMNAME ORDER BY COUNT( * ) DESC
										LIMIT 3;
										''', db.getDBConnection() )
		mostDiverseStore = pd.read_sql( '''
										SELECT BRAND, COUNT( * ) AS C FROM ( STORES INNER jOIN ITEMS ON
										BRAND=STORENAME )
										GROUP BY BRAND
										ORDER BY COUNT( * ) DESC;
										''', db.getDBConnection() )
		
		summary = ""
		summary += "Number of Unique Items in DB: " + str( numItems[ 'ITEMNAME' ].count() )
		summary += "\nNumber of Prices Logged: " + str( numPrices[ 'ITEMNAME' ].count() )
		summary += "\nMost value item ( Price/Quantity ): " + str( mostValue[ 'ITEMNAME' ][ 0 ] )
		summary += "\nItem most on sale: " + str( mostRecorded[ 'ITEMNAME' ][ 0 ] ) + " (x" + \
				   str( mostRecorded[ 'CNT' ][ 0 ] ) + ")"
		summary += "\nMost diverse in-house products: " + str( mostDiverseStore[ 'BRAND' ][ 0 ] )
		
		summaryLine = QHBoxLayout()
		summaryLine.addStretch( 1 )
		summaryLine.addWidget( QLabel( summary ) )
		summaryLine.addStretch( 1 )
		
		screen = QVBoxLayout()
		screen.addStretch( 1 )
		screen.addLayout( summaryLine )
		screen.addStretch( 1 )
		
		widget.setLayout( screen )

Example #6

File: Util.py Project: undergraver/QuickIndexer

def GetDbAccessHandle(fileName):
    if fileName is None:
        fileName = Defaults.GetDefaultDbFile()

    return DBAccess(fileName)

Example #7

File: ui.py Project: KevinDeLaTorre/Grocer

	def initDB( self ):
		db = DBAccess.DBAccess()
		db.initialize()
		self.status.showMessage( "Database initialized" )
		self.setCentralText( "Use menu to add/search items to database" )