Python 3

home

SQL Part 1: Databases and SQLite3 Client

RDBMS (Relational Database Management System) Tables

A relational database stores data in tabular form (columns and rows)


A database table is a tabular structure (like CSV) stored in binary form which can only be displayed through a database client or programmatically through a database driver (i.e., to allow a language like Python to read and write to it). The database client program is provided by the database and allows command-prompt access to the database. Every database provides its own client program. A database driver is a module that provides programmatic access to the database. Python has a full suite of drivers for most major databases (mariadb, oracle, postrgres, etc.) Common Databases SQLite (the system we will use in this course) is a file-based RDBMS that is extremely lightweight and requires no installation. It also supports many SQL statements although its data types are more limited. It is claimed to be the most-used database on the planet because it can work in very small environments (for example "internet of things" devices.) It is also ideal for learning. mariadb (formerly known as mysql, can be colloquially referred to as either although mysql is now owned by Oracle) is a production-quality RDBMS that is free and fully embraced by the industry. It supports most SQL statements and is very similar to Oracle, postgres, SQL Server, etc. postgres is another free and full-featured database system similar to mysql. Oracle, Microsoft SQL Server and IBM DB2 are commercially sold databases. mongodb and Amazon DynamoDB are nosql databases, which use other schemes for storing and retrieving information such as key-value stores and document stores. It has advantages (such as distributability among servers) and disadvantages (such as instant data integrity) when compared to RDBMS.





Starting the SQLite3 Client, Opening a Database File and Formatting Client Output

Most databases come with a database client for issuing SQL queries from a command prompt, and each has its own commands and syntax.


We start the sqlite3 client from a Command Prompt or Terminal.

You can start the SQLite3 client from a command prompt with one of three commands:


Open a new database archive

sqlite3 new.db

If the file does not exist, this creates a brand new SQLite database file with the name new.db. If file does exist, opens the file.


Open an existing database archive:

sqlite3 session_2.db

(assumes that this file exists and is in current directory - if not, a new file is created)


Open an "in-memory" database (which can be saved later)

sqlite3

Please note that the syntax for opening a new file and opening an existing file are the same! This means that if you intend to open a new file but misspell the name, SQLite3 will simply create a new file; you'll then be confused to see that the file you thought you opened has nothing in it!

sqlite3 client column formatting


At the start of your session, issue the following two commands -- these will format your sqlite3 output so it is clearer, and add column headers.

sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM students;

              company     state       cost
              ----------  ----------  ----------
              Haddad's    PA          239.5
              Westfield   NJ          53.9
              The Store   NJ          211.5
              Hipster's   NY          11.98
              Dothraki F  NY          5.98
              Awful's     PA          23.95
              The Clothi  NY          115.2

You may note that columns are 10 characters wide and that longer values are cut off. You can set the width with values for each column for example .width 5 13 11 5 5 for the above table. Unfortunately this must be done separately for each table.





Viewing and Exploring Tables in your Database

sqlite3 client commands start with a dot; SQL queries end with a semicolon.


Show tables in this database:

sqlite> .tables
ad_buys              revenue              students
ad_companies         student_status       user
companyrev           student_status_orig  user_classes
sqlite>

Special note: if you don't see any tables, then you have created a new database. If you meant to open an existing database, then you may have misspelled the name or are currently in a different directory than the database file.


Show structure of a selected database:

sqlite> .schema revenue
CREATE TABLE revenue (company TEXT, state TEXT, cost REAL);

The available database column types are defined by the specific database. Most are very similar, with small variations between databases. sqlite data types include INTEGER, REAL (for floating-point values), TEXT (for string data), and BLOB (for other data)


View contents of a table:

sqlite> SELECT * FROM revenue;
company     state       cost
----------  ----------  ----------
Haddad's    PA          239.5
Westfield   NJ          53.9
The Store   NJ          211.5
Hipster's   NY          11.98
Dothraki F  NY          5.98
Awful's     PA          23.95
The Clothi  NY          115.2
sqlite>

Note that SQL queries end in a semicolon. If you see a continuation line indicator ( ...> ) this means that the client is waiting for more SQL -- queries can be expressed in more than one line. If you're done, simply type the semicolon and hit [Enter].





Relational Table Structure and CREATE TABLE

Table columns specify a data type.


Again, to view the structure of a table:

sqlite> .schema students
CREATE TABLE revenue (company TEXT, state TEXT, cost REAL);

.schema shows us the statement used to create this table. (In other databases, the DESC [tablename] statement shows table columns and types.) As you can see, each column is paired with a column type, which describes what kind of data can be stored in that column. To create a new table, we must specify a name and a type for each column.


The CREATE TABLE statement syntax is the same that you see from the .schema command:

CREATE TABLE revenue (company TEXT, state TEXT, cost REAL);

SQLite and mysql Column Types

valuesqlite3mysql, et al.
whole numberINTEGERall int types (TINYINT, BIGINT, INT, etc.)
floating-point numberREALFLOAT, DOUBLE, REAL, etc.
string dataTEXTCHAR, VARCHAR, etc.
non-typed data, including binary (image, etc.)BLOBBLOB


Note that sqlite3 datatypes are nonstandard and don't exactly match types found in databases such as mysql/MariaDB, Oracle, etc.:





Basic SQL Commands for SQLite3

This slide can serve as a reference for SQL commands needed for this session.



list common commands

sqlite> .help


show name and filename of open database

sqlite> .databases


save result of next command in excel (defaults to .csv format)

sqlite> .excel


exit sqlite3

sqlite> .exit


output result of subsequent queries to a file

sqlite> .output <filename>


save current database to a new file

sqlite> .save <filename>


run an OS (command line) command

sqlite> .system <command>


connect to a database (open a file)

sqlite> .open sqlite3_trades.db


show tables in the database

sqlite> .tables


describe a table

sqlite> .schema stocks


select specified columns from the table

sqlite> SELECT date, trans_type,
        symbol, qty FROM stocks;


select ALL columns from the table

sqlite> SELECT * FROM stocks;


select only rows WHERE a value is found

sqlite> SELECT date, symbol, qty
        FROM stocks
        WHERE trans_type = 'BUY';


INSERT INTO: insert a row

sqlite> INSERT INTO stocks
        (date, trans_type, symbol, qty)
        VALUES ('2015-01-01', 'BUY', 'MSFT', 10);


connect to database from Python

import sqlite3
conn = sqlite3.connect('sqlite3_trades.db')

c = conn.cursor()


execute a SELECT query from Python

c.execute('SELECT date, symbol, qty
           FROM stocks
           WHERE trans_type = 'BUY')


retrieve one row or many rows from a result set

c.execute('SELECT * FROM stocks
           ORDER BY price')

tuple_row = c.fetchone()      # tuple row

tuple_row = c.fetchmany(3)    # list of tuple rows - specify # of rows

tuple_rows = c.fetchall()     # list of tuple rows - entire result set


iterate over result set

c.execute('SELECT * FROM stocks
           ORDER BY price')

for tuple_row in c:
    print(tuple_row)


insert a row

c.execute("INSERT INTO stocks
           (date, trans_type, symbol, qty)
           VALUES ('2015-01-01', 'BUY', 'MSFT', 10)")

conn.commit()    # all changes to db must be committed








Sidebar: mysql / mariadb Commands

These commands are similar to those in SQLite3.



start mysql client (command-line utility)

$ mysql


show available databases

Maria DB []> show databases;


select a database to use (mysql remembers this selection)

Maria DB []> use trades;


show tables within a selected database

Maria DB [trades]> show tables;


show a table's structure

Maria DB [trades]> desc stocks;

A mysql database table description looks like this:

+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| date  | int(8) | YES  |     | NULL    |       |
| mktrf | float  | YES  |     | NULL    |       |
| hml   | float  | YES  |     | NULL    |       |
| smb   | float  | YES  |     | NULL    |       |
| rf    | float  | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+

"Field" is the column name. "Type" specifies the required data type for that column.


select rows and show specific columns

Maria DB [trades]> SELECT date, trans_type,
                   symbol, qty FROM stocks;


select rows and show all columns

Maria DB [trades]> SELECT * FROM stocks;


select rows that meet a condition

Maria DB [trades]> SELECT date, symbol, qty
                   FROM stocks
                   WHERE trans_type = 'BUY';


add a row to a table

Maria DB [trades]> INSERT INTO stocks
                   (date, trans_type, symbol, qty)
                   VALUES ('2015-01-01', 'BUY', 'MSFT', 10);


connect to mysql from Python (sample connect information)

import pymysql

host = 'localhost'
database = 'test'
port = 3306
username = 'ta'
password = 'pepper'

conn = pymysql.connect(host=host,
                       port=port,
                       user=username,
                       passwd=password,
                       db=database)

cur = conn.cursor()


select rows that meet a condition

cur.execute("SELECT date, symbol, qty
             FROM stocks
             WHERE trans_type = 'BUY'")


select rows and order by column value; retrieve one or many rows from result set

c.execute('SELECT * FROM stocks
                ORDER BY price')

tuple_row = c.fetchone()      # tuple row

tuple_rows = c.fetchmany()    # list of tuple rows


iterate over rows in result set

for tuple_row in c:
    print(tuple_row)


insert a table row

c.execute("INSERT INTO stocks
                (date, trans_type, symbol, qty)
                VALUES ('2015-01-01', 'BUY', 'MSFT', 10)")

conn.commit()    # all changes to db must be committed


Databases are cool. Just saying.





[pr]