def insertPerson():
nome = input("Nome: ")
sobrenome = input("Sobrenome: ")
con = Conexao()
sql = "INSERT INTO Person (personal,family) VALUES(%s,%s);"
con.insertQuery(sql, (nome, sobrenome))
def insertVisited():
local = int(input("ID local (site): "))
data = input("Data: ")
con = Conexao()
sql = "INSERT INTO Visited (site_id, `date`) VALUES(%s,%s);"
con.insertQuery(sql, (local,data))
def insertSite():
nome = input("Nome: ")
lat = float(input("Latitude: "))
longi = float(input("Longitude: "))
con = Conexao()
sql = "INSERT INTO Site (name,lat,`long`) VALUES(%s,%s,%s);"
con.insertQuery(sql, (nome, lat, longi))
def insertSurvey():
visita = int(input("ID visita: "))
pessoa = int(input("ID pessoa: "))
quant = input("Medida: ")
read = float(input("Valor: "))
con = Conexao()
sql = "INSERT INTO Survey (visited_id, person_id, quant, reading) VALUES (%s, %s, %s, %s);"
con.insertQuery(sql, (visita,pessoa,quant,read))
def insertRandom():
con = Conexao()
sqlperson = "INSERT INTO Person (personal,family) VALUES(%s,%s);"
sqlsite = "INSERT INTO Site (name,lat,`long`) VALUES(%s,%s,%s);"
sqlvisited = "INSERT INTO Visited (site_id, `date`) VALUES(%s,%s);"
sqlsurvey = "INSERT INTO Survey (visited_id, person_id, quant, reading) VALUES (%s, %s, %s, %s);"
con.insertQuery(sqlperson, (randomName(6),randomName(12)))
con.insertQuery(sqlsite, (randomName(8), randomFloat(), randomFloat()))
con.insertQuery(sqlvisited, (randomIDFromTab("Site"), randomWord(["1930-11-12","1929-01-08","1928-12-08","1927-05-29"])))
con.insertQuery(sqlsurvey, (randomIDFromTab('Visited'),randomIDFromTab('Person'), randomWord(["rad","sal","temp"]), randomFloat() ))
def randomIDFromTab(tabela):
con = Conexao()
quant = con.getQuery("SELECT * FROM %s;" % tabela)
ids = [x["id"] for x in quant]
result = random.choice(ids)
return result
def quantidadeMedicoesTemp():
con = Conexao()
query = "SELECT ps.personal Pessoa, count(*) Medicoes FROM Survey sv INNER JOIN Person ps ON sv.person_id = ps.id WHERE sv.quant = 'temp' AND sv.reading >= 10 AND sv.reading <= 30 GROUP BY sv.person_id ORDER BY count(*) DESC LIMIT 1;"
result = con.getQuery(query)
print(result)
def quantidadeMedicoesPessoa():
con = Conexao()
query = "SELECT ps.personal Pessoa, count(*) Medicoes FROM Survey sv INNER JOIN Person ps ON sv.person_id = ps.id GROUP BY sv.person_id;"
result = con.getQuery(query)
print(result)
def cidadesSemVisitas():
con = Conexao()
query = "SELECT st.name Site FROM Site st LEFT JOIN Visited vt ON vt.site_id = st.id WHERE vt.id IS NULL;"
result = con.getQuery(query)
print(result)
def colunaQuantNull():
con = Conexao()
query = "SELECT count(*) Linhas FROM Survey WHERE quant IS NULL OR quant = '';"
result = con.getQuery(query)
print(result)
def visitasCidade():
con = Conexao()
query = "SELECT st.name Site, count(*) Visitas FROM Visited vt INNER JOIN Site st ON st.id = vt.site_id GROUP BY site_id ;"
result = con.getQuery(query)
print(result)
def visitasPorSite():
con = Conexao()
lista = (('DR-1', 'DR-3'), )
query = "SELECT DISTINCT st.name Site, vt.`date` DataVisita FROM Visited vt INNER JOIN Site st ON st.id = vt.site_id INNER JOIN Survey sv ON sv.visited_id = vt.id WHERE st.name IN %s;"
result = con.getQuery(query, lista)
print(result)
def sobrenomeDyr():
con = Conexao()
query = "SELECT * FROM Person WHERE family LIKE '%DYR%';"
result = con.getQuery(query)
print(result)
def pessoasMaisDoisLevantamentos():
con = Conexao()
query = "SELECT DISTINCT ps.personal Pessoa, COUNT(sv.id) Levantamentos FROM Survey sv INNER JOIN Person ps ON ps.id = sv.person_id GROUP BY sv.person_id HAVING COUNT(sv.id) > 2;"
result = con.getQuery(query)
print(result)
def mtricasObservadas():
con = Conexao()
query = "SELECT DISTINCT quant Metrica FROM Survey;"
result = con.getQuery(query)
print(result)
def mediaLatLong():
con = Conexao()
datas = ('1927-02-08', '1930-01-12')
query = "SELECT AVG(st.lat) MediaLat, AVG(st.`long`) MediaLong FROM Site st INNER JOIN Visited vt ON vt.site_id = st.id WHERE `date` BETWEEN %s AND %s;"
result = con.getQuery(query, datas)
print(result)
def conexao():
conexao_obj = Conexao()
yield conexao_obj
conexao_obj.fechar()