insert data into a table mysql-CodePudding

I extract data from truecar.com. This info contains price and miles of different car. Now, I want to insert this information into a table but my code doesn't work to create a table and return only 'price' and 'miles' instead of their numbers. could you help me? Here is my code:

import requests
from bs4 import BeautifulSoup
import mysql.connector
car=str(input())
ur='https://www.truecar.com/used-cars-for-sale/listings/'
url=ur car
r=requests.get(url)
soup=BeautifulSoup(r.text,'html.parser')

data = []
for card in soup.select('[]'):
price = card.select_one('[]').text

miles = card.select_one('div[]').text

data.append({
    'price':price,
    'miles':miles
})
print(data)

cnx = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='truecar')
cursor = cnx.cursor()
for price,miles in data:
     cursor.execute("INSERT INTO car VALUES(\'%s\',\'%s\')"%(price,miles))
     cnx.commit()
cnx.close()

CodePudding user response：

If you just execute the following code:

price = 10
miles = 20

data = []
data.append({
    'price':price,
    'miles':miles
})

for price, miles in data:
    print(price, miles)

the output on the console will be price miles. So you are not accessing the values of the dictionary but the keys. Therefore you do not write the keys to the database instead of the values. What works is the following code:

price = 10
miles = 20

data = []
data.append({
    'price':price,
    'miles':miles
})
    
for entry in data:
    print(entry["price"], entry["miles"])

I think that Saqibs answer should also be considered because adding the columns of the database to which the data is added will lead to less bugs in more complex software.

CodePudding user response：

Try:

cursor.execute("INSERT INTO car(price, miles) VALUES(\'%s\',\'%s\')"%(price,miles))

cursor.execute("INSERT INTO car(price, miles) VALUES(?,?)", (price, miles))

I ran your code. Your problem is here because data var list is empty:

See:

CodePudding user response：

If you table have the table in database means you have to use

INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('
   Mac', 'Mohan', 20, 'M', 2000
)

Else you need to create the new table means execute this

sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)

My suggestion is to use the ORM library. Please refer this link sqlmodel