import pymysql

 HOST = '127.0.0.1'

 PORT = 3306

 USER = 'root'

 PASSWD = ''

 DB = 'test'

 CHARSET = 'utf8'

 conn = pymysql.connect(

     host=HOST,

     port=PORT,

     user=USER,

     passwd=PASSWD,

     db=DB,

     charset=CHARSET

 )

 cursor = conn.cursor()

 create_dep = """CREATE TABLE department(

 dep_id INT PRIMARY KEY,

 dep_name VARCHAR(20) NOT NULL UNIQUE

 )"""

 create_emps = """CREATE TABLE employees(

 emp_id INT PRIMARY KEY,

 emp_name VARCHAR(20) NOT NULL UNIQUE,

 birth_date DATE,

 email VARCHAR(30),

 dep_id INT,

 FOREIGN KEY(dep_id) REFERENCES department(dep_id)

 )"""

 create_slary = """CREATE TABLE slary(

 auto_id INT PRIMARY KEY,

 date DATE,

 basic INT,

 awards INT,

 emp_id INT,

 FOREIGN KEY(emp_id) REFERENCES employees(emp_id)

 )"""

 # cursor.execute(create_dep)

 # cursor.execute(create_emps)

 # cursor.execute(create_slary)

 # insert = 'insert into department VALUES (%s,%s)'

 # cursor.execute(insert,(1,'HR'))

 # cursor.execute(insert,(2,'DEVOPS'))

 # cursor.execute(insert,(3,'DEV'))

 # cursor.executemany(insert,[(4,'OPS'),(5,'MANAGER')])

 # select = 'select * from department order by dep_id'

 # cursor.execute(select)

 # result = cursor.fetchone()

 # print(result)

 # result = cursor.fetchmany(2)

 # print(result)

 # result = cursor.fetchall()

 # print(result)

 # select = 'select * from department order by dep_id'

 # cursor.execute(select)

 # cursor.scroll(4)  # 默认以相对方式，从当前位置向下移动

 # result = cursor.fetchone()

 # print(result)

 # cursor.scroll(0,mode='absolute')  # absolute 一定是从开头移动

 # result = cursor.fetchone()

 # print(result)

 #

 # update1 = 'update department set dep_name=%s where dep_name=%s'

 # cursor.execute(update1,('rs','HR'))

 delete1 = 'delete from department where dep_id=%s'

 cursor.execute(delete1,(5,))

 conn.commit()

 cursor.close()

 conn.close()

 if __name__ == "__main__":

     pass