Postgresql 数据库操作类

# -*- coding: utf-8 -*-

'''
Postgresql 数据库操作类
'''
import psycopg2
import json
# import pandas as pd

# 从postgresql获取数据
class Postgredata():
    def __init__(self):
        self.dbname = "----"
        self.user = "-----"
        self.password = "-----"
        self.host = "--------"
        self.port = "5432"

    def getdata(self,query=None):
        try:
            conn=psycopg2.connect(dbname=self.dbname, user=self.user, password=self.password, host=self.host, port=self.port)
            cursor = conn.cursor()
            try:
                cursor.execute(query)
                data=cursor.fetchall()
                if len(data)==0:
                    print('没有查到数据')
                    return data
                else:
                    return data
            except Exception as e:
                print(e)
                print('查询错误')
        except Exception as e:
            print(e)
            print('连接失败')
        finally:
            conn.close()

    def setdbname(self, dbname=None):
        self.dbname = dbname

    def getdbname(self):
        return self.dbname

    def getuser(self):
        return self.user

    def setuser(self, user=None):
        self.user = user

    def getpassword(self):
        return self.password

    def setpassword(self, password):
        self.password = password

    def gethost(self):
        return self.host

    def sethost(self, host=None):
        self.host = host

    def getport(self):
        return self.port

    def setport(self, port=None):
        self.port = port

    def savedata(self, query=None):
        #connection.commit()
        #connection.rollback()
        try:
            conn=psycopg2.connect(dbname=self.dbname, user=self.user, password=self.password, host=self.host, port=self.port)
            cursor = conn.cursor()
            try:
                cursor.execute(query)
                conn.commit()
            except Exception as e:
                print(e)
                print('保存失败')
        except Exception as e:
            print(e)
            print('连接失败')
        finally:
            conn.close()

pg = Postgredata()
query = 'select * from ship_info_trail_normal'
data_list = pg.getdata(query=query)

# print(len(data_list), type(data_list[0]),data_list[0][1])
# import json
# print(json.loads(data_list[0][1])["data"])

li = []
for i in range(len(data_list)):
    data_json = json.loads(data_list[i][1])["data"]
    s = [[str(json.loads(data_list[i][0]))], [float(data_json[0]['lng']),float(data_json[0]['lat'])], [float(data_json[-1]['lng']),float(data_json[-1]['lat'])]]
    li.append(s)

label1 = []
for j in range(len(li)):
    li1 = li[j]
    li2 = li[:j]+li[j+1:]
#     len(new_li),new_li
    label2 = []
    for i in range(len(li2)):
        if abs(li2[i][1][0]-li1[1][0])<2 and abs(li2[i][1][1]-li1[1][1])<2 and abs(li2[i][2][0]-li1[2][0])<2 and abs(li2[i][2][1]-li1[2][1])<2:
            label2.append(str(li2[i][0][0]))
    li[j].append(label2)
    print(li[j])
    label1.append(label2)

pg = Postgredata()
for i in range(len(li)):
    print(li[i][3])
    same = li[i][3]
#     same = ','.join(same)
    dict={}
    dict['result']=same
    json_data = json.dumps(dict) #转成字典，存成json格式
    print(json_data)
    id = li[i][0][0]
#     id = ','.join(id)
    querys = 'update ship_info_trail_normal_copy1 set same_id=\'' + json_data  + '\' where shipid=\'' + str(id)+ '\''  #注意转义字符
    print(querys)
    pg.savedata(query=querys) #更新用savedata函数


# columns = ['shipid','ship_trail','a_timestamp']
# data_df = pd.DataFrame(columns=columns, data=data_list)
# data_df.to_csv('ship_info_trail_normal.csv', encoding='utf-8', index=False)