Python MySQL连接操作|极客笔记

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);

Python MySQL连接操作

如上图所示，我们已经创建了一个名为Departments的新表。然而，我们还没有在其中插入任何值。

让我们插入一些部门id和部门名称，这样我们就可以将其映射到我们的Employee表中。

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

让我们来看看插入到每个表格中的值。请参考以下图像。

Python MySQL连接操作

现在，让我们创建一个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为空的雇员）。但是，为了了解右连接的概念，让我们创建一个。

在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