How to save scraped data to a MySQL database-CodePudding

I have a python script that scrapes data from a job website. I want to save these scraped data to MySQL database but after writing the code, it connects to the database. Now after connecting, it doesn't create table and as result couldn't insert those data into the table. Please i need my code to store these scraped data to a table in the MYSQL database.

Here's my code

import requests
from bs4 import BeautifulSoup
import mysql.connector

for x in range(1, 210):
    html_text = requests.get(f'https://www.timesjobs.com/candidate/job-search.html? from=submit&actualTxtKeywords=Python&searchBy=0&rdoOperator=OR&searchType=personalizedSearch&luceneResultSize=25&postWeek=60&txtKeywords=Python&pDate=I&sequence={x}&startPage=1').text
    soup = BeautifulSoup(html_text, 'lxml')
    jobs = soup.find_all('li', class_ = 'clearfix job-bx wht-shd-bx')

job_list = []
for job in jobs:
     company_name = job.find('h3', class_ = 'joblist-comp-name').text.strip().replace(' ','')
     keyskill = job.find('span', class_ = 'srp-skills').text.strip().replace(' ','')
     
       
     all_detail = {company_name, keyskill}
     job_list.append(all_detail)
         
     db = mysql.connector.connect(host= 'localhost',
                         user= 'root',
                         password= 'Maxesafrica2')
     
     cursor = db.cursor()
     cursor.execute("CREATE DATABASE first_db")
     print("Connection to MYSQL Established!")

     db = mysql.connector.connect(host= 'localhost',
                     user= 'root',
                     password= 'Maxesafrica2',
                     database = 'first_db'
                    )
     
     print("Connected to Database!")
     cursor = db.cursor()
     mysql_create_table_query = """CREATE TABLE first_tbl (Company Name Varchar(300) NOT NULL,
                                                            Keyskill Varchar(400) NOT NULL)"""
                                
     result = cursor.execute(mysql_create_table_query)
     insert_query = """INSERT INTO first_tbl (Company Name, Keyskill)
                         VALUES (%s, %s)"""
                         
     records_to_insert = job_list  
     
     cursor = db.cursor()
     cursor.executemany(mysql_create_table_query, records_to_insert)                  

     db.commit()
     cursor.close()
     db.close()
     
     print('Done!')

Here's the error I get

Connection to MYSQL Established!
Connected to Database!
Traceback (most recent call last):
File "C:\Users\LP\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 565, in cmd_query
self._cmysql.query(mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Name Varchar(300) NOT NULL,
                                                ' at line 1

CodePudding user response：

Slight issue with the column name. Instead of 'Company Name' it needs to be 'Company_Name'. SQL doesn't like spaces in column names. Updated queries that you should run:

CREATE TABLE first_tbl 
(
    Company_Name Varchar(300) NOT NULL,
    Keyskill Varchar(400) NOT NULL
)

INSERT INTO first_tbl 
    (Company_Name, Keyskill)
VALUES (%s, %s)

CodePudding user response：

I think the issue is related to the field name 'Company Name' you put with a blank in between. Try the name with and _ like this

mysql_create_table_query = """CREATE TABLE first_tbl (Company_Name Varchar(300) NOT NULL,
Keyskill Varchar(400) NOT NULL)"""