[mysql] Python - MySQL Join (테이블 결합)

목차

1. 2개 테이블 존재 가정
2. JOIN (= INNER JOIN) - 공통 레코드 기준 결합
3. LEFT JOIN - 왼쪽 기준으로 결합
4. RIGHT JOIN - 오른쪽 기준으로 결합

2개 테이블 존재 가정

[hz_member 테이블] ※ 컬럼: (mb_id, mb_name, mb_level)

 

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="root",

  password="autoset",

  database="hz"

)

mycursor = mydb.cursor()

sql = "INSERT INTO hz_member (mb_id, mb_name, mb_level)  VALUES (%s, %s, %s)"

val = [

    (1, 'AAA', '1'),

    (2, 'BBB', '2'),

    (3, 'CCC', '3'),

    (4, 'DDD', '4'),

    (5, 'EEE', '5')

]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "recode inserted.")

 

---

[hz_point 테이블] ※ 컬럼: (mb_id, mb_point)

 

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="root",

  password="autoset",

  database="hz"

)

mycursor = mydb.cursor()

sql = "INSERT INTO hz_point (mb_id, mb_point)  VALUES (%s, %s)"

val = [

    (1, 100),

    (2, 200),

    (6, 600)

]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "recode inserted.")

 

JOIN (= INNER JOIN) - 공통 레코드 기준 결합

 

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="root",

  password="autoset",

  database="hz"

)

mycursor = mydb.cursor()

sql = "SELECT \

  hz_member.mb_name AS user, \

  hz_point.mb_point AS point \

  FROM hz_member \

  JOIN hz_point ON hz_member.mb_id = hz_point.mb_id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:

  print(x)

 

결과값:

('AAA', 100)

('BBB', 200) 

※ JOIN 대신 INNER JOIN 사용 가능. (동일)

LEFT JOIN - 왼쪽 기준으로 결합

 

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="root",

  password="autoset",

  database="hz"

)

mycursor = mydb.cursor()

sql = "SELECT \

  hz_member.mb_name AS user, \

  hz_point.mb_point AS point \

  FROM hz_member \

  LEFT JOIN hz_point ON hz_member.mb_id = hz_point.mb_id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:

  print(x)

 

결과값: 

('AAA', 100)

('BBB', 200)

('CCC', None)

('DDD', None)

('EEE', None) 

RIGHT JOIN - 오른쪽 기준으로 결합

 

import mysql.connector

mydb = mysql.connector.connect(

  host="localhost",

  user="root",

  password="autoset",

  database="hz"

)

mycursor = mydb.cursor()

sql = "SELECT \

  hz_member.mb_name AS user, \

  hz_point.mb_point AS point \

  FROM hz_member \

  RIGHT JOIN hz_point ON hz_member.mb_id = hz_point.mb_id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:

  print(x)

결과값:

('AAA', 100)

('BBB', 200)

(None, 600)