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
Advertisement
Answer
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)