[Database] 2. pymysql , pandas 사용하기

1. pymysql

• connect > cursor > sql구문 > execute > commit > close

#pip install pymysql
# 1. 라이브러리 가져오기
import pymysql

host_name = 'localhost'
host_port = 3306
username = 'root'
password = 'funcoding'
database_name = 'ecommerce'

# 2. 접속하기
db = pymysql.connect(
    host=host_name,     # MySQL Server Address
    port=host_port,          # MySQL Server Port
    user=username,      # MySQL username
    passwd=password,    # password for MySQL username
    db=database_name,   # Database name
    charset='utf8'
)

# 3. 커서 가져오기
cursor = db.cursor()

# 4. SQL 구문 만들기
SQL = """
UPDATE product SET 
    TITLE='달리샵린넨원피스 뷔스티에 썸머 가디건 코디전', 
    ORI_PRICE=33000, 
    DISCOUNT_PRICE=9900, 
    DISCOUNT_PERCENT=70 
    WHERE PRODUCT_CODE='215673141'
"""

# 5. SQL 구문 실행하기
cursor.execute(SQL)

# 6. commit 하기
db.commit()

# 7. close 하기
db.close()

 

2. pandas : 결과 바로 확인, csv 저장

• read_sql( ) //SQL바로 확인
• to_csv( ) //csv 저장, index : 앞에 번호 저장 여부

#pip install pandas
import pymysql
import pandas as pd

host_name = 'localhost'
host_port = 3306
username = 'root'
password = 'funcoding'
database_name = 'student_mgmt'

db = pymysql.connect(
    host=host_name,     # MySQL Server Address
    port=host_port,          # MySQL Server Port
    user=username,      # MySQL username
    passwd=password,    # password for MySQL username
    db=database_name,   # Database name
    charset='utf8'
)

SQL = "SELECT * FROM students"
df = pd.read_sql(SQL, db)
df.to_csv('students.csv', sep=',', index=False, encoding='utf-8')
df