Databases

Creating a Table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(6) unsigned NOT NULL,  
  `username` varchar(200) NOT NULL,
  `role` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
);

• NOT NULL - Each row must contain a value for that column, null values are not allowed
• DEFAULT value - Set a default value that is added when no other value is passed
• UNSIGNED - Used for number types, limits the stored data to positive numbers and zero
• AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each time a new record is added
• PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

Inserting Data

INSERT INTO `users` (`id`, `username`, `role`) VALUES
  ('1', 'Kelvin', 'Tutor'),
  ('2', 'Tina', 'Tutor'),
  ('3', 'Stephen', 'Tutor'),
  ('4', 'Artem', 'Lecturer');

Retrieving Data

全选

SELECT *
FROM users

选择所有列

选择某一列

SELECT username, role
FROM users

查询选择

SELECT *
FROM users
WHERE role = 'Tutor'

选择固定行数数据

SELECT *
FROM users
WHERE role = 'Tutor'
LIMIT 2

选择排序后结果

SELECT *
FROM users
WHERE role = 'Tutor'
ORDER BY id DESC

DESC 降序， ASC 升序[http://www.sqlitetutorial.net/sqlite-order-by/]

更新数据

UPDATE users
SET role = 'Lecturer'
WHERE username = 'Stephen';

先查后更新

删除数据

DELETE FROM users
WHERE username = 'Stephen';

先查后删除

Foreign key

FOREIGN KEY是用于将两个表链接在一起的键。

FOREIGN KEY是一个表中的一个字段（或字段集合），它引用另一个表中的PRIMARY KEY。

包含foreign key 的表称为子表，包含primary key的表称为引用表或父表。

FOREIGN KEY约束用于防止会破坏表之间链接的操作。

FOREIGN KEY约束还可以防止将无效数据插入到外键列中，因为它必须是它指向的表中包含的值之一。

CREATE TABLE IF NOT EXISTS staff (
  staff_id int(6) NOT NULL,
  username varchar(200) NOT NULL,
  PRIMARY KEY (staff_id)
);

INSERT INTO staff (staff_id, username) VALUES
  ('1', 'Kelvin'),
  ('2', 'Harrison'),
  ('3', 'Stephen'),
  ('4', 'Prasad'),
  ('5', 'David');

CREATE TABLE IF NOT EXISTS roles
(
  role_id int(6) NOT NULL,
  staff_id int(6) NOT NULL,
  role varchar(200),
  unit varchar(200),
  PRIMARY KEY (role_id),
  FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
);

INSERT INTO roles (role_id, staff_id, role, unit) VALUES
  ('1', '1', 'Tutor', 'BUSS6002'),
  ('2', '2', 'Tutor', 'BUSS6002'),
  ('3', '3', 'Lecturer', 'BUSS6002'),
  ('4', '3', 'Tutor', 'BUSS6002'),
  ('5', '4', 'Tutor', 'BUSS6002'),
  ('6', '1', 'Tutor', 'QBUS6810');

staff_id	username
1	Kelvin
2	Harrison
3	Stephen
4	Prasad
5	David

role_id	staff_id	role	unit
1	1	Tutor	BUSS6002
2	2	Tutor	BUSS6002
3	3	Lecturer	BUSS6002
4	3	Tutor	BUSS6002
5	4	Tutor	BUSS6002
6	1	Tutor	QBUS6810

Join

SELECT staff.username, roles.role, roles.unit
FROM roles
INNER JOIN staff ON staff.staff_id = roles.staff_id

username	role	unit
Kelvin	Tutor	BUSS6002
Kelvin	Tutor	QBUS6810
Harrison	Tutor	BUSS6002
Stephen	Lecturer	BUSS6002
Stephen	Tutor	BUSS6002
Prasad	Tutor	BUSS6002

David is not present in both tables

SQLITE3

基本操作（连接、执行、查看）

import sqlite3

conn = sqlite3.connect("staff.db")

c = conn.cursor()

c.execute("SELECT roles.role, roles.unit FROM roles")

c.fetchall()

• sqlite3.connect(database [,timeout ,other optional arguments])

该 API 打开一个到 SQLite 数据库文件 database 的链接

• connection.cursor([cursorClass])

该例程创建一个 cursor对象，

Once you have a Connection, you can create a Cursor object and call its execute() method to perform SQL commands

• cursor.execute(sql [, optional parameters])

该例程执行一个 SQL 语句。该 SQL 语句可以被参数化（即使用占位符代替 SQL 文本）

• cursor.fetchall()

该例程获取查询结果集中所有（剩余）的行，返回一个列表。当没有可用的行时，则返回一个空的列表

[('Tutor', 'BUSS6002'),
 ('Tutor', 'BUSS6002'),
 ('Lecturer', 'BUSS6002'),
 ('Tutor', 'BUSS6002'),
 ('Tutor', 'BUSS6002'),
 ('Tutor', 'QBUS6810')]

导入数据到Pandas

import pandas as pd

conn = sqlite3.connect("staff.db")

roles_df = pd.read_sql("SELECT roles.role, roles.unit FROM roles", conn)

roles_df.head()

	role	unit
0	Tutor	BUSS6002
1	Tutor	BUSS6002
2	Lecturer	BUSS6002
3	Tutor	BUSS6002
4	Tutor	BUSS6002

Remote Database

对于不同的数据库类型，create_engine的参数也不一样

The create_engine() function produces an Engine object based on a URL. These URLs follow RFC-1738, and usually can include username, password, hostname, database name as well as optional keyword arguments for additional configuration. In some cases a file path is accepted, and in others a “data source name” replaces the “host” and “database” portions. The typical form of a database URL is:

dialect+driver://username:password@host:port/database

sqlalchemy.create_engine(*args, **kwargs)¶

The string form of the URL is dialect[+driver]://user:password@host/dbname[?key=value..],

dialect is a database type such as mysql, oracle, postgresql, etc.,

driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle, etc.

username the username we use to connect to the database with. Different users may have different permissions

password the password for the user

host is the domain name where the database is hosted

portthe port to use when connecting. When you go to a website you are using port 80.

databasethe name of the database. The server can have multiple databases.

SQLite

db = create_engine('sqlite:///staff.db')

roles_df = pd.read_sql("SELECT roles.role, roles.unit FROM roles", db)

roles_df.head()

	role	unit
0	Tutor	BUSS6002
1	Tutor	BUSS6002
2	Lecturer	BUSS6002
3	Tutor	BUSS6002
4	Tutor	BUSS6002

PostgreSQL¶

from sqlalchemy import create_engine
engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')