Python MySQL - Partecipa

Dopo aver diviso i dati in due tabelle, è possibile recuperare i record combinati da queste due tabelle utilizzando i join.

Esempio

Supponiamo di aver creato una tabella con il nome EMPLOYEE e di aver inserito i dati in essa come mostrato di seguito -

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

Quindi, se abbiamo creato un'altra tabella e l'abbiamo popolata come -

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

La seguente istruzione recupera i dati combinando i valori in queste due tabelle:

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)

MYSQL JOIN utilizzando python

L'esempio seguente recupera i dati dalle due tabelle precedenti combinate per colonna contatto della tabella EMPLOYEE e colonna ID della tabella CONTATTI.

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

Produzione

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