|
|
# -*— 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") |
