Python – Sqlite3 Custom Module

#sqliteBaseLib.py
#Referenced Help Document: https://docs.python.org/3/library/sqlite3.html
#v1.0 

'''
Sqlite3 custom class for quicker implementation for small database within projects. 
Please come back on a regular basis for continued updates
'''

import sqlite3

class sqliteBaseLib: 

	#connect db
	def connectDB(self,thisDb):
		try:
			self.thisDb = thisDb
			self.thisDb = self.thisDb + '.db'
			self.con = sqlite3.connect(self.thisDb)
			print('connection established')
		except:
			print('There was a problem connecting to the database')
		return self.con

	#create table
	def createTable(self,con,tableName,fields):
		try:
			self.con = con
			self.fields = fields
			self.tableName = tableName

			self.cur = self.con.cursor()

			'''
			fields format
			, 
			(date text, trans text, symbol text, qty real, price real)
			'''
			print(self.con)
			print(self.tableName)
			print(self.fields)

			a = "CREATE TABLE "
			b = str(self.tableName) + ' ' + str(self.fields)
			#print(a+b)

			# Create table
			#self.cur.execute('''CREATE TABLE stocks(date text, trans text, symbol text, qty real, price real)''')
			self.cur.execute(a+b)
			# Insert a row of data
			print(self.tableName + ' Created Successfully')
		except: 
			print('there was a problem creating a table')
	#create fields
	def createField(self,con,table,fields): 
		self.con = con
		self.table = table
		self.fields = fields

		self.cur = self.con.cursor()
		#ALTER TABLE table_name ADD new_column_name column_definition; table_name. The name of the table to modify.
		#ALTER TABLE equipment ADD COLUMN location text;

		# 

		a = "ALTER TABLE " 

		c = " ADD "

		self.cur.execute(a + str(self.table) + c + str(self.fields))

		print('Fields created successfully!')

	#create record
	def createRecord(self,con,tableName,values): 
		try:
			self.con = con
			self.cur = self.con.cursor()
			self.tableName = tableName
			self.values = values

			self.a =  "INSERT INTO "

			self.c = " VALUES "

			self.z = self.a + self.tableName + self.c + self.values
			#print(self.z)

			#values format
			self.cur.execute(self.z)
			# Save (commit) the changes
			self.con.commit()
			print('record created successfully')
		except: 
			print('there was a problem creating a record')

	#query records
	def queryRecords(self,con,table,fields, searchCondition): 
		
		#planning to query with fields and search condition in 1.1

		try:
			self.con = con
			self.cur = self.con.cursor()
			self.table = table
			self.fields = fields
			self.searchCondition = searchCondition

			if self.fields == '' and self.table != '':
				self.query1 = "SELECT * FROM " + self.table
				for self.row in self.cur.execute(self.query1):
					print(self.row)
		except: 
			print('there was a problem querying records')

	#delete record
	def deleteRecord(self,con,table,searchCondition,critiera,limit, offset): 
		pass
		'''
		DELETE FROM table
		WHERE search_condition
		ORDER BY criteria
		LIMIT row_count OFFSET offset;
		'''
		try: 
			self.con = con
			self.cur = self.con.cursor()
			self.table = table
			self.searchCondition = searchCondition
			self.criteria = criteria
			self.limit = limit
			self.offset = offset

			if self.table != '' and self.searchCondition != '' and (self.criteria == '' or self.limit == ''):
				delStr1 = 'DELETE FROM' + ' ' + self.table + ' ' + 'WHERE' + ' ' + self.searchCondition
				self.cur.execute(delStr1)
			elif	self.table != '' and self.searchCondition != '' and self.criteria != '' and self.limit == '': 
				delStr2 = 'DELETE FROM'+ ' ' + self.table + ' ' + 'WHERE' + ' ' + self.searchCondition + ' OrderBy' + ' ' + self.criteria
				self.cur.execute(delStr2)
			elif self.table != '' and self.searchCondition != '' and self.criteria != '' and self.limit != '' and self.offset == '': 
				delStr3 = 'DELETE FROM'+ ' ' + self.table + ' ' + 'WHERE' + ' ' + self.searchCondition + ' OrderBy' + ' ' + self.criteria + 'Limit' + ' ' + self.limit
				self.cur.execute(delStr3)
			elif self.table != '' and self.searchCondition != '' and self.criteria != '' and self.limit != '' and self.offset!= '':
				delStr4 = 'DELETE FROM'+ ' ' + self.table + ' ' + 'WHERE' + ' ' + self.searchCondition + ' OrderBy' + ' ' + self.criteria + 'Limit' + ' ' + self.limit + ' OFFSET' + ' ' + self.offset 
				self.cur.execute(delStr4)
		except: 
			print('there was a problem deleting records')

Example of use:

 

#testbasedb.py
# python testbasedb.py

import sqliteBaseLib as sbl

class testDB:
	def testDB(self): 
		self.tableName = "stocks"
		self.values = "('2006-01-05','BUY','RHAT',100,35.14)"

		self.a = sbl.sqliteBaseLib()
		self.con = self.a.connectDB('test')
		self.a.createTable(self.con,'stocks','(date text, trans text, symbol text, qty real, price real)')
		self.a.createRecord(self.con,self.tableName,self.values)
		self.a.queryRecords(self.con,self.tableName,'', '')
		self.a.createField(self.con,'stocks','location text')

a = testDB()
a.testDB()

About the Author

One thought on “Python – Sqlite3 Custom Module

Leave a Reply

Your email address will not be published. Required fields are marked *

You may also like these