{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# **Sql Alchemy**\n", "1. https://towardsdatascience.com/sqlalchemy-python-tutorial-79a577141a91\n", "1. https://github.com/elasarto/sql/blob/master/adv_sql_hw/climate_analysis.ipynb\n", "1. https://edykim.com/ko/post/getting-started-with-sqlalchemy-part-2/\n", "1. https://code-examples.net/ko/q/1d3659d\n", "1. 필드내용의 정의 https://hyeonstorage.tistory.com/290\n", "1. https://www.fun-coding.org/mysql_advanced2.html\n", "1. http://zetcode.com/db/sqlalchemy/rawsql/\n", "1. ! pip install sqlalchemy -U\n", "```python\n", "create_engine(\n", " \"mysql+pymysql://아이디:\"+\"암호\"+\\\n", " \"@mysql주소:포트/데이터베이스이름?charset=utf8\", \n", " encoding='utf-8'\n", ")\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# **MySQL 연결**\n", "SQL Alchemy 을 활용한 MariaDB 적용" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# SQL Alchemy 로 DataBase 연결하기\n", "from sqlalchemy import create_engine\n", "db_engine = create_engine(\n", " \"mysql+pymysql://\" + \"root\" + \":\" + \"erdosql\" +\\\n", " \"@localhost:3306/?charset=utf8\", encoding='utf-8')\n", "\n", "con = db_engine.connect()\n", "con.execute(\"use food_data;\")\n", "print(con.execute(\"show tables;\").fetchall())" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
car_idnamepriceyear
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [car_id, name, price, year]\n", "Index: []" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 테이블 내용 Pandas 로 확인하기\n", "import pandas as pd\n", "pd.read_sql(\"SELECT * FROM cars\", con)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[('car_id', INTEGER(display_width=10, unsigned=True)), ('name', CHAR(length=10)), ('price', INTEGER(display_width=11)), ('year', CHAR(length=4))]\n" ] } ], "source": [ "# 테이블 Info 내용확인\n", "from sqlalchemy import inspect\n", "columns = inspect(con).get_columns('cars')\n", "print([(_['name'], _['type']) for _ in columns])\n", "con.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
\n", "\n", "# **TABLE**\n", "raw SQL, SQL Alchemy, Pandas 활용" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## **1 raw SQL 을 사용한 TABLE**\n", "SQL raw 문법의 적용, Pandas 활용\n", "```sql\n", "\"DROP DATABASE IF EXISTS food_data;\",\n", "\"CREATE DATABASE food_data DEFAULT CHARACTER SET=utf8;\",\n", "\"DROP TABLE IF EXISTS cars;\",\n", "\"\"\"CREATE TABLE cars (\n", " car_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n", " name NCHAR(10) NOT NULL,\n", " PRIMARY KEY(car_id));\"\"\"\n", "```" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('car_data',), ('cars',), ('estate',), ('estatedb',)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# SQL Alchemy 로 DataBase 연결하기\n", "from sqlalchemy import create_engine\n", "db_engine = create_engine(\n", " \"mysql+pymysql://root:\" + \"erdosql\" +\\\n", " \"@localhost:3306/?charset=utf8\", encoding='utf-8')\n", "\n", "con = db_engine.connect()\n", "con.execute(\"use food_data;\")\n", "con.execute(\"show tables;\").fetchall()" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[('car_data',), ('cars',), ('estate',), ('estatedb',)]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# raw Query 로 TABLE 추가\n", "SQL_QUERY = [\n", " \"DROP TABLE IF EXISTS cars;\",\n", " \"\"\"CREATE TABLE cars (\n", " car_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n", " name NCHAR(10) NOT NULL,\n", " price INT NOT NULL,\n", " year CHAR(4) NOT NULL,\n", " PRIMARY KEY(car_id));\"\"\"\n", "]\n", "_ = [con.execute(_) for _ in SQL_QUERY] \n", "con.execute(\"show tables;\").fetchall()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{'car_id': INTEGER(display_width=10, unsigned=True), 'name': CHAR(length=10), 'price': INTEGER(display_width=11), 'year': CHAR(length=4)}\n" ] } ], "source": [ "# 추가된 cars 테이블 내용의 확인\n", "from sqlalchemy import inspect\n", "columns = inspect(con).get_columns('cars')\n", "print({_['name']: _['type'] for _ in columns})\n", "con.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## **2 SQL Alchamy 의 API 활용**\n", "SQL Alchemy 의 API 를 활용" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# SQL Alchemy 로 DataBase 연결하기\n", "import sqlalchemy as db\n", "import pandas as pd\n", "engine = db.create_engine(\n", " \"mysql+pymysql://root:\" + \"erdosql\" +\\\n", " \"@localhost:3306/?charset=utf8\", encoding='utf-8')\n", "\n", "con = engine.connect()\n", "con.execute(\"use food_data;\")\n", "con.execute(\"DROP TABLE IF EXISTS car_data;\")" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[('car_data',), ('cars',), ('estate',), ('estatedb',)]\n" ] } ], "source": [ "# car_data 테이블 생성하기\n", "import sqlalchemy as db\n", "metadata = db.MetaData()\n", "emp = db.Table('car_data', metadata,\n", " db.Column('Id', db.Integer()),\n", " db.Column('name', db.String(255), nullable=False),\n", " db.Column('salary', db.Float(), default=100.0),\n", " db.Column('active', db.Boolean(), default=True)\n", ")\n", "metadata.create_all(con) #Creates the table\n", "print(con.execute(\"show tables;\").fetchall())\n", "con.close()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "
\n", "\n", "# **RECORDS**\n", "raw SQL, SQL Alchemy, Pandas 활용" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## **1 INSERT Data**\n", "SQL raw 문법의 적용, Pandas 활용" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "# SQL Alchemy 로 DataBase 연결하기\n", "import sqlalchemy as db\n", "import pandas as pd\n", "engine = db.create_engine(\n", " \"mysql+pymysql://root:\" + \"erdosql\" +\\\n", " \"@localhost:3306/?charset=utf8\", encoding='utf-8')\n", "\n", "metadata = db.MetaData()\n", "con = engine.connect()\n", "con.execute(\"use food_data;\")\n", "# 'car_data' 테이블 Instance 만들기\n", "# con.execute(\"DROP TABLE IF EXISTS car_data;\")\n", "emp = db.Table('car_data', metadata, \n", " autoload=True, autoload_with=con)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(1, 'naveen', 60000.0, 1)]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 데이터 입력\n", "query = db.insert(emp).values(\n", " Id=1, name='naveen', salary=60000.00, active=True\n", ")\n", "ResultProxy = con.execute(query)\n", "con.execute(db.select([emp])).fetchall()" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(1, 'naveen', 60000.0, 1),\n", " (1, 'satvik', 60000.0, 1),\n", " (2, 'ramesh', 70000.0, 1),\n", " (3, 'ram', 80000.0, 0)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 데이터 입력 (Multi-Lines)\n", "query = db.insert(emp)\n", "values_list = [\n", " {'Id':'1', 'name':'satvik', 'salary':60000, 'active':True},\n", " {'Id':'2', 'name':'ramesh', 'salary':70000, 'active':True},\n", " {'Id':'3', 'name':'ram', 'salary':80000, 'active':False},\n", "]\n", "ResultProxy = con.execute(query, values_list)\n", "con.execute(db.select([emp])).fetchall()" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Idnamesalaryactive
01naveen60000.01
11satvik60000.01
22ramesh70000.01
33ram80000.00
\n", "
" ], "text/plain": [ " Id name salary active\n", "0 1 naveen 60000.0 1\n", "1 1 satvik 60000.0 1\n", "2 2 ramesh 70000.0 1\n", "3 3 ram 80000.0 0" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# TABLE 의 데이터와 필드값을 호출하여 DataFrame 출력\n", "results = con.execute(db.select([emp])).fetchall()\n", "df = pd.DataFrame(results)\n", "df.columns = results[0].keys()\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## **2 UPDATE Data**\n", "SQL raw 문법의 적용, Pandas 활용" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "([(1, 'naveen', 100000.0, 1),\n", " (1, 'satvik', 100000.0, 1),\n", " (2, 'ramesh', 70000.0, 1),\n", " (3, 'ram', 80000.0, 0)],\n", " ['Id', 'name', 'salary', 'active'])" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Id 가 1인 salary 값을 모두 100000 으로 UPDATE\n", "query = db.update(emp).values(salary = 100000)\n", "query = query.where(emp.columns.Id == 1)\n", "con.execute(query)\n", "result_data = con.execute(db.select([emp])).fetchall()\n", "result_data, result_data[0].keys()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## **3 READ TABLE**\n", "SQL raw 문법의 적용, Pandas 활용" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(1, 'naveen', 100000.0, 1),\n", " (1, 'satvik', 100000.0, 1),\n", " (2, 'ramesh', 70000.0, 1),\n", " (3, 'ram', 80000.0, 0)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# # 테이블 내용 Pandas 로 확인하기\n", "# import pandas as pd\n", "# pd.read_sql(\"SELECT * FROM car_data\", con)\n", "con.execute(\"SELECT * FROM car_data;\").fetchall()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## **4 DELETE TABLE**\n", "SQL raw 문법의 적용, Pandas 활용" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[(2, 'ramesh', 70000.0, 1), (3, 'ram', 80000.0, 0)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 100,000 필드값 인덱스 삭제\n", "emp = db.Table('car_data', metadata, \n", " autoload=True, autoload_with=engine)\n", "query = db.delete(emp)\n", "query = query.where(emp.columns.salary == 100000)\n", "con.execute(query)\n", "results = con.execute(db.select([emp])).fetchall()\n", "con.close()\n", "results\n", "# engine.dispatch()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.8" } }, "nbformat": 4, "nbformat_minor": 4 }