save scraped data to database python

Question

I scraped data and saved the scraped data to the five lists and I created the table with five rows, now i do not know how to save my scraped data to the database.

my code is:

import requests
from bs4 import BeautifulSoup
import re
import mysql.connector

url = 'https://www.ebay.com/b/Cars-Trucks/6001?_fsrp=0&_sacat=6001&LH_BIN=1&LH_ItemCondition=3000%7C1000%7C2500&rt=nc&_stpos=95125&Model%2520Year=2020%7C2019%7C2018%7C2017%7C2016%7C2015'
res = requests.get(url)
soup = BeautifulSoup(res.text, 'html.parser')
car_titles =[]
title = soup.find_all('h3', class_='s-item__title', limit = 20)
for title_of_car in title:
    car_titles.append(title_of_car.text)

car_brands = []
brands = soup.find_all('span', class_='s-item__dynamic s-item__dynamicAttributes1', limit = 20)
for brand in brands:
    brand = re.sub(r'Make: ','', brand.text)
    car_brands.append(brand)

car_models = []
models = soup.find_all('span', class_='s-item__dynamic s-item__dynamicAttributes2', limit = 20)
for model in models:
    model = re.sub(r'Model: ', '', model.text)
    car_models.append(model)

car_transmissions = []
transmissions = soup.find_all('span', class_='s-item__dynamic s-item__dynamicAttributes3', limit = 20)
for transmission in transmissions:
    transmission = re.sub(r'Transmission: ', '', transmission.text)
    car_transmissions.append(transmission)

car_prices = []
char_list = ['\$', '\,', '\.']
prices = soup.find_all('span', class_='s-item__price', limit = 20)
for price in prices:
    price = re.sub('|'.join(char_list), '', price.text)
    car_prices.append(int(price))

Hi, what have you tried or researched to store the data in your database? Have you done anything? Remember this is a site to support people with specific questions, don't ask unless you have researched or attempted by yourself first. Also, please refer to this general guidelines: [how to ask?](https://stackoverflow.com/help/how-to-ask). Thanks! — EnriqueBet, Jun 02 '20 at 04:47
Hi, yes I have searched a day for this case and triyed that ways but not worked(may be it's because of this my english is not very good) , Thanks — nishida, Jun 02 '20 at 07:43
Your English has not to be perfect or good, but I can't see anything in your code related to the database rather just importing mysql.connector. Add your mysql code to the post! Otherwise, how people will be able to help you? — EnriqueBet, Jun 02 '20 at 17:36
yes, you are right, I'm wipe that part of code to post in here, I told myself it might be better — nishida, Jun 03 '20 at 06:46

score 0 · Answer 1 · answered Jun 02 '20 at 06:48

First get connected to mysql database by

import mysql.connector

config = {
  'user': 'username',
  'password': 'password',
  'host': '127.0.0.1',
  'database': 'dbname',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)

# create cursor to execute mysql commands
cursor = cnx.cursor()

# Now create table like this one
cursor.execute("CREATE TABLE car_titles (title VARCHAR(255))")

# insert data to table like this one
for row in car_titles:
    cursor.execute("INSERT INTO car_titles (title) VALUES (%s)", row)

cnx.close()

Mainly you have to use sql query to create table and insert data. So learn using mysql with python

Then create table

Hi, I know why how to connect mysql in python and insert data in the mysql, but I do not how to insert my scraped data into the my sql becuse I'm not so pro(if possible guide me in this case) — nishida, Jun 02 '20 at 07:35

save scraped data to database python

1 Answers1

Linked