def upload2mysql(sql_list):
try:
user = get_username_and_password()['user']
passwd = get_username_and_password()['passwd']
# 打开数据库连接
conn = MySQLdb.connect(host='localhost',
user=user,
passwd=passwd,
db='traffic_db',
port=3306)
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
for sql in sql_list:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
conn.commit()
#关闭游标和链接
cursor.close()
conn.close()
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
cursor.close()
conn.close()
def post(self):
user = get_username_and_password()['user']
passwd = get_username_and_password()['passwd']
print "exc"
try:
#连接数据库
conn = MySQLdb.connect(host='localhost',
port=3306,
user=user,
passwd=passwd,
db='traffic_db',
cursorclass=MySQLdb.cursors.DictCursor)
cur = conn.cursor()
#查询数据库
cur.execute("select * from location")
#获取返回值
results = cur.fetchall()
#解析为json格式
arr = json.dumps(results)
self.write(arr)
cur.close()
conn.close()
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
cur.close()
conn.close()
def getsegmentlist():
try:
user = get_username_and_password()['user']
passwd = get_username_and_password()['passwd']
# 打开数据库连接
# 使用cursor()方法获取操作游标,指定游标的类型为MySQLdb.cursors.DictCursor则结果集以字典返回，带有字段名；不知名类型则默认以元组返回，无字段名
conn = MySQLdb.connect(host='localhost',
user=user,
passwd=passwd,
db='traffic_db',
port=3306)
# 使用cursor()方法获取操作游标
cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
#执行sql语句
count = cur.execute('select * from roadnet order by segmentid')
print 'there are %s segments in the whole roadnet' % count
#获得结果集
results = cur.fetchall()
#必须提交请求
conn.commit()
cur.close()
conn.close()
print 'there are ' + str(len(results)) + ' roads'
return results
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
cur.close()
conn.close()
def get_segmentlist_of_area(area):
try:
user = get_username_and_password()['user']
passwd = get_username_and_password()['passwd']
# 打开数据库连接
# 使用cursor()方法获取操作游标,指定游标的类型为MySQLdb.cursors.DictCursor则结果集以字典返回，带有字段名；不知名类型则默认以元组返回，无字段名
conn = MySQLdb.connect(host='localhost',
user=user,
passwd=passwd,
db='traffic_db',
port=3306,
cursorclass=MySQLdb.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cur = conn.cursor()
#执行sql语句
count = cur.execute("select * from roadnet where area like '%" + area +
"%'")
print 'there has %s segments in area %s' % (count, area)
#获得结果集
results = cur.fetchall()
#必须提交请求
conn.commit()
cur.close()
conn.close()
return results
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
cur.close()
conn.close()
def getMacListByAPid(APid):
mac_list = []
try:
user = get_username_and_password()['user']
passwd = get_username_and_password()['passwd']
# 打开数据库连接
# 使用cursor()方法获取操作游标,指定游标的类型为MySQLdb.cursors.DictCursor则结果集以字典返回，带有字段名；不知名类型则默认以元组返回，无字段名
conn = MySQLdb.connect(host='localhost',
user=user,
passwd=passwd,
db='traffic_db',
port=3306)
cur = conn.cursor(MySQLdb.cursors.DictCursor)
#执行sql语句
cur.execute("select * from location where num=" + str(APid))
#获得一条结果
results = cur.fetchall()
#必须提交请求
conn.commit()
cur.close()
conn.close()
for result in results:
mac_list.append(result['mac'])
return mac_list
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
cur.close()
conn.close()
def uploadlocation(num,mac,lon,lat):
try:
user=get_username_and_password()['user']
passwd=get_username_and_password()['passwd']
# 打开数据库连接
conn=MySQLdb.connect(host='localhost',user=user,passwd=passwd,port=3306)
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
#如果数据库不存在，则创建数据库
cursor.execute("create database if not exists traffic_db")
conn.select_db('traffic_db')
#若表不存在则建表
cursor.execute("CREATE TABLE IF NOT EXISTS location(num int(4) NOT NULL,lon DOUBLE NOT NULL,lat DOUBLE NOT NULL,mac CHAR(12) NOT NULL UNIQUE,PRIMARY KEY (mac))")
conn.commit()
#查询数据库里是否已经存在该探针记录
recordcount=cursor.execute("SELECT * FROM location WHERE mac='"+mac+"'")
conn.commit()
if recordcount>0:
#执行更新语句
print 'update location set num='+num+',lon='+lon+',lat='+lat+" where mac='"+mac+"'"
cursor.execute('update location set num='+num+',lon='+lon+',lat='+lat+" where mac='"+mac+"'")
conn.commit()
response=cursor.execute("SELECT * FROM location WHERE mac='"+mac+"'")
conn.commit()
else:
# 执行插入语句
response=cursor.execute('INSERT INTO location VALUES('+num+','+lon+','+lat+",'"+mac+"')")
conn.commit()
cursor.close()
conn.close()
return response
except MySQLdb.Error,e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
cursor.close()
conn.close()
def post(self):
#接收路网数据
data1 = self.get_argument("rr")
data = json.loads(data1)
length = len(data)
user = get_username_and_password()['user']
passwd = get_username_and_password()['passwd']
#循环迭代每条路段的数据
for i in range(0, length, 1):
#起点ap
sap = data[i]["sap"]
#终点ap
eap = data[i]["eap"]
#起点经纬度
sposition = data[i]["sposition"]
#终点经纬度
eposition = data[i]["eposition"]
#路段名
name = data[i]["name"]
#区域名
area = data[i]["area"]
#道路等级
level = data[i]["level"]
#道路长度
dist = data[i]["length"]
#最大限速
maxspeed = data[i]["maxspeed"]
#速度等级
line1 = data[i]["line1"]
line2 = data[i]["line2"]
line3 = data[i]["line3"]
line4 = data[i]["line4"]
try:
#连接数据库
conn = MySQLdb.connect(host='localhost',
port=3306,
user=user,
passwd=passwd,
db='traffic_db',
charset="utf8")
cur = conn.cursor()
#若表不存在则建表
cur.execute(
"CREATE TABLE IF NOT EXISTS roadnet (segmentid int(11) AUTO_INCREMENT NOT NULL,"
"startAPid int(11) NOT NULL,"
"endAPid int(11) NOT NULL,"
"dist int(11) NOT NULL,"
"level int(11) NOT NULL,"
"max_speed double NOT NULL,"
"line1 double NOT NULL,"
"line2 double NOT NULL,"
"line3 double NOT NULL,"
"line4 double NOT NULL,"
"area varchar(10) DEFAULT NULL,"
"sposition varchar(50) DEFAULT NULL,"
"eposition varchar(50) DEFAULT NULL,"
"name char(20) DEFAULT NULL,"
"PRIMARY KEY (segmentid))"
"ENGINE=InnoDB DEFAULT CHARSET=utf8")
conn.commit()
#查询是否已经存在记录，如果存在则覆盖旧的记录
count = cur.execute("select * from roadnet where startAPid=" +
sap + " and endAPid=" + eap)
conn.commit()
#判断某条记录是否重复插入
if count >= 1:
sql = "UPDATE roadnet SET dist=%s,level=%s,max_speed=%s,line1=%s,line2=%s,line3=%s,line4=%s,area=%s,sposition=%s,eposition=%s,name=%s where startAPid=%s and endAPid=%s"
cur.execute(
sql,
(dist, level, maxspeed, line1, line2, line3, line4,
area, sposition, eposition, name, sap, eap))
conn.commit()
cur.close()
conn.close()
else:
sql = "INSERT INTO roadnet (startAPid, endAPid, dist, level, max_speed, line1, line2, line3, line4, area, sposition, eposition, name) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cur.execute(
sql, (sap, eap, dist, level, maxspeed, line1, line2,
line3, line4, area, sposition, eposition, name))
conn.commit()
cur.close()
conn.close()
self.write("success")
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
cur.close()
conn.close()