SQL

Structured Query Language: The basics

Structured Query Language(SQL) is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert, etc. to carry out the required tasks.

These SQL commands are mainly categorized into four categories as

• Data definition language (DDL): CREATE, DROP, ALTER, ADD, CHECK

• Data Manipulation language (DML): SELECT, INSERT INTO, UPDATE, DELETE, SET

• Data Authorization language (DAL): GRANT, REVOKE

• Data control language (DCL):CREATE INDEX

See also https://www.w3schools.com/SQL/

The most commonly used are explained below

CREATE A TABLE

A table is created with the statement CREATE TABLE <tablename> ( <attributename> <datatype>, <attributename> <datatype>.....)

CREATE TABLE Users( 
  name VARCHAR(128), 
  email VARCHAR(128)
)

There are many more configurations for a table, such as auto-increment keys, unique conditions, or not null conditions. These are required when you want to relate data tables to each other, which is often the case in a relational database. In the example below an automated index is created as a primary key and the pubsource_id is the foreign key referring to the primary key in another table. Furthermore, there is a restriction on uniqueness for the title of the Article.

CREATE TABLE Article (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    pubsource_id  INTEGER,
    title   TEXT UNIQUE
);

SQL INSERT INTO .... VALUES

The Insert statement inserts a row into a table, for each attribute, a value is given

INSERT INTO Users (name, email) VALUES ('funny', '[e-mail]')

SQL UPDATE .... SET

The UPDATE statement allows the updating of a field with a WHERE clause

UPDATE Users SET name='fenna' WHERE email='[e-mail]'

SQL SELECT ... FROM

The SELECT statement retrieves a group of records - you can either retrieve all the records or a subset of the records with a WHERE clause. The star * means all the attributes

SELECT * FROM Users
SELECT * FROM Users WHERE email='[e-mail]'
SELECT name FROM Users

SQL DELETE FROM

The statement DELETEDeletes a row in a table based on selection criteria

DELETE FROM Users WHERE email='[e-mail]'

Sorting with ORDER BY

You can add an ORDER BY clause to SELECT statements to get the results sorted in ascending or descending order

SELECT * FROM Users ORDER BY name DESC

Mind you! You have to commit changes

Since more clients can assess and modify the data at the same time it is important to control these changes. Supose you want to retrieve data but some other client wants to delete the same data? In order to control this data is modified on the client side and only if all changes are completed, then use connection.commit() to persist the changes in the database to maintain the data integrity and consistency. If commit() is not called, the effect of any data manipulation will get lost.

If any of the operations fail inside of a running transaction, you can revert the changes made by all of the previous operations using a connection.rollback()

Pandas and SQL

Pandas has a read_sql_query function that can read tables from querys. It needs the SQL query in the data manipulation language and a connection.

import pandas as pd
df = pd.read_sql_query('SELECT * FROM Artist', conn)
print(type(df))
df

	id	name
0	1	Opeth
1	2	Drake