import mysql.connector
import pandas as pd
conn = mysql.connector.connect(
host = "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com",
port = 3306,
user = "admin",
password = "94woals!!",
database = "zerobase"
)
df = pd.read_csv('sandwich.csv', encoding='utf-8')
df.info()
df.tail(3)
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()
for row in result:
print(row)
sql = "insert into sandwich values (%s,%s,%s,%s,%s)"
for i, row in df.iterrows():
cursor.execute(sql, tuple(row))
print(tuple(row))
conn.commit()
conn.close()
영문을 대문자로 반환
mysql> select ucase('This Is ucase Test.');
+------------------------------+
| ucase('This Is ucase Test.') |
+------------------------------+
| THIS IS UCASE TEST. |
+------------------------------+
mysql> select ucase(menu)
-> from sandwich
-> where price > 15;
+----------------------------------+
| ucase(menu) |
+----------------------------------+
| LOBSTER ROLL |
| GRILLED LAUGHING BIRD SHRIMP AND |
| SHAVED PRIME RIB |
| LOBSTER ROLL |
| GRILLED LAUGHING BIRD SHRIMP AND |
| SHAVED PRIME RIB |
| LOBSTER ROLL |
| GRILLED LAUGHING BIRD SHRIMP AND |
| SHAVED PRIME RIB |
+----------------------------------+
소문자로 반환하는 함수
mysql> select lcase('This Is LCASE Test.');
+------------------------------+
| lcase('This Is LCASE Test.') |
+------------------------------+
| this is lcase test. |
+------------------------------+
mysql> select lcase(menu)
-> from sandwich
-> where price < 5;
+--------------+
| lcase(menu) |
+--------------+
| meatball sub |
| meatball sub |
| meatball sub |
+--------------+
문자열 부분을 반환하는 함수
SELECT MID(string, start_position, lenth);
SELECT MID (가져올 부분, 시작위치, 길이)
mysql> select mid('This is mid test', 1, 4);
+-------------------------------+
| mid('This is mid test', 1, 4) |
+-------------------------------+
| This |
+-------------------------------+
mysql> select mid('This is mid test', 6, 5);
+-------------------------------+
| mid('This is mid test', 6, 5) |
+-------------------------------+
| is mi |
+-------------------------------+
mysql> select mid('This is mid test', -4, 4);
+--------------------------------+
| mid('This is mid test', -4, 4) |
+--------------------------------+
| test |
+--------------------------------+
mysql> select mid(cafe, -4, 4)
-> from sandwich
-> where ranking = 11;
+------------------+
| mid(cafe, -4, 4) |
+------------------+
| Cafe |
| Cafe |
| Cafe |
+------------------+
문자열의 길이를 반환하는 함수
mysql> select length('This is len test');
+----------------------------+
| length('This is len test') |
+----------------------------+
| 16 |
+----------------------------+
mysql> select length(' ');
+-------------+
| length(' ') |
+-------------+
| 1 |
+-------------+
mysql> select length(null);
+--------------+
| length(null) |
+--------------+
| NULL |
+--------------+
지정한 자리에서 반올림
SELECT ROUND (반올림 대상, 반올림 소수점 위치);
위치를 지정하지 않는 경우, 소수점 0자리에서 반올림
현재 날짜 및 시간을 반환
숫자를 천단위 콤마가 있는 형식으로 반환
SELECT FORMAT (포맷을 적용한 문자/숫자, 표시할 소수점 위치);
SELECT FORMAT (12345.6789, 0);