mysql_pandas.py

mysql_pandas.py

#!/usr/bin/python
import MySQLdb
import pandas as pd


db = MySQLdb.connect(host= ENJOY_CONFIG['host'],
user=ENJOY_CONFIG['user'],  
                     passwd=ENJOY_CONFIG['password'], 
db=ENJOY_CONFIG['database']
                     )


sqlcode="""





select distinct ep.product_id
    , short_name as product_name
    , merchant.name as merchant_name, ep.merchant_id as merchant_id
    , base_city.city_name as city
    , bd_user_name as bd_name
    , bd_user_info.bd_user_id
    , bd_product.status
    , bd_user_info.user_type  
from enjoy_product ep

left join(
    select source_id,
             count(destination_id) cat ,
             min(destination_id) city_channel
      from edge_product_channel
      group by source_id 
      having count(destination_id) < 2
    ) epc 
         on epc.source_id = ep.product_id

left join enjoy_product_base_info ebinfo
        on ebinfo.product_id = ep.product_id

left join merchant 
    on merchant.merchant_id = ep.merchant_id
        and merchant.state=1

left join base_city 
    on base_city.city_id= epc.city_channel

left join bd_product 
    on bd_product.product_id=ep.product_id
        and bd_product.status = 1

left join bd_user_info 
    on bd_product.bd_user_id=bd_user_info.bd_user_id

    
where bd_user_info.user_type=1
    and city_channel = 140
    and bd_user_name = '洪辰'
        
 
order by bd_user_name, ep.product_id



"""
df = pd.read_sql(sqlcode, con=db)  


df.head()

db.close()