Databases - Fri, Nov 22

Create Table

The CREATE TABLE expression can take many arguments, but we'll focus on a subset:

CREATE TABLE IF NOT EXISTS table-name (column-def)

Examples of this include:

CREATE TABLE numbers (n, note);
CREATE TABLE numbers (n UNIQUE, note);
CREATE TABLE numbers (n, note DEFAULT 'No comment');

Drop Table

To drop a table you don't need anymore, use:

DROP TABLE IF EXISTS table-name

Insert Into Table

To insert a value (or a set thereof) into a table, use:

INSERT INTO table-name (column-name) VALUES (expr)

For example, if you have a table with two columns, to insert into one column:

INSERT INTO t(column) VALUES (value)

To insert into both columns:

INSERT INTO t VALUES (value0, value1)

Update

To update a value or a column, use:

UPDATE table-name SET column-name = expr WHERE expr

Delete

To delete some rows, use:

DELETE FROM table-name WHERE expr

Python + SQL

The Python3 interpreter comes with a built-in sqlite3 connector.

import sqlite3

db = sqlite3.Connection('number.db')
db.executre('CREATE TABLE nums AS select 2, 3 UNION SELECT 4, 5;')
db.execute('INSERT INTO nums VALUES (?, ?), (?, ?);', range(6, 10))
print(db.execute('SELECT * FROM nums;').fetchall())

This code prints [(2, 3), (4, 5), (6, 7), (8, 9)]

We can also save our database to a file:

db.commit()

This will create a file in the project directory called number.db which will contain the table nums.

SQL Injection Attack

Don't use db.executescript().