SQL-Basics: Create – Read – Update – Delete

This episode is about the basic statements needed to create, read, update and delete data in a database system.

Let’s assume we work as a data scientist for Knight Industries.  We want to help the Foundation of Law and Government to keep track of our operatives.

We decide to use a classic relational database management system or RDBMS. In order to explore Database Management Systems we can either install one locally or we can use an online tool like SQLFiddle.

To interact with RDBMS we use SQL – the Structured Query Language.

As the name says SQL (speak either S-Q-L or Sequel) is used to write structured queries. Think of “conversations” when You think of “queries”.

So, let’s fire up SQLFiddle.

At first we have an empty database, so we create a table named operatives with the following columns: id, name and birthday

CREATE TABLE operatives 
(id INTEGER, name TEXT, birthday DATE);

So we learned our first SQL statement: CREATE TABLE. It does exactly what its name suggest: create a table in a database.

 

We add Michael Arthur Long to our operatives:

INSERT INTO operatives (id, name, birthday) VALUES 
(1, "Michael Arthur Long", "1949-01-09");

Good practice: Although You could insert the first record into the database without repeating the column names it is considered a good practice to state the names of the columns you want to insert your data into.

We add three more of our employees to the operatives table.

INSERT INTO operatives (id, name, birthday)VALUES 
(2, "Devon Miles", "1942-07-12");
INSERT INTO operatives (id, name, birthday)VALUES 
(3, "Dr. Bonnie Barstow", "1954-11-24");
INSERT INTO operatives (id, name, birthday)VALUES 
(4, "Reginald Cornelius III", "1952-05-04");

Michael Arthur Long gets his new name: Michael Knight. We update his record in our database.

UPDATE operatives SET name = "Michael Knight" WHERE id = 1;

We want to track the different occupations of the team members and add a new column to operatives table.

ALTER TABLE operatives ADD COLUMN occupation TEXT;

Update the occupation of all operatives

UPDATE operatives SET occupation = "Field Agent" WHERE id = 1;
UPDATE operatives SET occupation = "Head of Operations" WHERE id = 2;
UPDATE operatives SET occupation = "Technician" WHERE id = 3;
UPDATE operatives SET occupation = "Truck Driver" WHERE id = 4;

Bonnie left in 1983 to pursue graduate studies. We remove her from operatives.

DELETE FROM operatives WHERE id = 3;

We add April Curtis as Head of Design Team.

INSERT INTO operatives VALUES 
(5, "April Curtis", "1952-05-04", "Senior Design Officer");

To evaluate ebery step of our implementation we can always use:

SELECT * FROM operatives;

Python 3 – there shall be just int

Trying to contribute to the Flask plugin flask-login I just added these lines:

if isinstance(duration, (int, long)):
    duration = timedelta(seconds=duration)

Looking quite plausible, isn’t it? But lo and behold: it doesn’t work under Python 3.x. Dang!

The reason: Python 2 has two integer types: int and long. In Python 3 there is only int, which makes it necessary to distinguish between these two major versions. I’ve found a nice page which deals with this issue. Here is what You must do to make it work in both Python 2 and 3:

import sys
if sys.version_info < (3,):
    integer_types = (int, long,)
else:
    integer_types = (int,)

isinstance(1, integer_types)

 

Lint your JavaScript with grunt and jshint

After I’ve introduced You to Yarn I will show You more client side tools in this post.

Grunt is a task runner which comes in handy for a lot of setup and configuring work e.g. concatenating and minimizing js or css files.

To get started You can add grunt via yarn to your project

yarn add grunt

Configuration

Grunt looks for a Gruntfile.js file in your root directory. Yes it is Gruntfile.js with a capital ‘G’. An empty

Linting with JShint

yarn add grunt-contrib-jshint
module.exports = function(grunt) {
  grunt.initConfig({
    jshint: {
      all: ['Gruntfile.js', '/js/*.js'],
      options: {
        globals: {
          jQuery: true
        }
      }
    }
  });
  grunt.loadNpmTasks('grunt-contrib-jshint');
  grunt.registerTask('lint', ['jshint']);
};

This configuration will print all findings directly onto the console which is nice for testing the script but when you burn down your lint issues a file comes in handy.

Configuring JSHint’s Output Format

To visualize the findings in Jenkins you can configure the checkstyle format. It produces a xml file which you can use inside Jenkins’ checkstyle plugin.

When You want to have a more human readable format you can generate an html report. Therefore You have to install the jshint-html-reporter:

yarn add jshint-html-reporter

and configure the JSHint task accordingly.

options: {
   reporter: require('jshint-html-reporter'),
   reporterOutput: 'jshint.html'
 },

You can have both configurations in one file

module.exports = function(grunt) {

  grunt.initConfig({
    jshint: {
      options: {
        globals: {
          jQuery: true
        }
      },
      src_files: ['Gruntfile.js', 'app/static/js/*.js'],
      local: {
        options: {
          reporter: require('jshint-html-reporter'),
          reporterOutput: 'jshint.html'
        },
        src: [ "<%= jshint.src_files %>" ]
      },
      jenkins: {
        options: {
            reporter: 'checkstyle',
            reporterOutput: 'jshint.xml'
        },
        src: [ "<%= jshint.src_files %>" ]
    }
    }
  });
  grunt.loadNpmTasks('grunt-contrib-jshint');
  grunt.registerTask('lint', ['jshint:jenkins']);
  grunt.registerTask('lint-local', ['jshint:local']);
};

 

Node.js with PyCharm

PyCharm amazes me every time. Although it is first and foremost a python IDE you can do full stack web development with it, i. e. you have code completion for HTML, CSS, JavaScript and even TypeScript, CoffeeScript.

I wanted to code some node.js stuff and jetbrains already got a plugin for PyCharm. Before installation:

https://www.jetbrains.com/help/pycharm/2017.1/node-js-and-npm.html

The installation is painless and after a restart you have full node.js support!