def on_ok(self):
if self.function == 'INSERT':
# prep text for the columns
colsqltext = '('
valsqltext = '('
valueslist = []
for i in self.data:
colsqltext += '\"' + i[0] + '\",'
valsqltext += '%s,'
valueslist.append(i[len(i) - 1])
# trim that last comma
colsqltext = colsqltext[:-1]
colsqltext += ')'
# trim that last comma
valsqltext = valsqltext[:-1]
valsqltext += ')'
try:
# execute the SQL
SQL = 'insert into \"{0}\" {1} VALUES {2};'.format(self.table, colsqltext, valsqltext)
value = tuple(valueslist)
conn = Connection.Instance().getconnection()
cursor = conn.cursor()
cursor.execute(SQL, value)
npyscreen.notify_confirm('{0} rows affected'.format(cursor.rowcount))
self.showagain = False
except Exception as e:
npyscreen.notify_confirm('PSQL Error: {0}'.format(e.message))
self.showagain = True
elif self.function == 'EDIT':
# prep text for the columns
sqltext = ''
wheretext = ''
valueslist = []
for idx, i in enumerate(self.data):
sqltext += '\"' + i[0] + '\" = (%s), '
valueslist.append(i[len(i) - 1])
wheretext += '\"' + i[0] + '\" = \'' + str(self.wherevalues[idx]) + '\' AND '
# trim that last comma and space
sqltext = sqltext[:-2]
# trim that last and and 2 spaces
wheretext = wheretext[:-5]
try:
# execute the SQL
SQL = 'update \"{0}\" set {1} where {2};'.format(self.table, sqltext, wheretext)
value = tuple(valueslist)
conn = Connection.Instance().getconnection()
cursor = conn.cursor()
cursor.execute(SQL, value)
npyscreen.notify_confirm('{0} rows affected'.format(cursor.rowcount))
self.showagain = False
except Exception as e:
npyscreen.notify_confirm('PSQL Error: {0}'.format(e.message))
self.showagain = True
if not self.showagain:
self.parentApp.switchForm('QUERYRESULTS')
def logout(self):
"""
Close the DB connection, bring user back to login form, hide widgets
:return:
"""
Connection.Instance().logout()
if Connection.Instance().getconnection() is not None:
for i in self._widgets__:
i.hidden = True
i.update()
self.parentApp.switchForm("LOGIN")
else:
npyscreen.notify_confirm("Logout Failure")
def submenu_drop(self):
"""
Drop the selected table
:return:
"""
# need the table to drop
table = self.cleaneddata[self.grid.edit_cell[0]][
self.grid.edit_cell[1]]
# determine if user wants to cascade or not
cascadeval = npyscreen.notify_yes_no(
'Would you like to cascade to tables having foreign-key references?',
editw=1)
if cascadeval:
SQL = 'drop table \"{0}\" CASCADE;'.format(table)
else:
SQL = 'drop table \"{0}\";'.format(table)
retval = npyscreen.notify_yes_no(
'Are you sure you wish to drop this table?', editw=1)
if retval:
value = ()
try:
conn = Connection.Instance().getconnection()
cursor = conn.cursor()
cursor.execute(SQL, value)
npyscreen.notify_confirm('Table {0} dropped'.format(table),
editw=1)
except Error as e:
npyscreen.notify_confirm(e.message, editw=1)
try:
self.cell = [0, 0]
self.updateresults()
except Exception as e:
self.submenucmd = None
self.parentApp.setNextForm('MAIN')
raise
def submenu_empty(self):
"""
Truncate the selected table if possible
:return:
"""
# need the table to truncate
table = self.cleaneddata[self.grid.edit_cell[0]][
self.grid.edit_cell[1]]
# determine if user wants to cascade or not
cascadeval = npyscreen.notify_yes_no(
'Would you like to cascade to tables having foreign-key references?',
editw=1)
if cascadeval:
SQL = 'truncate \"{0}\" CASCADE;'.format(table)
else:
SQL = 'truncate \"{0}\";'.format(table)
retval = npyscreen.notify_yes_no(
'Are you sure you wish to empty this table?', editw=1)
if retval:
value = ()
try:
conn = Connection.Instance().getconnection()
cursor = conn.cursor()
cursor.execute(SQL, value)
npyscreen.notify_confirm('Table {0} emptied'.format(table),
editw=1)
except Exception as e:
npyscreen.notify_confirm(e.message)
def submenu_delete(self):
# get current row's value to form query with headers
curvalues = self.grid.selected_row()
# prompt user to be sure, then just execute delete query from here
retval = npyscreen.notify_yes_no(
'Are you sure you wish to delete this row?', editw=1)
if retval:
# do the delete
wheretext = ''
for idx, i in enumerate(self.headers):
wheretext += '\"' + i + '\" = (%s) AND '
# trim that last and and 2 spaces
wheretext = wheretext[:-5]
SQL = 'delete from \"{0}\" where {1};'.format(
self.table, wheretext)
try:
value = tuple(curvalues)
conn = Connection.Instance().getconnection()
cursor = conn.cursor()
cursor.execute(SQL, value)
npyscreen.notify_confirm('{0} rows affected'.format(
cursor.rowcount),
editw=1)
self.cell = [0, 0]
except Exception as e:
npyscreen.notify_confirm(e.message, editw=1)
# reflect the change immediately
try:
self.updateresults()
except Exception as e:
npyscreen.notify_confirm(e.message, editw=1)
self.backonequery()
def submenu_edit(self):
# helpful to show the structure of data to insert
SQL = "select column_name as \"Column\", udt_name as \"Type\", is_nullable as \"Null\" from information_schema.columns where table_name = (%s) order by 1 ASC;"
value = (self.table, )
# execute the query to gather table column data
conn = Connection.Instance().getconnection()
cursor = conn.cursor()
cursor.execute(SQL, value)
data = cursor.fetchall()
# http://stackoverflow.com/questions/10252247/how-do-i-get-a-list-of-column-names-from-a-psycopg2-cursor
headers = [desc[0] for desc in cursor.description]
headers.append('Value')
cleaneddata = self.cleanUpListForOutput(data)
# need to append a new entry to each list
curvalues = self.grid.selected_row()
count = 0
for i in cleaneddata:
i.append(curvalues[count])
count += 1
# set up the editing form
editform = self.parentApp.getForm('EDITFORM')
editform.headers = headers
editform.wherevalues = curvalues
editform.data = cleaneddata
editform.table = self.table
editform.function = 'EDIT'
# reset to None so results get updated when we're done
self.submenucmd = None
self.parentApp.switchForm('EDITFORM')
def activate(self):
"""
Need to override activate here in order to do checking of the Connection
** NOTE ** : you must call before/edit/after editing on your own if you override activate
:return:
"""
if self.value is None or self.SQL is None:
npyscreen.notify_confirm('Error: No query data provided')
self.parentApp.switchForm('MAIN')
elif self.value == ';':
npyscreen.notify_confirm('Error: Empty query provided')
self.parentApp.switchForm("MAIN")
elif Connection.Instance().getconnection() is None:
npyscreen.notify_confirm('Error: No connection present')
self.parentApp.switchForm('LOGIN')
else:
if self.mode == 'QUERY':
self.cell = [0, 0]
self.submenucmd = None
"""
there are times when we need to open a form indicated from submenu selection and in order to do so
we need to branch before reaching the edit of this form
"""
try:
if self.beforeEditing():
self.edit()
else:
self.editing = False
except Exception as e:
npyscreen.notify_confirm(e.message, editw=1)
self.editing = False
self.parentApp.switchFormPrevious()
def exit(self):
"""
Logout and exit
:return:
"""
self.parentApp.setNextForm(None)
Connection.Instance().logout()
self.editing = False
self.parentApp.switchFormNow()
def activate(self):
"""
Check if there's an active DB connection, otherwise, show widgets and edit
:return:
"""
if Connection.Instance().getconnection() is None:
self.parentApp.switchForm("LOGIN")
else:
for i in self._widgets__:
i.hidden = False
i.update()
self.edit()
def updateresults(self):
"""
Actual data gathering of results
:return:
"""
conn = Connection.Instance().getconnection()
cursor = conn.cursor()
try:
cursor.execute(self.SQL, self.value)
count = cursor.rowcount
if count > 0:
try:
self.data = cursor.fetchall()
except ProgrammingError as p:
raise Exception('{0} row affected'.format(count))
# http://stackoverflow.com/questions/10252247/how-do-i-get-a-list-of-column-names-from-a-psycopg2-cursor
self.headers = [desc[0] for desc in cursor.description]
self.cleaneddata = self.cleanUpListForOutput(self.data)
self.grid.col_titles = self.headers
self.grid.columns = len(cursor.description)
self.grid.values = self.cleaneddata
self.grid.edit_cell = self.cell
self.grid.make_contained_widgets()
self.grid.ensure_cursor_on_display_up()
self.grid.ensure_cursor_on_display_down_right()
elif count == 0:
if cursor.statusmessage.split()[1] == '0':
raise Exception('No results to display')
else:
raise Exception('Query successful: {0}'.format(
cursor.statusmessage))
else:
raise Exception('Query successful: {0}'.format(
cursor.statusmessage))
except Exception as e:
raise e
