#!/usr/bin/env python
# coding: utf-8

# # **Sql Alchemy**
# 1. https://towardsdatascience.com/sqlalchemy-python-tutorial-79a577141a91
# 1. https://github.com/elasarto/sql/blob/master/adv_sql_hw/climate_analysis.ipynb
# 1. https://edykim.com/ko/post/getting-started-with-sqlalchemy-part-2/
# 1. https://code-examples.net/ko/q/1d3659d
# 1. 필드내용의 정의 https://hyeonstorage.tistory.com/290
# 1. https://www.fun-coding.org/mysql_advanced2.html
# 1. http://zetcode.com/db/sqlalchemy/rawsql/
# 1. ! pip install sqlalchemy -U
# ```python
# create_engine(
#     "mysql+pymysql://아이디:"+"암호"+\
#         "@mysql주소:포트/데이터베이스이름?charset=utf8", 
#     encoding='utf-8'
# )
# ```

# # **MySQL 연결**
# SQL Alchemy 을 활용한 MariaDB 적용

# In[ ]:


# SQL Alchemy 로 DataBase 연결하기
from sqlalchemy import create_engine
db_engine = create_engine(
    "mysql+pymysql://" + "root" + ":" + "erdosql" +\
    "@localhost:3306/?charset=utf8", encoding='utf-8')

con = db_engine.connect()
con.execute("use food_data;")
print(con.execute("show tables;").fetchall())


# In[2]:


# 테이블 내용 Pandas 로 확인하기
import pandas as pd
pd.read_sql("SELECT * FROM  cars", con)


# In[3]:


# 테이블 Info 내용확인
from sqlalchemy import inspect
columns = inspect(con).get_columns('cars')
print([(_['name'], _['type'])  for _ in columns])
con.close()


# <br/>
# 
# # **TABLE**
# raw SQL, SQL Alchemy, Pandas 활용

# ## **1 raw SQL 을 사용한 TABLE**
# SQL raw 문법의 적용, Pandas 활용
# ```sql
# "DROP DATABASE IF EXISTS food_data;",
# "CREATE DATABASE food_data DEFAULT CHARACTER SET=utf8;",
# "DROP TABLE IF EXISTS cars;",
# """CREATE TABLE cars (
#         car_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
#         name NCHAR(10) NOT NULL,
#         PRIMARY KEY(car_id));"""
# ```

# In[4]:


# SQL Alchemy 로 DataBase 연결하기
from sqlalchemy import create_engine
db_engine = create_engine(
    "mysql+pymysql://root:" + "erdosql" +\
    "@localhost:3306/?charset=utf8", encoding='utf-8')

con = db_engine.connect()
con.execute("use food_data;")
con.execute("show tables;").fetchall()


# In[5]:


# raw Query 로 TABLE 추가
SQL_QUERY = [
    "DROP TABLE IF EXISTS cars;",
    """CREATE TABLE cars (
        car_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        name NCHAR(10) NOT NULL,
        price INT NOT NULL,
        year CHAR(4) NOT NULL,
        PRIMARY KEY(car_id));"""
]
_ = [con.execute(_) for _ in SQL_QUERY]    
con.execute("show tables;").fetchall()


# In[6]:


# 추가된 cars 테이블 내용의 확인
from sqlalchemy import inspect
columns = inspect(con).get_columns('cars')
print({_['name']: _['type']  for _ in columns})
con.close()


# ## **2 SQL Alchamy 의 API 활용**
# SQL Alchemy 의 API 를 활용

# In[7]:


# SQL Alchemy 로 DataBase 연결하기
import sqlalchemy as db
import pandas as pd
engine = db.create_engine(
    "mysql+pymysql://root:" + "erdosql" +\
    "@localhost:3306/?charset=utf8", encoding='utf-8')

con = engine.connect()
con.execute("use food_data;")
con.execute("DROP TABLE IF EXISTS car_data;")


# In[8]:


# car_data 테이블 생성하기
import sqlalchemy as db
metadata = db.MetaData()
emp = db.Table('car_data', metadata,
    db.Column('Id',     db.Integer()),
    db.Column('name',   db.String(255), nullable=False),
    db.Column('salary', db.Float(),     default=100.0),
    db.Column('active', db.Boolean(),   default=True)
)
metadata.create_all(con) #Creates the table
print(con.execute("show tables;").fetchall())
con.close()


# <br/>
# 
# # **RECORDS**
# raw SQL, SQL Alchemy, Pandas 활용

# ## **1 INSERT Data**
# SQL raw 문법의 적용, Pandas 활용

# In[9]:


# SQL Alchemy 로 DataBase 연결하기
import sqlalchemy as db
import pandas as pd
engine = db.create_engine(
    "mysql+pymysql://root:" + "erdosql" +\
    "@localhost:3306/?charset=utf8", encoding='utf-8')

metadata = db.MetaData()
con = engine.connect()
con.execute("use food_data;")
# 'car_data' 테이블 Instance 만들기
# con.execute("DROP TABLE IF EXISTS car_data;")
emp = db.Table('car_data', metadata, 
               autoload=True, autoload_with=con)


# In[10]:


# 데이터 입력
query = db.insert(emp).values(
    Id=1, name='naveen', salary=60000.00, active=True
)
ResultProxy = con.execute(query)
con.execute(db.select([emp])).fetchall()


# In[11]:


# 데이터 입력 (Multi-Lines)
query = db.insert(emp)
values_list = [
    {'Id':'1', 'name':'satvik', 'salary':60000, 'active':True},
    {'Id':'2', 'name':'ramesh', 'salary':70000, 'active':True},
    {'Id':'3', 'name':'ram',    'salary':80000, 'active':False},
]
ResultProxy = con.execute(query, values_list)
con.execute(db.select([emp])).fetchall()


# In[12]:


# TABLE 의 데이터와 필드값을 호출하여 DataFrame 출력
results = con.execute(db.select([emp])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df


# ## **2 UPDATE Data**
# SQL raw 문법의 적용, Pandas 활용

# In[13]:


# Id 가 1인 salary 값을 모두 100000 으로 UPDATE
query = db.update(emp).values(salary = 100000)
query = query.where(emp.columns.Id == 1)
con.execute(query)
result_data = con.execute(db.select([emp])).fetchall()
result_data, result_data[0].keys()


# ## **3 READ TABLE**
# SQL raw 문법의 적용, Pandas 활용

# In[14]:


# # 테이블 내용 Pandas 로 확인하기
# import pandas as pd
# pd.read_sql("SELECT * FROM  car_data", con)
con.execute("SELECT * FROM car_data;").fetchall()


# ## **4 DELETE TABLE**
# SQL raw 문법의 적용, Pandas 활용

# In[15]:


# 100,000 필드값 인덱스 삭제
emp = db.Table('car_data', metadata, 
               autoload=True, autoload_with=engine)
query = db.delete(emp)
query = query.where(emp.columns.salary == 100000)
con.execute(query)
results = con.execute(db.select([emp])).fetchall()
con.close()
results
# engine.dispatch()


# In[ ]: