[MySQL to Python] 테이블 생성(Create) 및 조회(Selete) 실습

import pymysql
import json
import datetime

#MySQL Connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password', # 자신이 설정한 비밀번호 입력
    db='python_app_dbtest', # 자신이 설정한 database
    charset='utf8'
)

#pyMysql 버전확인
print('pymysql.version', pymysql.__version__)

#데이터베이스 선택
conn.select_db('python_app_dbtest')

#Cursor 연결
c = conn.cursor()
print(type(c))

now = datetime.datetime.now()
nowDatetime = now.strftime('%Y-%m-%d %H:%M:%S')
print('nowDatetime',nowDatetime)

c.execute("CREATE TABLE IF NOT EXISTS users(id bigint(20) NOT NULL, \
                                            username varchar(20) NOT NULL, \
                                            email varchar(30),  \
                                            phone varchar(30), \
                                            website varchar(30), \
                                            regdate varchar(20) NOT NULL, PRIMARY KEY(id))" \
                                            ) #default, AUTO_INCREMENT

try:
    with conn.cursor() as c:
        #json to mysql
        with open('user.json', 'r') as infile:
            r = json.load(infile)
            userData = []
            for user in r:
                t = (user['id'], user['username'], user['email'], user['phone'], user['website'], nowDatetime)
                userData.append(t)
            c.executemany("INSERT INTO users(id, username, email, phone, website, regdate) VALUES (%s, %s, %s, %s, %s, %s)", userData)
            #c.executemany("INSERT INTO users(id, username, email, phone, website, regdate) VALUES (%s, %s, %s, %s, %s, %s)", tuple(userData))
    conn.commit()
finally:
    conn.close()