Question 22052 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I save scraped data to a MySQL database?

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:UsersLPAppDataLocalProgramsPythonPython310libsite-packagesmysqlconnectorconnection_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
Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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)"""
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search