db.close()
import requests
from bs4 import BeautifulSoup
import pandas as pd
import pymysql
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
db = pymysql.connect(
host=host_name, # DATABASE_HOST
port=3306,
user=username, # DATABASE_USERNAME
passwd=password, # DATABASE_PASSWORD
db=database_name, # DATABASE_NAME
charset='utf8'
)
CREATE TABLE userTbl (
userID CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDate DATE
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
SQL = "DESC userTbl"
df = pd.read_sql(SQL, db)
df
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
CREATE TABLE buyTbl (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(4),
groupName CHAR(4),
price INT NOT NULL,
amount SMALLINT NOT NULL,
FOREIGN KEY (userID) REFERENCES userTbl(userID)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
SQL = "DESC buyTbl"
df = pd.read_sql(SQL, db)
df
SQL = "SHOW INDEX FROM buyTbl"
df = pd.read_sql(SQL, db)
df
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
SQL_QUERY = "ALTER TABLE userTbl ADD CONSTRAINT TESTDate UNIQUE(mDate);"
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
SQL_QUERY = "CREATE INDEX idx_name ON userTbl (name);"
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
SQL_QUERY = "ALTER TABLE userTbl ADD INDEX idx_addr (addr)"
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
SQL_QUERY = "CREATE INDEX idx_groupName ON buyTbl (groupName);"
cursor.execute(SQL_QUERY)
db.commit()
SQL_QUERY = "ALTER TABLE buyTbl ADD INDEX idx_prodName (prodName)"
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM buyTbl"
df = pd.read_sql(SQL, db)
df
db.close()
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
db = pymysql.connect(
host=host_name, # DATABASE_HOST
port=3306,
user=username, # DATABASE_USERNAME
passwd=password, # DATABASE_PASSWORD
db=database_name, # DATABASE_NAME
charset='utf8'
)
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
SQL_QUERY = """
DROP DATABASE IF EXISTS sqlDB;
CREATE DATABASE sqlDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
USE sqlDB;
DROP TABLE IF EXISTS userTbl;
CREATE TABLE userTbl (
userID CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) UNIQUE NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDate DATE,
UNIQUE INDEX idx_userTbl_name (name),
INDEX idx_userTbl_addr (addr)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS buyTbl;
CREATE TABLE buyTbl (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(4),
groupName CHAR(4),
price INT NOT NULL,
amount SMALLINT NOT NULL
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO userTbl VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '운동화', '의류', 30, 2);
"""
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_name"
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_addr"
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
SQL_QUERY = ""
cursor.execute(SQL_QUERY)
db.commit()
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df