[MySQL] pymysql, pandas

pymysql

python에서 mysql 사용하는 라이브러리. 크롤링과 같은 작업때문에 파이썬에서 다룰 일이 생김.

import pymysql

db = pymysql.connect(host='localhost', port=3306, 
	user='root', passwd='비밀번호', db='ecommerce', charset='utf8')
    
ecommerce = db.cursor()

sql = """
    CREATE TABLE product (
        PRODUCT_CODE VARCHAR(20) NOT NULL,
        TITLE VARCHAR(200) NOT NULL,
        ORI_PRICE INT,
        DISCOUNT_PRICE INT,
        DISCOUNT_PERCENT INT,
        DELIVERY VARCHAR(2),
        PRIMARY KEY(PRODUCT_CODE)
    );
    
ecommerce.execute(sql)
db.commit()
db.close()
"""

import pymysql

db = pymysql.connect(host='localhost', port=3306,
	user='root', passwd='비밀번호', db='ecommerce', charset='utf8')
    
cursor = db.cursor()

sql = "SELECT * FROM product"

cursor.execute(sql)

result = cursor.fetchmany(2)

for record in result:
    print(result)
    
db.close()

pandas

python에서 mysql을 쉽게 읽기 위한 라이브러리. csv파일로 저장할 때에도 사용됨

import pymysql
import pandas as pd

host_name = 'localhost'
host_port = 3306
user_name = 'root'
password = '비밀번호'
database_name = 'student_mgmt'

db = pymysql.connect(
    host=host_name,
    port=host_port,
    user=user_name,
    password=password,
    db=database_name,
    charset='utf8'
)

SQL = 'select * from students'
df = pd.read_sql(SQL, db)
df

df.to_csv('students.csv', sep=',', index=False, encoding='utf-8')