python pymysql将数据存入mysql中

ゞ浴缸里的玫瑰 2023-05-29 03:51 5阅读 0赞

上周用requests 抓取到fsk招聘网的信息忘记存到数据库里面了  
链接：https://blog.csdn.net/qq\_37561833/article/details/102770180  
所以记录一下怎么把数据存到mysql数据库里面，并简单罗列一下pymysql模块的一些用法  
介绍pymysql用法的链接：  
https://blog.csdn.net/qq\_37561833/article/details/102887329

##### 1. 用navicat 新建一个数据表 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTYxODMz_size_16_color_FFFFFF_t_70]

##### 2. 读取之前json的数据 #####

因为之前抓取到的数据已经保存到本地的json文件中，所以现在只要读取文件即可！  
json 模块提供4 种的方法：  
\# dumps ： 把数据类型转换成字符串  
\# dump ： 把数据类型转换成字符串并存储在文件中  
\# loads ： 把字符串转换成数据类型  
\# load ： 把文件打开从字符串转换成数据类型

with open(self.file_path, 'r', encoding='utf-8') as f:
          data = json.load(f)

##### 3. 将数据插入到数据库中 #####

for data in all_data:
        sql = 'insert into {}(job_name,job_type,pubuli_date,organ_name,request_education,request_major,' \
              'request_job_years,request_count,request_detail) values ' \
              '("{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(
               self.my_db_table,
               data['job_name'], data['job_type'], data['pubuli_date'], data['organ_name'], data['request_education'],
               data['request_major'], data['request_job_years'], data['request_count'], data['request_detail']
              )
        self.insert_data(sql)

##### 4.结果![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTYxODMz_size_16_color_FFFFFF_t_70 1] #####

##### 4.记录一下实现过程的代码 #####

import pymysql
    import json
    import os
    
    
    class SaveFoxInfoToMysql(object):
       def __init__(self, file_name):
           # os.getcwd() # 获取当前文件夹的绝对路径
           # os.path.abspath(__file__) # 获取当前文件的绝对路径
           self.file_path = os.path.join(os.getcwd(), 'file_json\\{}'.format(file_name))
           self.conn = None
           self.cursor = None
           self.host = '192.168.163.129'
           self.port = 3306
           self.my_db = 'my_search_db'
           self.my_db_table = 'fox_job_info'
           self.username = 'root'
           self.password = 'root'
           self.charset = 'utf8'
    
       def open_josn_file(self):
           print(self.file_path)
           # json 模块提供的4 个方法
           # dumps ： 把数据类型转换成字符串
           # dump ： 把数据类型转换成字符串并存储在文件中
           # loads ： 把字符串转换成数据类型
           # load ： 把文件打开从字符串转换成数据类型
           with open(self.file_path, 'r', encoding='utf-8') as f:
               data = json.load(f)
           return data
    
       def conn_databases(self):
           # 链接mysql
           self.conn = pymysql.connect(host=self.host, port=self.port, database=self.my_db, user=self.username,
                                       password=self.password, charset=self.charset)
           # 创建游标对象
           self.cursor = self.conn.cursor()
    
       def insert_data(self, sql):
           # 执行insert sql
           if self.cursor:
               self.cursor.execute(sql)
               print('insert ok!')
    
       def search_info(self, sql):
           # 执行 select sql
           data = ''
           if self.cursor:
               data = self.cursor.execute(sql)
               print('search ok!')
           return data
    
       def commit(self):
           # 提交
           if self.conn:
               self.conn.commit()
    
       def close_conn(self):
           # 关闭
           if self.cursor:
               self.cursor.close()
           if self.conn:
               self.conn.close()
    
       def main(self):
           all_data = self.open_josn_file()
           # print(len(all_data))
           self.conn_databases()
           for data in all_data:
               sql = 'insert into {}(job_name,job_type,pubuli_date,organ_name,request_education,request_major,' \
                     'request_job_years,request_count,request_detail) values ' \
                     '("{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(
                      self.my_db_table,
                      data['job_name'], data['job_type'], data['pubuli_date'], data['organ_name'], data['request_education'],
                      data['request_major'], data['request_job_years'], data['request_count'], data['request_detail']
                     )
               self.insert_data(sql)
           # sql1 = 'select * from {}'.format(self.my_db_table)
           # data = self.search_info(sql1)
           # print(data)
           self.commit()
           self.close_conn()
    
    
    if __name__ == "__main__":
       file_name = 'fox_rec_info.json'
       save = SaveFoxInfoToMysql(file_name=file_name)
       save.main()

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTYxODMz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191103222556695.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTYxODMz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTYxODMz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191103223913469.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTYxODMz,size_16,color_FFFFFF,t_70