Python-data-access-python-mysql-join

提供:Dev Guides
移動先:案内検索

Python MySQL-参加

データを2つのテーブルに分割したら、結合を使用してこれらの2つのテーブルから結合レコードをフェッチできます。

以下に示すように、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, '[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

次のステートメントは、これら2つのテーブルの値を組み合わせたデータを取得します-

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を使用したMYSQL JOIN

次の例では、EMPLOYEEテーブルのcontact列とCONTACTテーブルのID列で結合された上記の2つのテーブルからデータを取得します。

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

出力

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