| 193 |
#reader = csv.reader(open('/root/UserGroupData.tsv'), delimiter="\t" )
|
193 |
#reader = csv.reader(open('/root/UserGroupData.tsv'), delimiter="\t" )
|
| 195 |
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
|
195 |
db = MySQLdb.connect('localhost',"root","shop2020","dtr" )
|
| 198 |
sql = "select GROUP_CONCAT( distinct x.user_ids ) user_ids, x.mobileNos, GROUP_CONCAT( distinct x.imeis) imeis, GROUP_CONCAT(distinct x.referrers) referrers from (SELECT GROUP_CONCAT( distinct u.id ) user_ids, GROUP_CONCAT(distinct u.mobile_number) mobileNos, d.imeinumber imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is not null and u.mobile_number is not null GROUP BY d.imeinumber) as x group by x.mobileNos UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is not null GROUP BY u.mobile_number UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is null and LOWER(u.referrer) not like 'emp%' and LOWER(u.referrer) not like 'crm%' and LOWER(u.referrer) not like 'fos%' GROUP BY u.referrer"
|
208 |
sql = "select GROUP_CONCAT( distinct x.user_ids ) user_ids, x.mobileNos, GROUP_CONCAT( distinct x.imeis) imeis, GROUP_CONCAT(distinct x.referrers) referrers from (SELECT GROUP_CONCAT( distinct u.id ) user_ids, GROUP_CONCAT(distinct u.mobile_number) mobileNos, d.imeinumber imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is not null and u.mobile_number is not null GROUP BY d.imeinumber) as x group by x.mobileNos UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is not null GROUP BY u.mobile_number UNION SELECT GROUP_CONCAT( distinct u.id ) user_ids, u.mobile_number mobileNos, GROUP_CONCAT( distinct d.imeinumber) imeis, GROUP_CONCAT(distinct u.referrer) referrers FROM `users` u LEFT JOIN devices d ON u.id = d.user_id where d.imeinumber is null and u.mobile_number is null and LOWER(u.referrer) not like 'emp%' and LOWER(u.referrer) not like 'crm%' and LOWER(u.referrer) not like 'fos%' GROUP BY u.referrer"
|
| - |
|
215 |
sql = "insert into usergroup (user_ids, mobileNos, imeis, referrers) values('%s', '%s', '%s', '%s')"%(record_data[0], record_data[1], record_data[2], record_data[3])
|