Python MySQL - 连接

将数据分成两个表后，可以使用连接从这两个表中获取组合记录。

示例

假设我们创建了一个名为 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)

使用 python 的 MYSQL JOIN

以下示例从上述两个表中检索数据，这些数据由 EMPLOYEE 表的 contact 列和 CONTACT 表的 ID 列组合而成。

import mysql.connector

#建立连接
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb'
)

#使用 cursor() 方法创建游标对象
cursor = conn.cursor()

#检索单行
sql = '''SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID'''

#执行查询
cursor.execute(sql)

#从表中获取第一行
result = cursor.fetchall();
print(result)

#关闭连接
conn.close()

输出

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