From 3b22586890425c8b8e555bce649649228b26c02b Mon Sep 17 00:00:00 2001 From: Mitsuo Tokumori Date: Sun, 29 Oct 2023 14:28:43 -0500 Subject: Add students blueprint using psycopg --- .gitignore | 2 + tests/test_auth.py | 2 +- ustayml/__init__.py | 7 +- ustayml/db.py | 19 +++-- ustayml/schema.sql | 141 ++++++++++++++++++++++++++++++++-- ustayml/static/img/logo_pucp.svg | 11 +++ ustayml/static/logo_pucp.svg | 11 --- ustayml/static/style.css | 64 +++++++++++++-- ustayml/templates/auth/login.html | 2 +- ustayml/templates/base.html | 6 ++ ustayml/templates/blog/create.html | 2 +- ustayml/templates/students/index.html | 82 ++++++++++++++++++++ ustayml/views/auth.py | 8 +- ustayml/views/blog.py | 14 ++-- ustayml/views/students.py | 55 +++++++++++++ 15 files changed, 380 insertions(+), 46 deletions(-) create mode 100644 ustayml/static/img/logo_pucp.svg delete mode 100644 ustayml/static/logo_pucp.svg create mode 100644 ustayml/templates/students/index.html create mode 100644 ustayml/views/students.py diff --git a/.gitignore b/.gitignore index 1d9734b..80dc7e1 100644 --- a/.gitignore +++ b/.gitignore @@ -12,3 +12,5 @@ htmlcov/ dist/ build/ *.egg-info/ + +bin/ diff --git a/tests/test_auth.py b/tests/test_auth.py index 6684f43..d2735dc 100644 --- a/tests/test_auth.py +++ b/tests/test_auth.py @@ -12,7 +12,7 @@ def test_register(client, app): with app.app_context(): assert get_db().execute( - "SELECT * FROM user WHERE username = 'a'", + "SELECT * FROM \"user\" WHERE username = 'a'", ).fetchone() is not None diff --git a/ustayml/__init__.py b/ustayml/__init__.py index 1c411e7..135ef57 100644 --- a/ustayml/__init__.py +++ b/ustayml/__init__.py @@ -35,8 +35,11 @@ def create_app(test_config=None): from .views import auth app.register_blueprint(auth.bp) - from .views import blog - app.register_blueprint(blog.bp) + # from .views import blog + # app.register_blueprint(blog.bp) + + from .views import students + app.register_blueprint(students.bp) # Extra app.add_url_rule('/', endpoint='index') diff --git a/ustayml/db.py b/ustayml/db.py index eb4e8b4..8fb1ec9 100644 --- a/ustayml/db.py +++ b/ustayml/db.py @@ -1,4 +1,4 @@ -import sqlite3 +import psycopg import click from flask import current_app, g @@ -10,12 +10,17 @@ def get_db(): sqlite3: https://docs.python.org/3/library/sqlite3.html """ if 'db' not in g: - g.db = sqlite3.connect( - current_app.config['DATABASE'], - detect_types=sqlite3.PARSE_DECLTYPES + # g.db = sqlite3.connect( + # current_app.config['DATABASE'], + # detect_types=sqlite3.PARSE_DECLTYPES + # ) + # # Return rows that behave like dicts + # g.db.row_factory = sqlite3.Row + + g.db = psycopg.connect( + "dbname=ustayml user=mitsuo", + row_factory=psycopg.rows.dict_row ) - # Return rows that behave like dicts - g.db.row_factory = sqlite3.Row return g.db @@ -33,7 +38,7 @@ def init_db(): db = get_db() with current_app.open_resource('schema.sql') as f: - db.executescript(f.read().decode('utf8')) + db.execute(f.read().decode('utf8')) @click.command('init-db') diff --git a/ustayml/schema.sql b/ustayml/schema.sql index be76d7e..8062be8 100644 --- a/ustayml/schema.sql +++ b/ustayml/schema.sql @@ -1,17 +1,144 @@ -DROP TABLE IF EXISTS user; -DROP TABLE IF EXISTS post; +-- System -CREATE TABLE user ( - id INTEGER PRIMARY KEY AUTOINCREMENT, +CREATE TABLE user_role ( + id SERIAL PRIMARY KEY, + name TEXT +); + +CREATE TABLE "user" ( + id SERIAL PRIMARY KEY, username TEXT UNIQUE NOT NULL, - password TEXT NOT NULL + password TEXT NOT NULL, + role_id INTEGER NOT NULL, + FOREIGN KEY (role_id) REFERENCES user_role (id) ); CREATE TABLE post ( - id INTEGER PRIMARY KEY AUTOINCREMENT, + id SERIAL PRIMARY KEY, author_id INTEGER NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, title TEXT NOT NULL, body TEXT NOT NULL, - FOREIGN KEY (author_id) REFERENCES user (id) + FOREIGN KEY (author_id) REFERENCES "user" (id) +); + +-- Students + +create table pucp_unit ( + id serial primary key, + "name" text +); + +create table administrative_division ( + id serial primary key, + "name" text, + country text, + subdivision1 text, + subdivision2 text, + subdivision3 text, + subdivision10 text, + ubigeo text, + "population" integer, + poverty real +); + +CREATE TABLE student ( + id SERIAL PRIMARY KEY, + pucp_code INTEGER, + first_name TEXT, + last_name TEXT, + email TEXT, + -- district TEXT, + administrative_division_id integer, + pucp_unit_id integer, + tuition_bracket TEXT, + current_semester integer, + current_attendance REAL, + current_craest REAL, + current_ppne3 REAL, + current_merit REAL, + est_study_length INTEGER, + est_dessertion_risk TEXT, + FOREIGN KEY (administrative_division_id) REFERENCES administrative_division (id), + FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id) ); + +-- alter table student +-- add constraint fk_administrative_division +-- FOREIGN KEY (administrative_division_id) +-- REFERENCES administrative_division (id) +-- ; + +-- Sample data (later move to tests) + +INSERT INTO user_role ("id", "name") VALUES + (1, 'admin'), + (2, 'user') +; + +INSERT INTO "user" ("username","password","role_id") VALUES + ('mitsuo','scrypt:32768:8:1$DNNf1Ah1SwUcFHIU$c0e40293c9ecff498b74e7727a8d9a348371262b85cc880e8c653438b1afa84849adb7230f4fcd995c82de543a7836a5cda2a411a45c17dc653e7036fe3de482',1) +; + +INSERT INTO pucp_unit ("name") VALUES + ('Facultad de Ciencias e Ingenieria'), + ('Estudios Generales Ciencias'); + +-- sample 2019 data +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','','','','','',29009326,28.9,'country'), + ('Peru','Lima','','','','150000',8981440,15.4,'region'), + ('Peru','Lima','Lima','','','150100',8095747,17.5,'province'), + ('Peru','Lima','Lima','LIMA ','Lima Centro','150101',302056,12.3,'district'), + ('Peru','Lima','Lima','ANCÓN ','Lima Norte','150102',36401,19.6,'district'), + ('Peru','Lima','Lima','ATE ','Lima Este','150103',521692,18.9,'district'), + ('Peru','Lima','Lima','BARRANCO ','Lima Centro','150104',33996,5.3,'district'), + ('Peru','Lima','Lima','BREÑA ','Lima Centro','150105',82987,8.5,'district'), + ('Peru','Lima','Lima','CARABAYLLO ','Lima Norte','150106',237269,26.3,'district'), + ('Peru','Lima','Lima','CHACLACAYO ','Lima Este','150107',42884,10.1,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','CHORRILLOS ','Lima Centro','150108',303913,17.2,'district'), + ('Peru','Lima','Lima','CIENEGUILLA ','Lima Este','150109',31160,24.8,'district'), + ('Peru','Lima','Lima','COMAS ','Lima Norte','150110',509976,22.3,'district'), + ('Peru','Lima','Lima','EL AGUSTINO ','Lima Este','150111',188138,22.1,'district'), + ('Peru','Lima','Lima','INDEPENDENCIA ','Lima Norte','150112',215941,21.3,'district'), + ('Peru','Lima','Lima','JESÚS MARÍA ','Lima Centro','150113',71139,1.7,'district'), + ('Peru','Lima','Lima','LA MOLINA ','Lima Este','150114',144491,0.7,'district'), + ('Peru','Lima','Lima','LA VICTORIA ','Lima Centro','150115',193592,14.9,'district'), + ('Peru','Lima','Lima','LINCE ','Lima Centro','150116',55733,4.0,'district'), + ('Peru','Lima','Lima','LOS OLIVOS ','Lima Norte','150117',339028,13.4,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','LURIGANCHO ','Lima Este','150118',184593,24.4,'district'), + ('Peru','Lima','Lima','LURÍN ','Lima Sur','150119',69282,30.2,'district'), + ('Peru','Lima','Lima','MAGDALENA DEL MAR ','Lima Centro','150120',54116,2.3,'district'), + ('Peru','Lima','Lima','PUEBLO LIBRE','Lima Centro','150121',77892,2.0,'district'), + ('Peru','Lima','Lima','MIRAFLORES ','Lima Centro','150122',86920,0.8,'district'), + ('Peru','Lima','Lima','PACHACAMAC ','Lima Este','150123',81145,32.9,'district'), + ('Peru','Lima','Lima','PUCUSANA ','Lima Sur','150124',12148,26.7,'district'), + ('Peru','Lima','Lima','PUENTE PIEDRA ','Lima Norte','150125',263594,35.6,'district'), + ('Peru','Lima','Lima','PUNTA HERMOSA ','Lima Sur','150126',6309,7.4,'district'), + ('Peru','Lima','Lima','PUNTA NEGRA ','Lima Sur','150127',5951,9.5,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','RIMAC ','Lima Centro','150128',178869,16.5,'district'), + ('Peru','Lima','Lima','SAN BARTOLO ','Lima Sur','150129',6368,9.7,'district'), + ('Peru','Lima','Lima','SAN BORJA ','Lima Centro','150130',111208,0.8,'district'), + ('Peru','Lima','Lima','SAN ISIDRO ','Lima Centro','150131',58920,0.6,'district'), + ('Peru','Lima','Lima','SAN JUAN DE LURIGANCHO ','Lima Este','150132',962554,27.0,'district'), + ('Peru','Lima','Lima','SAN JUAN DE MIRAFLORES ','Lima Sur','150133',382531,19.7,'district'), + ('Peru','Lima','Lima','SAN LUIS ','Lima Este','150134',57080,7.1,'district'), + ('Peru','Lima','Lima','SAN MARTÍN DE PORRES ','Lima Norte','150135',620193,10.9,'district'), + ('Peru','Lima','Lima','SAN MIGUEL ','Lima Centro','150136',134666,2.3,'district'), + ('Peru','Lima','Lima','SANTA ANITA ','Lima Este','150137',199282,12.0,'district'); +INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES + ('Peru','Lima','Lima','SANTA MARÍA DEL MAR ','Lima Sur','150138',924,4.0,'district'), + ('Peru','Lima','Lima','SANTA ROSA ','Lima Norte','150139',12641,12.0,'district'), + ('Peru','Lima','Lima','SANTIAGO DE SURCO ','Lima Centro','150140',309889,3.3,'district'), + ('Peru','Lima','Lima','SURQUILLO ','Lima Centro','150141',93271,5.2,'district'), + ('Peru','Lima','Lima','VILLA EL SALVADOR ','Lima Sur','150142',410313,25.9,'district'), + ('Peru','Lima','Lima','VILLA MARÍA DEL TRIUNFO ','Lima Sur','150143',404692,21.1,'district'); + + +INSERT INTO student (pucp_code,first_name,last_name,email,administrative_division_id,pucp_unit_id,tuition_bracket,current_semester,current_attendance,current_craest,current_ppne3,current_merit,est_study_length,est_dessertion_risk) VALUES + (20171234,'Alberto','Alvarez','aalvarez@pucp.edu.pe',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L'), + (20171235,'Benito','Bueno','bbueno@pucp.edu.pe',41,2,'G5',5,0.8,58.35,55.23,0.1532,10,'H'), + (20171236,'Carlos','Canto','ccanto@pucp.edu.pe',42,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L'); \ No newline at end of file diff --git a/ustayml/static/img/logo_pucp.svg b/ustayml/static/img/logo_pucp.svg new file mode 100644 index 0000000..8ffb164 --- /dev/null +++ b/ustayml/static/img/logo_pucp.svg @@ -0,0 +1,11 @@ + + + + + + + + + + + \ No newline at end of file diff --git a/ustayml/static/logo_pucp.svg b/ustayml/static/logo_pucp.svg deleted file mode 100644 index 8ffb164..0000000 --- a/ustayml/static/logo_pucp.svg +++ /dev/null @@ -1,11 +0,0 @@ - - - - - - - - - - - \ No newline at end of file diff --git a/ustayml/static/style.css b/ustayml/static/style.css index 1a73cc5..08b5e9f 100644 --- a/ustayml/static/style.css +++ b/ustayml/static/style.css @@ -1,13 +1,14 @@ html { font-family: sans-serif; background: #eee; - padding: 1rem; + /* padding: 1rem; */ } body { + min-width: 480px; max-width: 960px; margin: 0 auto; - background: white; + /* background: white; */ } h1, @@ -31,14 +32,21 @@ hr { } nav { - background: lightgray; + min-height: 80px; + background: #042254; /* PUCP dark blue 1 */ display: flex; align-items: center; + justify-content: space-between; padding: 0 0.5rem; } +nav a, +nav span { + color: white; +} + nav h1 { - flex: auto; + /* flex: auto; */ margin: 0; } @@ -54,6 +62,11 @@ nav ul { padding: 0; } +.pad-navbar-links { + margin-left: 2rem; + margin-right: 2rem; +} + nav ul li a, nav ul li span, header .action { @@ -62,6 +75,8 @@ header .action { } .content { + background: white; + margin-top: 1rem; padding: 0 1rem 1rem; } @@ -76,6 +91,22 @@ header .action { margin: 1rem 0 0.25rem 0; } +.content > .filters { + margin-top: 1em; + margin-bottom: 1em; +} + +.content .filters-list { + display: flex; + flex-direction: row; + align-items: flex-end; + /* justify-content: center; */ +} + +.filters-list select { + margin-right: 2em; +} + .flash { margin: 1em 0; padding: 1em; @@ -83,6 +114,29 @@ header .action { border: 1px solid #377ba8; } +/* students */ + +.students-table { + padding: 2em; + width: 100%; + border-collapse: collapse; +} + +.students-table th, +.students-table td { + border: 1px solid #ccc; + padding: 0.2em 0.2em; +} + +.report-info { + display: flex; + justify-content: space-between; + padding: 5em; + margin-top: 5em; +} + +/* post */ + .post > header { display: flex; align-items: flex-end; @@ -111,7 +165,7 @@ header .action { margin-bottom: 0; } -.content form { +.content .simple-form { margin: 1em 0; display: flex; flex-direction: column; diff --git a/ustayml/templates/auth/login.html b/ustayml/templates/auth/login.html index b7dd5dc..45acd7f 100644 --- a/ustayml/templates/auth/login.html +++ b/ustayml/templates/auth/login.html @@ -5,7 +5,7 @@ {% endblock %} {% block content %} -
+ diff --git a/ustayml/templates/base.html b/ustayml/templates/base.html index 1b2c384..b8d243c 100644 --- a/ustayml/templates/base.html +++ b/ustayml/templates/base.html @@ -4,6 +4,12 @@