눈물이 쏙 빠지는 시험이었고, 해답이었다.
9시간 걸려 겨우 문제 풀고 제출...
해답영상 무한루프 돌려보면서 따라했지만 번번히 에러...

언제쯤 잘 할 수 있게 되는 걸까..?

ERROR 1396 (HY000): Operation CREATE USER failed for

MySQL에서 새로운 계정을 생성하려고 하면 다음과 같은 에러가 발생할 때가 있다.

근본적인 원인은 계정 생성 및 권한 부여를 mysql.user, mysql.db에서 직접 조작을 하였기 때문이다.

select from mysql.user 혹은 select from mysql.db를 하면 생성 또는 권한을 부여하려는 계정이 이미 존재하고 있을 것이다.

따라서 다음 명령어들을 통해 데이터를 삭제 후 다시 생성을 해주어야 한다.

• delete from mysql.user where User ='삭제할 아이디';
• delete from mysql.db where User ='삭제할 아이디';

계정 생성 및 권한 부여는 테이블에 직접 접근이 아닌 create와 grant를 이용하는 것을 적극 권장한다.

DatabaseError: 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

cmd로 DB, TABLE을 만든 뒤 VS Code 작업을 하면 위와 같은 에러를...도저히 해결해낼 수 없었다...

그래서, 처음부터 다시 VS Code로 작업했고, 위와 같은 에러는 발생되지 않았다...

(삽질만 3hr...🙄)

SQL with PYTHON

---

내답

• sql_ws > test01 > [DS]21기_sql1_ohjaemim

import mysql.connector

conn = mysql.connector.connect(
    host = "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "[비번]",
    database = "zerobase"
)

cursor = conn.cursor(buffered = True)
sql = 'create database oneday default character set utf8mb4'
cursor.execute(sql)

cursor.execute("create user 'oneday'@'%' identified by '1234'")
cursor.execute("grant all on oneday.* to 'oneday'@'%'")

result1 = "show create database oneday"
cursor.execute(result1)

result1 = cursor.fetchall()
for i in result1:
    print(i)

result2 = "show grants for 'oneday'@'%'"
cursor.execute(result2)

result2 = cursor.fetchall()
for i in result2:
    print(i)

cursor.execute('use oneday')

sql_Brand = "create table COFFEE_BRAND(id int not null auto_increment primary key, name varchar(12))"
cursor.execute(sql_Brand)

sql_Store = "create table COFFEE_STORE(id int not null auto_increment primary key, brand int, name varchar(32) not null, gu_name varchar(5) not null, address varchar(128) not null, lat decimal(16,14) not null, lng decimal(17,14) not null, foreign key (brand) references COFFEE_BRAND(id))"
cursor.execute(sql_Store)

result3 = 'desc COFFEE_BRAND'
cursor.execute(result3)

result3 = cursor.fetchall()
for i in result3:
    print(i)


print('-'*25)


result4 = 'desc COFFEE_STORE'
cursor.execute(result4)

result4 = cursor.fetchall()
for i in result4:
    print(i)

cursor.execute("insert into COFFEE_BRAND values (1, 'STARBUCKS'), (2, 'EDIYA')")
conn.commit()

conn.close()

result5 = "select * from COFFEE_BRAND"
cursor.execute(result5)

result5 = cursor.fetchall()
for i in result5:
    print(i)

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.common.exceptions import NoSuchElementException

#페이지 접근

url = 'https://www.starbucks.co.kr/store/store_map.do'
driver = webdriver.Chrome(executable_path= '../driver/chromedriver.exe')

# 검색 페이지 접속
driver.get(url)
  
# 존재하지 않는 요소 찾기
try:
    driver.find_element(By.CSS_SELECTOR,'.nonexistent-element')
    print(element.text)
except NoSuchElementException as e:
    print("요소를 찾지 못했습니다.")

# 브라우저 종료
finally:
    driver.quit()

#페이지 접근

url = 'https://www.starbucks.co.kr/store/store_map.do'
driver = webdriver.Chrome(executable_path= '../driver/chromedriver.exe')

# 검색 페이지 접속
driver.get(url)

driver.find_element(By.CSS_SELECTOR, '#container > div > form > fieldset > div > section > article.find_store_cont > article > header.loca_search > h3 > a').click()
time.sleep(0.5)
driver.find_element(By.CSS_SELECTOR, '#container > div > form > fieldset > div > section > article.find_store_cont > article > article:nth-child(4) > div.loca_step1 > div.loca_step1_cont > ul > li:nth-child(1) > a').click()
time.sleep(0.5)

xpath = '//*[@id="mCSB_2_container"]/ul/li[1]/a'
tag = driver.find_element(By.XPATH, xpath)
tag.click()
time.sleep(0.5)

import mysql.connector

conn = mysql.connector.connect(
    host = "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "94woals!!",
    database = "oneday"
)

from tqdm import tqdm_notebook
from bs4 import BeautifulSoup


soup = BeautifulSoup(driver.page_source, "html.parser")
seoul_list = driver.find_elements(By.CSS_SELECTOR, '#mCSB_3_container ul li')
soup.select_one(f'#mCSB_3_container > ul > li:nth-child(100) > p').text[:-9]
cursor = conn.cursor(buffered=True)

sql = "insert into COFFEE_STORE (brand, name, gu_name, address, lat, lng) values (1, %s, %s, %s, %s, %s)"

cnt = 1

for content in tqdm_notebook(seoul_list):
    name = content.get_attribute('data-name')
    address = soup.select_one(f'#mCSB_3_container > ul > li:nth-child({cnt}) > p').text[:-9]
    lat = content.get_attribute('data-lat')
    lng = content.get_attribute('data-long')
    gu_name = address.split()[1] if address else ''

    cnt += 1

    cursor.execute(sql, (name, gu_name, address, lat, lng))
    conn.commit()

# 데이터 총 갯수
count_query = 'SELECT COUNT(*) FROM COFFEE_STORE where brand = 1;'
cursor.execute(count_query)

record_count = cursor.fetchone()[0]
record_count

# 데이터 상위 10개
check = "select * from COFFEE_STORE where brand = 1 limit 10"
cursor.execute(check)

result = cursor.fetchall()
for i in result:
    print(i)

#페이지 접근

url = 'https://ediya.com/contents/find_store.html'
driver = webdriver.Chrome(executable_path='../driver/chromedriver.exe')
driver.get(url)

driver.find_element(By.CSS_SELECTOR, '#contentWrap > div.contents > div > div.store_search_pop > ul > li:nth-child(2) > a').click()

cursor = conn.cursor(buffered = True)

gu_list = []
cursor.execute("select distinct(gu_name) from COFFEE_STORE")
result = cursor.fetchall()

for row in result:
    gu_list.append(str('서울 ') + row[0])

# 구 검색

gu = driver.find_element(By.XPATH, '//*[@id="keyword"]')
gu.clear()
gu.send_keys(gu_list[0])
time.sleep(0.5)

driver.find_element(By.XPATH, '//*[@id="keyword_div"]/form/button').click()

ediya = driver.page_source
soup_ediya = BeautifulSoup(ediya, "html.parser")
contents = soup_ediya.select('#placesList li')

import googlemaps
import time
gmaps_key = 'AIzaSyALyv5xMRzF_RJUIeJ84qh25GgNWoIJ8LM' 
gmaps = googlemaps.Client(key = gmaps_key)

url = 'https://ediya.com/contents/find_store.html'
driver = webdriver.Chrome(executable_path='../driver/chromedriver.exe')
driver.get(url)

driver.find_element(By.CSS_SELECTOR, '#contentWrap > div.contents > div > div.store_search_pop > ul > li:nth-child(2) > a').click()

sql = "INSERT INTO COFFEE_STORE (brand, name,gu_name, address, lat, lng) VALUES (2, %s, %s, %s, %s, %s)"

for gu in tqdm_notebook(gu_list):

    # 검색어 입력
    keyword = driver.find_element(By.CSS_SELECTOR, '#keyword')
    keyword.clear()
    keyword.send_keys(gu)
    
    # 검색 클릭
    driver.find_element(By.CSS_SELECTOR, '#keyword_div > form > button').click()

    time.sleep(1)  # 검색하고 로딩 기다리기
 
    
    html = driver.page_source
    soup_ed = BeautifulSoup(html, 'html.parser')
    contents = soup_ed.select('#placesList li')
    
    for content in contents:
        name = content.select_one('dt').text
        address = content.select_one('dd').text
        gu_name = address.split(' ')[1]

        print(f'{name}--{address}--{gu_name}')

        tmp = gmaps.geocode(address, language ='ko')
        lat = tmp[0].get("geometry")["location"]["lat"] if tmp else ''
        lng = tmp[0].get("geometry")["location"]["lng"] if tmp else ''
        
        cursor.execute(sql,(name, gu_name, address, lat, lng))
        conn.commit()

driver.close()

# 데이터 총 갯수
count_query = 'SELECT COUNT(*) FROM COFFEE_STORE where brand = 2;'
cursor.execute(count_query)

record_count = cursor.fetchone()[0]
record_count

# 데이터 상위 10개
check = "select * from COFFEE_STORE where brand = 2 limit 10"
cursor.execute(check)

result = cursor.fetchall()
for i in result:
    print(i)

strb = "select s.gu_name, count(s.brand) from COFFEE_BRAND as b, COFFEE_STORE as s where b.id = s.brand and b.name='STARBUCKS' group by s.gu_name order by count(s.brand) desc limit 5"

cursor.execute(strb)
result = cursor.fetchall()

for row in result:
    print(row)

edy = "select s.gu_name, count(s.brand) from COFFEE_BRAND as b, COFFEE_STORE as s where b.id = s.brand and b.name='EDIYA' group by s.gu_name order by count(s.brand) desc limit 5"

cursor.execute(edy)
result = cursor.fetchall()

for row in result:
    print(row)

# 스타벅스
gu_each_st = "select gu_name, '스타벅스' as brand, count(brand) as count from COFFEE_STORE where brand = 1 group by gu_name"

cursor.execute(gu_each_st)
result = cursor.fetchall()

for row in result:
    print(row)
    
# 이디야
gu_each_ed = "select gu_name, '이디야' as brand, count(brand) as count from COFFEE_STORE where brand = 2 group by gu_name"

cursor.execute(gu_each_ed)
result = cursor.fetchall()

for row in result:
    print(row)

count = ("select s.gu_name, "
         "sum(s.brand=1) as count1, "
         "sum(s.brand=2) as count2 "
         "from COFFEE_BRAND b, COFFEE_STORE s " 
         "where b.id = s.brand " 
         "group by s.gu_name " 
         "order by s.gu_name;")

cursor.execute(count)
result = cursor.fetchall()

# (구이름, 스타벅스 매장 개수, 이디야 매장 개수)
for row in result:
    gu_name, count1, count2 = row
    print(f'({gu_name}, {count1}, {count2})')

import pandas as pd

final = ("select * " 
        "from (select s.id as s_id, b.name as s_brand, s.name as s_name, s.gu_name as s_gu, s.address as s_address, s.lat as s_lat, s.lng as s_lng " 
        "from COFFEE_STORE as s, COFFEE_BRAND as b " 
        "where b.id = s.brand and b.name like 'STARBUCKS') as st, " 
        "(select s.id as e_id, b.name as e_brand, s.name as e_name, s.gu_name as e_gu, s.address as e_address, s.lat as e_lat, s.lng as e_lng " 
        "from COFFEE_STORE as s, COFFEE_BRAND as b " 
        "where b.id = s.brand and b.name like 'EDIYA') as ed " 
        "order by st.s_id, ed.e_id")

cursor.execute(final)
result = cursor.fetchall()

df = pd.DataFrame(result)
df.columns = ['s_id', 's_brand', 's_name', 's_gu', 's_address', 's_lat', 's_lng', 
              'e_id', 'e_brand', 'e_name', 'e_gu', 'e_address', 'e_lat', 'e_lng']
              
df.to_csv('./starbucks_ediya.csv', index = False, encoding = "euc-kr")