def dumpdb(cursor, table, format=True):
if not format:
cursor.execute('select * from ' + table)
while True:
rec = cursor.fetchone()
if not rec: break
print(rec)
else:
from makedicts import makedicts
recs = makedicts(cursor, 'select * from ' + table)
showformat(recs)
def dumpdb(cursor, table, format=True):
if not format:
cursor.execute('select * from ' + table)
while True:
rec = cursor.fetchone()
if not rec: break
print(rec)
else:
from makedicts import makedicts
recs = makedicts(cursor, 'select * from ' + table)
showformat(recs)
def clean(data, parent=None):
global DATA
names = [name for name in data.keys()]
values = [val for val in data.values()]
row = dict(zip(names, values))
#print(Queries,'\n',DATA)
if DATA not in Queries.keys():
showinfo('Query Error', 'Unknown command')
elif DATA == 'INSERT':
placeholder = []
for v in values:
placeholder.append('%s')
query = Queries[DATA]
vals = [val.get() for val in values]
query = query.format(TABLE, ','.join(placeholder))
#print(query)
update_table(parent, query, params=vals)
elif DATA == 'SEARCH':
command = []
for i in range(len(values)):
command.append(f'{names[i]} = \'{values[i]}\'')
#print(command)
if len(command):
a = command[0]
b = command[1]
condition = f'{a} AND {b}'
query = Queries[DATA]
#print(query)
query = query.format(TABLE, condition)
#print(query)
result = makedicts(parent.cur, query)
#print(result)
pack = ''
for data in result:
pack += "\t\t".join([str(var) for var in data.values()])
print(pack)
elif DATA == 'DELETE':
command = []
for i in range(len(values)):
command.append(f'{names[i]} = \'{values[i]}\'')
#print(command)
if len(command):
a = command[0]
b = command[1]
condition = f'{a} AND {b}'
query = Queries[DATA]
#print(query)
query = query.format(TABLE, condition)
#print(query)
update_table(parent, query)
elif DATA == 'UPDATE':
pass
""" run a query string, display formatted result table example: querydb.py dbase1 "select name, job from people where pay > 50000" """ import sys database, querystr = 'dbase1', 'select * from people' if len(sys.argv) > 1: database = sys.argv[1] if len(sys.argv) > 2: querystr = sys.argv[2] from makedicts import makedicts from dumpdb import showformat from loaddb import login conn, curs = login(database) rows = makedicts(curs, querystr) showformat(rows)
""" run a query string, display formatted result table example: querydb.py dbase1 "select name, job from people where pay > 50000" """ import sys database, querystr = 'dbase1', 'select * from people' if len(sys.argv) > 1: database = sys.argv[1] if len(sys.argv) > 2: querystr = sys.argv[2] from makedicts import makedicts from dumpdb import showformat from loaddb import login conn, curs = login(database) rows = makedicts(curs, querystr) showformat(rows)
def makeQuery(self, *args, **kwargs):
rows = makedicts(*args, **kwargs)
return rows[:30]
def searchLoan(data):
conn, cur = connectDb()
return makedicts(
cur, "SELECT * FROM prestamo WHERE {f} LIKE '%{v}%'".format(
f=data["field"], v=data["value"]))
def consultas():
from makedicts import makedicts
from dumpdb import showformat
from loaddb import login
conn, curs = login('Universidad')
print "\n\n1.- Obtener los nombres y notas de los estudiantes así como el resultado de su solicitud de \
manera que tengan un valor de corrección menor que 1000 y hayan solicitado la carrera de \
Informática en la Universidad Complutense de Madrid.\n"
rows = makedicts(curs, "SELECT nombre_est, nota \
FROM estudiantes, solicitudes \
WHERE estudiantes.id = solicitudes.id \
AND estudiantes.valor < 1000 \
AND solicitudes.carrera = 'Informatica' \
AND solicitudes.nombre_univ = 'Universidad Complutense de Madrid'")
showformat(rows)
print "\n\nObtener los estudiantes cuya nota ponderada cambia en más de\
un punto respecto a la nota original.\n"
rows = makedicts(curs, "SELECT * \
FROM estudiantes \
WHERE estudiantes.nota - (estudiantes.valor * estudiantes.nota / 1000) > 1")
showformat(rows)
print '\n\nModificar la tabla solicitudes de forma que aquellos estudiantes que no solicitaron ninguna\
universidad, soliciten "Informática" en la "Universidad de Jaen".\n'
rows = makedicts(curs, "SELECT id FROM estudiantes WHERE id NOT IN (SELECT id FROM solicitudes)")
for row in rows:
curs.execute('''INSERT INTO solicitudes VALUES (?, 'Universidad de Jaen', 'Informatica', 'No')''', (row["id"], ))
rows = makedicts(curs, "SELECT * FROM solicitudes WHERE nombre_univ = 'Universidad de Jaen'")
showformat(rows)
print '\n\nAdmitir en la "Universidad de Jaen" a todos los estudiantes de Económicas quienes no \
fueron admitidos en dicha carrera en otras universidades.\n'
rows = makedicts(curs, "SELECT id\
FROM solicitudes\
WHERE carrera = 'Economia'\
AND decision = 'No'\
AND nombre_univ != 'Universidad de Jaen'\
AND id NOT IN (SELECT id\
FROM solicitudes\
WHERE carrera = 'Economia'\
AND decision = 'Si')");
for row in rows:
curs.execute('''INSERT INTO solicitudes VALUES (?, 'Universidad de Jaen', 'Economia', 'Si')''', (row["id"], ))
rows = makedicts(curs, "SELECT * FROM solicitudes WHERE nombre_univ = 'Universidad de Jaen' AND carrera = 'Economia'")
showformat(rows)
print "\n\nBorrar a todos los estudiantes que solicitaron más de 2 carreras diferentes.\n"
rows = makedicts (curs, 'SELECT id,count(DISTINCT carrera) AS c FROM solicitudes GROUP BY id HAVING c>2')
for r in rows:
curs.execute('DELETE FROM estudiantes WHERE id = ?',(r['id'], ))
rows = makedicts(curs, 'SELECT * FROM estudiantes')
showformat(rows)
def getLoansByGameId(gameId=0):
conn, cur = connectDb()
return makedicts(
cur, 'SELECT * FROM prestamo WHERE juego_id = ? ORDER BY fecha_inicio',
(gameId, ))
def searchAllLoan():
conn, cur = connectDb()
return makedicts(cur, 'SELECT * FROM prestamo ORDER BY fecha_inicio')
def getLoanById(loanId=0):
conn, cur = connectDb()
return makedicts(
cur, 'SELECT * FROM prestamo WHERE id = ? ORDER BY fecha_inicio',
(loanId, ))
def searchLoan(data):
conn, cur = connectDb()
return makedicts(cur, "SELECT * FROM prestamo WHERE {f} LIKE '%{v}%'".format(f=data["field"], v=data["value"]))
def searchAllLoan():
conn, cur = connectDb()
return makedicts(cur, 'SELECT * FROM prestamo ORDER BY fecha_inicio')
def getLoansByGameId(gameId=0):
conn, cur = connectDb()
return makedicts(cur, 'SELECT * FROM prestamo WHERE juego_id = ? ORDER BY fecha_inicio', (gameId, ))
def getLoanById(loanId=0):
conn, cur = connectDb()
return makedicts(cur, 'SELECT * FROM prestamo WHERE id = ? ORDER BY fecha_inicio', (loanId, ))
