zero-base 취업스쿨/스터디노트/6주차/ SQL #21

1. Scalar Functions

1-1 환경설정

• 'sandwich' 테이블 만들기

import mysql.connector
import pandas as pd

conn = mysql.connector.connect(
    host = "엔드포인트입력",
    port=포트입력,
    user="사용자입력",
    password="패스워드입력",
    database="zerobase"
)

df = pd.read_csv("sandwiches.csv", encoding='utf-8')

sql = "create table sandwich(ranking int, cafe varchar(32), munu varchar(32), price float, address varchar(32))"
cursor = conn.cursor(buffered=True)
cursor.execute(sql)

cursor.execute('desc sandwich')
result = cursor.fetchall()

#result 실행결과 
[('ranking', 'int', 'YES', '', None, ''),
 ('cafe', 'varchar(32)', 'YES', '', None, ''),
 ('munu', 'varchar(32)', 'YES', '', None, ''),
 ('price', 'float', 'YES', '', None, ''),
 ('address', 'varchar(32)', 'YES', '', None, '')]

sql = "insert into sandwich values (%s,%s,%s,%s,%s)"

for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    conn.commit()

conn.close()

1-2