pymysql CRUD

 

 

import pymysql

import flask

conn, cur = None,None

data1,data2,data3,data4 ='','','',''

row=None

conn = pymysql.connect(

    host="127.0.0.1",

    user="root",

    passwd="0000",

    db='mylist',

    charset='utf8'

)

cur =conn.cursor()

# cur.execute("DROP TABLE IF EXISTS userTable")

# # 입력학기

# cur.execute(f"CREATE TABLE userTable(\

#             number INT PRIMARY KEY AUTO_INCREMENT,\

#             user_id char(4),\

#             userName char(15),\

#             email char(20),\

#             birthYear int)")

# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('hong','홍지윤','hong@naver.com',1996)")

# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('kim','김태연','kim@daum.net',2011)")

# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('star','별사탕','star@paran.com',1990)")

# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('yang','양지은','yang@gmail.com',1993)")

# 삭제

cur.execute(f"DELETE FROM userTable WHERE number='{3}'")

# conn.close()

#변경

new_name = "니도변신"

cur.execute(f'UPDATE userTable SET userName="{new_name}" WHERE number={5}')

# 조회하기

cur.execute("SELECT * FROM userTable")

print(F" ID   사용자ID    사용자이름     이메일        출생연도")

print("-------------------------------------------------------------------")

rows = cur.fetchall()

for row in rows:

    data1=row[0]

    data2=row[1]

    data3=row[2]

    data4=row[3]

    data5=row[4]

    print(f"{data1:<7} {data2:<10} {data3:<10} {data4:<18} {data5:<7}")

# 조회

cur.execute(f"SELECT * FROM userTable WHERE number='{70}'")

row = cur.fetchone()

data1=row[0]

data2=row[1]

data3=row[2]

data4=row[3]

data5=row[4]

print(f'번호:{data1} 아이디:{data2} 이름:{data3} 이메일:{data4} 출생연도:{data5}')

conn.commit()

conn.close()