def search_in_two_tables(self, table, table2, rel1, rel2, search_col, mods,
values):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
col_names2, col_types2, pkeys2, fkeys2 = self.get_col_info(table2)
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL(
"SELECT * FROM public.{0} JOIN public.{1} ON public.{0}.{2} = public.{1}.{3} WHERE {4};"
).format(
psycopg2.sql.Identifier(table),
psycopg2.sql.Identifier(table2), psycopg2.sql.SQL(rel1),
psycopg2.sql.SQL(rel2),
psycopg2.sql.SQL(" AND ".join([
(c + "=%s" if m[1] == '' else "(" + c + " BETWEEN " +
v[1].split()[0] + " AND " + v[1].split()[1] + ")")
for c, m, v in zip(search_col, mods, values)
]))),
tuple([v[1] for m, v in zip(mods, values) if m[1] != 'r']))
data = self.cr.fetchall()
view.print_table(col_names + col_names2, col_types + col_types2,
pkeys + pkeys2, fkeys + fkeys2, data)
except (Exception, ps2.Error) as e:
view.error(e)
finally:
self.close()
def run():
while True:
choice = view.welcome_menu()
if choice == "1":
#Create an account
username = view.get_username()
pin = view.get_pin()
balance = view.amount()
new_account = Account(username)
new_account.pin = pin
new_account.balance = balance
new_account.save()
main_menu(new_account)
elif choice == "2":
#login
username = view.get_username()
pin = view.get_pin()
account = Account.login(username, pin)
if not account:
view.error()
else:
main_menu(account)
elif choice == "3":
#exit
return
else:
view.bad_input()
def insert(self, table, sels):
try:
self.connect()
self.session.add(self.base.classes[table](**dict(sels)))
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def connect(self):
try:
self.cn = ps2.connect(host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
self.cr = self.cn.cursor(cursor_factory=psycopg2.extras.DictCursor)
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
def connect(self):
try:
self.engine = sqlal.create_engine('postgresql://'+self.user+':' +
self.password+'@'+self.host+':'+self.port+'/'+self.database)
self.meta = sqlal.MetaData()
self.meta.reflect(self.engine)
self.base = automap_base(metadata=self.meta)
self.base.prepare()
self.session = Session(self.engine)
except Exception as e:
view.error(e)
controller.selection()
def full_text_search(self, table, text_col, txt, search_mode=1):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
try:
self.connect()
data = self.session.execute(text("SELECT * FROM public.\"{}\" WHERE {} (to_tsvector({}) @@ to_tsquery('{}'));"
.format(table, ("NOT" if search_mode == 1 else ""),
text_col, ('|' if search_mode == 1 else '&').join(txt)))).fetchall()
view.print_table(col_names, col_types, pkeys, fkeys, data)
except Exception as e:
view.error(e)
finally:
self.close()
def select_all(self, table):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
try:
self.connect()
qr = self.session.query(self.base.classes[table]).limit(1000)
data = [[getattr(q, c) for c in col_names] for q in qr]
view.print_table(col_names, col_types, pkeys, fkeys, data)
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def update(self, table, sels, where):
try:
self.connect()
qr = self.session.query(self.base.classes[table]).filter(text(where)).all()
for tq in qr:
for cs,cv in sels:
setattr(tq, cs, cv)
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def delete(self, table, where):
try:
self.connect()
qr = self.session.query(
self.base.classes[table]).filter(text(where)).all()
for tq in qr:
self.session.delete(tq)
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def delete(self, table, where):
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL("DELETE FROM {} {};").format(
psycopg2.sql.Identifier(table),
(psycopg2.sql.SQL("WHERE " + where)
if where else psycopg2.sql.SQL(""))))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def select_all(self, table):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL("SELECT * FROM public.{};").format(
psycopg2.sql.Identifier(table)))
data = self.cr.fetchall()
view.print_table(col_names, col_types, pkeys, fkeys, data)
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def search_in_two_tables(self, table, table2, rel1, rel2, search_col, mods, values):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
col_names2, col_types2, pkeys2, fkeys2 = self.get_col_info(table2)
try:
self.connect()
data = self.session.execute(
text("SELECT * FROM public.\"{0}\" JOIN public.\"{1}\" ON public.\"{0}\".{2} = public.\"{1}\".{3} WHERE {4};"
.format(table, table2, rel1, rel2,
" AND ".join([(c+"=:"+c if m[1] == '' else "("+c+" BETWEEN "+v[1].split()[0]+" AND "+v[1].split()[1]+")")
for c, m, v in zip(search_col, mods, values)]))),
dict([(c, v[1]) for c, m, v in zip(search_col, mods, values) if m[1] != 'r'])).fetchall()
view.print_table(col_names+col_names2, col_types+col_types2, pkeys+pkeys2, fkeys+fkeys2, data)
except Exception as e:
view.error(e)
finally:
self.close()
def update(self, table, sels, where):
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL("UPDATE public.{} SET {} {};").format(
psycopg2.sql.Identifier(table),
psycopg2.sql.SQL(", ".join([s[0] + "=%s" for s in sels])),
(psycopg2.sql.SQL("WHERE " + where)
if where else psycopg2.sql.SQL(""))),
tuple([s[1] for s in sels]))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def insert(self, table, sels):
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL(
"INSERT INTO public.{} ({}) VALUES ({});").format(
psycopg2.sql.Identifier(table),
psycopg2.sql.SQL(", ".join([s[0] for s in sels])),
psycopg2.sql.SQL(", ".join(
["%s" for i in range(len(sels))]))),
tuple([s[1] for s in sels]))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def gen_random(self, table, gen_len):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
self.delete(table, '')
try:
self.connect()
self.session.execute(text("INSERT INTO public.\"{}\" (SELECT {} FROM generate_series(1, {}));"
.format(table, ", ".join(["(SELECT {} FROM public.\"{}\" ORDER BY RANDOM()+generate_series LIMIT 1)"
.format([f[2] for f in fkeys if f[0] == c][0],
[f[1] for f in fkeys if f[0] == c][0])
if c in [f[0] for f in fkeys] else (("generate_series" if 'INTEGER' in t or 'NUMERIC' in t else "generate_series::text")
if c in pkeys else (("NOW()+(random()*(interval '90 days'))+'30 days'" if 'TIMESTAMP' in t
else ("cast(random()::int as boolean)" if t == 'BOOLEAN'
else ("SUBSTRING(md5(random()::text),1,5)" if 'TEXT' in t or 'VARCHAR' in t
else "(random()*1000000)::int")))))
for c, t in zip(col_names, col_types)]), gen_len)))
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def gen_random(self, table, gen_len):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
self.delete(table, '')
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL(
"INSERT INTO public.{} (SELECT {} FROM generate_series(1, {}));"
).format(
psycopg2.sql.Identifier(table),
psycopg2.sql.SQL(", ".join([(psycopg2.sql.SQL(
"(SELECT {} FROM public.{} ORDER BY RANDOM()+generate_series LIMIT 1)"
).format(
psycopg2.sql.SQL([f[2] for f in fkeys
if f[0] == c][0]),
psycopg2.sql.Identifier([
f[1] for f in fkeys if f[0] == c
][0])).as_string(self.cn) if c in [
f[0]
for f in fkeys
] else (
("generate_series" if t in ['integer', 'numeric']
else "generate_series::text") if c in pkeys else
(("NOW()+(random()*(interval '90 days'))+'30 days'"
if 'timestamp' in t else
("cast(random()::int as boolean)" if t ==
'boolean' else
("SUBSTRING(md5(random()::text),1,5)" if t in
['text', 'character varying'
] else "(random()*1000)::int")))
))) for c, t in zip(col_names, col_types)])),
psycopg2.sql.SQL(gen_len)))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def showProjectPath():
current_project = ProjectExplorer.ProjectTree.instance().currentProject()
if current_project:
view.show(current_project.projectDirectory().toString())
else:
view.error("Please open a project")
while True:
choice = view.main_menu()
if choice.upper() == 'C':
view.add_class()
subject = input('Subject: ')
room = int(input('Room number: '))
t_id = int(input('Teacher ID: '))
capacity = int(input('Class capaciiy: '))
c_id = int(input('Class ID: '))
success = model.add_class(subject,room,t_id,capacity,c_id)
if success:
view.success('Class')
else:
view.error()
elif choice.upper() == 'T':
view.add_teacher()
first = input('First name: ')
last = input('Last name: ')
homeroom = int(input('Homeroom number: '))
subj = input('Subject: ')
phone = input('Phone number: ')
tenure = input('Tenure (1/0): ')
age = int(input('Age: '))
email = input('Email: ')
t_id = int(input('Teacher ID: '))
success = model.add_teacher(first,last,homeroom,subj,phone,
tenure,age,email,t_id)
if success:
## $QT_END_LICENSE$
##
#############################################################################
# This example demonstrates how to work with the current
# project. It also includes a dependency on a plugin
# that can be disabled.
import sys, view
# When importing optional bindings, we can warn the user in case things go south
try:
from PythonExtension.QtCreator import ProjectExplorer
except ImportError:
view.error(
"The extension \"projects\" could not be loaded, since it depends on a disabled plugin."
)
raise Exception("Dependencies missing")
# Now we can assume, that ProjectExplorer was imported
def showProjectPath():
current_project = ProjectExplorer.ProjectTree.instance().currentProject()
if current_project:
view.show(current_project.projectDirectory().toString())
else:
view.error("Please open a project")
helpMenu = QtCreator.Core.ActionManager.actionContainer(
"QtCreator.Menu.Window")
return a, b
while True:
#########
# INPUT #
#########
view.welcome()
filename = view.get_file_read(
'Por favor insira o nome do arquivo de texto contendo os dados (com a extensao): '
)
if filename == '':
view.error('Nome de arquivo vazio')
if not os.path.isfile(filename):
col1_name = view.get_file_creation(
'Insira o nome da primeira coluna: ')
col2_name = view.get_file_creation('Insira o nome da segunda coluna: ')
sep = view.get_file_creation_nc(
('Insira o(s) caractere(s) separador(es) desejado (ex: virgula, '
'ponto e virgula, dois pontos, etc): '))
print()
col_1 = [
float(x) for x in view.get_input((
'Insira os valores numericos da coluna "' + col1_name + '", '
'separados pelo(s) caractere(s) escolhido(s): ')).split(sep)