#In PAWS
from sqlalchemy import create_engine
import sys, os
import pandas as pd
constr = 'mysql+pymysql://{user}:{pwd}@{host}'.format(user=os.environ['MYSQL_USERNAME'],pwd=os.environ['MYSQL_PASSWORD'],host=os.environ['MYSQL_HOST'])
con = create_engine(constr)
q_en_uk = """
SELECT  
    uk.ips_site_page as uk_page_title
    ,count(*)-1 as uk_translations_count
FROM 
    wikidatawiki_p.wb_items_per_site as uk
join wikidatawiki_p.wb_items_per_site as any_lang
    on uk.ips_item_id = any_lang.ips_item_id
    and uk.ips_site_id= 'ukwiki'
    and any_lang.ips_site_id like '__wiki'
group by
    any_lang.ips_item_id
    ,uk.ips_site_page
"""
d_en_uk = pd.read_sql(q_en_uk, con)
d_en_uk['uk_page_title'] =d_en_uk['uk_page_title'].str.decode('utf-8')
d_en_uk.to_csv("uk_translations.csv")