Python高级：MySQL联接操作实现细节

本文概述

• 正确加入
• 左加入

我们可以通过使用join语句在两个或多个表中使用一些公共列来合并这些列。

我们的数据库中只有一个表, 让我们再创建一个表Departments, 其中两列为department_id和department_name。

create table Departments (Dept_id int(20) primary key not null, Dept_Name varchar(20) not null);

如上图所示, 我们创建了一个新的表Departments。但是, 我们尚未在其中插入任何值。

让我们插入一些Departments ID和Departments名称, 以便我们可以将其映射到Employee表。

insert into Departments values (201, "CS");
insert into Departments values (202, "IT");

让我们看一下插入每个表中的值。考虑下图。

现在, 让我们创建一个Python脚本, 该脚本将共同列上的两个表即dept_id连接起来。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #joining the two tables on departments_id
    cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments join Employee on Departments.Dept_id = Employee.Dept_id")
    print("ID    Name    Salary    Dept_Id    Dept_Name")
    for row in cur:
        print("%d    %s    %d    %d    %s"%(row[0], row[1], row[2], row[3], row[4]))
        
except:
    myconn.rollback()

myconn.close()

输出

ID    Name    Salary    Dept_Id    Dept_Name
101   John    25000    	201    		CS
102   John    25000    	201    		CS
103   David   25000       202    		IT
104   Nick    90000   	201    		CS
105   Mike    28000   	202   		IT

正确加入

右联接显示右侧表的所有列, 因为我们在数据库PythonDB中有两个表, 即Departments和Employee。表中没有任何不在任何部门工作的Employee(部门ID为null的雇员)。但是, 要了解正确连接的概念, 我们来创建一个。

在MySQL服务器上执行以下查询。

insert into Employee(name, id, salary, branch_name) values ("Alex", 108, 29900, "Mumbai");

这将插入一个不在任何部门工作的员工Alex(部门ID为null)。

现在, 我们在Employee表中有一个雇员, 而Departments表中没有该部门的ID。现在让我们在两个表上执行正确的联接。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #joining the two tables on departments_id
    result = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments right join Employee on Departments.Dept_id = Employee.Dept_id")
    
	print("ID    Name    Salary    Dept_Id    Dept_Name")
    
	for row in cur:
        print(row[0], "    ", row[1], "    ", row[2], "    ", row[3], "    ", row[4])
    
    
        
except:
    myconn.rollback()

myconn.close()

输出

ID    Name    Salary    Dept_Id    Dept_Name
101      John      25000.0      201      CS
102      John      25000.0      201      CS
103      David      25000.0      202      IT
104      Nick      90000.0      201      CS
105      Mike      28000.0      202      IT
108      Alex      29900.0      None      None

左加入

左联接涵盖了左侧表中的所有数据。它与正确的连接正好相反。考虑以下示例。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #joining the two tables on departments_id
    result = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments left join Employee on Departments.Dept_id = Employee.Dept_id")
    print("ID    Name    Salary    Dept_Id    Dept_Name")
    for row in cur:
        print(row[0], "    ", row[1], "    ", row[2], "    ", row[3], "    ", row[4])
    
    
        
except:
    myconn.rollback()

myconn.close()

输出

ID    Name    Salary    Dept_Id    Dept_Name
101      John      25000.0      201      CS
102      John      25000.0      201      CS
103      David      25000.0      202      IT
104      Nick      90000.0      201      CS
105      Mike      28000.0      202      IT