Sql Alchemy¶

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)

Out[2]:

	car_id	name	price	year

In [3]:

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

[('car_id', INTEGER(display_width=10, unsigned=True)), ('name', CHAR(length=10)), ('price', INTEGER(display_width=11)), ('year', CHAR(length=4))]

TABLE¶

raw SQL, SQL Alchemy, Pandas 활용

1 raw SQL 을 사용한 TABLE¶

SQL raw 문법의 적용, Pandas 활용

"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()

Out[4]:

[('car_data',), ('cars',), ('estate',), ('estatedb',)]

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()

Out[5]:

[('car_data',), ('cars',), ('estate',), ('estatedb',)]

In [6]:

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

{'car_id': INTEGER(display_width=10, unsigned=True), 'name': CHAR(length=10), 'price': INTEGER(display_width=11), 'year': CHAR(length=4)}

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;")

Out[7]:

<sqlalchemy.engine.result.ResultProxy at 0x7fe2c43a8400>

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()

[('car_data',), ('cars',), ('estate',), ('estatedb',)]

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()

Out[10]:

[(1, 'naveen', 60000.0, 1)]

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()

Out[11]:

[(1, 'naveen', 60000.0, 1),
 (1, 'satvik', 60000.0, 1),
 (2, 'ramesh', 70000.0, 1),
 (3, 'ram', 80000.0, 0)]

In [12]:

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

Out[12]:

	Id	name	salary	active
0	1	naveen	60000.0	1
1	1	satvik	60000.0	1
2	2	ramesh	70000.0	1
3	3	ram	80000.0	0

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()

Out[13]:

([(1, 'naveen', 100000.0, 1),
  (1, 'satvik', 100000.0, 1),
  (2, 'ramesh', 70000.0, 1),
  (3, 'ram', 80000.0, 0)],
 ['Id', 'name', 'salary', 'active'])

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()

Out[14]:

[(1, 'naveen', 100000.0, 1),
 (1, 'satvik', 100000.0, 1),
 (2, 'ramesh', 70000.0, 1),
 (3, 'ram', 80000.0, 0)]

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()

Out[15]:

[(2, 'ramesh', 70000.0, 1), (3, 'ram', 80000.0, 0)]

In [ ]: