Python Data Access 简明教程

Python MySQL - Join

当你在两张表中划分数据时，你可以使用连接从这两张表中获取组合记录。

When you have divided the data in two tables you can fetch combined records from these two tables using Joins.

Example

假设我们创建了一个名为 EMPLOYEE 的表，并将数据填充到其中，如下所示：

Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −

mysql> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);
Query OK, 0 rows affected (0.36 sec)
INSERT INTO Employee VALUES ('Ramya', 'Rama Priya', 27, 'F', 9000, 101),
   ('Vinay', 'Bhattacharya', 20, 'M', 6000, 102),
   ('Sharukh', 'Sheik', 25, 'M', 8300, 103),
   ('Sarmista', 'Sharma', 26, 'F', 10000, 104),
   ('Trupthi', 'Mishra', 24, 'F', 6000, 105);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0

然后，如果我们创建了另一个表并填充数据，如下所示：

Then, if we have created another table and populated it as −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);
Query OK, 0 rows affected (0.49 sec)

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
   (101, 'Krishna@mymail.com', 'Hyderabad'),
   (102, 'Raja@mymail.com', 'Vishakhapatnam'),
   (103, 'Krishna@mymail.com', 'Pune'),
   (104, 'Raja@mymail.com', 'Mumbai');
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0

以下语句检索组合这两张表中值的块数据：

Following statement retrieves data combining the values in these two tables −

mysql> SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID;
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
| FIRST_NAME | LAST_NAME    | AGE  | SEX  | INCOME | CONTACT | ID  | EMAIL              | PHONE | CITY           |
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
| Ramya      | Rama Priya   | 27   | F    | 9000   | 101     | 101 | Krishna@mymail.com | NULL  | Hyderabad      |
| Vinay      | Bhattacharya | 20   | M    | 6000   | 102     | 102 | Raja@mymail.com    | NULL  | Vishakhapatnam |
| Sharukh    | Sheik        | 25   | M    | 8300   | 103     | 103 | Krishna@mymail.com | NULL  | Pune           |
| Sarmista   | Sharma       | 26   | F    | 10000  | 104     | 104 | Raja@mymail.com    | NULL  | Mumbai         |
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
4 rows in set (0.00 sec)

MYSQL JOIN using python

下面的示例从以上两个表中检索数据，这两个表通过 EMPLOYEE 表的联系方式列和 CONTACT 表的 ID 列组合在一起。

Following example retrieves data from the above two tables combined by contact column of the EMPLOYEE table and ID column of the CONTACT table.

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb'
)

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
sql = '''SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID'''

#Executing the query
cursor.execute(sql)

#Fetching 1st row from the table
result = cursor.fetchall();
print(result)

#Closing the connection
conn.close()

Output

[('Krishna', 'Sharma', 26, 'M', 2000, 101, 101, 'Krishna@mymail.com', 9848022338, 'Hyderabad'),
   ('Raj', 'Kandukuri', 20, 'M', 7000, 102, 102, 'Raja@mymail.com', 9848022339, 'Vishakhapatnam'),
   ('Ramya', 'Ramapriya', 29, 'F', 5000, 103, 103, 'Krishna@mymail.com', 9848022337, 'Pune'),
   ('Mac', 'Mohan', 26, 'M', 2000, 104, 104, 'Raja@mymail.com', 9848022330, 'Mumbai')]