engkimbs.tistory.com/613

 

[Python 재무제표 크롤링 #4] Requests, BeautifulSoup로 크롤링(Crawling), 데이터 추출하기(Data Extraction) - 1

| Requests, BeautifulSoup 라이브러리 Requests는 웹상의 html문서를 파이썬 언어를 통해 쉽게 사용자 컴퓨터로 가져올 수 있게 하는 라이브러리입니다. 그리고 BeautifulSoup는 가져온 HTML문서를 파싱하여 

engkimbs.tistory.com

- flask 로 프로젝트 만들기 



-터미널에서 설치 

pip install requests
pip install bs4

html 만들어놓고 

일단 간단하게 만들어놓기 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    데이터 수집중

</body>
</html>

-app.py

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    print(soup.prettify())

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

이렇게 뜸

 


- 영화 포스터 이미지 클릭해서 

class 선택하면 아래처럼 됨

 

 

-app.py

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    #print(soup.prettify())

    ul = soup.find('ul', class_="lst_detail_t1")
    print(ul)

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

 

-> lst_detail_t1 부분만 나옴 ( 클릭된 곳만)


- 이미지 가져오기 

 

li > div > a > img 

 

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    #print(soup.prettify())

    ul = soup.find('ul', class_="lst_detail_t1")
    img = ul.select('li > div > a > img')
    print(len(img))
    for i in img:
        print(i.get('src'))

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

 

이렇게 뜸 


- 전체 관람가 가져오기 

 

    rating = ul.select('li > dl > dt > span')
    for rate in rating:
        print(rate.text)

-app.py 

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    #print(soup.prettify())

    ul = soup.find('ul', class_="lst_detail_t1")
    img = ul.select('li > div > a > img')

    rating = ul.select('li > dl > dt > span')
    for rate in rating:
        print(rate.text)

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

 

이렇게 뜸


- 영화제목 가져오기 

 

 

    movie_names = ul.select('li > dl > dt > a')
    for movie_name in movie_names:
        print(movie_name.text)
import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    #print(soup.prettify())

    ul = soup.find('ul', class_="lst_detail_t1")
    img = ul.select('li > div > a > img')

    movie_names = ul.select('li > dl > dt > a')
    for movie_name in movie_names:
        print(movie_name.text)

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

이렇게 뜸 


- 네티즌 글자 가져오기

우클릭해서 copy selector 해보면 

#content > div.article > div:nth-child(1) > div.lst_wrap > ul > li:nth-child(1) > dl > dd.star > dl.info_star > dt

- 이것만 가져오기 

dl > dd.star > dl.info_star > dt

 

    estimate_poeple = ul.select('dl > dd.star > dl.info_star > dt')
    for estimated_p in estimate_poeple:
        print(estimated_p.text)

- app.py

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')

    ul = soup.find('ul', class_="lst_detail_t1")
    #print(ul)
    img = ul.select('li > div > a > img')
    # print(len(img))

    estimate_poeple = ul.select('dl > dd.star > dl.info_star > dt')
    for estimated_p in estimate_poeple:
        print(estimated_p.text)

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

- 평점 가져오기 

#content > div.article > div:nth-child(1) > div.lst_wrap > ul > li:nth-child(1) > dl > dd.star > dl.info_star > dd > div > a > span.num

- 위에 copy한 것 중에서 아래만 가져오기 (그 위에는 ul로 이미 가져와져있어서) 

dl > dd.star > dl.info_star > dd > div > a > span.num

- app.py 

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    ul = soup.find('ul', class_="lst_detail_t1")
    
    estimate_scores = ul.select('dl > dd.star > dl.info_star > dd > div > a > span.num')
    for estimate_score in estimate_scores:
        print(estimate_score.text)

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

 

 

 


- 참여 인원 가져오기 

#content > div.article > div:nth-child(1) > div.lst_wrap > ul > li:nth-child(1) > dl > dd.star > dl.info_star > dd > div > a > span.num2 > em

여기서 가져와 쓰기 

 dl > dd.star > dl.info_star > dd > div > a > span.num2 > em
import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn' #네이버영화 주소


@app.route('/')
def data_gathering():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    #print(soup.prettify())

    ul = soup.find('ul', class_="lst_detail_t1")
    img = ul.select('li > div > a > img')

    number_of_participants = \
        ul.select('dl > dd.star > dl.info_star > dd > div > a > span.num2 > em')
    for number_of_participant in number_of_participants:
        print(number_of_participant.text)

    return render_template('data_gathering.html')


if __name__ == '__main__':
    app.run()

이렇게 뜸 

 


지금까지 했던 것들 list로 만들기 

 

app.py 

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn'


@app.route('/')
def data_gathering():
    img_list = []
    rating_list = []
    movie_name_list = []
    estimated_people_list = []
    estimate_scores_list = []
    number_of_participants_list = []

    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    # print(soup.prettify())

    ul = soup.find('ul', class_="lst_detail_t1")
    img = ul.select('li > div > a > img')
    # print(len(img))
    for i in img:
        img_list.append(i.get('src'))

    rating = ul.select('li > dl > dt > span')
    for rate in rating:
        rating_list.append(rate.text)
    movie_names = ul.select('li > dl > dt > a')
    for movie_name in movie_names:
        movie_name_list.append(movie_name.text)

    estimated_people = ul.select('dl > dd.star > dl.info_star > dt')
    for estimated_p in estimated_people:
        estimated_people_list.append(estimated_p.text)

    estimate_scores = ul.select('dl > dd.star > dl.info_star > dd > div > a > span.num')
    for estimate_score in estimate_scores:
        estimate_scores_list.append(estimate_score.text)

    number_of_participants = \
        ul.select('dl > dd.star > dl.info_star > dd > div > a > span.num2 > em')
    for number_of_participant in number_of_participants:
        number_of_participants_list.append(number_of_participant.text)

    print(number_of_participants_list)
    return render_template('data_gathering.html');


if __name__ == '__main__':
    app.run()


- 감독, 배우는 여러 명이라서 리스트로 만들었음 

-연령 등급은 없는 것도 있어서 리스트로 만들었음 없으면 빈 리스트로 채워짐 

 

 

- app.py

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn'


@app.route('/')
def hello_world():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    ul = soup.find('ul', class_='lst_detail_t1')
    movie_list = []
    li = ul.find_all('li')
    for piece in li:
        img = piece.find('img')
        actor_list = [act.text for act in piece.select('dl > dd:nth-child(3) > dl > dd:nth-child(6) > span > a')]
        img_src = img.get('src')
        directer = [dir.text for dir in piece.select('dl > dd:nth-child(3) > dl > dd:nth-child(4) > span > a')]
        age = [ag.text for ag in piece.select('dl > dt > span')]
        movie_name = piece.select(' dl > dt > a')[0].text
        netizen = piece.select('dl > dd.star > dl > dd > div > a > span.num')[0].text
        netizen_num = piece.select('dl > dd.star > dl.info_star > dd > div > a > span.num2 > em')[0].text
        movie_list.append([img_src, age, movie_name, netizen, netizen_num, actor_list, directer])
    for Movie in movie_list:
        print(Movie)
    return render_template('data_gathering.html', Movie=movie_list)


if __name__ == '__main__':
    app.run()

 

- data_gathering.html 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        .flex-container {
            display: flex;
            flex-direction: row;
            margin: 20px;

            width: 400px;
            margin: 0 auto;

            box-shadow: 2px 2px 4px gray;
        }
    </style>
</head>
<body>
    {% for movie in Movie %}
      <div class="flex-container">
        <img style="width: 100px; height: 150px;" src="{{ movie[0] }}">
     <ul>
         <li>관람가 등급 : {% for i in movie[1] %} {{ i }} {% endfor %}</li>
         <li>제목 : {{ movie[2] }}</li>
         <li>평 점 : {{ movie[3] }}</li>
        <li> 감독 :
            {% for director in movie[6] %}
                {{ director }},
            {% endfor %}
         <li> 배우 :
            {% for actor in movie[5] %}
                {{ actor }},
            {% endfor %}
         </li>
     </ul>
     </div>


    {%  endfor %}
</body>
</html>


-리스트 안에 리스트 말고 class로 만들기 

 

- app.py

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn'


@app.route('/')
def hello_world():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    ul = soup.find('ul', class_='lst_detail_t1')
    movie_list = []
    li = ul.find_all('li')
    for piece in li:
        img = piece.find('img')
        actor_list = [act.text for act in piece.select('dl > dd:nth-child(3) > dl > dd:nth-child(6) > span > a')]
        img_src = img.get('src')
        directer = [dir.text for dir in piece.select('dl > dd:nth-child(3) > dl > dd:nth-child(4) > span > a')]
        age = [ag.text for ag in piece.select('dl > dt > span')]
        movie_name = piece.select(' dl > dt > a')[0].text
        netizen = piece.select('dl > dd.star > dl > dd > div > a > span.num')[0].text
        netizen_num = piece.select('dl > dd.star > dl.info_star > dd > div > a > span.num2 > em')[0].text
        movie_list.append([img_src, age, movie_name, netizen, netizen_num, actor_list, directer])
    for Movie in movie_list:
        print(Movie)
    return render_template('data_gathering.html', Movie=movie_list)


class Book:
    def __init__(self, title, author):
        self.title = title
        self.author = author


@app.route('/study')
def study():
    book_list = []
    book = Book('제목1', '저자1')
    book_list.append(book)

    book = Book('제목2', '저자2')
    book_list.append(book)

    for b in book_list:
        print(b.title, b.author)
    return render_template('study.html', book_list=book_list)


if __name__ == '__main__':
    app.run()

 

-study.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<br>
    {% for book in book_list %}
        {{ book.title }}
        <br>
        {{ book.author }}
    {% endfor %}

</body>
</html>

이런 식으로 간단해짐. 

 

- app.py

import requests
from bs4 import BeautifulSoup
from flask import Flask, render_template

app = Flask(__name__)

url = 'https://movie.naver.com/movie/running/current.nhn'


class NaverMovie:
    def __init__(self, img_src, age, movie_name, netizen, netizen_num,\
                 actor_list, directer):
        self.img_src = img_src
        self.age = age
        self.movie_name = movie_name
        self.netizen = netizen
        self.netizen_num = netizen_num
        self.actor_list = actor_list
        self.actor_list = actor_list
        self.directer = directer


@app.route('/')
def hello_world():
    response = requests.get(url)
    soup = BeautifulSoup(response.content, 'html.parser')
    ul = soup.find('ul', class_='lst_detail_t1')
    movie_list = []
    li = ul.find_all('li')
    for piece in li:
        img = piece.find('img')
        actor_list = [act.text for act in piece.select('dl > dd:nth-child(3) > dl > dd:nth-child(6) > span > a')]
        img_src = img.get('src')
        directer = piece.select('dl > dd:nth-child(3) > dl > dd:nth-child(4) > span > a')[0].text
        age = [ag.text for ag in piece.select('dl > dt > span')]
        movie_name = piece.select(' dl > dt > a')[0].text
        netizen = piece.select('dl > dd.star > dl > dd > div > a > span.num')[0].text
        netizen_num = piece.select('dl > dd.star > dl.info_star > dd > div > a > span.num2 > em')[0].text
        naverMovie = NaverMovie(img_src, age, movie_name, netizen, netizen_num, actor_list, directer)
        movie_list.append(naverMovie)
        #movie_list.append([img_src, age, movie_name, netizen, netizen_num, actor_list, directer])
    for Movie in movie_list:
        print(Movie)
    return render_template('data_gathering.html', movie_list=movie_list)


class Book:
    def __init__(self, title, author):
        self.title = title
        self.author = author


@app.route('/study')
def study():
    book_list = []
    book = Book('제목1', '저자1')
    book_list.append(book)

    book = Book('제목2', '저자2')
    book_list.append(book)

    for b in book_list:
        print(b.title, b.author)

    return render_template('study.html', book_list=book_list)


if __name__ == '__main__':
    app.run()

 

- data_gathering.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        .flex-container {
            display: flex;
            flex-direction: row;
            margin: 20px;

            width: 400px;
            margin: 0 auto;

            box-shadow: 2px 2px 4px gray;
        }
    </style>
</head>
<body>
    {% for movie in movie_list %}
      <div class="flex-container">
        <img style="width: 100px; height: 150px;" src="{{ movie.img_src }}">
     <ul>
         <li>관람가 등급 : {% for i in movie.age %} {{ i }} {% endfor %}</li>
         <li>제목 : {{ movie.movie_name }}</li>
         <li>평 점 : {{ movie.netizen }}</li>
         <li>감독 : {{ movie.directer }}</li>
         <li> 배우 :
            {% for actor in movie.actor_list %}
                {{ actor }},
            {% endfor %}
         </li>
     </ul>
     </div>


    {%  endfor %}
</body>
</html>

* a = [1]

print(a) -> [1] 로 되니까 .text가 안됨 

그래서 print(a[0]) -> 1 이렇게 값만 뽑은거 

'mongodb' 카테고리의 다른 글

mysql  (0) 2021.01.27
로또 웹  (0) 2021.01.27
mysql - python 연동  (0) 2021.01.26
mysql  (0) 2021.01.25
flex box  (0) 2021.01.25

 

 

- apply 하기 

2번째 동그라미 누름 

 

- mysql 접속

 

call my_procedure();

 

- 외부에서 불러서 in 여기다 넣는데 데이터 형식은 int, 이름은 s다

x값 외부에서 받아서 sql문으로 +1하기 

 

apply하기 

 

apply

 

- out ->mysql에서 클라이언트로 x를 주겠다 

 

SET: OUT되는 x에 3을 넣음 

 

 


 

Add Account 클릭
Apply
UPDATE하면 Custom 체크됨


- 모델링해서 저장해두기  

forward engineering 순공학 

reverse enginnering 역공학 

 

- 기존에 있던 bookstore 삭제 

store vault 클릭해서 비번입력 

 

 

NEXT 하기

 

그러고 홈 누르고 다시 들어오면 bookstore 있음 

 


- join 

table 만들기
table 만들기 
데이터 넣기 
데이터 넣기

 

-- use company;
select e.sabun, e.name, d.name from employee e
	inner join dept d
	on e.dept_id = d.dept_id; 

'mongodb' 카테고리의 다른 글

네이버 영화 크롤링  (0) 2021.01.28
로또 웹  (0) 2021.01.27
mysql - python 연동  (0) 2021.01.26
mysql  (0) 2021.01.25
flex box  (0) 2021.01.25

 

 

'mongodb' 카테고리의 다른 글

네이버 영화 크롤링  (0) 2021.01.28
mysql  (0) 2021.01.27
mysql - python 연동  (0) 2021.01.26
mysql  (0) 2021.01.25
flex box  (0) 2021.01.25

- mysql 기초 

www.w3schools.com/sql/

 

SQL Tutorial

SQL Tutorial SQL is a standard language for storing, manipulating and retrieving data in databases. Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems. Start learn

www.w3schools.com

 

여기 경로에서 cmd 

mysql -u root -p

 

show databases;

use bookstore;

show tables;

 

insert into books (id,title, author) values(2,'python book', '이미자');

select id, title, author from books; 

select id, title, author from books where id=2; 

select id, title, author from books where author like'이%';

들어와있음 


- python 연동 

pymysql 

mysqlclient 

 

pip install mysqlclient

utf8임

 

import MySQLdb


def db_select():
    """ 접속테스트 """
    # 접속
    connect = MySQLdb.connect(
        user='root',
        password='1234',
        host='localhost', #127.0.0.1
        db='bookstore',
        charset='utf8')

    cursor = connect.cursor()
    sql = 'select id, title, author from books'
    cursor.execute(sql)

    rows = cursor.fetchall()
    for row in rows:
        print(row)

    cursor.close()
    connect.close()


if __name__ == "__main__":
    db_select()


참고) 

AI 체크해야 id 추가안해도 들어감 

import MySQLdb


def db_select():
    """ 접속테스트 """
    # 접속
    connect = MySQLdb.connect(
        user='root',
        password='1234',
        host='localhost', #127.0.0.1
        db='bookstore',
        charset='utf8')

    cursor = connect.cursor()
    sql = 'select id, title, author from books'
    cursor.execute(sql)

    rows = cursor.fetchall()
    for row in rows:
        print(row)

    cursor.close()
    connect.close()


def db_insert():
    """ insert """

    # 접속한다
    connect = MySQLdb.connect(
        user='root',
        password='1234',
        host='localhost',
        db='bookstore',
        charset='utf8')

    cursor = connect.cursor()
    title = 'machine learning'
    author = '이순자'
    sql = f"insert into books(title, author) values('{title}', '{author}')"
    cursor.execute(sql)

    connect.commit() # db에 반영

    cursor.close()
    connect.close()


db_insert()

들어와있음 

 


- dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchmany.html

 

MySQL :: MySQL Connector/Python Developer Guide :: 10.5.7 MySQLCursor.fetchmany() Method

10.5.7 MySQLCursor.fetchmany() Method Syntax: rows = cursor.fetchmany(size=1) This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list. The number of rows returned can

dev.mysql.com

 

import MySQLdb


class Connect:
    def __init__(self):
        self.connect = MySQLdb.connect(
            user='root',
            password='1234',
            host='localhost',  # 127.0.0.1
            db='bookstore',
            charset='utf8')

        self.cursor = self.connect.cursor()


while True:
    con = Connect()
    print('1: 전체 조회 2: 등록 7:건수 표시 9: 종료 ', end='')
    choice = input()
    choice = int(choice)

    # 전체 조회
    if choice == 1:
        sql = 'select id, title, author from books'
        con.cursor.execute(sql)

        rows = con.cursor.fetchall()
        for row in rows:
            print(row)

    # 등록
    if choice == 2:
        title = input('제목 입력: ')
        author = input('저자명 입력: ')
        sql = f"insert into books(title, author) values('{title}', '{author}')"
        con.cursor.execute(sql)
        con.connect.commit()

    #건수 표시
    if choice == 7:
        sql = 'select count(*)  from books'
        con.cursor.execute(sql)
        (count, ) = con.cursor.fetchone()
        print('전체 건수 =', count)


    #종료
    if choice == 9:
        exit(0)


 

- 쌤 코드 

import MySQLdb


class Connect:
    def __init__(self):
        self.connect = MySQLdb.connect(
            user = 'root',
            password = '1234',
            host = 'localhost',
            db = 'bookstore',
            charset = 'utf8')

        self.cursor = self.connect.cursor()


while True:
    con = Connect()
    print ('1: 전체 조회  2: 등록  3: 업데이트  4: 삭제  5: 제목 유사검색  7: 건수 표시   9: 종료 ', end='')
    choice = input()
    choice = int(choice)

    if choice == 1:
       sql = 'select id, title, author from books'
       con.cursor.execute(sql)

       rows = con.cursor.fetchall()
       for row in rows:
           print(row)

    if choice == 2:
        title = input('제목 입력 ')
        author = input('저자명 입력 ')
        sql = f"insert into books(title, author) values('{title}', '{author}')"
        affected_rows = con.cursor.execute(sql)
        if affected_rows == 1:
            print('정상 등록 되었음')
        con.connect.commit()

    if choice == 3:  # 업데이트
        sql = 'select id, title, author from books'
        con.cursor.execute(sql)
        rows = con.cursor.fetchall()
        for row in rows:
            (id, title, author) = row
            print(f'{id}, {title},   {author}')

        id = input('업데이트할 id 입력 ')
        id = int(id)
        sql = f'select id, title, author from books where id={id}'
        con.cursor.execute(sql)
        (id, title, author) = con.cursor.fetchone()
        print(id, title, author)

        title = input('변경할 제목 입력 ')
        author = input('변경할 저자명 입력 ')

        sql = f"update books set title = '{title}', author='{author}' where id={id}"
        affeted_rows = con.cursor.execute(sql)
        if affeted_rows == 1:
            print('정상 변경 되었음')
        con.connect.commit()

    if choice == 4:  # 삭제
        sql = 'select id, title, author from books'
        con.cursor.execute(sql)
        rows = con.cursor.fetchall()
        for row in rows:
            (id, title, author) = row
            print(f'{id}, {title},   {author}')

        id = input('삭제할 id 입력 ')
        id = int(id)

        sql = f'delete from books where id={id}'
        affeted_rows = con.cursor.execute(sql)
        if affeted_rows == 1:
            print('정상 삭제 되었음')
        con.connect.commit()

    if choice == 5:  # 제목 유사검색
        title = input('유사 검색할 제목 입력 ')
        sql = f"select id, title, author from books where title like '{title}%'"
        con.cursor.execute(sql)

        rows = con.cursor.fetchall()
        for row in rows:
            (id, title, author) = row
            print(f'{id}, {title},   {author}')

    if choice == 7:
        sql = 'select count(*) from books'
        con.cursor.execute(sql)
        (total_count, ) = con.cursor.fetchone()
        print('전체 건수=', total_count)

    if choice == 9:
        exit(0)

- 쌤꺼 보면서 내 코드 수정함

 

import MySQLdb


class Connect:
    def __init__(self):
        self.connect = MySQLdb.connect(
            user='root',
            password='1234',
            host='localhost',  # 127.0.0.1
            db='bookstore',
            charset='utf8')

        self.cursor = self.connect.cursor()


while True:
    con = Connect()
    print('1: 전체 조회 2: 등록 3: 업데이트 4: 삭제 5: 유사검색 7:건수 표시 9: 종료 ', end='')
    choice = input()
    choice = int(choice)

    # 전체 조회
    if choice == 1:
        sql = 'select id, title, author from books'
        con.cursor.execute(sql)

        rows = con.cursor.fetchall()
        for row in rows:
            (id_, title, author) = row
            print(f'{id_}, {title},   {author}')

    # 등록
    if choice == 2:
        title = input('제목 입력: ')
        author = input('저자명 입력: ')

        sql = f"insert into books(title, author) values('{title}', '{author}')"
        con.cursor.execute(sql)
        con.connect.commit()

    # 업데이트
    if choice == 3:
        sql = 'select id, title, author from books'
        con.cursor.execute(sql)

        rows = con.cursor.fetchall()
        for row in rows:
            print(row)

        id_ = input('업데이트할 id 입력: ')
        sql = f'select id, title, author from books where id={id_}'
        con.cursor.execute(sql)
        rows = con.cursor.fetchall()
        for row in rows:
            (id_, title, author) = row
            print(f'{id_}, {title},   {author}')

        title = input('업데이트 할 제목 입력: ')
        author = input('업데이트 할 저자명 입력: ')
        sql = f"update books set title = '{title}', author = '{author}' where id = '{id_}'"
        affeted_rows = con.cursor.execute(sql)
        if affeted_rows == 1:
            print("업데이트 되었습니다")
        con.connect.commit()


    # 삭제
    if choice == 4:
        sql = 'select id, title, author from books'
        con.cursor.execute(sql)

        rows = con.cursor.fetchall()
        for row in rows:
            (id_, title, author) = row
            print(f'{id_}, {title},   {author}')

        id_ = input('삭제할 id 입력: ')
        sql = f'select id, title, author from books where id={id_}'
        con.cursor.execute(sql)
        rows = con.cursor.fetchall()
        for row in rows:
            (id_, title, author) = row
            print(f'{id_}, {title},   {author}')

        dele = input('삭제하시겠습니까?: y/n')
        print(dele)
        if dele == "y":
            sql = f"delete from books where id = '{id_}'"
            print("삭제되었습니다")
            con.cursor.execute(sql)
            con.connect.commit()


    # 유사검색
    if choice == 5:
        title = input('유사검색할 제목 입력: ')
        sql = f"select id, title, author from books where title LIKE '%{title}%'"
        con.cursor.execute(sql)

        rows = con.cursor.fetchall()
        for row in rows:
            (id_, title, author) = row
            print(f'{id_}, {title}, {author}')

    #건수 표시
    if choice == 7:
        sql = 'select count(*)  from books'
        con.cursor.execute(sql)
        (total_count, ) = con.cursor.fetchone()
        print('전체 건수 =', total_count)


    #종료
    if choice == 9:
        exit(0)

 

'mongodb' 카테고리의 다른 글

mysql  (0) 2021.01.27
로또 웹  (0) 2021.01.27
mysql  (0) 2021.01.25
flex box  (0) 2021.01.25
날짜, 위경도, 구글글꼴  (0) 2021.01.25

 

- 모든 쿠키 수락 

MySQL Community Server 클릭
클릭
2번째 클릭
밑에 No, thanks 클릭

- Next 

Next

 

2번째꺼

 

- 비번 만들고 계속 가면됨 

 

 

 

'mongodb' 카테고리의 다른 글

로또 웹  (0) 2021.01.27
mysql - python 연동  (0) 2021.01.26
flex box  (0) 2021.01.25
날짜, 위경도, 구글글꼴  (0) 2021.01.25
몽고디비- 로그인 수정중  (0) 2021.01.22

- flex box사용

 

    <div class="d-flex flex-row bd-highlight mb-3">
        <div class="p-2 bd-highlight">Flex item 1</div>
        <div class="p-2 bd-highlight">Flex item 2</div>
        <div class="p-2 bd-highlight">Flex item 3</div>
    </div>
    <div class="d-flex flex-row-reverse bd-highlight">
        <div class="p-2 bd-highlight">Flex item 1</div>
        <div class="p-2 bd-highlight">Flex item 2</div>
        <div class="p-2 bd-highlight">Flex item 3</div>
    </div>

필요없는 것 지우기 

 

    <div class="d-flex flex-row mb-3">
        <div> 날씨 1</div>
        <div> 날씨 2</div>
        <div >날씨 3</div>
    </div>
    <div class="d-flex flex-column mb-3">
        <div>시간 1</div>
        <div>시간 2</div>
        <div>시간 3</div>
    </div>

 

 

    <div class="d-flex flex-row">
    <div class="d-flex flex-row mb-3" style="background-color: blue;">
        <div> 날씨 1</div>
        <div> 날씨 2</div>
        <div >날씨 3</div>
    </div>
    <div class="d-flex flex-column mb-3" style="background-color: green;">
        <div>시간 1</div>
        <div>시간 2</div>
        <div>시간 3</div>
    </div>
    </div>  

 


 

<!doctype html>
<html lang="en">
  <head>
    <title>Title</title>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Nanum+Brush+Script&display=swap" rel="stylesheet">
    
  </head>
  <body>
    <div class="d-flex flex-row justify-content-end">
        <div class="d-flex flex-column" style="border: 1px solid blue;">
            <div id="cityname">
            </div>
            <div>
                <span>
                    <img id="weather_icon">
                </span>
                <span id="weather_description">
                </span>
            </div>
            <div id="temp"></div>
        </div>
        <div class="d-flex flex-column" style="border: 1px solid red;">
            <div>시간 1</div>
            <div>시간 2</div>
            <div>시간 3</div>
        </div>  
    </div>      
      <div style="font-family: 'Nanum Brush Script', cursive; font-size: 40px" id="cityname">
      </div>
      <div class="temp">
      </div>  
      <img class="myicon">

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.5.1.min.js" integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>

<script>
    if ("geolocation" in navigator) {
        navigator.geolocation.getCurrentPosition(getweather);
    }

    function getweather(position) {
        var latitude = position.coords.latitude;
        var longitude = position.coords.longitude;
        var apiKey = "e5d4ba22d1c0aae4130753ea87c69eec";

        $.getJSON("http://api.openweathermap.org/data/2.5/weather?lat=" + latitude + "&lon=" + longitude + "&appid=" + apiKey, function(data) {
        var city_name = data["name"];
        
        var country_name = data["sys"]["country"];
        var weather_description = data["weather"][0]["description"];
        var temp = data["main"]["temp"] - 273;
        var icon = data["weather"][0]["icon"]

        $("#cityname").html(city_name + " &#40;" + country_name + "&#41; ");
        $("#temp").html(temp + '&deg;' + 'C');
        $("#weather_description").html(weather_description)
        
        var imgsrc = 'http://openweathermap.org/img/wn/' + icon + '@2x.png'
        $("#weather_icon").attr('src', imgsrc);
      });
    }    
 </script>

 

-

layout.html 에서 수정된 부분 

<div class="container">
<div class="container">
    <div class="d-flex flex-row justify-content-end">
        <div class="d-flex flex-column" style="border: 1px solid blue;">
            <div id="cityname">
            </div>
            <div>
                <span>
                    <img id="weather_icon">
                </span>
                <span id="weather_description">
                </span>
            </div>
            <div id="temp"></div>
        </div>
        <div class="d-flex flex-column" style="border: 1px solid red;">
            <div>시간 1</div>
            <div>시간 2</div>
            <div>시간 3</div>
        </div>
    </div>

    {% block content %}
    <!-- 여기는 콘텐츠가 replace -->
    {% endblock %}
</div>
$("#temp").html(Math.round(temp) + '&deg;' + 'C');

'mongodb' 카테고리의 다른 글

mysql - python 연동  (0) 2021.01.26
mysql  (0) 2021.01.25
날짜, 위경도, 구글글꼴  (0) 2021.01.25
몽고디비- 로그인 수정중  (0) 2021.01.22
몽고디비 - 날씨, 온도표시  (0) 2021.01.22

f12눌러서 console창에 위도 경도 뜸 

 

 

<!DOCTYPE html>
<html>
    <head>
        <title>title</title>
    </head>
    <body>
        <div id="current_datetime">
        </div>
    </body>
    <script>
        function current_datetime() {
            var today = new Date();
        var year = today.getFullYear()
        var month = today.getMonth() + 1
        var day = today.getDate()

        var hh = today.getHours();
        var mi = today.getMinutes();
        var ss = today.getSeconds();

        document.getElementById("current_datetime").innerHTML = year + '-' 
        + month + '-' + day + ' ' + hh + ':' + mi + ':' + ss
        }
    
    
    setInterval(current_datetime,1000);

    if ("geolocation" in navigator) {
        navigator.geolocation.getCurrentPosition(getweather);
    }

    function getweather(position) {
        var latitude = position.coords.latitude;
        var longitude = position.coords.longitude;
        console.log('latitude= ' + latitude);
        console.log('longitude= '+ longitude);
        var apiKey = "e5d4ba22d1c0aae4130753ea87c69eec";  
    }
    </script>
</html>

 

 

- bootstrap 다운 

 

-b4치면 

여러 개 뜸 

- b4-$ 누르면 

 

<!doctype html>
<html lang="en">
  <head>
    <title>Title</title>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
  </head>
  <body>
      
    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>

여기서 slim 지우기, jquery 버전 바꾸기  

 

jquery.com/

 

jQuery

What is jQuery? jQuery is a fast, small, and feature-rich JavaScript library. It makes things like HTML document traversal and manipulation, event handling, animation, and Ajax much simpler with an easy-to-use API that works across a multitude of browsers.

jquery.com

download 맨 밑 클릭
copy 

 

- 구글에서 기상정보 가져오기 

 

 

 

- .temp 는 class #cityname은 id 

<!doctype html>
<html lang="en">
  <head>
    <title>Title</title>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
  </head>
  <body>
      <div id="cityname">
      </div>
      <div class="temp">
      </div>  

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.5.1.min.js" integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>

<script>
    if ("geolocation" in navigator) {
        navigator.geolocation.getCurrentPosition(getweather);
    }

    function getweather(position) {
        var latitude = position.coords.latitude;
        var longitude = position.coords.longitude;
        console.log('latitude = ' + latitude);
        console.log('longitude = ' + longitude);

        var apiKey = "e5d4ba22d1c0aae4130753ea87c69eec";

         $.getJSON("http://api.openweathermap.org/data/2.5/weather?lat=" + latitude + "&lon=" + longitude + "&appid=" + apiKey, function(data) {
        console.log(data);
        var city_name = data["name"];
        
        var country_name = data["sys"]["country"];
        var weather_description = data["weather"][0]["description"];
        var temp = data["main"]["temp"] - 273;
        var icon = data["weather"][0]["icon"]

        console.log(icon);

        $("#cityname").html(city_name + " &#40;" + country_name + "&#41; " + "has " + weather_description);
        $(".temp").html(temp + '&deg;' + 'C');
       

      });
    }    
 </script>

 

- 이미지 추가 

 

 

<!doctype html>
<html lang="en">
  <head>
    <title>Title</title>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
  </head>
  <body>
      <div id="cityname">
      </div>
      <div class="temp">
      </div>  
      <img class="myicon">

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.5.1.min.js" integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>

<script>
    if ("geolocation" in navigator) {
        navigator.geolocation.getCurrentPosition(getweather);
    }

    function getweather(position) {
        var latitude = position.coords.latitude;
        var longitude = position.coords.longitude;
        console.log('latitude = ' + latitude);
        console.log('longitude = ' + longitude);

        var apiKey = "e5d4ba22d1c0aae4130753ea87c69eec";

         $.getJSON("http://api.openweathermap.org/data/2.5/weather?lat=" + latitude + "&lon=" + longitude + "&appid=" + apiKey, function(data) {
        console.log(data);
        var city_name = data["name"];
        
        var country_name = data["sys"]["country"];
        var weather_description = data["weather"][0]["description"];
        var temp = data["main"]["temp"] - 273;
        var icon = data["weather"][0]["icon"]

        console.log(icon);

        $("#cityname").html(city_name + " &#40;" + country_name + "&#41; " + "has " + weather_description);
        $(".temp").html(temp + '&deg;' + 'C');
        var srcinfo = 'http://openweathermap.org/img/wn/' + icon + '@2x.png'
        $(".myicon").attr('src', srcinfo);
      });
    }    
 </script>

 

-글꼴 바꾸기

fonts.google.com/

 

Google Fonts

Making the web more beautiful, fast, and open through great typography

fonts.google.com

한국어로 바꾸기

 

원하는 글꼴 클릭 
copy 하기 

 

<link rel="preconnect" href="https://fonts.gstatic.com">
<link href="https://fonts.googleapis.com/css2?family=Gamja+Flower&display=swap" rel="stylesheet">

copy한 것을 </head> 윗 줄에 넣기 

 

font-family: 'Gamja Flower', cursive;

copy한 것을 <body> 원하는 곳에 넣기

'mongodb' 카테고리의 다른 글

mysql  (0) 2021.01.25
flex box  (0) 2021.01.25
몽고디비- 로그인 수정중  (0) 2021.01.22
몽고디비 - 날씨, 온도표시  (0) 2021.01.22
몽고디비 7일차 - book_store 시간, 검색기능 추가  (0) 2021.01.22

Create Database -> Create

 

추가하기 

 

 

'mongodb' 카테고리의 다른 글

flex box  (0) 2021.01.25
날짜, 위경도, 구글글꼴  (0) 2021.01.25
몽고디비 - 날씨, 온도표시  (0) 2021.01.22
몽고디비 7일차 - book_store 시간, 검색기능 추가  (0) 2021.01.22
몽고디비 7일차 -atlas  (0) 2021.01.22

+ Recent posts