In [0]:
!sudo apt install sqlcipher libsqlcipher0 libsqlcipher-dev -q -y
In [0]:
!sudo -H pip3 install pysqlcipher3
In [0]:
!pip install pycryptodome
In [0]:
!pip install validate_email
!pip install pyDNS
!apt-get install python3-dns
In [0]:
import re
from datetime import datetime
import pytz
from time import time #importamos la función time para capturar tiempos
import os
import pandas as pd
from pandas.io.json import json_normalize
import numpy as np
import json
from zipfile import ZipFile
import requests, io
from sqlalchemy import create_engine
import csv
import string, random
import Crypto
from Crypto.PublicKey import RSA
from Crypto.Cipher import PKCS1_OAEP
from Crypto import Random
import ast
#https://www.novixys.com/blog/using-aes-encryption-decryption-python-pycrypto/
In [0]:
#----------------------------------------------------------------------------
# Encripta la clave de la BD SQLCipher utilizando la llave pública de la
# Superintendencia de Educación.
#----------------------------------------------------------------------------
def encryptTextUsingSiePublicKey(txt):
url_to_pem_file = "https://static.superintendencia-educacion.cl/KP/clave.pub.txt"
r = requests.get(url_to_pem_file, verify=False, stream=True)
path_to_public_pem_file = io.BytesIO(r.content).read()
publickey = RSA.importKey(path_to_public_pem_file)
encryptor = PKCS1_OAEP.new(publickey)
encrypted = encryptor.encrypt(bytes(txt,"utf-8"))
return encrypted
encryptTextUsingSiePublicKey('El Mundo')
In [0]:
from itertools import cycle
def validarRut(rut):
if(rut is not None):
dv = ''.join([c for c in list(rut.upper()) if c.isalpha()])
aux = ''.join([c for c in list(rut) if c.isdigit()])
if(dv == ''):
dv = aux[-1:]
aux = aux[:-1]
revertido = map(int, reversed(str(aux)))
factors = cycle(range(2,8))
s = sum(d * f for d, f in zip(revertido,factors))
res = (-s)%11
if ((str(res) == dv) or (dv=="K" and res==10)):
return True
return False
validarRut('22171685-K')
In [0]:
import requests
from lxml import html
def nameFromRUN(r,d):
root = 'https://zeus.sii.cl/cvc_cgi/nar/nar_consulta'
url = f'{root}?ACEPTAR=consulta&RUT={r}&DV={d}'
pageContent=requests.get(url)
tree = html.fromstring(pageContent.content)
return tree.xpath('//*/tr[1]/td/*/text()')[1]
nameFromRUN('1','9')
In [0]:
from validate_email import validate_email
is_valid = validate_email('miguel.aedo@mineduc.cl')
print(is_valid)
In [0]:
test = """
def openConnection(DB_NAME,secPhrase):
global dfLog,_sep,_encode
_r = True
try:
params = 'cipher=aes-256-cfb&kdf_iter=256000&cipher_page_size=4096'
engine = create_engine(f"sqlite+pysqlcipher://:{secPhrase}@/{DB_NAME}?{params}")
engine.execute("PRAGMA cipher_compatibility = 4;")
conn = engine.connect()
rows = conn.execute("SELECT * FROM Person;")
if(not rows.returns_rows):
raise Exception("Error al leer los datos de la BD")
else:
print(rows)
except Exception as e:
_t = "ERROR COMMIT: "+str(e)
print(_t)
_r = False
finally:
conn.close()
return _r
openConnection('/content/ceds-nds-v7_1_encryptedD4.db','test')
"""
In [0]:
#----------------------------------------------------------------------------
#PASO N° 20 - Transformar el archivo JSON en archivos CSV's. Uno por tabla.
#----------------------------------------------------------------------------
#----------------------------------------------------------------------------
# Transforma archivo JSON en un DataFrame de pandas con todas sus columnas.
# Agrega las columnas que faltan.
#----------------------------------------------------------------------------
def jsonToDataframe(elem, jsonData):
global dfLog
data=json_normalize(jsonData[elem['JSONGroupName']],elem['TableName'])
df = pd.DataFrame(data, columns=elem['ColumnList'])
df.drop_duplicates(inplace=True)
if(elem['TableName']=='Person'):
print(df)
_t = f"Tabla: {elem['TableName']} cargada como DataFrame exitosamente"
print(_t); dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return df
In [0]:
def validaLosTiposDeDatos(df, elem):
global dfLog
#Mapeo de tipos de datos SQL -> Pyhton
_dTypes = {
"bit": [pd.api.types.is_bool_dtype,np.int_,"bool"],
"char": [pd.api.types.is_string_dtype,np.unicode_,"str"],
"nchar": [pd.api.types.is_string_dtype,np.unicode_,"str"],
"nvarchar": [pd.api.types.is_string_dtype,np.unicode_,"str"],
"nvarcharmax": [pd.api.types.is_string_dtype,np.unicode_,"str"],
"varchar": [pd.api.types.is_string_dtype,np.unicode_,"str"],
"bigint": [pd.api.types.is_integer_dtype,np.int_,"int64"],
"int": [pd.api.types.is_integer_dtype,np.int_,"int32"],
"smallint": [pd.api.types.is_integer_dtype,np.int_,"int64"],
"tinyint": [pd.api.types.is_integer_dtype,np.int_,"int64"],
"float": [pd.api.types.is_float_dtype,np.float_,"float64"],
"real": [pd.api.types.is_float_dtype,np.float_,"float64"],
"decimal": [pd.api.types.is_float_dtype,np.float_,"float64"],
"numeric": [pd.api.types.is_float_dtype,np.float_,"float64"],
"varbinary": ['bytes'],
"binary": ['raw'],
"date": [pd.api.types.is_string_dtype,np.unicode_,"str"],
"time": [pd.api.types.is_string_dtype,np.unicode_,"str"],
"datetime": [pd.api.types.is_string_dtype,np.unicode_,"str"]}
_columnNames = elem['ColumnList']
_dataTypes=elem['DataType']
for idx,dt in enumerate(_dataTypes):
_tipo = ''.join([s for s in list(dt) if s.isalpha()])
field = _columnNames[idx]
fn = _dTypes[_tipo][1]
if(_tipo=='bit'):
df[field] = df[field].astype(fn, errors='ignore')
elif(_tipo=='date'):
df[field].replace('0000-00-00','',inplace=True)
_t = f"Tipos de datos de la tabla {elem['TableName']} verificados con éxito";
print(_t); dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return df
In [0]:
#----------------------------------------------------------------------------
# Convierte archivo JSON en varios archivos CSV. Uno por cada tabla del modelo.
# Se genera un elemento por cada tabla del JSON
# {'Column': ['OrganizationCalendarId','OrganizationId','CalendarCode','CalendarDescription','CalendarYear'],
# 'DataType': ['int', 'int', 'nvarchar(30)', 'nvarchar(60)', 'nchar(4)'],
# 'JSONGroup': '_Calendarios',
# 'SIERequired': ['YES', 'YES', 'NO', 'YES', 'NO'],
# 'Table': 'OrganizationCalendar'
# }
#----------------------------------------------------------------------------
def parseJsonToCSVs(path_to_zip_file,path_to_dir_csv_file):
global dfLog, _encode, _sep
xd = cargarPlanillaConDatosDelModelo()
jsonData,jsonFileName = readJsonData(path_to_zip_file)
for row in list(xd[xd["JSONGroup"].notnull()].groupby(["JSONGroup","Table"])):
elem = {
"JSONGroupName":row[0][0],
"TableName":row[0][1],
"ColumnList":list(row[1]["Column"]),
"DataType": list(row[1]["Data Type"]),
"SIERequired": list(row[1]["SIERequired"])
}
df = jsonToDataframe(elem,jsonData)
df = validaLosTiposDeDatos(df, elem)
_fileName = path_to_dir_csv_file+elem['TableName']+'.csv'
_c = str(df.count()[0])
print('Guardando : '+_fileName+' -> '+_c+' registros procesados.\n')
df.to_csv(_fileName,sep=_sep,encoding=_encode,index=False)
dfLog = dfLog.append(pd.Series({'json':jsonFileName,
'csv': elem['TableName']+'.csv',
'#savingRows':_c,
'resultSaving':'OK'}), ignore_index=True)
_t = 'Archivo JSON completamente transformado.'
print(_t);dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return True
In [0]:
In [0]:
def eliminarDuplicados(mylist):
seen = set()
newlist = []
for item in mylist:
t = tuple(item)
if t not in seen:
newlist.append(item)
seen.add(t)
return newlist
In [0]:
def crearCSV(jsonFileName, fileName,TableName,columnList,unique_records):
#https://pymotw.com/2/csv/
global dfLog,_sep
try:
csv.register_dialect('escaped', delimiter=_sep, lineterminator ='\n',
skipinitialspace=0, escapechar=None, doublequote=True,
quoting=csv.QUOTE_MINIMAL, quotechar='"')
_c = len(unique_records)
_f = open(fileName, 'w', encoding=_encode)
dialect = csv.get_dialect("escaped")
writer = csv.writer(_f, dialect=dialect)
writer.writerow(columnList)
writer.writerows(unique_records)
_t = f"Table {TableName} -> {_c} registros procesados."
except Exception as e:
_t = f"ERROR:'{str(e)}'. Tabla:'{TableName}'. {_c} registros perdidos."
finally:
_f.close()
print(_t);
dfLog = dfLog.append(pd.Series({'json':jsonFileName,
'csv': fileName,
'#savingRows':_c,
'resultSaving':_t}),
ignore_index=True)
return True
In [0]:
def readJsonData(path_to_zip_file):
global dfLog
# Descomprime el contenido del archivo ZIP y lo carga en memoria
if(path_to_zip_file):
with ZipFile(path_to_zip_file, 'r') as zip_ref:
zip_ref.extractall('./')
_t=f'Archivo ZIP "{path_to_zip_file}" descomprimido con éxito'; print(_t)
dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
for file in zip_ref.namelist():
_t=f"Trabajando sobre archivo: '{file}'"; print(_t)
dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
with open(file, mode='r', encoding="utf-8") as jsonfile:
jsonData = json.load(jsonfile)
_t=f"Archivo '{jsonfile}' leído sin inconvenientes\n"; print(_t)
dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
jsonfile.close()
os.remove(file)
return jsonData,file
In [0]:
def cargarPlanillaConDatosDelModelo():
global dfLog
#Carga planilla con todas las tablas y campos del modelo https://ceds.ed.gov
idFile = '1R8iEWpa2-buQijoI9NzniCbyZm5-zZcN'
url = f'http://drive.google.com/uc?export=download&id={idFile}'
xd = pd.read_excel(url,'NDS Columns')
_t=f'Planilla {url} cargada satisfactoriamente'; print(_t)
dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return xd;
In [0]:
def leerTodosLosRegistrosDeLaTalaDesdeArchivoJson(jsonData,elem):
#Mapeo de tipos de datos SQL -> Pyhton
records = []
for grupo in jsonData[elem['JSONGroupName']]:
for tbl in grupo[elem['TableName']]:
record = []
for indice,col in enumerate(elem['ColumnList']):
dt = elem['DataType'][indice]
_tipo = ''.join([s for s in list(dt) if s.isalpha()])
value = tbl.get(col) if (tbl.get(col) is not None) else ''
if(_tipo in {'bit', 'bigint', 'int', 'smallint', 'tinyint'} and value!=''):
value = int(value)
elif(_tipo=='date'):
value = str(value).replace('0000-00-00','')
record.append(value)
records.append(record)
return eliminarDuplicados(records)
In [0]:
def readJsonSaveCSV(path_to_zip_file,path_to_dir_csv_file):
global dfLog, _encode, _sep
xd = cargarPlanillaConDatosDelModelo()
jsonData,jsonFileName = readJsonData(path_to_zip_file)
for row in list(xd[xd["JSONGroup"].notnull()].groupby(["JSONGroup","Table"])):
elem = {
"JSONGroupName":row[0][0],
"TableName":row[0][1],
"ColumnList":list(row[1]["Column"]),
"DataType": list(row[1]["Data Type"]),
"SIERequired": list(row[1]["SIERequired"])
}
records = leerTodosLosRegistrosDeLaTalaDesdeArchivoJson(jsonData,elem)
crearCSV(jsonFileName,path_to_dir_csv_file+elem['TableName']+'.csv',
elem['TableName'],
elem['ColumnList'],
records)
_t = 'Archivo JSON completamente transformado.'
print(_t);dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return True
In [0]:
#----------------------------------------------------------------------------
#PASO N° 30 - Guarda elmentos en CSV en archivos CSV
#----------------------------------------------------------------------------
#----------------------------------------------------------------------------
# Zip the files from given directory that matches the filter
#----------------------------------------------------------------------------
def zipFilesInDir(dirName, zipFileName, filter):
global dfLog
# create a ZipFile object
with ZipFile(zipFileName, 'w') as zipObj:
# Iterate over all the files in directory
for folderName, subfolders, filenames in os.walk(dirName):
for filename in filenames:
if filter(filename):
# create complete filepath of file in directory
filePath = os.path.join(folderName, filename)
# Add file to zip
zipObj.write(filePath)
_t = "Archivo ZIP con todos los CSV's creado con éxito"; print(_t)
dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return True
In [0]:
def transferCSVToSQL_withPandas(path_to_dir_csv_file,DB_NAME,timestamp):
global dfLog,_sep,_encode
_r = True
secPhase = 'BD en blanco solo con parámetros definidos por Enlaces-Mineduc'
engine = create_engine(f"sqlite+pysqlcipher://:{secPhase}@/{DB_NAME}?cipher=aes-256-cfb&kdf_iter=64000")
conn = engine.connect()
try:
for root, dirs, files in os.walk(path_to_dir_csv_file, topdown=False):
for name in files:
_fileName = os.path.join(root, name)
df = pd.read_csv(_fileName,sep=_sep,encoding=_encode)
_c = str(df.count()[0])
tbl = name[:-4]
print(f'\nLeyendo: {_fileName} -> {_c} registros procesados.');
try:
df.to_sql(tbl, con = conn, index=False, if_exists='append')
_result = "OK"
except Exception as e:
print(f'RollBack')
_result='ERROR: '+str(e)
_r = False
pass
finally:
print("name:",name)
dfLog.loc[dfLog['csv']==_fileName,dfLog.columns=='#readingRows']=_c
dfLog.loc[dfLog['csv']==_fileName,dfLog.columns=='resultReading']=_result
print("Table:",tbl,"#Rows:",len(df.index),_result)
#trans.commit()
#-------------------- REVISION DE REGLAS DEL NEGOCIO --------------------------
# VERIFICA LA INTEGRIDAD REFERENCIAL DE LOS DATOS
print("# VERIFICA LA INTEGRIDAD REFERENCIAL DE LOS DATOS")
rows = conn.execute("PRAGMA foreign_key_check;")
if(rows.returns_rows):
pd.DataFrame(rows
,columns=['Table', 'rowId', 'Parent', 'FkId']
).to_csv('ForenKeyErrors.csv'
,sep=_sep
,encoding=_encode
,index=False)
raise Exception("BD con errores de Integridad Referencial. Revise ForenKeyErrors.csv para más detalle")
# VERIFICA QUE LA BD CONTENGA EL RBD DEL ESTABLECIMIENTO
print("# VERIFICA QUE LA BD CONTENGA EL RBD DEL ESTABLECIMIENTO")
RBD = conn.execute("""
SELECT
i.Identifier as RBD
,Organization.Name as 'NombreEstablecimiento'
,i.OrganizationId
FROM OrganizationIdentifier i
INNER JOIN Organization USING(OrganizationId)
INNER JOIN RefOrganizationIdentificationSystem rbd
ON i.RefOrganizationIdentificationSystemId = rbd.RefOrganizationIdentificationSystemId
AND i.RefOrganizationIdentificationSystemId = (
SELECT RefOrganizationIdentificationSystemId
FROM RefOrganizationIdentificationSystem
WHERE Code = 'RBD')
INNER JOIN RefOrganizationIdentifierType Mineduc
ON i.RefOrganizationIdentifierTypeId = Mineduc.RefOrganizationIdentifierTypeId
AND i.RefOrganizationIdentifierTypeId = (
SELECT RefOrganizationIdentifierTypeId
FROM RefOrganizationIdentifierType
WHERE Code = 'Mineduc')
""")
if(not RBD.returns_rows):
raise Exception("RBD del establecimiento no fue encontrado en la Base de datos")
else:
row = RBD.fetchall();
r = re.compile('^RBD[0-9]{5}$')
print(row[0][0])
if r.match(row[0][0]) is not None:
print('RBD con formato correcto',row[0][0])
else:
raise Exception("RBD con formato incorrecto", row[0][0])
# VERIFICA QUE LA BD CONTENGA LA INFORMACIÓN DE SOLO UN AÑO
print("# VERIFICA QUE LA BD CONTENGA LA INFORMACIÓN DE SOLO UN AÑO")
Year = conn.execute("""
SELECT CalendarYear as 'AñoEscolar'
FROM OrganizationCalendar
GROUP BY CalendarYear
""");
if(Year.returns_rows):
rows = Year.fetchall();
print("Año: ",rows[0])
if(len(rows)!=1):
raise Exception("La BD contiene más de un año de referencia en los datos")
else:
raise Exception("La BD no contiene el año de referencia de los datos")
# VERIFICA JERARQUIA DE LOS DATOS
# la jerarquí es:
# RBD -> Modalidad -> Jornada -> Niveles -> Rama ->
# Sector Económico (shorName) + Especialidad (Name)
# Tipo de Curso -> COD_ENSE (shortName) + Grado (Name) -> Curso -> Asignatura
print("# VERIFICA JERARQUIA DE LOS DATOS")
Jerarquias = conn.execute("""
SELECT
ee.RBD
, ee.nombreEstablecimiento
, modalidad.Name as modalidad
, jornada.Name as jornada
, nivel.Name as nivel
, rama.Name as rama
, sector.Name as sector
, especialidad.Name as especialidad
, tipoCurso.Name as tipoCurso
, codEnse.Name as codigoEnseñanza
, grado.Name as grado
, curso.Name as letraCurso
, curso.OrganizationId as OrganizationIdDelCurso
, profesorJefe.apellidoPaternoDocenteLiderCurso
, profesorJefe.apellidoMaternoDocenteLiderCurso
, profesorJefe.primerNombreDocenteLiderCurso
, profesorJefe.otrosNombresDocenteLiderCurso
, profesorJefe.runDocenteLiderCurso
FROM Organization as curso
INNER JOIN OrganizationRelationship as rsCurso on curso.OrganizationId=rsCurso.OrganizationId
INNER JOIN Organization as grado on grado.OrganizationId=rsCurso.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsGrado on grado.OrganizationId=rsGrado.OrganizationId
INNER JOIN Organization as codEnse on codEnse.OrganizationId=rsGrado.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsCodEnse on codEnse.OrganizationId=rsCodEnse.OrganizationId
INNER JOIN Organization as tipoCurso on tipoCurso.OrganizationId=rsCodEnse.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsTipoCurso on tipoCurso.OrganizationId=rsTipoCurso.OrganizationId
INNER JOIN Organization as especialidad on especialidad.OrganizationId=rsTipoCurso.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsEspecialidad on especialidad.OrganizationId=rsEspecialidad.OrganizationId
INNER JOIN Organization as sector on sector.OrganizationId=rsEspecialidad.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsSector on sector.OrganizationId=rsSector.OrganizationId
INNER JOIN Organization as rama on rama.OrganizationId=rsSector.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsRama on rama.OrganizationId=rsRama.OrganizationId
INNER JOIN Organization as nivel on nivel.OrganizationId=rsRama.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsNivel on nivel.OrganizationId=rsNivel.OrganizationId
INNER JOIN Organization as jornada on jornada.OrganizationId=rsNivel.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsJornada on jornada.OrganizationId=rsJornada.OrganizationId
INNER JOIN Organization as modalidad on modalidad.OrganizationId=rsJornada.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsModalidad on modalidad.OrganizationId=rsModalidad.OrganizationId
INNER JOIN (
SELECT
i.Identifier as RBD
,Organization.Name as 'nombreEstablecimiento'
,i.OrganizationId as OrganizationId
FROM OrganizationIdentifier i
INNER JOIN Organization USING(OrganizationId)
INNER JOIN RefOrganizationIdentificationSystem rbd
ON i.RefOrganizationIdentificationSystemId = rbd.RefOrganizationIdentificationSystemId
AND i.RefOrganizationIdentificationSystemId = (
SELECT RefOrganizationIdentificationSystemId
FROM RefOrganizationIdentificationSystem
WHERE Code = 'RBD' )
INNER JOIN RefOrganizationIdentifierType Mineduc
ON i.RefOrganizationIdentifierTypeId = Mineduc.RefOrganizationIdentifierTypeId
AND i.RefOrganizationIdentifierTypeId = (
SELECT RefOrganizationIdentifierTypeId
FROM RefOrganizationIdentifierType
WHERE Code = 'Mineduc' )) as ee on ee.OrganizationId=rsModalidad.Parent_OrganizationId
INNER JOIN (
SELECT
OrganizationPersonRoleId
, OrganizationId
, PersonId
, LastName as 'apellidoPaternoDocenteLiderCurso'
, SecondLastName as 'apellidoMaternoDocenteLiderCurso'
, FirstName as 'primerNombreDocenteLiderCurso'
, MiddleName as 'otrosNombresDocenteLiderCurso'
, runDocenteLiderCurso
FROM K12StaffAssignment
INNER JOIN OrganizationPersonRole USING(OrganizationPersonRoleId)
INNER JOIN (
SELECT DISTINCT
Person.PersonId
,Person.LastName
,Person.SecondLastName
,Person.FirstName
,Person.MiddleName
,rut.Identifier as RunDocenteLiderCurso
FROM Person
INNER JOIN PersonIdentifier rut ON rut.PersonId = Person.PersonId
AND rut.RefPersonIdentificationSystemId = 51
) USING(PersonId)
WHERE RefTeachingAssignmentRoleId = 1
) profesorJefe ON OrganizationIdDelCurso = profesorJefe.OrganizationId
WHERE curso.RefOrganizationTypeId = 21
""");
print("Jerarquias.returns_rows->",Jerarquias.returns_rows)
if(Jerarquias.returns_rows):
rows = Jerarquias.fetchall()
if(len(rows)==0):
raise Exception("No se encuentra ningún dato de jerarquía")
else:
raise Exception("No se encuentra ningún dato de jerarquía")
modalidades = list(set([m[2] for m in rows]))
madalidadesList = ['Regular','Especial','Adulto']
if(False in [m in madalidadesList for m in modalidades]):
raise Exception("La modalidad de enseñanza no corresponde")
jornadas = list(set([m[3] for m in rows]))
jornadasList = ['Mañana','Tarde','Mañana y Tarde','Vespertina/Nocturna']
if(False in [m in jornadasList for m in jornadas]):
raise Exception("La jornada de enseñanza no corresponde")
nivel = list(set([m[4] for m in rows]))
nivelList = [
'01:Educación Parvularia',
'02:Enseñanza Básica Niños',
'03:Educación Básica Adultos',
'04:Educación Especial',
'05:Enseñanza Media Humanístico Científica Jóvenes',
'06:Educación Media Humanístico Científica Adultos',
'07:Enseñanza Media Técnico Profesional y Artística, Jóvenes',
'08:Educación Media Técnico Profesional y Artística, Adultos']
if(False in [m in nivelList for m in nivel]):
raise Exception("El nivel de enseñanza agrupado no corresponde")
rama = list(set([m[5] for m in rows]))
ramaList = ['000:Ciclo General',
'000:Sin Información',
'400:Comercial',
'500:Industrial',
'600:Técnica',
'700:Agrícola',
'800:Marítima',
'900:Artística',]
if(False in [m in ramaList for m in rama]):
raise Exception("La rama de enseñanza no corresponde")
sector = list(set([m[6] for m in rows]))
# Ciclo general corresponde a alumnos de 1° y 2° Medio
#en Enseñanza Media T-P y Artística niños y jóvenes y
#primer nivel en educación media T-P y Artística Adultos.
#En todo otro caso colocar "Sin Información"
sectorList = ['000:Ciclo General',
'000:Sin Información',
'410:Administración y Comercio',
'510:Construcción',
'520:Metalmecánico',
'530:Electricidad',
'540:Minero',
'550:Gráfica',
'560:Químico',
'570:Confección',
'580:Tecnología y Telecomunicaciones',
'610:Alimentación',
'620:Programas y Proyectos Sociales',
'630:Hotelería y Turismo',
'640:Salud y Educación',
'710:Maderero',
'720:Agropecuario',
'810:Marítimo',
'910:Artes Visuales',
'920:Artes Escénicas Teatro',
'930:Artes Escénicas Danza']
if(False in [m in sectorList for m in sector]):
raise Exception("El sector de enseñanza no corresponde")
especialidad = list(set([m[7] for m in rows]))
especialidadList = ['000:Ciclo General',
'000:Sin Información',
'410.41001:Administración',
'410.41002:Contabilidad',
'410.41003:Secretariado',
'410.41004:Ventas',
'410.41005:Administración (con mención)',
'510.51001:Edificación',
'510.51002:Terminaciones de Construcción',
'510.51003:Montaje Industrial',
'510.51004:Obras viales y de infraestructura',
'510.51005:Instalaciones sanitarias',
'510.51006:Refrigeración y climatización',
'510.51009:Construcción (con mención)',
'520.52008:Mecánica Industrial',
'520.52009:Construcciones Metálicas',
'520.52010:Mecánica Automotriz',
'520.52011:Matricería',
'520.52012:Mecánica de mantención de aeronaves',
'520.52013:Mecánica Industrial (con mención)',
'530.53014:Electricidad',
'530.53015:Electrónica',
'530.53016:Telecomunicaciones hasta el año 2015',
'540.54018:Explotación minera',
'540.54019:Metalurgia Extractiva',
'540.54020:Asistencia de geología',
'550.55022:Gráfica',
'550.55023:Dibujo Técnico',
'560.56025:Operación de planta química',
'560.56026:Laboratorio químico',
'560.56027:Química Industrial (con mención)',
'570.57028:Tejido',
'570.57029:Textil',
'570.57030:Vestuario y Confección Textil',
'570.57031:Productos del cuero',
'580.58033:Conectividad y Redes',
'580.58034:Programación',
'580.58035:Telecomunicaciones',
'610.61001:Elaboración Industrial de Alimentos',
'610.61002:Servicio de Alimentación Colectiva',
'610.61003:Gastronomía (con mención)',
'620.62004:Atención de párvulos hasta año 2015',
'620.62005:Atención de adultos mayores',
'620.62006:Atención de Enfermería',
'620.62007:Atención Social y Recreativa',
'620.62008:Atención de Enfermería (con mención) hasta año 2015',
'630.63009:Servicio de turismo',
'630.63010:Servicios Hoteleros',
'630.63011:Servicio de hotelería',
'640.64001:Atención de párvulos',
'640.64008:Atención de Enfermería (con mención)',
'710.71001:Forestal',
'710.71002:Procesamiento de la madera',
'710.71003:Productos de la madera',
'710.71004:Celulosa y Papel',
'710.71005:Muebles y Terminaciones de la madera',
'720.72006:Agropecuaria',
'720.72007:Agropecuaria (con mención)',
'810.81001:Naves mercantes y especiales',
'810.81002:Pesquería',
'810.81003:Acuicultura',
'810.81004:Operación portuaria',
'810.81005:Tripulación naves mercantes y especiales',
'910.91001:Artes Visuales',
'910.91002:Artes Audiovisuales',
'910.91003:Diseño',
'920.92004:Interpretación Teatral',
'920.92005:Diseño Escénico',
'930.93006:Interpretación en Danza de Nivel Intermedio',
'930.93007:Monitoría de Danza']
if(False in [m in especialidadList for m in especialidad]):
raise Exception("La especialidad de enseñanza no corresponde")
tipoCurso = list(set([m[8] for m in rows]))
tipoCursoList = ['01:Simple','02:Combinado']
if(False in [m in tipoCursoList for m in tipoCurso]):
raise Exception("El codigo de nivel agrupado no corresponde")
codigoEnse = list(set([m[9] for m in rows]))
codigoEnseList = ['010:Educación Parvularia',
'110:Enseñanza Básica',
'160:Educación Básica Común Adultos (Decreto 584/2007)',
'161:Educación Básica Especial Adultos',
'163:Escuelas Cárceles (Básica Adultos)',
'165:Educación Básica Adultos Sin Oficios (Decreto 584/2007)'
'167:Educación Básica Adultos Con Oficios (Decreto 584/2007 y 999/2009)',
'211:Educación Especial Discapacidad Auditiva',
'212:Educación Especial Discapacidad Intelectual',
'213:Educación Especial Discapacidad Visual',
'214:Educación Especial Trastornos Específicos del Lenguaje',
'215:Educación Especial Trastornos Motores',
'216:Educación Especial Autismo',
'217:Educación Especial Discapacidad Graves Alteraciones en la Capacidad de Relación y Comunicación',
'299:Opción 4 Programa Integración Escolar',
'310:Enseñanza Media H-C niños y jóvenes',
'360:Educación Media H-C adultos vespertino y nocturno (Decreto N° 190/1975)',
'361:Educación Media H-C adultos (Decreto N° 12/1987)',
'362:Escuelas Cárceles (Media Adultos)',
'363:Educación Media H-C Adultos (Decreto N°1000/2009)',
'410:Enseñanza Media T-P Comercial Niños y Jóvenes',
'460:Educación Media T-P Comercial Adultos (Decreto N° 152/1989)',
'461:Educación Media T-P Comercial Adultos (Decreto N° 152/1989)',
'463:Educación Media T-P Comercial Adultos (Decreto N° 1000/2009)',
'510:Enseñanza Media T-P Industrial Niños y Jóvenes',
'560:Educación Media T-P Industrial Adultos (Decreto N° 152/1989)',
'561:Educación Media T-P Industrial Adultos (Decreto N° 152/1989)',
'563:Educación Media T-P Industrial Adultos (Decreto N° 1000/2009)',
'610:Enseñanza Media T-P Técnica Niños y Jóvenes',
'660:Educación Media T-P Técnica Adultos (Decreto N° 152/1989)',
'661:Educación Media T-P Técnica Adultos (Decreto N° 152/1989)',
'663:Educación Media T-P Técnica Adultos (Decreto N° 1000/2009)',
'710:Enseñanza Media T-P Agrícola Niños y Jóvenes',
'760:Educación Media T-P Agrícola Adultos (Decreto N° 152/1989)',
'761:Educación Media T-P Agrícola Adultos (Decreto N° 152/1989)',
'763:Educación Media T-P Agrícola Adultos (Decreto N° 1000/2009)',
'810:Enseñanza Media T-P Marítima Niños y Jóvenes',
'860:Enseñanza Media T-P Marítima Adultos (Decreto N° 152/1989)',
'863:Enseñanza Media T-P Marítima Adultos (Decreto N° 1000/2009)',
'910:Enseñanza Media Artística Niños y Jóvenes',
'963:Enseñanza Media Artística Adultos',]
if(False in [m in codigoEnseList for m in codigoEnse]):
raise Exception("El código de enseñanza no corresponde")
grado = list(set([m[10] for m in rows]))
gradoList = ['010.01:Sala Cuna',
'010.02:Nivel Medio Menor',
'010.03:Nivel Medio Mayor',
'010.04:Primer Nivel de Transición (Pre-kinder)',
'010.05:Segundo Nivel de Transición (Kinder)',
'110.01:1º Básico',
'110.02:2º Básico',
'110.03:3º Básico',
'110.04:4º Básico',
'110.05:5º Básico',
'110.06:6º Básico',
'110.07:7º Básico',
'110.08:8º Básico',
'165.01:Nivel Básico 1 (1º a 4º básico)',
'165.02:Nivel Básico 2 (5º a 6º básico)',
'165.03:Nivel Básico 3 (7º a 8º básico)',
'167.02:Nivel Básico 2 (5º a 6º básico)',
'167.03:Nivel Básico 3 (7º a 8º básico)',
'211.01:Prebásico materno 1º',
'211.02:Prebásico 1º - 1',
'211.03:Prebásico 1º - 2',
'211.04:Prebásico 1º - 3',
'211.05:Prebásico 2º - 4',
'211.06:Prebásico 2º - 5',
'211.07:Básico 1º - 1',
'211.08:Básico 1º - 2',
'211.09:Básico 1º - 3',
'211.10:Básico 1º - 4',
'211.11:Básico 2º - 5',
'211.12:Básico 2º - 6',
'211.13:Básico 2º - 7',
'211.14:Básico 2º - 8',
'211.15:Laboral 1',
'211.16:Laboral 2',
'211.17:Laboral 3',
'212.01:Prebásico 1º - 1',
'212.02:Prebásico 1º - 2',
'212.03:Prebásico 1º - 3',
'212.04:Prebásico 2º - 4',
'212.05:Básico 1º - 5',
'212.06:Básico 1º - 6',
'212.07:Básico 1º - 7',
'212.08:Básico 2º - 8',
'212.09:Básico 2º - 9',
'212.10:Básico 2º - 10',
'212.11:Laboral 1',
'212.12:Laboral 2',
'212.13:Laboral 3',
'212.14:Prebásico Materno 1° (Estimulación Temprana)',
'213.01:Estimulación temprana 1º - 1',
'213.02:Estimulación temprana 1º - 2',
'213.03:Prebásico 1º - 1',
'213.04:Prebásico 1º - 2',
'213.05:Prebásico 2º - 3',
'213.06:Prebásico 2º - 4',
'213.07:Básico 1º - 1',
'213.08:Básico 1º - 2',
'213.09:Básico 1º - 3',
'213.10:Básico 1º - 4',
'213.11:Básico 2º - 5',
'213.12:Básico 2º - 6',
'213.13:Básico 2º - 7',
'213.14:Básico 2º - 8',
'213.15:Laboral 1º - 1',
'213.16:Laboral 1º - 2',
'213.17:Laboral 2º - 3',
'213.18:Laboral 2º - 4',
'214.01:Medio Menor',
'214.02:Medio Mayor',
'214.03:Primer Nivel de Transición (Pre-kinder)',
'214.04:Segundo Nivel de Transición (Kinder)',
'215.01:Estimulación temprana 1º - 1',
'215.02:Estimulación temprana 1º - 2',
'215.03:Prebásico 1º - 1',
'215.04:Prebásico 1º - 2',
'215.05:Prebásico 1º - 3',
'215.06:Prebásico 2º - 4',
'215.07:Prebásico 2º - 5',
'215.08:Básico 1º - 1',
'215.09:Básico 1º - 2',
'215.10:Básico 1º - 3',
'215.11:Básico 1º - 4',
'215.12:Básico 2º - 5',
'215.13:Básico 2º - 6',
'215.14:Básico 2º - 7',
'215.15:Básico 2º - 8',
'215.16:Laboral 1º - 1',
'215.17:Laboral 1º - 2',
'215.18:Laboral 2º - 3',
'215.19:Laboral 2º - 4',
'216.05:Básico 1° - 5',
'216.06:Básico 1° - 6',
'216.07:Básico 1° - 7',
'216.08:Básico 2° - 8',
'216.09:Básico 2° - 9',
'216.10:Básico 2° - 10',
'216.11:Laboral 1',
'216.12:Laboral 2',
'216.13:Laboral 3',
'216.14:Prebásico Materno 1° (Estimulación Temprana)',
'216.15:Prebásico 1° - 1',
'216.16:Prebásico 1° - 2',
'216.17:Prebásico 2° - 3',
'216.18:Prebásico 2° - 4',
'217.01:Prebásico 1º - 1',
'217.02:Prebásico 1º - 2',
'217.03:Prebásico 2º - 3',
'217.04:Prebásico 2º - 4',
'217.05:Básico 1º - 5',
'217.06:Básico 1º - 6',
'217.07:Básico 1º - 75',
'217.08:Básico 2º - 8',
'217.09:Básico 2º - 9',
'217.10:Básico 2º - 10',
'217.11:Laboral 1',
'217.12:Laboral 2',
'217.13:Laboral 3',
'217.14:Prebásico Materno 1º (Estimulación temprana)',
'299.01:Prebásico 1',
'299.02:Prebásico 2',
'299.03:Prebásico 3',
'299.04:Prebásico 4',
'299.05:Prebásico 5',
'299.06:Básico 1',
'299.07:Básico 2',
'299.08:Básico 3',
'299.09:Básico 4',
'299.10:Básico 5',
'299.11:Básico 6',
'299.12:Básico 7',
'299.13:Básico 8',
'299.14:Básico 9',
'299.15:Básico 10',
'299.16:Laboral 1',
'299.17:Laboral 2',
'299.18:Laboral 3',
'299.19:Laboral 4',
'310.01:1º medio',
'310.02:2º medio',
'310.03:3º medio',
'310.04:4º medio',
'363.01:Primer nivel (1º y 2º medio)',
'363.03:Segundo nivel (3º y 4º medio)',
'410.01:1º medio',
'410.02:2º medio',
'410.03:3º medio',
'410.04:4º medio',
'463.01:Primer nivel (1º y 2º medio)',
'463.03:Segundo nivel (3º medio)',
'463.04:Tercero nivel (4º medio)',
'510.01:1º medio',
'510.02:2º medio',
'510.03:3º medio',
'510.04:4º medio',
'563.01:Primer nivel (1º y 2º medio)',
'563.03:Segundo nivel (3º medio)',
'563.04:Tercero nivel (4º medio)',
'610.01:1º medio',
'610.02:2º medio',
'610.03:3º medio',
'610.04:4º medio',
'663.01:Primer nivel (1º y 2º medio)',
'663.03:Segundo nivel (3º medio)',
'663.04:Tercero nivel (4º medio)',
'710.01:1º medio',
'710.02:2º medio',
'710.03:3º medio',
'710.04:4º medio',
'763.01:Primer nivel (1º y 2º medio)',
'763.03:Segundo nivel (3º medio)',
'763.04:Tercero nivel (4º medio)',
'810.01:1º medio',
'810.02:2º medio',
'810.03:3º medio',
'810.04:4º medio',
'863.01:Primer nivel (1º y 2º medio)',
'863.03:Segundo nivel (3º medio)',
'863.04:Tercero nivel (4º medio)',
'910.01:1º medio',
'910.02:2º medio',
'910.03:3º medio',
'910.04:4º medio',
'963.01:Primer nivel (1º y 2º medio)',
'963.03:Segundo nivel (3º medio)',
'963.04:Tercero nivel (4º medio)']
if(False in [m in gradoList for m in grado]):
raise Exception("El grado no corresponde")
letraCurso = list(set([m[11] for m in rows]))
r = re.compile('^[A-Z]{1,2}$')
if(None in [r.match(letra) for letra in letraCurso]):
raise Exception("La letra de curso inválida")
runDocenteLider = list(set([m[17] for m in rows]))
if(False in [validarRut(run) for run in runDocenteLider]):
raise Exception("Existe RUN de docentes inválidos")
# VERIFICA LISTA DE ESTUDIANTES
print("# VERIFICA LISTA DE ESTUDIANTES")
estudiantes = conn.execute("""
SELECT DISTINCT
person.PersonId as 'personIdEstudiante'
,numLista.StudentListNumber as 'númeroListaEstudiante'
,mat.Identifier as 'númeroMatriculaEstudiante'
,Person.LastName as apellidoPaternoEstudiante, Person.SecondLastName as apellidoMaternoEstudiante, Person.FirstName as primerNombreEstudiante, Person.MiddleName as otrosNombresEstudiante
,rut.Identifier as runEstudiante
,CASE RefSex.Code WHEN 'Male' THEN 'M' WHEN 'Female' THEN 'F' ELSE 'Sin Registro' END as sexoEstudiante
,Birthdate as fechaNacimientoEstudiante
,address.StreetNumberAndName as DirecciónEstudiante
,address.comuna as ComunaEstudiante
,padre.ApellidoPaternoPadre, padre.ApellidoMaternoPadre, padre.PrimerNombrePadre, padre.OtrosNombresPadre, padre.RunPadre
,madre.ApellidoPaternoMadre, madre.ApellidoMaternoMadre, madre.PrimerNombreMadre, madre.OtrosNombresMadre, madre.RunMadre
,tutor.ApellidoPaternoTutor, tutor.ApellidoMaternoTutor, tutor.PrimerNombreTutor, tutor.OtrosNombresTutor, tutor.RunTutor, tutor.FonoTutor, tutor.EmailTutor
,curso.RBD
,curso.nombreEstablecimiento
,curso.modalidad
,curso.jornada
,curso.nivel
,curso.grado
,curso.letraCurso
,curso.OrganizationIdDelCurso
,curso.apellidoPaternoDocenteLiderCurso, curso.apellidoMaternoDocenteLiderCurso, curso.primerNombreDocenteLiderCurso, curso.otrosNombresDocenteLiderCurso, curso.runDocenteLiderCurso
,oc.AñoCalendario
,Opr.EntryDate as 'fechaIncorporaciónEstudiante'
,Opr.ExitDate as 'fechaRetiroEstudiante'
FROM Person
INNER JOIN PersonIdentifier mat
ON mat.PersonId = Person.PersonId
AND mat.RefPersonIdentificationSystemId = 6
LEFT OUTER JOIN RefSex
USING(RefSexId)
LEFT OUTER JOIN PersonIdentifier rut
ON rut.PersonId = Person.PersonId
AND rut.RefPersonIdentificationSystemId = 51
LEFT OUTER JOIN (
SELECT
PersonId
,StreetNumberAndName
,RefCounty.Description as Comuna
FROM PersonAddress
INNER JOIN RefCounty ON PersonAddress.RefCountyId = RefCounty.RefCountyId
) address USING(PersonId)
LEFT OUTER JOIN (
SELECT DISTINCT
Person.PersonId
,Person.LastName as ApellidoPaternoPadre
,Person.SecondLastName as ApellidoMaternoPadre
,Person.FirstName as PrimerNombrePadre
,Person.MiddleName as OtrosNombresPadre
,rut.Identifier as RunPadre
FROM Person
INNER JOIN PersonIdentifier rut ON rut.PersonId = Person.PersonId
AND rut.RefPersonIdentificationSystemId = 51
INNER JOIN PersonRelationship padre ON padre.PersonId = Person.PersonId
AND padre.RefPersonRelationshipId IN (8,9,10,11)
) padre USING(PersonId)
LEFT OUTER JOIN (
SELECT DISTINCT
Person.PersonId
,Person.LastName as ApellidoPaternoMadre
,Person.SecondLastName as ApellidoMaternoMadre
,Person.FirstName as PrimerNombreMadre
,Person.MiddleName as OtrosNombresMadre
,rut.Identifier as RunMadre
FROM Person
INNER JOIN PersonIdentifier rut ON rut.PersonId = Person.PersonId
AND rut.RefPersonIdentificationSystemId = 51
INNER JOIN PersonRelationship ON PersonRelationship.PersonId = Person.PersonId
AND PersonRelationship.RefPersonRelationshipId IN (18,19,20)
) madre USING(PersonId)
LEFT OUTER JOIN (
SELECT DISTINCT
Person.PersonId
,Person.LastName as ApellidoPaternoTutor
,Person.SecondLastName as ApellidoMaternoTutor
,Person.FirstName as PrimerNombreTutor
,Person.MiddleName as OtrosNombresTutor
,rut.Identifier as RunTutor
,p.TelephoneNumber as FonoTutor
,PersonEmailAddress.EmailAddress as EmailTutor
FROM Person
INNER JOIN PersonIdentifier rut
ON rut.PersonId = Person.PersonId
AND rut.RefPersonIdentificationSystemId = 51
INNER JOIN PersonRelationship
ON PersonRelationship.PersonId = Person.PersonId
AND PersonRelationship.PrimaryContactIndicator = 1
LEFT OUTER JOIN (
SELECT *
FROM PersonTelephone
ORDER BY PrimaryTelephoneNumberIndicator DESC
LIMIT 1
) p USING(PersonId)
LEFT OUTER JOIN PersonEmailAddress
USING(PersonId)
) tutor USING(PersonId)
LEFT OUTER JOIN (
SELECT DISTINCT
StudentListNumber
,OrganizationPersonRole.PersonId
FROM K12StudentEnrollment
INNER JOIN OrganizationPersonRole
USING(OrganizationPersonRoleId)
WHERE StudentListNumber NOT NULL AND StudentListNumber != 0
) numLista USING(PersonId)
LEFT OUTER JOIN OrganizationPersonRole as Opr
USING(PersonId)
INNER JOIN (
SELECT
ee.RBD
, ee.nombreEstablecimiento
, modalidad.Name as modalidad
, jornada.Name as jornada
, nivel.Name as nivel
, rama.Name as rama
, sector.Name as sector
, especialidad.Name as especialidad
, tipoCurso.Name as tipoCurso
, codEnse.Name as codigoEnseñanza
, grado.Name as grado
, curso.Name as letraCurso
, curso.OrganizationId as OrganizationIdDelCurso
, profesorJefe.apellidoPaternoDocenteLiderCurso
, profesorJefe.apellidoMaternoDocenteLiderCurso
, profesorJefe.primerNombreDocenteLiderCurso
, profesorJefe.otrosNombresDocenteLiderCurso
, profesorJefe.runDocenteLiderCurso
FROM Organization as curso
INNER JOIN OrganizationRelationship as rsCurso on curso.OrganizationId=rsCurso.OrganizationId
INNER JOIN Organization as grado on grado.OrganizationId=rsCurso.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsGrado on grado.OrganizationId=rsGrado.OrganizationId
INNER JOIN Organization as codEnse on codEnse.OrganizationId=rsGrado.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsCodEnse on codEnse.OrganizationId=rsCodEnse.OrganizationId
INNER JOIN Organization as tipoCurso on tipoCurso.OrganizationId=rsCodEnse.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsTipoCurso on tipoCurso.OrganizationId=rsTipoCurso.OrganizationId
INNER JOIN Organization as especialidad on especialidad.OrganizationId=rsTipoCurso.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsEspecialidad on especialidad.OrganizationId=rsEspecialidad.OrganizationId
INNER JOIN Organization as sector on sector.OrganizationId=rsEspecialidad.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsSector on sector.OrganizationId=rsSector.OrganizationId
INNER JOIN Organization as rama on rama.OrganizationId=rsSector.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsRama on rama.OrganizationId=rsRama.OrganizationId
INNER JOIN Organization as nivel on nivel.OrganizationId=rsRama.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsNivel on nivel.OrganizationId=rsNivel.OrganizationId
INNER JOIN Organization as jornada on jornada.OrganizationId=rsNivel.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsJornada on jornada.OrganizationId=rsJornada.OrganizationId
INNER JOIN Organization as modalidad on modalidad.OrganizationId=rsJornada.Parent_OrganizationId
INNER JOIN OrganizationRelationship as rsModalidad on modalidad.OrganizationId=rsModalidad.OrganizationId
INNER JOIN (
SELECT
i.Identifier as RBD
,Organization.Name as 'nombreEstablecimiento'
,i.OrganizationId as OrganizationId
FROM OrganizationIdentifier i
INNER JOIN Organization USING(OrganizationId)
INNER JOIN RefOrganizationIdentificationSystem rbd
ON i.RefOrganizationIdentificationSystemId = rbd.RefOrganizationIdentificationSystemId
AND i.RefOrganizationIdentificationSystemId = (
SELECT RefOrganizationIdentificationSystemId
FROM RefOrganizationIdentificationSystem
WHERE Code = 'RBD' )
INNER JOIN RefOrganizationIdentifierType Mineduc
ON i.RefOrganizationIdentifierTypeId = Mineduc.RefOrganizationIdentifierTypeId
AND i.RefOrganizationIdentifierTypeId = (
SELECT RefOrganizationIdentifierTypeId
FROM RefOrganizationIdentifierType
WHERE Code = 'Mineduc' )) as ee on ee.OrganizationId=rsModalidad.Parent_OrganizationId
INNER JOIN (
SELECT
OrganizationPersonRoleId
, OrganizationId
, PersonId
, LastName as 'apellidoPaternoDocenteLiderCurso'
, SecondLastName as 'apellidoMaternoDocenteLiderCurso'
, FirstName as 'primerNombreDocenteLiderCurso'
, MiddleName as 'otrosNombresDocenteLiderCurso'
, runDocenteLiderCurso
FROM K12StaffAssignment
INNER JOIN OrganizationPersonRole USING(OrganizationPersonRoleId)
INNER JOIN (
SELECT DISTINCT
Person.PersonId
,Person.LastName
,Person.SecondLastName
,Person.FirstName
,Person.MiddleName
,rut.Identifier as RunDocenteLiderCurso
FROM Person
INNER JOIN PersonIdentifier rut ON rut.PersonId = Person.PersonId
AND rut.RefPersonIdentificationSystemId = 51
) USING(PersonId)
WHERE RefTeachingAssignmentRoleId = 1
) profesorJefe ON OrganizationIdDelCurso = profesorJefe.OrganizationId
WHERE curso.RefOrganizationTypeId = 21
) curso ON Opr.OrganizationId = curso.OrganizationIdDelCurso
LEFT OUTER JOIN (
Select MAX(CalendarYear) as 'AñoCalendario', OrganizationId
FROM OrganizationCalendar
) oc ON oc.OrganizationId=curso.OrganizationIdDelCurso
ORDER BY nivel, grado, letraCurso, StudentListNumber
""")
print('Estudiantes.returns_rows->',estudiantes.returns_rows)
if(estudiantes.returns_rows):
rows = estudiantes.fetchall()
if(len(rows)==0):
raise Exception("No se encuentra ningún dato de los studiante")
else:
raise Exception("No se encuentra ningún dato de los estudiantes")
runEstudiante = list(set([m[7] for m in rows]))
if(False in [validarRut(run) for run in runEstudiante]):
print([run for run in runEstudiante if not validarRut(run)])
raise Exception("Existe RUN inválidos (runEstudiante)")
runPadres = list(set([m[16] for m in rows if m[16] is not None]))
runPadresErroneos = [run for run in runPadres if not validarRut(run)]
if(len(runPadresErroneos)>0):
print(runPadresErroneos)
raise Exception("Existe RUN inválidos (runPadres)")
runMadres = list(set([m[21] for m in rows if m[21] is not None]))
runMadresErroneos = [run for run in runMadres if not validarRut(run)]
if(len(runMadresErroneos)>0):
print(runMadresErroneos)
raise Exception("Existe RUN inválidos (runMadres)")
runTutores = list(set([m[26] for m in rows if m[26] is not None]))
runTutoresErroneos = [run for run in runTutores if not validarRut(run)]
if(len(runTutoresErroneos)>0):
print(runTutoresErroneos)
raise Exception("Existe RUN inválidos (runTutores)")
#Teléfonos con formato E164
phoneTutor = list(set([m[27] for m in rows if m[27] is not None]))
r = re.compile('^\+56\d{9,15}$')
phoneTutorErroneos = [phone for phone in phoneTutor if not r.match(phone)]
if(len(phoneTutorErroneos)>0):
print(phoneTutorErroneos)
raise Exception("El teléfono del tutor no tiene el formato correcto")
emailTutor = list(set([m[28] for m in rows if m[28] is not None]))
emailTutoresErroneos = [email for email in emailTutor if not valida_email(email)]
if(len(runTutoresErroneos)>0):
print(emailTutoresErroneos)
raise Exception("Existe un email inválido (emailTutor)")
# CAMBIA CLAVE A LA BD Y CREA ARCHIVO CON CLAVE PARA LA SIE
print("# CAMBIA CLAVE A LA BD Y CREA ARCHIVO CON CLAVE PARA LA SIE")
conn.execute(f"PRAGMA key = '{secPhase}';")
psw = ''.join(random.choice(string.ascii_uppercase + string.ascii_lowercase + string.digits) for _ in range(50))
tiempoPromedioDesifrado = pow(26+26+10,50)/4000000000
print(psw,tiempoPromedioDesifrado)
text_file = open("key.txt", "w");text_file.write(psw);text_file.close()
psw2 = encryptTextUsingSiePublicKey(psw)
text_file = open("key.encrypted", "wb");text_file.write(psw2);text_file.close()
conn.execute(f"PRAGMA rekey = '{psw}';")
except Exception as e:
_t = "ERROR COMMIT: "+str(e)
print(_t);dfLog=dfLog.append(pd.Series({'result': _t}),ignore_index=True);
_r = False
finally:
#closind database connection
conn.close()
return _r
In [0]:
def cargarBaseDeDatos():
global dfLog
idFile = '1hqAjAknc6dY720X5zO_ZU2FqI_mZa3nB'
url_to_zipDB_file = f'http://drive.google.com/uc?export=download&id={idFile}'
r = requests.get(url_to_zipDB_file, stream=True)
fileName = 'ceds-nds-v7_1_encryptedD3.db'
with open(fileName,'wb') as out:
out.write(io.BytesIO(r.content).read()) ## Read bytes into file
path_to_DB_file = os.path.join(os.path.dirname(fileName), fileName)
_t=f"Base de datos: '{path_to_DB_file}' descomprimida exitosamente "; print(_t)
dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return path_to_DB_file
In [0]:
#----------------------------------------------------------------------------
#PASO N° 10 - Preparar ambiente de trabajo
#----------------------------------------------------------------------------
# Clean o Create CSV directory
#----------------------------------------------------------------------------
def cleanDirectory(d):
global dfLog
if(not os.path.exists(d)):
os.mkdir(d)
else:
for root, dirs, files in os.walk(d, topdown=False):
for name in files:
os.remove(os.path.join(root, name))
for name in dirs:
os.rmdir(os.path.join(root, name))
_t = f'Directorio : {d} limpio y preparado con éxito!!!'; print(_t);
dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
return True
In [0]:
dfLog = pd.DataFrame(columns=['json', 'csv', '#savingRows', '#readingRows',
'resultSaving', 'resultReading', 'result'])
_encode = 'utf8' #Opciones Windows:'cp1252', Google Colab: 'utf8'
_sep = ';' #Opciones Windows:';', Google Colab: ','
def main():
global dfLog, _encode, _sep
tiempo_inicial = time()
now = datetime.now(pytz.timezone('Chile/Continental'))
t_stamp = datetime.timestamp(now)
path_to_dir_csv_file = './csv/'
path_to_DB_file = cargarBaseDeDatos()
path_to_zip_file = '_tmp_json_librodigital_mineduc_8833_02enero2020.zip' #Ingresar solo nombre del archivo
if(cleanDirectory(path_to_dir_csv_file)):
if(readJsonSaveCSV(path_to_zip_file,path_to_dir_csv_file)):
if(transferCSVToSQL_withPandas(path_to_dir_csv_file,path_to_DB_file,t_stamp)):
#zipFilesInDir (path_to_dir_csv_file, './'+str(int(t_stamp))+'_Data.zip',lambda name : 'csv' in name);
cleanDirectory(path_to_dir_csv_file)
_nameErrorFile = str(int(t_stamp))+'_ERRORES.csv'
dfLog.to_csv(_nameErrorFile, sep=_sep, encoding=_encode, index=False)
zip = ZipFile('./'+str(int(t_stamp))+'_Data.zip','a')
zip.write('./ceds-nds-v7_1_encryptedD3.db')
zip.write('./key.txt');zip.write('./key.encrypted')
zip.write('./'+str(int(t_stamp))+'_ERRORES.csv')
zip.close()
_t = "Proceso finalizado correctamente!!!"
print(_t);dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
else:
cleanDirectory(path_to_dir_csv_file)
_nameErrorFile = str(int(t_stamp))+'_ERRORES.csv'
dfLog.to_csv(_nameErrorFile, sep=_sep, encoding=_encode, index=False)
_t = "Proceso finalizado con ERRORES!!!"
print(_t);dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
if os.path.exists('./ceds-nds-v7_1_encryptedD3.db'):
os.remove('./ceds-nds-v7_1_encryptedD3.db')
if os.path.exists('./key.txt'):
os.remove('./key.txt')
if os.path.exists('./key.encrypted'):
os.remove('./key.encrypted')
_t = f'El tiempo de ejecucion fue: {str(time() - tiempo_inicial)}'
print(_t);dfLog = dfLog.append(pd.Series({'result': _t}), ignore_index=True)
del dfLog, tiempo_inicial, now, path_to_dir_csv_file
del path_to_zip_file, _t
if __name__== "__main__":
main()