DAY 14 - Flask, Web crawling(BS4), MySQL

 

- 작성중

!pip install pymysql
import pymysql

host_name = 'labuser17mysql.mysql.database.azure.com'
username = 'rahcelk'
password = '!Seoul2022'
database_name = 'classicmodels'

db = pymysql.connect(
    host = host_name, 
    port = 3306,
    user = username,
    passwd = password,
    db = database_name,
    charset = 'utf8',
    ssl={"fake_flag_to_enable_tls":True}
    ) #ssl채널 --> 보안관련

import pandas as pd

SQL = 'SELECT * FROM employees' #쿼리던지기! employees에 대한 데이터를 가져오기
df = pd.read_sql(SQL, db)

type(df)

print(df)

#테이블 만드는 쿼리 #''' 여러줄 만들기 #자동증가column #id TINYINT NOT NULL AUTO_INCREMENT --> PRIMARY KEY(id)

SQL = '''
CREATE TABLE tempTable(
  id TINYINT NOT NULL AUTO_INCREMENT,
  user_name varchar(10) NOT NULL,
  phone varchar(30) NULL,
  PRIMARY KEY(id)
)
'''

#커서만들기
print(SQL)
cursor = db.cursor()
cursor.execute(SQL)
cursor.close()

SQL = "INSERT INTO tempTable(user_name, phone) VALUES('ohmy', '010-1112-1111')"
cursor = db.cursor()
cursor.execute(SQL)
cursor.close()

SQL = 'SELECT * FROM tempTable'
df = pd.read_sql(SQL, db)
df