Python MySQL - Katıl

Verileri iki tabloya böldüğünüzde, Birleşimler'i kullanarak bu iki tablodan birleşik kayıtları getirebilirsiniz.

Misal

EMPLOYEE adında bir tablo oluşturduğumuzu ve verileri bu tabloya aşağıda gösterildiği gibi doldurduğumuzu varsayalım -

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

Sonra, başka bir tablo oluşturup bunu şu şekilde doldurduysak -

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, '[email protected]', 'Hyderabad'), 
   (102, '[email protected]', 'Vishakhapatnam'), 
   (103, '[email protected]', 'Pune'), 
   (104, '[email protected]', 'Mumbai');
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0

Aşağıdaki ifade, bu iki tablodaki değerleri birleştiren verileri alır -

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 | [email protected] | NULL  | Hyderabad      |
| Vinay      | Bhattacharya | 20   | M    | 6000   | 102     | 102 | [email protected]    | NULL  | Vishakhapatnam |
| Sharukh    | Sheik        | 25   | M    | 8300   | 103     | 103 | [email protected] | NULL  | Pune           |
| Sarmista   | Sharma       | 26   | F    | 10000  | 104     | 104 | [email protected]    | NULL  | Mumbai         |
+------------+--------------+------+------+--------+---------+-----+--------------------+-------+----------------+
4 rows in set (0.00 sec)

Python kullanarak MYSQL JOIN

Aşağıdaki örnek, EMPLOYEE tablosunun kontak sütunu ve CONTACT tablosunun ID sütunu ile birleştirilen yukarıdaki iki tablodan verileri alır.

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

Çıktı

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