# -*— codeing = utf-8 -*-
# @Time : 2022/2/26 12:54
# @Author : 吴锦婷
# @File : test.py
# @Softwate : PyCharm

import pymysql


class sql():

    def __init__(self):
        # 连接MySQL(socket)
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='84673013', charset="utf8", db="spidetbossdb")
        cursor = conn.cursor()
        self.conn = conn
        self.cursor = cursor
        # 1.查看数据库
        # 发送指令
        #cursor.execute("show databases;")
        # 获得指令结果
        #result = cursor.fetchall()


    def create_table(self,city):
        # 一个城市的一个职业的数据库结构
        # city是城市名字，本身就是utf-8
        cursor = self.cursor
        # 创建一个游标
        conn = self.conn
        create_table = "create table "+city+"""
                                        (
                                        id int not null primary key auto_increment,
                                        job_com varchar(40),
                                        job_names varchar(40),
                                        salary varchar(40),
                                        job_limit varchar(40),
                                        job_benefit text
                                        )default charset=utf8;"""
        cursor.execute(create_table)
        conn.commit()
        cursor.execute("show tables")
        result = cursor.fetchall()
        print(result)

    def Insert_datas(self, city, job_list):
        # job_list 是一个工作信息嵌套列表
        cursor = self.cursor
        conn = self.conn
        for i in job_list:
            sql = """insert into %s(job_com,job_names,salary,job_limit,job_benefit)
                         values("%s", "%s", "%s", "%s", "%s" );                
                """ % (city, i[0], i[1], i[2], i[3], i[4])
            cursor.execute(sql)
            conn.commit()

        #result = cursor.fetchall()
        # 显示整个表
        #cursor.execute("select * from %s;" % (city))
        #result = cursor.fetchall()
        #print(result)
        print("ok,已存入数据库")

    # 到时候要做个能力的副表，和主表连接最好简练


#print(result)
if __name__ == "__main__":
    sql().create_table("nanning")