def Select(nom_tabla, gid):
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
if (nom_tabla == 'tabla.placas'):
fields = 'gid,motivo,fecha_ini,fecha_fin,zona,st_asgeojson(geom)'
elif (nom_tabla == 'tabla.incidencias'):
fields = 'gid,descripcion,fecha,tipo,st_asgeojson(geom)'
elif (nom_tabla == 'tabla.zonas_metro'):
fields = 'gid,zona,precio,st_asgeojson(geom)'
else:
fields = '*'
res = pg_operations2.pg_select2(d_conn,
table_name=nom_tabla,
string_fields_to_select=fields,
cond_where='where gid=%s',
list_val_cond_where=[gid])
print res
res = res[0]
#print (js)
pg_operations2.pg_disconnect2(d_conn)
resp_json = json.dumps({
"ok": True,
'data': res,
'message': 'Select successful',
'gid': gid
})
print 'Selection: ' + resp_json
return resp_json
def Insert(nom_tabla, tipo_geom, json_data):
d_form_data = json.loads(json_data)
d_form_data['geom'] = pg_operations2.transform_coords_ol_to_postgis(
coords_geom=d_form_data['geom'])
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
d2 = pg_operations2.dict_to_string_fields_and_vector_values2(
d=d_form_data,
list_fields_to_remove=['gid'],
geom_field_name='geom',
epsg='25830',
geometry_type=tipo_geom,
epsg_to_reproject=None)
print "d2"
print d2
list_returning = pg_operations2.pgInsert2(d_conn=d_conn,
nom_tabla=nom_tabla,
d_str=d2,
str_fields_returning='gid')
new_gid = list_returning[0][0]
pg_operations2.pg_disconnect2(d_conn)
resp_json = json.dumps({
"ok": True,
'data': d_form_data,
'message': 'Row inserted',
'new_gid': new_gid
})
print 'insert_building: ' + resp_json
return resp_json
def buildingSelect(gid):
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
table_name = 'd.buildings'
string_fields_to_select = 'gid, descripcion, area, fecha, st_asgeojson(geom)'
cond_where = 'where gid=%s'
list_val_cond_where = [gid]
r = pg_operations2.pg_select2(d_conn, table_name, string_fields_to_select,
cond_where, list_val_cond_where)
pg_operations2.pg_disconnect2(d_conn)
if r == None:
resp_json = json.dumps({
"tableName":
"d.buildings",
"ok":
'false',
'data':
'',
'message':
'The gid {0} does not exist'.format(gid)
})
else:
resp_json = json.dumps({
"tableName": "d.buildings",
"ok": "true",
"data": r,
"message": "Row returned succefully"
})
return resp_json
def buildingDelete(form_data):
d_form_data = json.loads(form_data)
gid = d_form_data.get('gid', '')
if gid == '':
resp_json = json.dumps({
"ok": 'false',
'data': '',
'message': 'You have to specify a gid'
})
return resp_json
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
table_name = 'd.buildings'
cond_where = 'where gid=%s'
list_values_cond_where = [gid]
num_rows = pg_operations2.pg_delete2(d_conn, table_name, cond_where,
list_values_cond_where)
pg_operations2.pg_disconnect2(d_conn)
resp_json = json.dumps({
"tableName":
"d.buildings",
"ok":
'true',
'data':
'',
'message':
'Num of buildings deleted: {0}'.format(num_rows),
'new_gid':
''
})
print 'building_delete: ' + resp_json
return resp_json
def buildingUpdate(form_data):
#json_data='{"gid":"10","descripcion":"Hospital","area":"100","geom":"100,100,200,100,200,200,100,100"}'
d_form_data = json.loads(form_data)
gid = d_form_data.get('gid', '')
if gid == '':
resp_json = json.dumps({
"tableName": "d.buildings",
"ok": 'false',
'data': d_form_data,
'message': 'You have to specify the gid in the json form data',
'new_gid': ''
})
print 'building_update: ' + resp_json
return resp_json
#elimitate commas and put spaces to the list of coordinates
d_form_data['geom'] = pg_operations2.transform_coords_ol_to_postgis(
coords_geom=d_form_data['geom'])
#database connection
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
#getting parameters for pg_sinser2 function
d2 = pg_operations2.dict_to_string_fields_and_vector_values2(
d=d_form_data, list_fields_to_remove=['gid'])
table_name = 'd.buildings'
str_field_names = d2['str_field_names']
list_field_values = d2['list_field_values']
str_s_values = d2['str_s_values']
print 'str_field_names: ' + str_field_names
print 'list_field_values: ' + str(list_field_values)
print 'str_s_values: ' + str_s_values
#inserting the building
num_rows = pg_operations2.pg_update2(d_conn,
table_name,
str_field_names,
str_s_values,
list_field_values,
cond_where='where gid=%s',
list_values_cond_where=[gid])
#disconnecting from the database. DO NOT FORGET
pg_operations2.pg_disconnect2(d_conn)
#creating a json answer
resp_json = json.dumps({
"ok":
'true',
'data':
d_form_data,
'message':
'Num of buldings updated: {0}'.format(num_rows),
'new_gid':
''
})
print 'building_update: ' + resp_json
return resp_json
def Delete(nom_tabla, gid):
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
print gid
pg_operations2.pg_delete2(d_conn,
table_name=nom_tabla,
cond_where='where gid=%s',
list_values_cond_where=[gid])
pg_operations2.pg_disconnect2(d_conn)
resp_json = json.dumps({
"ok": True,
'message': 'Delete successful',
'deleted gid': gid
})
#print 'Selection: ' + resp_json
return resp_json
def insertUsers():
d_conn = pg_operations2.pg_connect2(database=mySettings.DATABASE,
user=mySettings.USER,
password=mySettings.PASSWORD,
host=mySettings.HOST,
port=mySettings.PORT)
conn = d_conn['conn']
cursor = d_conn['cursor']
q = 'DROP TABLE IF EXISTS tabla.users'
cursor.execute(q) #Deletes the table tabla.users if exists
q = 'create table tabla.users (gid serial primary key, usuario text, typeuser text, psw text);'
cursor.execute(q) #creates the table tabla.users
u1 = {
'usuario': 'usu1',
'typeuser': '******',
'psw': hashlib.sha512('ps1').hexdigest()
}
u2 = {
'usuario': 'usu2',
'typeuser': '******',
'psw': hashlib.sha512('ps2').hexdigest()
}
u3 = {
'usuario': 'usu3',
'typeuser': '******',
'psw': hashlib.sha512('ps3').hexdigest()
}
lu = [u1, u2, u3]
for u in lu:
d_str = pg_operations2.dict_to_string_fields_and_vector_values2(
d=u,
list_fields_to_remove=None,
geom_field_name=None,
epsg=None,
geometry_type=None,
epsg_to_reproject=None)
r = pg_operations2.pgInsert2(d_conn=d_conn,
nom_tabla='tabla.users',
d_str=d_str,
str_fields_returning='gid')
print r
conn.commit()
cursor.close()
conn.close()
print(hashlib.sha512('Hola').hexdigest())
def buildingInsert(form_data):
#json_data='{"gid":"10","descripcion":"Hospital","area":"100","geom":"100,100,200,100,200,200,100,100"}'
d_form_data = json.loads(form_data)
#elimitate commas and put spaces to the list of coordinates
print 'Before --> d_form_data["geom]": ' + d_form_data['geom']
d_form_data['geom'] = pg_operations2.transform_coords_ol_to_postgis(
coords_geom=d_form_data['geom'])
print 'After --> d_form_data["geom]": ' + d_form_data['geom']
#database connection
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
#getting parameters for pg_sinser2 function
d2 = pg_operations2.dict_to_string_fields_and_vector_values2(
d=d_form_data, list_fields_to_remove=['gid'])
str_field_names = d2['str_field_names']
list_field_values = d2['list_field_values']
str_s_values = d2['str_s_values']
print 'str_field_names: ' + str_field_names
print 'list_field_values: ' + str(list_field_values)
print 'str_s_values: ' + str_s_values
#inserting the building
list_returning = pg_operations2.pg_insert2(
d_conn=d_conn,
nom_tabla='d.buildings',
str_field_names=str_field_names,
list_field_values=list_field_values,
str_s_values=str_s_values,
str_fields_returning='gid')
new_gid = list_returning[0][0]
#disconnecting from the database. DO NOT FORGET
pg_operations2.pg_disconnect2(d_conn)
#creating a json answer
resp_json = json.dumps({
"tableName": "d.buildings",
"ok": 'true',
'data': d_form_data,
'message': 'Building inserted',
'new_gid': new_gid
})
print 'building_insert: ' + resp_json
return resp_json
def Update(nom_tabla, tipo_geom, json_data):
d_form_data = json.loads(json_data)
gidUpdate = int(d_form_data['gid'])
print gidUpdate
d_form_data['geom'] = pg_operations2.transform_coords_ol_to_postgis(
coords_geom=d_form_data['geom'])
d_conn = pg_operations2.pg_connect2(database, user, password, host, port)
d2 = pg_operations2.dict_to_string_fields_and_vector_values2(
d=d_form_data,
list_fields_to_remove=['gid'],
geom_field_name='geom',
epsg='25830',
geometry_type=tipo_geom,
epsg_to_reproject=None)
list_returning = pg_operations2.pg_update2(
d_conn=d_conn,
table_name=nom_tabla,
str_field_names=d2['str_field_names'],
str_s_values=d2['str_s_values'],
list_field_values=d2['list_field_values'],
cond_where='where gid=%s',
list_values_cond_where=[gidUpdate])
#new_gid=list_returning[0][0]
pg_operations2.pg_disconnect2(d_conn)
resp_json = json.dumps({
"ok": True,
'data': d_form_data,
'message': 'Row updated'
})
print 'Updated: ' + resp_json
return resp_json
def login(session, request):
"""
Cheks if the user and password exists
If true stores the user information in the session dictionary
"""
#gets the html form user data
form_data = request.form['form_data']
d_form_data = json.loads(form_data)
usuario = d_form_data['usuario']
psw = d_form_data['psw']
#encrypts the passord typed by the user in the html form
encrPsw = hashlib.sha512(psw).hexdigest()
d_conn = pg_operations2.pg_connect2(database=mySettings.DATABASE,
user=mySettings.USER,
password=mySettings.PASSWORD,
host=mySettings.HOST,
port=mySettings.PORT)
#selects the psw in the database for the user. Is already encrypted
r = pg_operations2.pg_select2(d_conn,
table_name='tabla.users',
string_fields_to_select='typeuser,psw',
cond_where='where usuario=%s',
list_val_cond_where=[usuario])
if r <> None:
databaseEncrPsw = r[0]['psw']
typeUser = r[0]['typeuser']
#check if matches
if databaseEncrPsw == encrPsw:
#stores the user information in the session dictionary
session['usuario'] = usuario
session['authenticated'] = SECRET
session['typeUser'] = typeUser
resp_json = json.dumps({
"tableName":
"",
"ok":
"true",
"data":
"",
"message":
"User {0} logged in".format(usuario),
"new_gid":
""
})
else:
resp_json = json.dumps({
"tableName":
"",
"ok":
"true",
"data":
"",
"message":
"Incorrect password".format(usuario),
"new_gid":
""
})
else:
resp_json = json.dumps({
"tableName":
"",
"ok":
"false",
"data":
"",
"message":
"The user {0} does not exits".format(usuario),
"new_gid":
""
})
d_conn['cursor'].close()
d_conn['conn'].close()
return resp_json