<jupyter_start><jupyter_text>DevOpsLab Jupyter: Notebook example

 Using a Database system

These are only examples to show the advantage of having all-in-one for Day-to-day Operations in a Jupyter Runbook

 Pre-requisites

1. Postgress database engine must be installed in the system (with a docker-compose statement or directly in the host)
2. Postgress python modules must be loaded in the container as startup scripts  1.Restart the PostgreSQL server and re-create the DEMO database

Because it uses for repeatable demos, so a restart and database re-creation is required<jupyter_code>%%bash -x
pg_ctl.exe restart --pgdata="/data"
psql.exe -c "drop database if exists DEMO;" postgres
psql.exe -c "create database DEMO;" postgres<jupyter_output><empty_output><jupyter_text>load the library and connect to DEMOuse `%reload_ext` to avoid the "The sql extension is already loaded." error with `%load_ext`<jupyter_code>%reload_ext sql
%sql postgresql://localhost/demo<jupyter_output><empty_output><jupyter_text>latest PostgreSQL release:<jupyter_code>pg_version=%sql select version()
print(pg_version)<jupyter_output><empty_output><jupyter_text>Create a demo table

Remember that Jupyter execute all the cell and, then, shows the output. Therefore, many sql statements can be executed at the same time but all output is displayed at the end.<jupyter_code>%%sql
drop table if exists EMP;
drop table if exists DEPT;
CREATE TABLE DEPT
       (DEPTNO integer CONSTRAINT PK_DEPT PRIMARY KEY,
        DNAME text ,
        LOC text ) ;
CREATE TABLE EMP
       (EMPNO integer CONSTRAINT PK_EMP PRIMARY KEY,
        ENAME text,
        JOB text,
        MGR integer,
        HIREDATE date,
        SAL integer,
        COMM integer,
        DEPTNO integer CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','LOGROÑO');
INSERT INTO DEPT VALUES (20,'RESEARCH','MADRID');
INSERT INTO DEPT VALUES (30,'SALES','BUENOS AIRES');
INSERT INTO DEPT VALUES (40,'OPERATIONS','MEXICO D.F.');
INSERT INTO EMP VALUES (3500,'<NAME>','DIRECTOR',3500,to_date('1-01-2001','dd-mm-yyyy'),3000,NULL,40);
INSERT INTO EMP VALUES (3501,'<NAME>','COMERCIAL',3500,to_date('3-12-2003','dd-mm-yyyy'),2900,NULL,30);
INSERT INTO EMP VALUES (3502,'<NAME>','IN<NAME>',3501,to_date('3-12-2003','dd-mm-yyyy'),2900,NULL,20);
INSERT INTO EMP VALUES (3503,'<NAME>','ADMINISTRATIVO',3500,to_date('23-1-2021','dd-mm-yyyy'),1300,NULL,10);<jupyter_output><empty_output><jupyter_text>PSQL special commandsThanks to the PGspecial plug-in, 'backslash' commands can be used<jupyter_code>%sql \dt<jupyter_output><empty_output><jupyter_text>Make a query to retrieve some rows with SQLBind variables can be used in the query statements!<jupyter_code>deptno=10
%sql select * from DEPT join EMP using (DEPTNO) where deptno=:deptno<jupyter_output><empty_output><jupyter_text>By default, the output is nicely formated in an HTML tableThe previous result is accessible with `_` and can be displayed as an ASCII table<jupyter_code>print(_)<jupyter_output><empty_output><jupyter_text>Access to the first row:<jupyter_code>print(_[0])<jupyter_output><empty_output><jupyter_text>Display the column names:<jupyter_code>print(_.keys)<jupyter_output><empty_output><jupyter_text>Access to one column by index or by name:<jupyter_code>print(_[0][4])
print(_[0]['hiredate'])<jupyter_output><empty_output><jupyter_text>Graphical displays

Using python mathplotlib library, and some functions like .plot(), .pie(), and .bar(), the Runbook can also shown graphical displays to help us.<jupyter_code>r=%sql select dname,avg(sal) from DEPT join EMP using (DEPTNO) group by dname
%matplotlib inline
r.bar()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10411622276029056, 'fraction_numerical': 0.02717245090126446, 'mean_word_length': 5.7355072463768115, 'pattern_counts': {'":': 0, '<': 50, '<?xml version=': 0, '>': 50, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '422847', 'license': 'Apache-2.0', 'n_tokens_mistral': 1317, 'n_tokens_neox': 1241, 'n_words': 367, 'path': 'runbooks/4. DevOps Lab Work with DB Postgres.ipynb', 'repo_name': 'JuanLuisGozaloFdez/DevOpsLab4-Jupyter', 'size': '3752'}