Python教程-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。我们没有任何员工不属于任何部门（部门 id 为 null）的 Employee。但是，为了理解右连接的概念，让我们创建一个。

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

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

这将插入一个员工 Alex，他不在任何部门工作（部门 id 为 null）。

现在，我们在 Employee 表中有一个员工，他的部门 id 不在 Departments 表中。现在让我们在这两个表上执行右连接。

示例

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