In [1]:

    

import psycopg2


    

In [15]:

    

class CloudDatabase(object):
    # Init database and input ip
    # 初始化数据库并传入ip
    def __init__(self, database, user, password, ip="127.0.0.1", port="5432"):
        self.database = database
        self.user = user
        self.password = password
        self.ip = ip
        self.port = port

    # Connect database and set input as host, return connection and cursor
    # 连接ip端数据库并返回connection和cursor
    def db_connect(self):
        self.connection = psycopg2.connect(database=self.database, user=self.user,
                                           password=self.password, host=self.ip, port=self.port)
        self.cursor = self.connection.cursor()

    # Write log file
    # 输出日志
    def write_log(self, e):
        self.connection.rollback()
        with open("log.txt", 'a') as log_file:
            log_file.writelines(str(e))
    
    #将数据导入
    def import_csv(self, start, end):
        for i in range(start, end):
            print(i)
            try:
                sql_command = '''
                COPY face{0}
                FROM 'D:\\Users\\KYH\\Desktop\\EmotionMap\\FlickrEmotionData\\4face_all\\face{0}.csv' 
                WITH csv;'''.format(i)
                self.cursor.execute(sql_command)
                self.connection.commit()
            except Exception as e:
                self.write_log(e)
                
    #将创建表
    def create_table(self, start, end):
        for i in range(start, end):
            print(i)
            try:
                sql_command = '''
CREATE TABLE face{0}
(id BIGINT NOT NULL,
userid TEXT,
photo_date_taken DATE,
photo_date_uploaded BIGINT,
title TEXT DEFAULT NULL,
description TEXT DEFAULT NULL,
user_tags TEXT DEFAULT NULL,
longitude FLOAT DEFAULT 0,
latitude FLOAT DEFAULT 0,
accuracy INTEGER DEFAULT 0,
download_url TEXT NOT NULL,
facenum INTEGER,
happiness FLOAT,
neutral FLOAT,
sadness FLOAT,
disgust FLOAT,
anger FLOAT,
fear FLOAT,
surprise FLOAT,
facequality_s FLOAT,
facequality_v FLOAT,
smile_s FLOAT,
smile_v FLOAT,
gender INTEGER,
ethnicity INTEGER,
age INTEGER,
point geometry(Point)
);
                '''.format(i)
                self.cursor.execute(sql_command)
                self.connection.commit()
            except Exception as e:
                self.write_log(e)
    
    
    def create_index(self,start,end):
        for i in range(start,end):
            print(i)
            try:
                sql_command='''
CREATE INDEX iface_lon{0} ON face{0}(longitude);
CREATE INDEX iface_lat{0} ON face{0}(latitude);

CREATE INDEX iface_id{0} ON face{0}(id);
CREATE INDEX iface_userid{0} ON face{0}(userid);
CREATE INDEX iface_photo_date_taken{0} ON face{0}(photo_date_taken);
CREATE INDEX iface_facenum{0} ON face{0}(facenum);
CREATE INDEX iface_happiness{0} ON face{0}(happiness);
CREATE INDEX iface_neutral{0} ON face{0}(neutral);
CREATE INDEX iface_sadness{0} ON face{0}(sadness);
CREATE INDEX iface_disgust{0} ON face{0}(disgust);
CREATE INDEX iface_anger{0} ON face{0}(anger);
CREATE INDEX iface_fear{0} ON face{0}(fear);
CREATE INDEX iface_surprise{0} ON face{0}(surprise);
CREATE INDEX iface_smile_s{0} ON face{0}(smile_s);
CREATE INDEX iface_smile_v{0} ON face{0}(smile_v);
CREATE INDEX iface_gender{0} ON face{0}(gender);
CREATE INDEX iface_ethnicity{0} ON face{0}(ethnicity);
CREATE INDEX iface_age{0} ON face{0}(age);

CREATE INDEX iface_pt{0} ON face{0} USING gist(point);
                '''.format(i)
                self.cursor.execute(sql_command)
                self.connection.commit()
            except Exception as e:
                self.write_log(e)    
    
    def drop_table(self,start,end):
        for i in range(start,end):
            print(i)
            try:
                sql_command='''
drop table face{0}
                '''.format(i)
                self.cursor.execute(sql_command)
                self.connection.commit()
            except Exception as e:
                self.write_log(e)
                
                
    def set_coordinate(self,start,end):
        for i in range(start,end):
            print(i)
            try:
                sql_command='''
SELECT UpdateGeometrySRID('face{0}', 'point', 4326);
                '''.format(i)
                self.cursor.execute(sql_command)
                self.connection.commit()
            except Exception as e:
                self.write_log(e)
                
    def add_country(self,start,end):
        for i in range(start,end):
            print(i)
            try:
                sql_command='''
    ALTER TABLE face{0} Add country text;
                '''.format(i)
                self.cursor.execute(sql_command)
                self.connection.commit()
            except Exception as e:
                self.write_log(e)      
                
    def match_pt_country(self,start,end):
        for i in range(start,end):
            print(i)
            try:
                sql_command='''
                UPDATE face{0}
SET country=t.admin
FROM countries AS t
WHERE ST_Intersects(face{0}.point,t.geom);
                '''.format(i)
                self.cursor.execute(sql_command)
                self.connection.commit()
            except Exception as e:
                self.write_log(e)


    

In [16]:

    

database = CloudDatabase("Face", "postgres", "postgres", "127.0.0.1")
        #database = CloudDatabase("Face", "postgres", "postgres", "47.254.24.30")
        database.db_connect()


    

In [20]:

    

# 创建照片表
        # start指的是从第几个数据库表开始end表示的是第几个数据库表结束
        start = 0
        end = 2
        #database.drop_table(start,end)
        #database.create_table(start,end)
        #database.import_csv(start,end)
        #database.create_index(start,end)
        #database.set_coordinate(start,end)
        #database.add_country(start,end)
        database.match_pt_country(start,end)


    

    




0
1

In [ ]: