def main():
if connectDB == True:
SQL.connMySQL() # Connect to MySQL
# Load MySQL resultset into Pandas dataframe
if load2Pandas == True:
sql = 'select * from tmp'
df = SQL.MySQL2Pandas(sql)
# Report: decisions por year
if decsPerYear == True:
grouped = df.groupby(
df['dat_criacao'].map(lambda x: x.year)).id_ta_main.nunique()
print grouped
# Report: decisions por class
if decsPerClass == True:
grouped = df.groupby('sig_classe_proces').id_ta_main.nunique()
print grouped
# Report: decisions por year per class
if decsPerYearClass == True:
grouped = df.groupby(
[df['dat_criacao'].map(lambda x: x.year),
'sig_classe_proces']).id_ta_main.nunique()
print grouped
# Report: decisions por justice
if decsPerJustice == True:
grouped = df.groupby('nom_ministro').id_ta_main.nunique()
print grouped
if decsPerJusticeClass == True:
grouped = df.groupby(['nom_ministro',
'sig_classe_proces']).id_ta_main.nunique()
print grouped
if decsPerJusticeYear == True:
grouped = df.groupby(
['nom_ministro',
df['dat_criacao'].map(lambda x: x.year)]).id_ta_main.nunique()
print grouped
if decsPerYearJustice == True:
grouped = df.groupby(
[df['dat_criacao'].map(lambda x: x.year),
'nom_ministro']).id_ta_main.nunique()
print grouped
if topWords == True:
grouped = df.groupby(['word']).id_ta_main.nunique()
print grouped.order()
def main():
if connectDB == True:
SQL.connMySQL() # Connect to MySQL
# Load MySQL resultset into Pandas dataframe
if load2Pandas == True:
sql = 'select * from tmp'
df = SQL.MySQL2Pandas(sql)
# Report: decisions por year
if decsPerYear == True:
grouped = df.groupby(df['dat_criacao'].map(lambda x: x.year)).id_ta_main.nunique()
print grouped
# Report: decisions por class
if decsPerClass == True:
grouped = df.groupby('sig_classe_proces').id_ta_main.nunique()
print grouped
# Report: decisions por year per class
if decsPerYearClass == True:
grouped = df.groupby([df['dat_criacao'].map(lambda x: x.year),'sig_classe_proces']).id_ta_main.nunique()
print grouped
# Report: decisions por justice
if decsPerJustice == True:
grouped = df.groupby('nom_ministro').id_ta_main.nunique()
print grouped
if decsPerJusticeClass == True:
grouped = df.groupby(['nom_ministro','sig_classe_proces']).id_ta_main.nunique()
print grouped
if decsPerJusticeYear == True:
grouped = df.groupby(['nom_ministro',df['dat_criacao'].map(lambda x: x.year)]).id_ta_main.nunique()
print grouped
if decsPerYearJustice == True:
grouped = df.groupby([df['dat_criacao'].map(lambda x: x.year),'nom_ministro']).id_ta_main.nunique()
print grouped
if topWords == True:
grouped = df.groupby(['word']).id_ta_main.nunique()
print grouped.order()
def main():
if connectDB == True:
SQL.connMySQL() # Connect to MySQL
# Report: Total decisions decisions
if totalDecs == True:
SQL.dropTable('rep_total_decs')
if st.debug >= 1: print 'Creating rep_total_decs'
sql = '''
create table rep_total_decs
select count(*) total_decs from {0}
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs created'
if st.debug >= 2:
SQL.getAll('rep_total_decs')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print 'Total decs: {0}'.format(row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs')
ut.exportTable('rep_total_decs')
print 'Table rep_total_decs exported to disk'
#############################
### Simple specs reports ###
#############################
# Report: Total decisions per Justice
if totalDecsPerJustice == True:
SQL.dropTable('rep_total_decs_justice')
if st.debug >= 1: print 'Creating rep_total_decs_justice'
sql = '''
create table rep_total_decs_justice
select nom_ministro, count(nom_ministro) total_decs
from {0}
group by nom_ministro
order by count(nom_ministro) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_justice created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_justice')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}'.format(row['nom_ministro'], row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_justice')
ut.exportTable('rep_total_decs_justice')
print 'Table rep_total_decs_justice exported to disk'
# Report: Total decisions per Class
if totalDecsPerClass == True:
SQL.dropTable('rep_total_decs_class')
if st.debug >= 1: print 'Creating rep_total_decs_class'
sql = '''
create table rep_total_decs_class
select sig_classe_proces, count(sig_classe_proces) total_decs
from {0}
group by sig_classe_proces
order by count(sig_classe_proces) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_class created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_class')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}'.format(row['sig_classe_proces'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_class')
ut.exportTable('rep_total_decs_class')
print 'Table rep_total_decs_class exported to disk'
# Report: Total decisions per judgement type
if totalDecsPerJudType == True:
SQL.dropTable('rep_total_decs_judtype')
if st.debug >= 1: print 'Creating rep_total_decs_judtype'
sql = '''
create table rep_total_decs_judtype
select tip_julgamento, count(tip_julgamento) total_decs
from {0}
group by tip_julgamento
order by count(tip_julgamento) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_judtype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_judtype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}'.format(row['tip_julgamento'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_judtype')
ut.exportTable('rep_total_decs_judtype')
print 'Table rep_total_decs_judtype exported to disk'
# Report: Total decisions per decision type
if totalDecsPerDecType == True:
SQL.dropTable('rep_total_decs_dectype')
if st.debug >= 1: print 'Creating rep_total_decs_dectype'
sql = '''
create table rep_total_decs_dectype
select dsc_tipo, count(dsc_tipo) total_decs
from {0}
group by dsc_tipo
order by count(dsc_tipo) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_dectype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_dectype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}'.format(row['dsc_tipo'], row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_dectype')
ut.exportTable('rep_total_decs_dectype')
print 'Table rep_total_decs_dectype exported to disk'
#############################
### Complex specs reports ###
#############################
# Report: Total decisions per Justice and Class
if totalDecsPerJustClass == True:
SQL.dropTable('rep_total_decs_justice_class')
if st.debug >= 1: print 'Creating rep_total_decs_justice_class'
sql = '''
create table rep_total_decs_justice_class
select nom_ministro, sig_classe_proces, count(sig_classe_proces) total_decs
from {0}
group by nom_ministro, sig_classe_proces
order by nom_ministro, sig_classe_proces desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_justice_class created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_justice_class')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}\t{2}'.format(row['nom_ministro'],
row['sig_classe_proces'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_justice_class')
ut.exportTable('rep_total_decs_justice_class')
print 'Table rep_total_decs_justice_class exported to disk'
# Report: Total decisions per Class and Justice
if totalDecsPerJustClass == True:
SQL.dropTable('rep_total_decs_class_justice')
if st.debug >= 1: print 'Creating rep_total_decs_class_justice'
sql = '''
create table rep_total_decs_class_justice
select sig_classe_proces, nom_ministro, count(nom_ministro) total_decs
from {0}
group by sig_classe_proces, nom_ministro
order by sig_classe_proces, nom_ministro desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_class_justice created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_class_justice')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}\t{2}'.format(row['sig_classe_proces'],
row['nom_ministro'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_class_justice')
ut.exportTable('rep_total_decs_class_justice')
print 'Table rep_total_decs_class_justice exported to disk'
# Report: Total decisions per Justice and Decision Type
if totalDecsPerJustDecType == True:
SQL.dropTable('rep_total_decs_justice_dectype')
if st.debug >= 1: print 'Creating rep_total_decs_justice_dectype'
sql = '''
create table rep_total_decs_justice_dectype
select nom_ministro, dsc_tipo, count(dsc_tipo) total_decs
from {0}
group by nom_ministro, dsc_tipo
order by nom_ministro, dsc_tipo desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_justice_dectype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_justice_dectype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}\t{2}'.format(row['nom_ministro'],
row['dsc_tipo'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_justice_dectype')
ut.exportTable('rep_total_decs_justice_dectype')
print 'Table rep_total_decs_justice_dectype exported to disk'
# Report: Total decisions per Decision Type and Justice
if totalDecsPerDecTypeJust == True:
SQL.dropTable('rep_total_decs_dectype_justice')
if st.debug >= 1: print 'Creating rep_total_decs_dectype_justice'
sql = '''
create table rep_total_decs_dectype_justice
select dsc_tipo, nom_ministro, count(nom_ministro) total_decs
from {0}
group by dsc_tipo, nom_ministro
order by dsc_tipo, nom_ministro desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_dectype_justice created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_dectype_justice')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}\t{2}'.format(row['dsc_tipo'],
row['nom_ministro'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_dectype_justice')
ut.exportTable('rep_total_decs_dectype_justice')
print 'Table rep_total_decs_dectype_justice exported to disk'
# Report: Total decisions per Class and Decision Type
if totalDecsPerClassDecT == True:
SQL.dropTable('rep_total_decs_class_dectype')
if st.debug >= 1: print 'Creating rep_total_decs_class_dectype'
sql = '''
create table rep_total_decs_class_dectype
select sig_classe_proces, dsc_tipo, count(dsc_tipo) total_decs
from {0}
group by sig_classe_proces, dsc_tipo
order by sig_classe_proces, dsc_tipo desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_class_dectype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_class_dectype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}\t{2}'.format(row['sig_classe_proces'],
row['dsc_tipo'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_class_dectype')
ut.exportTable('rep_total_decs_class_dectype')
print 'Table rep_total_decs_class_dectype exported to disk'
# Report: Total decisions per Decision Type and Class
if totalDecsPerDecTClass == True:
SQL.dropTable('rep_total_decs_dectype_class')
if st.debug >= 1: print 'Creating rep_total_decs_dectype_class'
sql = '''
create table rep_total_decs_dectype_class
select dsc_tipo, sig_classe_proces, count(sig_classe_proces) total_decs
from {0}
group by dsc_tipo, sig_classe_proces
order by dsc_tipo, sig_classe_proces desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_dectype_class created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_dectype_class')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}\t{2}'.format(row['dsc_tipo'],
row['sig_classe_proces'],
row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_dectype_class')
ut.exportTable('rep_total_decs_dectype_class')
print 'Table rep_total_decs_dectype_class exported to disk'
#############################
### Time reports ###
#############################
# Report: Total decisions per case year
if totalDecsPerCaseYear == True:
SQL.dropTable('rep_total_decs_case_year')
if st.debug >= 1: print 'Creating rep_total_decs_case_year'
sql = '''
create table rep_total_decs_case_year
select year(dat_autuacao) case_year, count(id) total_decs
from {0}
group by year(dat_autuacao)
order by year(dat_autuacao)
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_case_year created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_case_year')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}'.format(row['case_year'], row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_case_year')
ut.exportTable('rep_total_decs_case_year')
print 'Table rep_total_decs_case_year exported to disk'
# Report: Total decisions per year
if totalDecsPerYear == True:
SQL.dropTable('rep_total_decs_year')
if st.debug >= 1: print 'Creating rep_total_decs_year'
sql = '''
create table rep_total_decs_year
select year(dat_criacao) dec_year, count(id) total_decs
from {0}
group by year(dat_criacao)
order by year(dat_criacao)
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_year created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_year')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows:
print '{0}\t{1}'.format(row['dec_year'], row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_year')
ut.exportTable('rep_total_decs_year')
print 'Table rep_total_decs_year exported to disk'
def main():
if connectDB == True:
SQL.connMySQL() # Connect to MySQL
if dropTables == True:
SQL.dropTable(st.ta_main) # Drop table ta_main
SQL.dropTable(st.ta_words_all) # Drop table ta_words_all
if createTables == True:
# Create ta_main table
SQL.createTableFrom(st.ta_main,st.ta_main_source) # Create table ta_main based on monocraticas
SQL.addPK(st.ta_main) # Add primary key to ta_main
# Create ta_word_all
SQL.createTable(st.ta_words_all,st.ta_words_all_columns)
if createIndex == True:
# Index ta_main
for column in st.ta_main_idx:
SQL.addIndex(st.ta_main,column) # Indexing columns on ta_main
# Index ta_word_all
for column in st.ta_words_all_idx:
SQL.addIndex(st.ta_words_all,column) # Indexing columns on ta_word_all
if populateTable == True:
# Populate ta_main
SQL.populateTableFrom(st.ta_main,st.ta_main_source)
# Add hashes to ta_main
if addHash == True:
# Get data from ta_main and save it to rows
# Used to create hashes and to convert RTF to txt
SQL.getAll(st.ta_main,'where txt_conteudo is not null')
SQL.addHashes(st.ta_main,'txt_conteudo')
SQL.addIndex(st.ta_main,'hash_txt_conteudo')
if convertRTF2text == True:
if st.totalRows < 1:
SQL.getAll(st.ta_main,'where txt_conteudo is not null')
SQL.addPlain(st.ta_main,'txt_conteudo','txt_text')
if createWordFreq == True:
# Get data from ta_main and save it to rows
# Used to create word frequencies
SQL.getAll(st.ta_main,'where txt_text is not null')
for row in st.rows:
word_frequencies = ut.wordFrequence(row['txt_text'])
for word in word_frequencies:
if st.debug >= 2: print row['id'], word, word_frequencies[word]
sql = "insert into %s (id_monocratica,word,word_count) values (%s,'%s',%s)" % (st.ta_words_all, row['id'], word, word_frequencies[word])
try:
st.cur.execute(sql)
except Exception:
print sql
st.conn.commit()
if createViews == True:
#Create main view
sql = 'drop view vw_words_freq_main'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_words_freq_main'
sql = '''
create view vw_words_freq_main as
select
m.id id_ta_main, m.hash_txt_conteudo,
m.seq_objeto_incidente_principal,
m.sig_classe_proces,
m.num_processo,
m.dat_autuacao,
m.cod_recurso,
m.tip_julgamento,
m.seq_objeto_incidente,
m.cod_ministro,
m.nom_ministro,
m.dat_sessao,
m.dat_criacao,
m.cod_tipo_texto,
m.dsc_tipo,
wf.id id_wf,
wf.word,
wf.word_count
from
{0} m,
{1} wf
where
m.id = wf.id_monocratica
'''.format(st.ta_main,st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_words_freq_main'
#Create vw_words_freq_total_lines view
sql = 'drop view vw_words_freq_total_lines'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_words_freq_total_lines'
sql = '''
create view vw_words_freq_total_lines as
select max(id) total_lines
from {0};
'''.format(st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_words_freq_total_lines'
#Create vw_total_words view
sql = 'drop view vw_total_words'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_total_words'
sql = '''
create view vw_total_words as
select sum(word_count) total_words
from {0};
'''.format(st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_total_words'
#Create vw_total_distinct_words view
sql = 'drop view vw_total_distinct_words'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_total_distinct_words'
sql = '''
create view vw_total_distinct_words as
select count(distinct word) total_distinct_words
from {0};
'''.format(st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_total_distinct_words'
#Create vw_top_words view
sql = 'drop view vw_top_words'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_top_words'
sql = '''
create view vw_top_words as
select word, sum(word_count) word_count
from vw_words_freq_main
group by word
order by sum(word_count) desc;
'''
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_top_words'
#Create vw_words_per_decision view
sql = 'drop view vw_words_per_decision'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_words_per_decision'
sql = '''
create view vw_words_per_decision as
select
id_ta_main,
hash_txt_conteudo,
sig_classe_proces,
num_processo,
dat_autuacao,
tip_julgamento,
nom_ministro,
dat_criacao,
dsc_tipo,
sum(word_count) total_words
from
vw_words_freq_main
group by id_ta_main
order by sum(word_count) desc;
'''
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_words_per_decision'
#Create vw_distinct_words_per_decision view
sql = 'drop view vw_distinct_words_per_decision'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_distinct_words_per_decision'
sql = '''
create view vw_distinct_words_per_decision as
select
id_ta_main,
hash_txt_conteudo,
sig_classe_proces,
num_processo,
dat_autuacao,
tip_julgamento,
nom_ministro,
dat_criacao,
dsc_tipo,
count(distinct word) total_distinct_words
from
vw_words_freq_main
group by id_ta_main
order by count(distinct word) desc;
'''
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_distinct_words_per_decision'
def main():
if connectDB == True:
SQL.connMySQL() # Connect to MySQL
if dropTables == True:
SQL.dropTable(st.ta_main) # Drop table ta_main
SQL.dropTable(st.ta_words_all) # Drop table ta_words_all
if createTables == True:
# Create ta_main table
SQL.createTableFrom(
st.ta_main,
st.ta_main_source) # Create table ta_main based on monocraticas
SQL.addPK(st.ta_main) # Add primary key to ta_main
# Create ta_word_all
SQL.createTable(st.ta_words_all, st.ta_words_all_columns)
if createIndex == True:
# Index ta_main
for column in st.ta_main_idx:
SQL.addIndex(st.ta_main, column) # Indexing columns on ta_main
# Index ta_word_all
for column in st.ta_words_all_idx:
SQL.addIndex(st.ta_words_all,
column) # Indexing columns on ta_word_all
if populateTable == True:
# Populate ta_main
SQL.populateTableFrom(st.ta_main, st.ta_main_source)
# Add hashes to ta_main
if addHash == True:
# Get data from ta_main and save it to rows
# Used to create hashes and to convert RTF to txt
SQL.getAll(st.ta_main, 'where txt_conteudo is not null')
SQL.addHashes(st.ta_main, 'txt_conteudo')
SQL.addIndex(st.ta_main, 'hash_txt_conteudo')
if convertRTF2text == True:
if st.totalRows < 1:
SQL.getAll(st.ta_main, 'where txt_conteudo is not null')
SQL.addPlain(st.ta_main, 'txt_conteudo', 'txt_text')
if createWordFreq == True:
# Get data from ta_main and save it to rows
# Used to create word frequencies
SQL.getAll(st.ta_main, 'where txt_text is not null')
for row in st.rows:
word_frequencies = ut.wordFrequence(row['txt_text'])
for word in word_frequencies:
if st.debug >= 2: print row['id'], word, word_frequencies[word]
sql = "insert into %s (id_monocratica,word,word_count) values (%s,'%s',%s)" % (
st.ta_words_all, row['id'], word, word_frequencies[word])
try:
st.cur.execute(sql)
except Exception:
print sql
st.conn.commit()
if createViews == True:
#Create main view
sql = 'drop view vw_words_freq_main'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_words_freq_main'
sql = '''
create view vw_words_freq_main as
select
m.id id_ta_main, m.hash_txt_conteudo,
m.seq_objeto_incidente_principal,
m.sig_classe_proces,
m.num_processo,
m.dat_autuacao,
m.cod_recurso,
m.tip_julgamento,
m.seq_objeto_incidente,
m.cod_ministro,
m.nom_ministro,
m.dat_sessao,
m.dat_criacao,
m.cod_tipo_texto,
m.dsc_tipo,
wf.id id_wf,
wf.word,
wf.word_count
from
{0} m,
{1} wf
where
m.id = wf.id_monocratica
'''.format(st.ta_main, st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_words_freq_main'
#Create vw_words_freq_total_lines view
sql = 'drop view vw_words_freq_total_lines'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_words_freq_total_lines'
sql = '''
create view vw_words_freq_total_lines as
select max(id) total_lines
from {0};
'''.format(st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_words_freq_total_lines'
#Create vw_total_words view
sql = 'drop view vw_total_words'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_total_words'
sql = '''
create view vw_total_words as
select sum(word_count) total_words
from {0};
'''.format(st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_total_words'
#Create vw_total_distinct_words view
sql = 'drop view vw_total_distinct_words'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_total_distinct_words'
sql = '''
create view vw_total_distinct_words as
select count(distinct word) total_distinct_words
from {0};
'''.format(st.ta_words_all)
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_total_distinct_words'
#Create vw_top_words view
sql = 'drop view vw_top_words'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_top_words'
sql = '''
create view vw_top_words as
select word, sum(word_count) word_count
from vw_words_freq_main
group by word
order by sum(word_count) desc;
'''
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_top_words'
#Create vw_words_per_decision view
sql = 'drop view vw_words_per_decision'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_words_per_decision'
sql = '''
create view vw_words_per_decision as
select
id_ta_main,
hash_txt_conteudo,
sig_classe_proces,
num_processo,
dat_autuacao,
tip_julgamento,
nom_ministro,
dat_criacao,
dsc_tipo,
sum(word_count) total_words
from
vw_words_freq_main
group by id_ta_main
order by sum(word_count) desc;
'''
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_words_per_decision'
#Create vw_distinct_words_per_decision view
sql = 'drop view vw_distinct_words_per_decision'
try:
st.cur.execute(sql)
except Exception:
pass
if st.debug >= 1: print 'Creating view vw_distinct_words_per_decision'
sql = '''
create view vw_distinct_words_per_decision as
select
id_ta_main,
hash_txt_conteudo,
sig_classe_proces,
num_processo,
dat_autuacao,
tip_julgamento,
nom_ministro,
dat_criacao,
dsc_tipo,
count(distinct word) total_distinct_words
from
vw_words_freq_main
group by id_ta_main
order by count(distinct word) desc;
'''
st.cur.execute(sql)
if st.debug >= 1: print 'Done: view vw_distinct_words_per_decision'
def main():
if connectDB == True:
SQL.connMySQL() # Connect to MySQL
# Report: Total decisions decisions
if totalDecs == True:
SQL.dropTable('rep_total_decs')
if st.debug >= 1: print 'Creating rep_total_decs'
sql = '''
create table rep_total_decs
select count(*) total_decs from {0}
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs created'
if st.debug >= 2:
SQL.getAll('rep_total_decs')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print 'Total decs: {0}'.format(row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs')
ut.exportTable('rep_total_decs')
print 'Table rep_total_decs exported to disk'
#############################
### Simple specs reports ###
#############################
# Report: Total decisions per Justice
if totalDecsPerJustice == True:
SQL.dropTable('rep_total_decs_justice')
if st.debug >= 1: print 'Creating rep_total_decs_justice'
sql = '''
create table rep_total_decs_justice
select nom_ministro, count(nom_ministro) total_decs
from {0}
group by nom_ministro
order by count(nom_ministro) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_justice created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_justice')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}'.format(row['nom_ministro'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_justice')
ut.exportTable('rep_total_decs_justice')
print 'Table rep_total_decs_justice exported to disk'
# Report: Total decisions per Class
if totalDecsPerClass == True:
SQL.dropTable('rep_total_decs_class')
if st.debug >= 1: print 'Creating rep_total_decs_class'
sql = '''
create table rep_total_decs_class
select sig_classe_proces, count(sig_classe_proces) total_decs
from {0}
group by sig_classe_proces
order by count(sig_classe_proces) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_class created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_class')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}'.format(row['sig_classe_proces'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_class')
ut.exportTable('rep_total_decs_class')
print 'Table rep_total_decs_class exported to disk'
# Report: Total decisions per judgement type
if totalDecsPerJudType == True:
SQL.dropTable('rep_total_decs_judtype')
if st.debug >= 1: print 'Creating rep_total_decs_judtype'
sql = '''
create table rep_total_decs_judtype
select tip_julgamento, count(tip_julgamento) total_decs
from {0}
group by tip_julgamento
order by count(tip_julgamento) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_judtype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_judtype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}'.format(row['tip_julgamento'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_judtype')
ut.exportTable('rep_total_decs_judtype')
print 'Table rep_total_decs_judtype exported to disk'
# Report: Total decisions per decision type
if totalDecsPerDecType == True:
SQL.dropTable('rep_total_decs_dectype')
if st.debug >= 1: print 'Creating rep_total_decs_dectype'
sql = '''
create table rep_total_decs_dectype
select dsc_tipo, count(dsc_tipo) total_decs
from {0}
group by dsc_tipo
order by count(dsc_tipo) desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_dectype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_dectype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}'.format(row['dsc_tipo'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_dectype')
ut.exportTable('rep_total_decs_dectype')
print 'Table rep_total_decs_dectype exported to disk'
#############################
### Complex specs reports ###
#############################
# Report: Total decisions per Justice and Class
if totalDecsPerJustClass == True:
SQL.dropTable('rep_total_decs_justice_class')
if st.debug >= 1: print 'Creating rep_total_decs_justice_class'
sql = '''
create table rep_total_decs_justice_class
select nom_ministro, sig_classe_proces, count(sig_classe_proces) total_decs
from {0}
group by nom_ministro, sig_classe_proces
order by nom_ministro, sig_classe_proces desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_justice_class created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_justice_class')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}\t{2}'.format(row['nom_ministro'],row['sig_classe_proces'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_justice_class')
ut.exportTable('rep_total_decs_justice_class')
print 'Table rep_total_decs_justice_class exported to disk'
# Report: Total decisions per Class and Justice
if totalDecsPerJustClass == True:
SQL.dropTable('rep_total_decs_class_justice')
if st.debug >= 1: print 'Creating rep_total_decs_class_justice'
sql = '''
create table rep_total_decs_class_justice
select sig_classe_proces, nom_ministro, count(nom_ministro) total_decs
from {0}
group by sig_classe_proces, nom_ministro
order by sig_classe_proces, nom_ministro desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_class_justice created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_class_justice')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}\t{2}'.format(row['sig_classe_proces'],row['nom_ministro'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_class_justice')
ut.exportTable('rep_total_decs_class_justice')
print 'Table rep_total_decs_class_justice exported to disk'
# Report: Total decisions per Justice and Decision Type
if totalDecsPerJustDecType == True:
SQL.dropTable('rep_total_decs_justice_dectype')
if st.debug >= 1: print 'Creating rep_total_decs_justice_dectype'
sql = '''
create table rep_total_decs_justice_dectype
select nom_ministro, dsc_tipo, count(dsc_tipo) total_decs
from {0}
group by nom_ministro, dsc_tipo
order by nom_ministro, dsc_tipo desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_justice_dectype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_justice_dectype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}\t{2}'.format(row['nom_ministro'],row['dsc_tipo'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_justice_dectype')
ut.exportTable('rep_total_decs_justice_dectype')
print 'Table rep_total_decs_justice_dectype exported to disk'
# Report: Total decisions per Decision Type and Justice
if totalDecsPerDecTypeJust == True:
SQL.dropTable('rep_total_decs_dectype_justice')
if st.debug >= 1: print 'Creating rep_total_decs_dectype_justice'
sql = '''
create table rep_total_decs_dectype_justice
select dsc_tipo, nom_ministro, count(nom_ministro) total_decs
from {0}
group by dsc_tipo, nom_ministro
order by dsc_tipo, nom_ministro desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_dectype_justice created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_dectype_justice')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}\t{2}'.format(row['dsc_tipo'],row['nom_ministro'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_dectype_justice')
ut.exportTable('rep_total_decs_dectype_justice')
print 'Table rep_total_decs_dectype_justice exported to disk'
# Report: Total decisions per Class and Decision Type
if totalDecsPerClassDecT == True:
SQL.dropTable('rep_total_decs_class_dectype')
if st.debug >= 1: print 'Creating rep_total_decs_class_dectype'
sql = '''
create table rep_total_decs_class_dectype
select sig_classe_proces, dsc_tipo, count(dsc_tipo) total_decs
from {0}
group by sig_classe_proces, dsc_tipo
order by sig_classe_proces, dsc_tipo desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_class_dectype created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_class_dectype')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}\t{2}'.format(row['sig_classe_proces'],row['dsc_tipo'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_class_dectype')
ut.exportTable('rep_total_decs_class_dectype')
print 'Table rep_total_decs_class_dectype exported to disk'
# Report: Total decisions per Decision Type and Class
if totalDecsPerDecTClass == True:
SQL.dropTable('rep_total_decs_dectype_class')
if st.debug >= 1: print 'Creating rep_total_decs_dectype_class'
sql = '''
create table rep_total_decs_dectype_class
select dsc_tipo, sig_classe_proces, count(sig_classe_proces) total_decs
from {0}
group by dsc_tipo, sig_classe_proces
order by dsc_tipo, sig_classe_proces desc
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_dectype_class created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_dectype_class')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}\t{2}'.format(row['dsc_tipo'],row['sig_classe_proces'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_dectype_class')
ut.exportTable('rep_total_decs_dectype_class')
print 'Table rep_total_decs_dectype_class exported to disk'
#############################
### Time reports ###
#############################
# Report: Total decisions per case year
if totalDecsPerCaseYear == True:
SQL.dropTable('rep_total_decs_case_year')
if st.debug >= 1: print 'Creating rep_total_decs_case_year'
sql = '''
create table rep_total_decs_case_year
select year(dat_autuacao) case_year, count(id) total_decs
from {0}
group by year(dat_autuacao)
order by year(dat_autuacao)
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_case_year created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_case_year')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}'.format(row['case_year'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_case_year')
ut.exportTable('rep_total_decs_case_year')
print 'Table rep_total_decs_case_year exported to disk'
# Report: Total decisions per year
if totalDecsPerYear == True:
SQL.dropTable('rep_total_decs_year')
if st.debug >= 1: print 'Creating rep_total_decs_year'
sql = '''
create table rep_total_decs_year
select year(dat_criacao) dec_year, count(id) total_decs
from {0}
group by year(dat_criacao)
order by year(dat_criacao)
'''.format(st.ta_main)
st.cur.execute(sql)
if st.debug >= 1: print 'Table rep_total_decs_year created'
if st.debug >= 2:
SQL.getAll('rep_total_decs_year')
print 'Rows: {0}'.format(st.totalRows)
for row in st.rows: print '{0}\t{1}'.format(row['dec_year'],row['total_decs'])
if st.exportMySQL == True:
SQL.getAll('rep_total_decs_year')
ut.exportTable('rep_total_decs_year')
print 'Table rep_total_decs_year exported to disk'