def update_dt_vehicle_gps(**item):
"""
更新车辆当前位置信息
item:
{u'direction': 270.001, u'posInvalidate': 0, 'pt': 'POINT(104.0328 30.645)',
u'taxiNo': u'\u4eacA33333', u'empty': u'yes', u'time': u'/Date(1487317354000)/',
u'lat': 30.645, u'lng': 104.0328, 'vehicle_type': 3, u'speed': 15.433333333333334, u'alarmType': 0}
"""
#print item
action_type = "update"
time_str = parse_time2str(item['time'])
update_sql = "UPDATE here_is_db_table SET pt=ST_GeomFromText('{0}', 4326), is_empty='{1}', key={2}, key={3}, time='{4}', key={5} WHERE vehicle_card = '{6}'".format(
item['pt'], item['key'], int(item['key']), int(item['key']), time_str,
item['key'], item['taxiNo'])
try:
cursor.execute(update_sql)
connection.commit()
if log_details_true:
mylogger.info("[update gps data] {0}".format(update_sql))
except Exception, e:
connection.rollback()
mylogger.error("db Error -{0}- when update, sql---: {1}".format(
e, update_sql))
def query_vehicle_propertyid(VehId):
"""查询VehId的车辆的vehiclepropertyid"""
get_sql = "SELECT key FROM db_table where vehicle_card ='{0}' ".format(
VehId)
try:
cursor.execute(get_sql)
result = cursor.fetchone() #tuple
except Exception, e:
mylogger.error("db Error - %s: " % e)
def query_vehicle_is_exist(vehId):
"""查询VehId的车辆，是否在当前位置信息表里"""
query_sql = "select 1 from here_is_db_table where vehicle_card = '%s'" % vehId
result = False
try:
cursor.execute(query_sql)
if cursor.fetchone() is not None:
result = True
except Exception, e:
mylogger.error(
"db Error - %s - when query vehicle_card exist from db" % e)
def insert_dt_vehicle_gps(**item):
""" 向here_is_db_table 插入新数据"""
action_type = "insert"
time_str = parse_time2str(item['time'])
insert_sql = """INSERT INTO "here_is_db_table" (key, sync_type, key, key, time, alarm_type, is_empty, pt) VALUES ('{0}',{1},{2},{3},'{4}','{5}','{6}',ST_GeomFromText('{7}', 4326))""".format(
item['taxiNo'], SYNC_TYPE['weijie'], int(item['key']),
int(item['key']), time_str, item['alarmType'], item['empty'],
item['pt'])
try:
cursor.execute(insert_sql)
connection.commit()
if log_details_true:
mylogger.info("[insert gps data] {0}".format(insert_sql))
except Exception, e:
connection.rollback()
mylogger.error("db Error -{0}- when insert, sql---: {1}".format(
e, insert_sql))
def car_real_location_to_db(**item):
""" 将所有接收的gps信息写入总表 here_is_db_table """
_sql = """INSERT INTO "here_is_db_table" (key, key, key, key, time, key, is_empty, is_validate, pt)
VALUES ('{0}',{1},{2},{3},'{4}','{5}','{6}','{7}',ST_GeomFromText('{8}', 4326))""".format(
item['key'], SYNC_TYPE['key'], int(item['key']), int(item['key']),
parse_time2str(item['time']), item['key'], item['empty'],
is_validate(item['key']), item['pt'])
if log_details_true:
mylogger.info("[insert GPS datas] {0}".format(_sql))
try:
cursor.execute(_sql)
connection.commit()
except Exception, e:
connection.rollback()
mylogger.error("db Error -{0}- when insert sql---: {1}".format(
e, _sql))