Home > database >  create a database from web
create a database from web

Time:08-21

I scraped data from a website. I want to create a table in mysql to save data. I create table with this code in my database:

create table car (Model varchar(60), Mileage varchar(60), Price varchar(60))

I also have code to create this data from truecar.com. But I con not insert this data into my table with my code. Could you help me? I face with this error:"ProgrammingError:1064(42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ('$32,000')' at line 1"

import requests
from bs4 import BeautifulSoup
import mysql.connector
url='https://www.truecar.com/used-cars-for-sale/listings/'
r=requests.get(url)

soup=BeautifulSoup(r.text,'html.parser')

cards = soup.select('div.linkable.card.card-shadow.vehicle-card._1qd1muk')

data = []
for card in cards:
    vehicleCardYearMakeModel = card.find("div", {"data-test" : 
"vehicleCardYearMakeModel"}).text.replace('Sponsored', '')
    vehicleMileage = card.find("div", {"data-test" : "vehicleMileage"}).text
    vehiclePrice = card.find("div", {"data-test" : "vehicleCardPricingBlockPrice"}).text
  data.append({'price':vehiclePrice,'miles':vehicleMileage,'models':vehicleCardYearMakeModel})
print(data)
cnx = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='cars')
cursor = cnx.cursor()
for entry in data:
    cursor.execute("INSERT INTO car(Model,Mileage,Price) VALUES(\'%s\',\'%s\,\'%s\')"% 
(entry['models'],entry['miles'],entry['price']))
    cnx.commit()
cnx.close()

CodePudding user response:

You're missing the closing quote (') after the miles value:

cursor.execute("INSERT INTO car(Model,Mileage,Price) VALUES(\'%s\',\'%s\',\'%s\')"% (entry['models'],entry['miles'],entry['price']))
# Here -----------------------------------------------------------------^

Having said that, using placeholders will save you a lot of headaches:

cursor.execute("INSERT INTO car(Model,Mileage,Price) VALUES(%s,%s,%s)", (entry['models'],entry['miles'],entry['price']))
  • Related