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)
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 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, ))