def uploadUserAndPhoneInfo(item):
if not 'phone' in item or not 'address' in item:
return False
#pause = raw_input("q2312")
phone_list = item['phone'].split(";")
uid = None
for phone in phone_list:
res2 = database_execution.queryOne(
"SELECT uid FROM phone_info WHERE phone = %s", [phone])
if res2:
uid = int(res2['uid'])
break
if not uid: #uid does not exists
flag = False
for phone in phone_list:
res4 = database_execution.execute(
"SELECT * FROM register_info WHERE phone = %s", [phone])
if res4:
flag = True
break
# if the user has registered, we do not need to add his or her phone number into phone_info
res5 = database_execution.executeAndGetId(
"INSERT INTO user_info VALUES ( NULL, %s, %s )",
[str(item['address']), flag])
uid = int(res5) #new uid
if flag:
return True
for phone in phone_list:
if not database_execution.execute(
"INSERT INTO phone_info VALUES (NULL, %s, %s, NULL)",
[uid, phone]):
#pause = raw_input("adsawd")
return False
return True
def uploadUserAndPhoneInfo(item):
if not 'phone' in item or not 'address' in item:
return False
#pause = raw_input("q2312")
phone_list = item['phone'].split(";")
uid = None
for phone in phone_list:
res2 = database_execution.queryOne("SELECT uid FROM phone_info WHERE phone = %s", [phone])
if res2:
uid = int(res2['uid'])
break
if not uid: #uid does not exists
flag = False
for phone in phone_list:
res4 = database_execution.execute("SELECT * FROM register_info WHERE phone = %s", [phone])
if res4:
flag = True
break
# if the user has registered, we do not need to add his or her phone number into phone_info
res5 = database_execution.executeAndGetId("INSERT INTO user_info VALUES ( NULL, %s, %s )", [str(item['address']), flag])
uid = int(res5) #new uid
if flag:
return True
for phone in phone_list:
if not database_execution.execute("INSERT INTO phone_info VALUES (NULL, %s, %s, NULL)", [uid, phone]):
#pause = raw_input("adsawd")
return False
return True
def CrawlAndUploadUserAndPhoneInfo(item):
if not 'date' in item or not 'phone' in item or not 'address' in item:
return False
phone = ""
address = ""
for element in item['phone']:
phone += str(element) + ";"
phone = phone[:-1]
for element in item['address']:
address += str(element) + ";"
address = address[:-1]
date = formatDate(str(item['date'][0]))
res = database_execution.execute(
"SELECT * FROM crawled_info WHERE phone = %s", [phone])
if res:
return False
else:
if not database_execution.execute(
"INSERT INTO crawled_info VALUES ( NULL, %s, %s, %s )",
[phone, address, date]):
#print "hello"
#pause = raw_input("PAUSE")
return False
item['phone'] = phone
item['address'] = address
return uploadUserAndPhoneInfo(item)
def uploadCrawledInfo (item):
if not 'date' in item or not 'phone' in item or not 'address' in item:
return False
phone = ""
address = ""
for element in item['phone']:
phone += str(element) + ";"
phone = phone[:-1]
for element in item['address']:
address += str(element) + ";"
address = address[:-1]
date = formatDate(str(item['date'][0]))
res = database_execution.execute("SELECT * FROM crawled_info WHERE phone = %s", [phone])
if res:
return False
else:
if not database_execution.execute("INSERT INTO crawled_info VALUES ( NULL, %s, %s, %s )", [phone, address, date]):
return False
return True
def createRegisterInfoTable ():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'register_info'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res:',res
if not res['count(*)']:
database_execution.execute ("""
CREATE TABLE register_info (
id INT NOT NULL auto_increment,
phone CHAR(10) NOT NULL,
PRIMARY KEY (`id`)
)
""")
print "a new table register_info is created!"
return True
else:
print "table register_info exists"
return False
def createRegisterInfoTable():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'register_info'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res:',res
if not res['count(*)']:
database_execution.execute("""
CREATE TABLE register_info (
id INT NOT NULL auto_increment,
phone CHAR(10) NOT NULL,
PRIMARY KEY (`id`)
)
""")
print "a new table register_info is created!"
return True
else:
print "table register_info exists"
return False
def createMessengerTable ():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'messenger'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res:',res
if not res['count(*)']:
database_execution.execute ("""
CREATE TABLE messenger (
uid INT NOT NULL,
date DATE NULL,
PRIMARY KEY (`uid`),
FOREIGN KEY(`uid`) REFERENCES user_info(`uid`)
)
""")
print "a new table messenger is created!"
return True
else:
print "table messenger exists"
return False
def createUserInfoTable ():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'user_info'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res=', res
if not res['count(*)']:
database_execution.execute ("""
CREATE TABLE user_info (
uid INT NOT NULL auto_increment,
address VARCHAR(1000) NOT NULL,
flag BOOL NOT NULL,
PRIMARY KEY (`uid`)
);
""")
print "a new table user_info is created!"
return True
else:
print "table user_info exists"
return False
def createMessengerTable():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'messenger'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res:',res
if not res['count(*)']:
database_execution.execute("""
CREATE TABLE messenger (
uid INT NOT NULL,
date DATE NULL,
PRIMARY KEY (`uid`),
FOREIGN KEY(`uid`) REFERENCES user_info(`uid`)
)
""")
print "a new table messenger is created!"
return True
else:
print "table messenger exists"
return False
def createUserInfoTable():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'user_info'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res=', res
if not res['count(*)']:
database_execution.execute("""
CREATE TABLE user_info (
uid INT NOT NULL auto_increment,
address VARCHAR(1000) NOT NULL,
flag BOOL NOT NULL,
PRIMARY KEY (`uid`)
);
""")
print "a new table user_info is created!"
return True
else:
print "table user_info exists"
return False
def uploadCrawledInfo(item):
if not 'date' in item or not 'phone' in item or not 'address' in item:
return False
phone = ""
address = ""
for element in item['phone']:
phone += str(element) + ";"
phone = phone[:-1]
for element in item['address']:
address += str(element) + ";"
address = address[:-1]
date = formatDate(str(item['date'][0]))
res = database_execution.execute(
"SELECT * FROM crawled_info WHERE phone = %s", [phone])
if res:
return False
else:
if not database_execution.execute(
"INSERT INTO crawled_info VALUES ( NULL, %s, %s, %s )",
[phone, address, date]):
return False
return True
def createPhoneInfoTable ():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'phone_info'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res=', res
if not res['count(*)']:
database_execution.execute ("""
CREATE TABLE phone_info (
id INT NOT NULL auto_increment,
uid INT NOT NULL,
phone CHAR(10) NOT NULL,
carrier VARCHAR(20) NULL,
PRIMARY KEY (`id`, `phone`),
FOREIGN KEY(`uid`) REFERENCES user_info(`uid`)
);
""")
print "a new table phone_info is created!"
return True
else:
print "table phone_info exists"
return False
def CrawlAndUploadUserAndPhoneInfo (item):
if not 'date' in item or not 'phone' in item or not 'address' in item:
return False
phone = ""
address = ""
for element in item['phone']:
phone += str(element) + ";"
phone = phone[:-1]
for element in item['address']:
address += str(element) + ";"
address = address[:-1]
date = formatDate(str(item['date'][0]))
res = database_execution.execute("SELECT * FROM crawled_info WHERE phone = %s", [phone])
if res:
return False
else:
if not database_execution.execute("INSERT INTO crawled_info VALUES ( NULL, %s, %s, %s )", [phone, address, date]):
#print "hello"
#pause = raw_input("PAUSE")
return False
item['phone'] = phone
item['address'] = address
return uploadUserAndPhoneInfo(item)
def createPhoneInfoTable():
res = database_execution.queryOne("""
SELECT count(*)
FROM information_schema.TABLES
WHERE table_name = 'phone_info'
AND TABLE_SCHEMA = 'movingen_lead_info'
""")
# print 'res=', res
if not res['count(*)']:
database_execution.execute("""
CREATE TABLE phone_info (
id INT NOT NULL auto_increment,
uid INT NOT NULL,
phone CHAR(10) NOT NULL,
carrier VARCHAR(20) NULL,
PRIMARY KEY (`id`, `phone`),
FOREIGN KEY(`uid`) REFERENCES user_info(`uid`)
);
""")
print "a new table phone_info is created!"
return True
else:
print "table phone_info exists"
return False
def dropAllTable ():
database_execution.execute("DROP TABLE IF EXISTS crawled_info")
database_execution.execute("DROP TABLE IF EXISTS user_info")
database_execution.execute("DROP TABLE IF EXISTS phone_info")
database_execution.execute("DROP TABLE IF EXISTS messenger")
database_execution.execute("DROP TABLE IF EXISTS register_info")
def dropAllTable():
database_execution.execute("DROP TABLE IF EXISTS crawled_info")
database_execution.execute("DROP TABLE IF EXISTS user_info")
database_execution.execute("DROP TABLE IF EXISTS phone_info")
database_execution.execute("DROP TABLE IF EXISTS messenger")
database_execution.execute("DROP TABLE IF EXISTS register_info")
