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