API


In [15]:
import psycopg2

CREATE TABLE API
(key CHARACTER VARYING(100) NOT NULL PRIMARY KEY,
secret CHARACTER VARYING(100) NOT NULL, type CHARACTER VARYING(10) NOT NULL,
start_use BOOLEAN NOT NULL DEFAULT(FALSE)
);
CREATE INDEX key_index ON API(key);
CREATE INDEX secret_index ON API(secret);
CREATE INDEX type_index ON API(type);
CREATE INDEX start_use_index ON API(start_use);

INSERT INTO API VALUES
('199ed59000c39dd0844b59d01fa7570c','4a2ce28f1bb8a1fe','flickr', FALSE),
('81ec25b2e0093c6f2c2e70da0175a7ea','6eaa89f23ae0762e','flickr', FALSE),
('4f3c045b20127210215889331a6ab134','2010f9a1cf1abe18','flickr', FALSE),
('382e669299b2ea33fa2288fd7180326a','b556d443c16be15e','flickr', FALSE),
('b422ff64b04ecee4c169ca01a21f5bcb','57d1f80344df6188','flickr', FALSE),
('042e20a01e7080ae8a7a4889208d215f','c2d8a1f6b9760cb9','flickr', FALSE),
('fc7b495434337ecb3ea080a3f410b0c6','8ed6d435785a2023','flickr', FALSE),
('696005e9ef76d9ef438c99f21605b322','9085b2dbbae8ede5','flickr', FALSE),
('7ce333ecbbf8ab34a6843e0edeeed7f4','c72121c17578668c','flickr', FALSE),
('2c8b19dfe58aded2e3a756bfba941558','9ef37b12d94a3da4','flickr', FALSE),
('3d3687711d33d98a847a34e094cd228d','27b2d210f4142b6b','flickr', FALSE),
('3f647b14450052251bbda3d4f8e3efd4','afa71fda070402ad','flickr', FALSE),
('60ff62a1aa6701d0bbd642663cf35d96','729cd4f7b7c92931','flickr', FALSE),
('75b8a444d318c769ae8142b1351a2b3c','a8723ba65b8ca827','flickr', FALSE),
('089c5cfa0ff527fdb25cd8b0a547e914','2dbdc4ce5d3e752b','flickr', FALSE),
('40e4f0df81f75752f74e36683808d7ab','f1a8144378ed93d4','flickr', FALSE),
('622f1f3e57c9f337d1212444831ff475','fafddabfbe0405b7','flickr', FALSE),
('1f7c3204baf7159d4ec9e833ee21d11f','90b9ed8a906812f3','flickr', FALSE);

INSERT INTO API VALUES
('DCTCyC_D_3ZGW4VZVkmj25IJNUuNdXT4','T9NzaLTeiSe_Rqtye0pUCs3Ed-ZQiq6V','Face++',False),
('Dm18HrboYUuAVeX6A-_6Y0q27OkE3cvN','3FPheINGB2qLTDcV-FOpjloXmOjDDG4A','Face++',False),
('AB3ubqE6FNZGPxu8NE4c1F0OJCn8HInI','VHH-kDCN1MQ_Ru2qq21YPyOwVF-e2Hmb','Face++',False),
('HGLXmBY-gO6WsCB4ZkFpEadYDD3SO4S','oN8M-apFgr-4V4qW2W7TSuyUHy2Gw89','Face++',False),
('Xgv1FMjf7zaGVfcR_hlpdyif3Ez0q1m5','ISIsT6DYelUG9iPYS3NEKJ1dTfyReSph','Face++',False),
('D8KfUEN_6PM1yCHveoKuxgZ0GT21X1Bh','_7vXMF7dsWNa7CIZiSOKs5JZp_XACHvf','Face++',False),
('Fu4-6MVuWgGCUeIxjSYRusH8eVilBtAz','epLYs0n7YLAWaYjA7HlXuLILHjKnL5iJ','Face++',False),
('mhFDxxTfQy6Rd4u298kwFvxbCxAMCpHq','yMZBadr5wVFsUWSZ_I0jo0w9vzjDVEii','Face++',False),
('1iPU-v_kLZs5OXjUZBlFL6ooTci8U3Yp','jTt_gIkc2bqnYGKucZboLuSQuUBzT2Vk','Face++',False),
('h_KYerXL2QeTmsAJGGBiPZFxl7_QXmiO','v1uoVifc12-1SYRsyNxgSR5k4QQqyK9u','Face++',False),
('sPG0n8LFaQfxxglBtbverq7iJgPs8DMQ','HBd-aaVTudtepT9bhybHKtJaLx7sMw1Y','Face++',False),
('hOLmeAvF4_gPFycCYZO4HoyJsFa7rlHc','gqAKxnvHvxpgt-mnHZMc7EwCD2XyHk0-','Face++',False),
('nBbJ3_x3xmnmc7CJO_VrK8vT8lmGlZIB','HB12_nEr3Y2I9rgKCtN0KKYX-LPA-sUU','Face++',False),
('_VTJAxAE34Xt-h236X1LVbO6gMJwszLN','f9roZWjSMH7csIVn4RHuGQ6ZDOwxhuCx','Face++',False),
('oSzQJ7Owxqk2T5yZuIHKoJ3s_n11BDQP','wv-sD-NgKWm0kRK757UxUDsbuzj0TYs ','Face++',False),
('NCv1iO90aVJ4Axy-FjE2SemVMP50Fg8e','xjKxi7n4qfO9h3N1uAThcMD01L6478YK','Face++',False),
('jFIzayRBM4X3-YJzzXBkZlzXy5MPe1Pl','spDfyMN7FnwnS5hbtnxFhxtuFmKeNPRs','Face++',False),
('TUWlFehF2kAEowOZRVyLa-0XzjoFKvUR','d5T4Lpx6DAFMTNRr6kfkcr6ZuQgfhosc','Face++',False),
('RsqKoZtF2Zcj1zKdn0i4-3yTCrtxeuVo','J2qGXnlTLcUqdS9CJ_6M-gt57vVohjy-','Face++',False),
('bbhAbpc7IrCGCWzrUu1bftZhR9iSkOh4','2jTiWxN2dCdTefw8ZbX0V8f_EOVzxbvk','Face++',False),
('8k8rid_MisVBXRSuAeiLVA5ymeT8eYQ','gw2dA-e0fwv20icOC6OucBcRgc9yPi8k','Face++',False),
('ebfsStmOrxvn-kppu
-1AXrsYmrJSmB0','NqvtZaDgEfV4U5m2cn-7agaw5WEpLL2z','Face++',False),
('mDCcCZx7EqeQjRu2yv8byuCB_xWkFMyd','fn73kxqkkP0mesI-gcPBk0BUmrAmdzTe','Face++',False);


In [16]:
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()

    def execute(self, sql):
        try:
            self.cursor.execute(sql)
            self.connection.commit()
        except Exception as e:
            self.connection.rollback()
            
    # Write log file
    # 输出日志
    def write_log(self, e):
        self.connection.rollback()
        with open("log.txt", 'a') as log_file:
            log_file.writelines(str(e))

In [17]:
database = CloudDatabase("PlaceEmotion", "postgres", "postgres", "127.0.0.1")
database.db_connect()

In [14]:
# Test API
import requests
sql = '''
SELECT key, secret
FROM API
WHERE type = 'flickr'
'''
database.execute(sql)
for api in database.cursor.fetchall():
    print(api[0], api[1])


1f7c3204baf7159d4ec9e833ee21d11f 90b9ed8a906812f3
622f1f3e57c9f337d1212444831ff475 fafddabfbe0405b7
40e4f0df81f75752f74e36683808d7ab f1a8144378ed93d4
089c5cfa0ff527fdb25cd8b0a547e914 2dbdc4ce5d3e752b
75b8a444d318c769ae8142b1351a2b3c a8723ba65b8ca827
60ff62a1aa6701d0bbd642663cf35d96 729cd4f7b7c92931
3f647b14450052251bbda3d4f8e3efd4 afa71fda070402ad
3d3687711d33d98a847a34e094cd228d 27b2d210f4142b6b
2c8b19dfe58aded2e3a756bfba941558 9ef37b12d94a3da4
7ce333ecbbf8ab34a6843e0edeeed7f4 c72121c17578668c
696005e9ef76d9ef438c99f21605b322 9085b2dbbae8ede5
fc7b495434337ecb3ea080a3f410b0c6 8ed6d435785a2023
042e20a01e7080ae8a7a4889208d215f c2d8a1f6b9760cb9
b422ff64b04ecee4c169ca01a21f5bcb 57d1f80344df6188
382e669299b2ea33fa2288fd7180326a b556d443c16be15e
4f3c045b20127210215889331a6ab134 2010f9a1cf1abe18
81ec25b2e0093c6f2c2e70da0175a7ea 6eaa89f23ae0762e
199ed59000c39dd0844b59d01fa7570c 4a2ce28f1bb8a1fe

Location

CREATE TABLE location
(id SERIAL PRIMARY KEY,
city_name CHARACTER VARYING(100) NOT NULL,
lat FLOAT NOT NULL,
lon FLOAT NOT NULL,
start_query BOOLEAN NOT NULL DEFAULT(FALSE)
);
CREATE INDEX id_index ON location(id);
CREATE INDEX city_name_index ON location(city_name);
CREATE INDEX start_query_index ON location(start_query);


In [20]:
# Import all sampling points
file = open('/media/raid/PlaceEmotion/pts200_us.csv', 'r')
st = file.read()
file.close()
sql = '''
INSERT INTO location(city_name, lat, lon)
VALUES  
{0}
'''.format(st)
#print(sql)
database.execute(sql)

CREATE TABLE photo
(id BIGINT PRIMARY KEY,
url TEXT NOT NULL,
city CHARACTER VARYING(100) NOT NULL,
face_number INTEGER DEFAULT(NULL),
start_detect BOOLEAN NOT NULL DEFAULT(FALSE),
start_info BOOLEAN NOT NULL DEFAULT(FALSE)
);
CREATE INDEX photo_id_index ON photo(id);
CREATE INDEX photo_start_info_index ON photo(start_info);
CREATE INDEX photo_start_detect_index ON photo(start_detect);
CREATE INDEX photo_face_number_index ON photo(face_number);

CREATE TABLE photo_info
(id BIGINT PRIMARY KEY, owner CHARACTER VARYING (30), owner_location CHARACTER VARYING (30),
lat FLOAT,
lon FLOAT,
photo_take_date DATE,
photo_upload BIGINT,
accuracy INTEGER,
geotag TEXT,
neighbourhood TEXT; locality TEXT; county TEXT; region TEXT; country TEXT; );
CREATE INDEX photo_id_index ON photo(id);
CREATE INDEX photo_lat_index ON photo(lat);
CREATE INDEX photo_lon_index ON photo(lon);
CREATE INDEX photo_date_index ON photo(photo_take_date);
CREATE INDEX photo_f_hasface_index ON photo(f_hasface);
CREATE INDEX photo_start_detect_index ON photo(start_detect);
CREATE INDEX photo_start_info_index ON photo(start_info);
CREATE INDEX photo_start_recog_index ON photo(start_recog);
CREATE INDEX photo_facenum_index ON photo(facenum);

CREATE INDEX photo_lat_index ON photo(lat);
CREATE INDEX photo_lon_index ON photo(lon);
CREATE INDEX photo_date_index ON photo(photo_take_date);
CREATE INDEX photo_f_hasface_index ON photo(f_hasface);
CREATE INDEX photo_start_detect_index ON photo(start_detect);

Four layers framework:

1. API generation and location selection: return API and lat, lon, city.  
2. collect photo url and id to photo table: input API and lat, lon, city, return photo id and url.  
3. get photo information to photo_info table: input photo id, return photoinfo.  
4. detect photo face info to face table. input photo id, return emotion info.

In [25]:
import datetime
DATE=datetime.date(2012,1,1) 
while(True): 
    DATE2=DATE+datetime.timedelta(days=10) 
    datemin ="{0}-{1}-{2}".format(DATE.year,DATE.month,DATE.day) 
    datemax ="{0}-{1}-{2}".format(DATE2.year,DATE2.month,DATE2.day) 
    DATE=DATE+datetime.timedelta(days=10) 
    print(datemin,datemax) 
    #get_photo_from_location(db_connection, db_cursor, site, latitude, longitude, datemin, datemax) 
    if DATE.year==2018 and DATE.month==11: 
        break


2012-1-1 2012-1-11
2012-1-11 2012-1-21
2012-1-21 2012-1-31
2012-1-31 2012-2-10
2012-2-10 2012-2-20
2012-2-20 2012-3-1
2012-3-1 2012-3-11
2012-3-11 2012-3-21
2012-3-21 2012-3-31
2012-3-31 2012-4-10
2012-4-10 2012-4-20
2012-4-20 2012-4-30
2012-4-30 2012-5-10
2012-5-10 2012-5-20
2012-5-20 2012-5-30
2012-5-30 2012-6-9
2012-6-9 2012-6-19
2012-6-19 2012-6-29
2012-6-29 2012-7-9
2012-7-9 2012-7-19
2012-7-19 2012-7-29
2012-7-29 2012-8-8
2012-8-8 2012-8-18
2012-8-18 2012-8-28
2012-8-28 2012-9-7
2012-9-7 2012-9-17
2012-9-17 2012-9-27
2012-9-27 2012-10-7
2012-10-7 2012-10-17
2012-10-17 2012-10-27
2012-10-27 2012-11-6
2012-11-6 2012-11-16
2012-11-16 2012-11-26
2012-11-26 2012-12-6
2012-12-6 2012-12-16
2012-12-16 2012-12-26
2012-12-26 2013-1-5
2013-1-5 2013-1-15
2013-1-15 2013-1-25
2013-1-25 2013-2-4
2013-2-4 2013-2-14
2013-2-14 2013-2-24
2013-2-24 2013-3-6
2013-3-6 2013-3-16
2013-3-16 2013-3-26
2013-3-26 2013-4-5
2013-4-5 2013-4-15
2013-4-15 2013-4-25
2013-4-25 2013-5-5
2013-5-5 2013-5-15
2013-5-15 2013-5-25
2013-5-25 2013-6-4
2013-6-4 2013-6-14
2013-6-14 2013-6-24
2013-6-24 2013-7-4
2013-7-4 2013-7-14
2013-7-14 2013-7-24
2013-7-24 2013-8-3
2013-8-3 2013-8-13
2013-8-13 2013-8-23
2013-8-23 2013-9-2
2013-9-2 2013-9-12
2013-9-12 2013-9-22
2013-9-22 2013-10-2
2013-10-2 2013-10-12
2013-10-12 2013-10-22
2013-10-22 2013-11-1
2013-11-1 2013-11-11
2013-11-11 2013-11-21
2013-11-21 2013-12-1
2013-12-1 2013-12-11
2013-12-11 2013-12-21
2013-12-21 2013-12-31
2013-12-31 2014-1-10
2014-1-10 2014-1-20
2014-1-20 2014-1-30
2014-1-30 2014-2-9
2014-2-9 2014-2-19
2014-2-19 2014-3-1
2014-3-1 2014-3-11
2014-3-11 2014-3-21
2014-3-21 2014-3-31
2014-3-31 2014-4-10
2014-4-10 2014-4-20
2014-4-20 2014-4-30
2014-4-30 2014-5-10
2014-5-10 2014-5-20
2014-5-20 2014-5-30
2014-5-30 2014-6-9
2014-6-9 2014-6-19
2014-6-19 2014-6-29
2014-6-29 2014-7-9
2014-7-9 2014-7-19
2014-7-19 2014-7-29
2014-7-29 2014-8-8
2014-8-8 2014-8-18
2014-8-18 2014-8-28
2014-8-28 2014-9-7
2014-9-7 2014-9-17
2014-9-17 2014-9-27
2014-9-27 2014-10-7
2014-10-7 2014-10-17
2014-10-17 2014-10-27
2014-10-27 2014-11-6
2014-11-6 2014-11-16
2014-11-16 2014-11-26
2014-11-26 2014-12-6
2014-12-6 2014-12-16
2014-12-16 2014-12-26
2014-12-26 2015-1-5
2015-1-5 2015-1-15
2015-1-15 2015-1-25
2015-1-25 2015-2-4
2015-2-4 2015-2-14
2015-2-14 2015-2-24
2015-2-24 2015-3-6
2015-3-6 2015-3-16
2015-3-16 2015-3-26
2015-3-26 2015-4-5
2015-4-5 2015-4-15
2015-4-15 2015-4-25
2015-4-25 2015-5-5
2015-5-5 2015-5-15
2015-5-15 2015-5-25
2015-5-25 2015-6-4
2015-6-4 2015-6-14
2015-6-14 2015-6-24
2015-6-24 2015-7-4
2015-7-4 2015-7-14
2015-7-14 2015-7-24
2015-7-24 2015-8-3
2015-8-3 2015-8-13
2015-8-13 2015-8-23
2015-8-23 2015-9-2
2015-9-2 2015-9-12
2015-9-12 2015-9-22
2015-9-22 2015-10-2
2015-10-2 2015-10-12
2015-10-12 2015-10-22
2015-10-22 2015-11-1
2015-11-1 2015-11-11
2015-11-11 2015-11-21
2015-11-21 2015-12-1
2015-12-1 2015-12-11
2015-12-11 2015-12-21
2015-12-21 2015-12-31
2015-12-31 2016-1-10
2016-1-10 2016-1-20
2016-1-20 2016-1-30
2016-1-30 2016-2-9
2016-2-9 2016-2-19
2016-2-19 2016-2-29
2016-2-29 2016-3-10
2016-3-10 2016-3-20
2016-3-20 2016-3-30
2016-3-30 2016-4-9
2016-4-9 2016-4-19
2016-4-19 2016-4-29
2016-4-29 2016-5-9
2016-5-9 2016-5-19
2016-5-19 2016-5-29
2016-5-29 2016-6-8
2016-6-8 2016-6-18
2016-6-18 2016-6-28
2016-6-28 2016-7-8
2016-7-8 2016-7-18
2016-7-18 2016-7-28
2016-7-28 2016-8-7
2016-8-7 2016-8-17
2016-8-17 2016-8-27
2016-8-27 2016-9-6
2016-9-6 2016-9-16
2016-9-16 2016-9-26
2016-9-26 2016-10-6
2016-10-6 2016-10-16
2016-10-16 2016-10-26
2016-10-26 2016-11-5
2016-11-5 2016-11-15
2016-11-15 2016-11-25
2016-11-25 2016-12-5
2016-12-5 2016-12-15
2016-12-15 2016-12-25
2016-12-25 2017-1-4
2017-1-4 2017-1-14
2017-1-14 2017-1-24
2017-1-24 2017-2-3
2017-2-3 2017-2-13
2017-2-13 2017-2-23
2017-2-23 2017-3-5
2017-3-5 2017-3-15
2017-3-15 2017-3-25
2017-3-25 2017-4-4
2017-4-4 2017-4-14
2017-4-14 2017-4-24
2017-4-24 2017-5-4
2017-5-4 2017-5-14
2017-5-14 2017-5-24
2017-5-24 2017-6-3
2017-6-3 2017-6-13
2017-6-13 2017-6-23
2017-6-23 2017-7-3
2017-7-3 2017-7-13
2017-7-13 2017-7-23
2017-7-23 2017-8-2
2017-8-2 2017-8-12
2017-8-12 2017-8-22
2017-8-22 2017-9-1
2017-9-1 2017-9-11
2017-9-11 2017-9-21
2017-9-21 2017-10-1
2017-10-1 2017-10-11
2017-10-11 2017-10-21
2017-10-21 2017-10-31
2017-10-31 2017-11-10
2017-11-10 2017-11-20
2017-11-20 2017-11-30
2017-11-30 2017-12-10
2017-12-10 2017-12-20
2017-12-20 2017-12-30
2017-12-30 2018-1-9
2018-1-9 2018-1-19
2018-1-19 2018-1-29
2018-1-29 2018-2-8
2018-2-8 2018-2-18
2018-2-18 2018-2-28
2018-2-28 2018-3-10
2018-3-10 2018-3-20
2018-3-20 2018-3-30
2018-3-30 2018-4-9
2018-4-9 2018-4-19
2018-4-19 2018-4-29
2018-4-29 2018-5-9
2018-5-9 2018-5-19
2018-5-19 2018-5-29
2018-5-29 2018-6-8
2018-6-8 2018-6-18
2018-6-18 2018-6-28
2018-6-28 2018-7-8
2018-7-8 2018-7-18
2018-7-18 2018-7-28
2018-7-28 2018-8-7
2018-8-7 2018-8-17
2018-8-17 2018-8-27
2018-8-27 2018-9-6
2018-9-6 2018-9-16
2018-9-16 2018-9-26
2018-9-26 2018-10-6
2018-10-6 2018-10-16
2018-10-16 2018-10-26
2018-10-26 2018-11-5

In [ ]: