Python数据分析：数据库多表连接（SQLite）

Python数据分析：数据库多表连接（SQLite）

多表连接：

• 将多个表中的记录连接并返回结果

• join方式

  • 交叉连接（cross join）
  • 内连接（inner join）
  • 外连接（outer join)

• cross join:

  • 生成两张表的笛卡尔积
  • 返回的记录数为两张表记录数的乘积

• inner join

  • 生成两张表的交集
  • 返回的记录数为两张表交集的记录数

• outer join

  • left join（A,B) 返回表A的所有记录，另外表B中匹配的记录有值没有匹配的记录返回null

import sqlite3

db_path = 'test_join.sqlite'

conn = sqlite3.connect(db_path)
cur = conn.cursor()

# 建 depaetment 表，并插入数据
cur.execute("DROP TABLE IF EXISTS department")
cur.execute("CREATE TABLE department(\
                id INT PRIMARY KEY NOT NULL, \
                dept CHAR(50) NOT NULL, \
                emp_id INT NOT NULL)")
depts = (
        (1, 'IT Builing', 1),
        (2, 'Engineerin', 2),
        (3, 'Finance', 7)
)
cur.executemany("INSERT INTO department VALUES(?, ?, ?)", depts)

# 建 company 表，并插入数据
cur.execute("DROP TABLE IF EXISTS company")
cur.execute("CREATE TABLE company(\
                    id INT PRIMARY KEY NOT NULL, \
                    name CHAR(50) NOT NULL, \
                    age INT NOT NULL, \
                    address CHAR(50) NOT NULL,\
                    salary DOUBLE NOT NULL)")
companies = (
        (1, 'Paul', 32, 'California', 20000.0),
        (2, 'Allen', 25, 'Texas', 15000.0),
        (3, 'Teddy', 23, 'Norway', 20000.0),
        (4, 'Mark', 25, 'Rich-Mond', 65000.0),
        (5, 'David', 27, 'Texas', 85000.0),
        (6, 'Kim', 22, 'South-Hall', 45000.0),
        (7, 'James', 24, 'Houston', 10000.0)
)
cur.executemany("INSERT INTO company VALUES (?, ?, ?, ?, ?)", companies)

conn.commit()

交叉连接

cur.execute("SELECT emp_id, name, dept FROM company CROSS JOIN department;")
rows = cur.fetchall()
for row in rows:
    print(row)

运行结果：

内连接

cur.execute("SELECT emp_id, name, dept FROM company INNER JOIN department \
            ON company.id = department.emp_id;")
rows = cur.fetchall()
for row in rows:
    print(row)

运行结果：

外连接

# 左连接
cur.execute("SELECT emp_id, name, dept FROM company LEFT OUTER JOIN department \
            ON company.id = department.emp_id;")
rows = cur.fetchall()
for row in rows:
    print(row)

运行结果：