Python Data Access 简明教程

Python MySQL - Join

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

Example

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

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

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

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

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

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 列组合在一起。

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')]