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, '[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
다음 문은이 두 테이블의 값을 결합한 데이터를 검색합니다.
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 테이블의 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()
산출
[
('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')
]