def updateRow(self, table, info, condition):
query1 = param.paramComma(info)
(query2, clean) = param.paramKey(condition)
if (self.keep_log):
tq = param.paramDebug(condition)
self.record.note('''UPDATE {0} SET {1} WHERE {2}'''.format(
table, query1, tq))
try:
data = {**clean, **info}
self.cursor.execute(
'''UPDATE {0} SET {1} WHERE {2}'''.format(
table, query1, query2), data)
self.db.commit()
return True
except sql.OperationalError as e:
if ("syntax error" in str(e)):
query = param.paramDebug(condition)
raise SyntaxError('''UPDATE {0} SET {1} WHERE {2}'''.format(
table, changes, query))
elif ("no such table" in str(e)):
raise TableDNE_Error(table, self.getDBName())
elif ("no such column" in str(e)):
e = str(e)
begin = e.find(':') + 2
column = e[begin:]
raise ColumnDNE_Error(column, table, self.getDBName())
def getRow(self, row, condition):
(query, clean) = param.paramKey(condition)
if (self.keep_log):
tq = param.paramDebug(condition)
self.record.note('''SELECT * FROM {0} WHERE {1}'''.format(row, tq))
try:
self.cursor.execute('''SELECT * FROM {0} WHERE {1}'''.format(row, query), clean)
result = self.cursor.fetchall()
return result
except sql.OperationalError as e:
if ("syntax error" in str(e)):
query = param.paramDebug(condition)
raise SyntaxError('''SELECT * FROM {0} WHERE {1}'''.format(row, query))
elif ("no such table" in str(e)):
raise TableDNE_Error(row, self.getDBName())
elif ("no such column" in str(e)):
e = str(e)
begin = e.find(':') + 2
column = e[begin:]
raise ColumnDNE_Error(column, row, self.getDBName())
def deleteRow(self, table, condition):
(query, clean) = param.paramKey(condition)
if (self.keep_log):
tq = param.paramDebug(condition)
self.record.note('''DELETE FROM {0} WHERE {1}'''.format(table, tq))
try:
self.cursor.execute('''DELETE FROM {0} WHERE {1}'''.format(table, query), clean)
self.db.commit()
return True
except sql.OperationalError as e:
if ("no such table" in str(e)):
raise TableDNE_Error(table, self.getDBName())
elif ("no such column" in str(e)):
e = str(e)
begin = e.find(':') + 2
column = e[begin:]
raise ColumnDNE_Error(column, table, self.getDBName())
elif ("syntax error" in str(e)):
query = param.paramDebug(condition)
raise SyntaxError('''DELETE FROM {0} WHERE {1}'''.format(table, query))
def getRow(self, row, condition):
(query, clean) = param.paramKey(condition)
if (self.keep_log):
tq = param.paramDebug(condition)
self.record.note('''SELECT * FROM {0} WHERE {1}'''.format(row, tq))
try:
self.cursor.execute(
'''SELECT * FROM {0} WHERE {1}'''.format(row, query), clean)
result = self.cursor.fetchall()
return result
except sql.OperationalError as e:
if ("syntax error" in str(e)):
query = param.paramDebug(condition)
raise SyntaxError('''SELECT * FROM {0} WHERE {1}'''.format(
row, query))
elif ("no such table" in str(e)):
raise TableDNE_Error(row, self.getDBName())
elif ("no such column" in str(e)):
e = str(e)
begin = e.find(':') + 2
column = e[begin:]
raise ColumnDNE_Error(column, row, self.getDBName())
def deleteRow(self, table, condition):
(query, clean) = param.paramKey(condition)
if (self.keep_log):
tq = param.paramDebug(condition)
self.record.note('''DELETE FROM {0} WHERE {1}'''.format(table, tq))
try:
self.cursor.execute(
'''DELETE FROM {0} WHERE {1}'''.format(table, query), clean)
self.db.commit()
return True
except sql.OperationalError as e:
if ("no such table" in str(e)):
raise TableDNE_Error(table, self.getDBName())
elif ("no such column" in str(e)):
e = str(e)
begin = e.find(':') + 2
column = e[begin:]
raise ColumnDNE_Error(column, table, self.getDBName())
elif ("syntax error" in str(e)):
query = param.paramDebug(condition)
raise SyntaxError('''DELETE FROM {0} WHERE {1}'''.format(
table, query))
def updateRow(self, table, info, condition):
query1 = param.paramComma(info)
(query2, clean) = param.paramKey(condition)
if (self.keep_log):
tq = param.paramDebug(condition)
self.record.note('''UPDATE {0} SET {1} WHERE {2}'''.format(table, query1, tq))
try:
data = {**clean, **info}
self.cursor.execute('''UPDATE {0} SET {1} WHERE {2}'''.format(table, query1, query2), data)
self.db.commit()
return True
except sql.OperationalError as e:
if ("syntax error" in str(e)):
query = param.paramDebug(condition)
raise SyntaxError('''UPDATE {0} SET {1} WHERE {2}'''.format(table, changes, query))
elif ("no such table" in str(e)):
raise TableDNE_Error(table, self.getDBName())
elif ("no such column" in str(e)):
e = str(e)
begin = e.find(':') + 2
column = e[begin:]
raise ColumnDNE_Error(column, table, self.getDBName())
def test_parameterize(self):
#TODO: Need to test the second item returned by test_paramterize
#Setup
import slap
import parameterize as param
t = slap.DB()
#Seperate values with param dict => "('Count, Name, Number', ':Count, :Name, :Number')"
self.assertEquals(
param.paramTuple({
'Name': 'Test',
"Number": 9,
"Count": 20
}), ('Count, Name, Number', ':Count, :Name, :Number'))
#Seperate values with param dict => "('Count, Name, Number', ':Count, :Name, :Number')"
self.assertEquals(
param.paramTupleDebug({
'Name': 'Test',
"Number": 9,
"Count": 20
}), ('Count, Name, Number', '20, Test, 9'))
#Join keys with key lookups and equal comparisons => "Count=:Count, Name=:Name, Number=:Number"
self.assertEquals(
param.paramComma({
'Name': 'Test',
"Number": 9,
"Count": 20
}), "Count=:Count, Name=:Name, Number=:Number")
#Join keys with key and different comparators => "Count<:Count AND NOT Name=:Name AND Number=:Number"
self.assertEquals(
param.paramKey({
'Name': ('!=', 'Test'),
"Number": 9,
"Count": ("<", 20)
})[0], "Count<:Count AND NOT Name=:Name AND Number=:Number")
#Join keys with key values and different comparators => 'Count<20 AND NOT Name="Test" AND Number=9'
self.assertEquals(
param.paramDebug({
'Name': ('!=', 'Test'),
"Number": ("==", 9),
"Count": ("<", 20)
}), 'Count<20 AND NOT Name="Test" AND Number=9')
#Clean up
#Close an open DB => True
self.assertTrue(t.closeDB())
def test_parameterize(self):
#TODO: Need to test the second item returned by test_paramterize
#Setup
import slap
import parameterize as param
t = slap.DB()
#Seperate values with param dict => "('Count, Name, Number', ':Count, :Name, :Number')"
self.assertEquals(param.paramTuple({'Name': 'Test', "Number": 9, "Count": 20}), ('Count, Name, Number', ':Count, :Name, :Number'))
#Seperate values with param dict => "('Count, Name, Number', ':Count, :Name, :Number')"
self.assertEquals(param.paramTupleDebug({'Name': 'Test', "Number": 9, "Count": 20}), ('Count, Name, Number', '20, Test, 9'))
#Join keys with key lookups and equal comparisons => "Count=:Count, Name=:Name, Number=:Number"
self.assertEquals(param.paramComma({'Name': 'Test', "Number": 9, "Count": 20}), "Count=:Count, Name=:Name, Number=:Number")
#Join keys with key and different comparators => "Count<:Count AND NOT Name=:Name AND Number=:Number"
self.assertEquals(param.paramKey({'Name': ('!=', 'Test'), "Number": 9, "Count": ("<", 20)})[0], "Count<:Count AND NOT Name=:Name AND Number=:Number")
#Join keys with key values and different comparators => 'Count<20 AND NOT Name="Test" AND Number=9'
self.assertEquals(param.paramDebug({'Name': ('!=', 'Test'), "Number": ("==", 9), "Count": ("<", 20)}), 'Count<20 AND NOT Name="Test" AND Number=9')
#Clean up
#Close an open DB => True
self.assertTrue(t.closeDB())