from pycen import con_gn as con
import pandas as pd
from sqlalchemy import create_engine

user   = 'cgeier'
pwd    = 'adm1n*bdCen'
adr    = '91.134.194.221'
base   = 'cadastre'
con = create_engine('postgresql+psycopg2://{0}:{1}@{2}/{3}'.format(user,pwd,adr,base), echo=False)

lst_sch = con.dialect.get_schema_names(con)

for s in lst_sch:
    lst_tab = con.dialect.get_table_names(con,s)
    for t in lst_tab:
        lst_col = con.dialect.get_columns(con,t,s)
        lst_col = [x['name'] for x in lst_col ]
        if 'dnulot' in lst_col:
            sql = '''SELECT * FROM "%s".%s WHERE TRIM(dnulot) <> '' LIMIT 5;'''%(s,t)
            res = pd.read_sql_query(sql = sql, con=con)
            if not res.empty:
                print('Schema : %s ; Table : %s ; nrows : %s'%(s,t,str(res.shape[0])))


# before
obs_op = 880
veille = 1202

# after
obs_op = 2082
veille = 0