def main():
while True:
print(":=="*20)
print("\t\tCONSOLE CRUD WITH SQLITE3")
print(":=="*20)
print("[C]REATE NEW DATABASE")
print("[D]ROP DATABASE")
print("[U]SE DATABASE")
print("[E]xit")
print(":=="*20)
try:
opt = input('Select one option:\n')
if opt.upper() == 'C':
crud.clear()
db.createDB()
elif opt.upper() == 'D':
db.dropDB()
elif opt.upper() == 'U':
while True:
print("[1] Insert new record")
print("[2] Show all records")
print("[3] Update a record")
print("[4] Delete a record")
print("[5] Search a record")
print("[6] Exit")
print(":=="*20)
try:
option = int(input('Select a option:\n'))
if option == 1:
crud.clear()
crud.create()
if option == 2:
crud.clear()
crud.get()
if option == 3:
crud.update()
elif option == 4:
crud.delete()
elif option == 6:
crud.clear()
break
except:
print('Option invalid!')
elif opt.upper() == 'E':
break
except:
print('Option invalid!')
def test_crud():
company = Company()
session.add(company)
assert [] == crud.object_list(session, company, MyModel)
obj = crud.create(session, company, MyModel, name='xxx')
assert obj.name == 'xxx'
session.commit()
read_obj = crud.read(session, company, MyModel, obj.id)
assert read_obj == obj
assert 1 == crud.object_count(session, company, MyModel)
assert [read_obj] == crud.object_list(session, company, MyModel)
crud.update(session, company, MyModel, obj.id, name='aaa')
session.commit()
update_obj = crud.read(session, company, MyModel, obj.id)
assert update_obj.name == 'aaa'
crud.delete(session, company, MyModel, obj.id)
delete_obj = crud.read(session, company, MyModel, obj.id)
assert delete_obj is None
assert 0 == crud.object_count(session, company, MyModel)
async def add_zarowny_n_others(db: Session = Depends(get_db)):
url_list = [
'https://www.zarownymotors.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.westlockford.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.griffithsford.ca/inventory/search?stock_type=Used&page=1&page_length=100&sort_by=price&sort_order=DESC&query=',
'https://www.rainbowford.ca/inventory/search?stock_type=Used&page=1&page_length=100&sort_by=price&sort_order=DESC',
]
start_time = time()
done_for = []
for url in url_list:
car_count = 0
logging.info(f'For url: {url}')
res = get_car_info_from_web(url)
done_for.append(url)
for one_car in res:
logging.info(one_car)
car_return = crud.create(db, car_in=one_car, autocommit=True)
car_count = car_count + 1 if car_return["in_db"] == "Added" else car_count
logging.info(f' --- Car status in DB: {car_return["in_db"]}')
logging.info(f'Total Cars added: {car_count}')
# crud.create_car_dict(db, one_car, website=url)
# db.commit() # Uncomment if using autocommit=False
logging.info(f'Time in Hours: {(time() - start_time) / (60 * 60)}')
logging.info(f'Time in Minutes: {(time() - start_time) / 60}')
return done_for
def entry():
req = {}
if request.method == 'POST':
req = request.json
crud.create(req["message"])
return req
if request.method == 'GET':
req = request.json
retrive = crud.retrive(req["message"])
return retrive
if request.method == 'PUT':
req = request.json
update = crud.update(req["_id"], req["message"])
return update
if request.method == 'DELETE':
req = request.json
crud.delete(req["message"])
return req
return req
async def add_hardcoded_car( db: Session = Depends(get_db) ):
car_info = {"car_name": "Honda city",
"mileage": "12245km",
"exterior": "white",
"drivetrain": "4x4",
"transmission": "auto",
"engine": "vti",
"date": "2020-11-10"}
car_info = {'car_name': '2018 Ford F-350 Lariat | Nav | Quad Beams | BLIS | Only 37K!', 'price': 74221,
'car_page_link': 'http://www.hardcodded-link.com/first-car-on-the-page',
'website': 'http://www.hardcodded-link.com',
'mileage': 37825, 'body_style': 'Truck', 'engine': '6.7L Power Stroke', 'exterior': 'White',
'transmission': '6 Speed Automatic', 'drivetrain': '4x4',
'img_link': 'https://chrome.cdn.searchoptics.com/inventory_parser/uploads/2019/ford/ranger/1FTER4FH8KLA92282/640/2019-ford-ranger-1486285608.jpg'}
logging.info(car_info)
car_return = crud.create(db, car_in=car_info, autocommit=True)
logging.info(f' --- Car status in DB: {car_return["in_db"]}')
return car_return
async def add_few_sites(db: Session = Depends(get_db)):
""" Adds cars to DB from a few websites """
url_list = [ 'https://www.highriverford.com/used-cars-high-river-ab?sort=Sfield_Price&direction=desc', ]
start_time = time()
done_for = []
for url in url_list:
car_count = 0
logging.info(f'For url: {url}')
res = get_car_info_from_web(url)
done_for.append(url)
for one_car in res:
logging.info(one_car)
car_return = crud.create(db, car_in=one_car, autocommit=True)
car_count = car_count + 1 if car_return["in_db"] == "Added" else car_count
logging.info(f' --- Car status in DB: {car_return["in_db"]}')
logging.info(f'Total Cars added: {car_count}')
# crud.create_car_dict(db, one_car, website=url)
# db.commit() # Uncomment if using autocommit=False
logging.info(f'Time in Hours: {(time() - start_time) / (60 * 60)}')
logging.info(f'Time in Minutes: {(time() - start_time) / 60}')
return done_for
async def add_cars(db: Session = Depends(get_db)):
""" Add cars from websites to DB """
# res = crud.create_car(db, "toyota", f"12245km", "black", "4x4", "auto", "vti",
# "uo", "yes", "https://www.google.com")
url_list = [
# # 'https://www.royalford.ca/used/used-vehicle-inventory.html?reset=1', # Got changed
# # 'https://www.metcalfesgarage.ca/used/used-vehicle-inventory.html?reset=1', # Got changed
# # 'https://www.formomotors.com/used/used-vehicle-inventory.html?reset=1', # Got changed
'https://www.regalmotorsltd.com/used/used-vehicle-inventory.html',
'https://www.junctionmotors.com/used/used-vehicle-inventory.html?reset=1',
'https://www.northstarfordsalescalgary.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.collegefordlincoln.com/used/preowned-inventory.html?reset=1',
'https://www.truenorthford.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.lambford.com/used/used-vehicle-inventory.html?reset=1',
'https://www.metroford.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.cslford.ca/used/used-vehicle-inventory.html?reset=2',
'https://www.norrisford.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.patriciafordsales.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.castleford.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.collegefordtaber.com/used/used-vehicle-inventory.html?reset=1',
'https://www.westergardford.com/used/used-vehicle-inventory.html?reset=1',
'https://www.jubileeford.com/used/used-vehicle-inventory.html?reset=1',
'https://www.senchuk.com/used/used-vehicle-inventory.html?reset=1',
'https://www.centennialford.sk.ca/used/used-vehicle-inventory.html',
'https://www.meritford.com/used/used-vehicle-inventory.html?reset=1',
'https://www.twowayservice.com/used/used-vehicle-inventory.html?reset=1',
'https://www.northlandford.mb.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.rhinelandcar.com/used/used-vehicle-inventory.html?reset=1',
'https://www.steeltownford.com/used/used-vehicle-inventory.html?reset=1',
'https://www.gimliford.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.hometownford.ca/used/used-vehicle-inventory.html?reset=1',
'https://www.kelleherforddauphin.com/used/used-vehicle-inventory.html?reset=1',
'https://www.roblinfordsales.com/used/used-vehicle-inventory?reset=1',
'https://www.virdenford.ca/pre-owned/pre-owned-vehicle-inventory.html?reset=1',
'https://www.westwardford.com/used/used-vehicle-inventory.html?reset=1',
'https://www.woodridgeford.com/used/',
'https://www.okotoksford.com/used/',
'https://www.advantageford.ca/used/',
'https://www.countryford.ca/used/',
'https://www.zarownymotors.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.westlockford.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.griffithsford.ca/inventory/search?stock_type=Used&page=1&page_length=100&sort_by=price&sort_order=DESC&query=',
'https://www.bigmford.com/inventory/search?stock_type=Used&page=1&page_length=100&sort_by=price&sort_order=DESC',
'https://www.jerryford.com/inventory/used-inventory?stock_type=Used&page=1&page_length=100',
'https://www.draytonvalleyford.com/inventory/search?page=1&page_length=100&stock_type=Used',
'https://www.harwoodford.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.discoveryfordsales.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.langenburgmotors.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.melodymotors.com/inventory/search?stock_type=Used&sort_by=price&sort_order=ASC&page=1&page_length=100',
'https://www.valleyfordhague.ca/inventory/hague-used-vehicle-inventory?stock_type=Used&page=1&page_length=100',
'https://www.fairwayford.ca/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.coldlakeford.com/inventory/search?stock_type=Used&page=1&page_length=100',
'https://www.fourlaneford.com/used-cars-just-south-of-red-deer-in-innisfail?sort-by=price&direction=high-to-low',
'https://www.aspenford.ca/used-cars-stettler-ab',
'https://www.webbsford.com/used-inventory',
'https://www.suncityford.ca/medicine-hat-used-cars',
'https://www.pineridgeford.com/used-vehicle-sales?sort-by=price&direction=high-to-low',
'https://www.wilfselieford.ca/used-vehicles-just-west-of-winnipeg?sort-by=price&direction=high-to-low'
'https://www.marlboroughford.com/used-cars-calgary-ab?sort=DateInStock&direction=desc',
'https://www.universalford.com/used-cars-calgary-ab?sort=DateInStock&direction=desc&page=1', # scrape first page
'https://www.camclarkfordairdrie.com/vehicles/2020/ford/expedition/olds/ab/47305249/?sale_class=used',
'https://www.integrityford.ca/vehicles/used/?view=grid&sc=used&st=price,desc',
'https://www.moosejawfordsales.com/vehicles/used?view=grid&sc=used',
'https://www.bennettdunlopford.com/vehicles/used?st=price,asc&view=grid&sc=used',
'https://www.rivercityford.ca/vehicles/used/?st=price,desc&view=grid&sc=used',
'https://www.zenderford.com/vehicles/used/?view=grid&sc=used&st=price,desc',
'https://www.boundaryford.com/vehicles/used/?st=price,desc&sc=used&view=grid',
'https://www.maclinfordcalgary.com/vehicles/used/?st=price,desc&sc=used&view=grid',
'https://www.legacyfordponoka.ca/vehicles/used/?view=grid&sc=used',
'https://www.legacyfordrimbey.ca/vehicles/used/?view=grid&sc=used',
'https://www.vegford.ca/vehicles/used/?v1=All&view=grid&sc=used',
'https://www.vickarford.ca/vehicles/used/?view=grid&sc=used&st=price,desc',
'https://www.mid-townford.com/vehicles/used/?sc=used&st=price,desc&view=grid',
# Multi-page
'https://www.greatplainsford.com/vehicles/used/?st=year,desc&view=grid&sc=used&pg=1',
'https://www.denhamford.ca/vehicles/used/?sc=used&st=price,desc&view=grid&pg=1',
'https://www.esterhazyford.ca/vehicles/used/?st=year,desc&view=grid&sc=used&pg=1',
'https://www.futureford.ca/vehicles/used/?st=year,desc&view=grid&sc=used&pg=1',
'https://www.highriverford.com/used-cars-high-river-ab?sort=Sfield_Price&direction=desc',
'https://www.hansenford.ca/inventory/used-vehicles/price-40000--/srp-page-1/',
'https://www.windsorford.com/inventory/Used/?page=1',
'http://www.northstarfordcarsandtrucks.com/used/used-vehicle-inventory.html',
'https://www.strathmoreford.com/used-inventory/index.htm?start=0',
'https://www.revolutionford.com/used-inventory/page/1',
'https://novlanbros.com/inventory/list/#/?&orderBy=1&t=0&filter=[Used%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C',
'https://www.knightfordlincoln.ca/used-vehicles/?_p=0&_dFR%5Btype%5D%5B0%5D=Used&_dFR%5Btype%5D%5B1%5D=Certified%2520Used&_paymentType=our_price',
'https://www.capitalfordlincoln.com/used-vehicles/?_p=0&_dFR%5Btype%5D%5B0%5D=Used&_dFR%5Btype%5D%5B1%5D=Certified%2520Used&_paymentType=our_price',
'https://www.capitalfordwinnipeg.ca/used-vehicles/?_p=0&_dFR%5Btype%5D%5B0%5D=Used&_dFR%5Btype%5D%5B1%5D=Certified%2520Used&_paymentType=our_price',
'https://www.cypressmotors.com/used-vehicles-swift-current-sk',
'https://www.birchwoodford.ca/vehicles/?results_page=1&condition=used&sort=price',
'https://merlinford.com/used-inventory/?pag=1',
'https://kelleherford.com/used-inventory/sort_by/price/sort_order/desc/?pag=1',
'https://www.rainbowford.ca/inventory/?condition=pre-owned', # done
]
# url_list = [ 'https://www.truenorthford.ca/used/used-vehicle-inventory.html?reset=1', ]
start_time = time()
done_for = []
for url in url_list:
car_count = 0
logging.info(f'For url: {url}')
res = get_car_info_from_web(url)
done_for.append(url)
for one_car in res:
logging.info(one_car)
car_return = crud.create(db, car_in=one_car, autocommit=True)
car_count = car_count + 1 if car_return["in_db"] == "Added" else car_count
logging.info(f' --- Car status in DB: {car_return["in_db"]}')
# db.commit() # Uncomment if using autocommit=False
logging.info(f'Total Cars added: {car_count}')
del res
gc.collect()
logging.info(f'Time in Hours: {(time() - start_time) / (60 * 60)}')
logging.info(f'Time in Minutes: {(time() - start_time) / 60}')
return done_for
async def add_sites_set1(db: Session = Depends(get_db)):
""" Adds Cars from website set 1 """
url_list = [
'https://www.camclarkfordairdrie.com/vehicles/2020/ford/expedition/olds/ab/47305249/?sale_class=used',
'https://www.integrityford.ca/vehicles/used/?view=grid&sc=used&st=price,desc',
'https://www.moosejawfordsales.com/vehicles/used?view=grid&sc=used',
'https://www.bennettdunlopford.com/vehicles/used?st=price,asc&view=grid&sc=used',
'https://www.rivercityford.ca/vehicles/used/?st=price,desc&view=grid&sc=used',
'https://www.zenderford.com/vehicles/used/?view=grid&sc=used&st=price,desc',
'https://www.boundaryford.com/vehicles/used/?st=price,desc&sc=used&view=grid',
'https://www.maclinfordcalgary.com/vehicles/used/?st=price,desc&sc=used&view=grid',
'https://www.legacyfordponoka.ca/vehicles/used/?view=grid&sc=used',
'https://www.legacyfordrimbey.ca/vehicles/used/?view=grid&sc=used',
'https://www.vegford.ca/vehicles/used/?v1=All&view=grid&sc=used',
'https://www.vickarford.ca/vehicles/used/?view=grid&sc=used&st=price,desc',
'https://www.mid-townford.com/vehicles/used/?sc=used&st=price,desc&view=grid',
# Multi-page
'https://www.greatplainsford.com/vehicles/used/?st=year,desc&view=grid&sc=used&pg=1',
'https://www.denhamford.ca/vehicles/used/?sc=used&st=price,desc&view=grid&pg=1',
'https://www.esterhazyford.ca/vehicles/used/?st=year,desc&view=grid&sc=used&pg=1',
'https://www.futureford.ca/vehicles/used/?st=year,desc&view=grid&sc=used&pg=1',
'https://www.highriverford.com/used-cars-high-river-ab?sort=Sfield_Price&direction=desc',
'https://www.hansenford.ca/inventory/used-vehicles/price-40000--/srp-page-1/',
'https://www.windsorford.com/inventory/Used/?page=1',
'http://www.northstarfordcarsandtrucks.com/used/used-vehicle-inventory.html',
'https://www.strathmoreford.com/used-inventory/index.htm?start=0',
'https://www.revolutionford.com/used-inventory/page/1',
'https://novlanbros.com/inventory/list/#/?&orderBy=1&t=0&filter=[Used%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C',
'https://www.knightfordlincoln.ca/used-vehicles/?_p=0&_dFR%5Btype%5D%5B0%5D=Used&_dFR%5Btype%5D%5B1%5D=Certified%2520Used&_paymentType=our_price',
'https://www.capitalfordlincoln.com/used-vehicles/?_p=0&_dFR%5Btype%5D%5B0%5D=Used&_dFR%5Btype%5D%5B1%5D=Certified%2520Used&_paymentType=our_price',
'https://www.capitalfordwinnipeg.ca/used-vehicles/?_p=0&_dFR%5Btype%5D%5B0%5D=Used&_dFR%5Btype%5D%5B1%5D=Certified%2520Used&_paymentType=our_price',
'https://www.cypressmotors.com/used-vehicles-swift-current-sk',
'https://www.birchwoodford.ca/vehicles/?results_page=1&condition=used&sort=price',
'https://merlinford.com/used-inventory/?pag=1',
'https://kelleherford.com/used-inventory/sort_by/price/sort_order/desc/?pag=1',
'https://www.rainbowford.ca/inventory/?condition=pre-owned', # done
]
start_time = time()
done_for = []
for url in url_list:
car_count = 0
logging.info(f'For url: {url}')
res = get_car_info_from_web(url)
done_for.append(url)
for one_car in res:
logging.info(one_car)
car_return = crud.create(db, car_in=one_car, autocommit=True)
car_count = car_count + 1 if car_return["in_db"] == "Added" else car_count
logging.info(f' --- Car status in DB: {car_return["in_db"]}')
# db.commit() # Uncomment if using autocommit=False
logging.info(f'Total Cars added: {car_count}')
del res
gc.collect()
# crud.create_car_dict(db, one_car, website=url)
logging.info(f'Time in Hours: {(time() - start_time) / (60 * 60)}')
logging.info(f'Time in Minutes: {(time() - start_time) / 60}')
return done_for
"engine": "vti",
"entry_date": "2020-11-10"},
# {"car_name": "Toyota GLI 3",
{"mileage": 200,
"exterior": "White",
"drivetrain": "4x4",
"price": 200_000,
"transmission": "auto",
"engine": "vti",
"entry_date": "2020-11-10"},
# {"car_name": "Hustler",
# "mileage": 200,
# "exterior": "Black",
# "drivetrain": "4x4",
# "price": 200_000,
# "transmission": "auto",
# "engine": "vti",
# "entry_date": "2020-11-10"},
]
for one_car in list_of_car_info:
res = crud.create(db, car_in=one_car, autocommit=True)
return res
@router.get("/test/cars/")
async def read_item(skip: int = 0, limit: int = 10):
return {"skip": skip, "limit": limit}
# User enters wanted action
# 1 - To Create a new Password
# 2 - To Read selected Password
# 3 - To Update selected Password
# 4 - To Delete selected Password
# 5 - To Exit The Application
choice = input(
"Enter the number of the operation you would like to perform: ")
if choice == "1":
website_to_create = input(
"\nEnter the website for which you want the password to be created: "
)
password_to_create = input("Enter the password: "******"2":
website_to_read = input("\nEnter the name of the website: ")
read(website_to_read)
elif choice == "3":
website_to_update = input(
"\nEnter the website for which you want the password to be updated: "
)
password_to_update = input("Enter the new password: "******"4":
website_to_delete = input(
"\nEnter the website for which you want the password to be deleted: "
)
delete(website_to_delete)
elif choice == "5":
crud.load()
loaded = True
skipFirst = True
show_menu(skipFirst)
secondMenu = True
inner_decision2 = crud.take_decision()
while inner_decision2 not in range(1, 8) or None:
if inner_decision2 is None:
print("{}There is no option like that!{}".format(
colors["red_txt_black_bgr"], colors["white_txt"]))
inner_decision2 = crud.take_decision()
if inner_decision2 == 1:
crud.create()
elif inner_decision2 == 2:
crud.print_data()
elif inner_decision2 == 3:
crud.update()
elif inner_decision2 == 4:
crud.delete()
elif inner_decision2 == 5:
crud.count_age()
elif inner_decision2 == 6:
crud.count_gender()
elif inner_decision2 == 7:
skipFirst = False
secondMenu = False
else:
print(
def update():
CChis = []
res = []
details = []
Municipio = ""
Casos = ""
Tasa = ""
datos = urllib.request.urlopen(
'http://coronavirus.saludchiapas.gob.mx/casos-por-municipio').read(
).decode()
soup = BeautifulSoup(datos)
os.system("cls")
################################################## Detalles de Chiapas
tags = soup.find_all('h5', class_='card-title')
for itemC in tags:
num = itemC.get_text()
CChis.append({'num': num})
################################################## Historial
table = soup.find('table', class_='table')
for fila in table.find_all("tr"):
#if nroFila==2:
nroCelda = 0
for celda in fila.find_all('td'):
if nroCelda == 0:
Municipio = celda.text
#print("Municipios:", Municipio)
if nroCelda == 1:
Casos = celda.text
#print("Casos:", Casos)
if nroCelda == 2:
Tasa = celda.text
res.append({
'Municipio': Municipio,
'Casos': Casos,
'Tasa': Tasa
})
#print("Tasa", Tasa)
nroCelda = nroCelda + 1
#nroFila=nroFila+1
if len(crud.read('History')) == len(res):
print("No hay cambios")
else:
for itemD in crud.read('ActualChis'):
crud.delete('ActualChis', itemD)
for item in CChis:
crud.create('ActualChis', item)
for itemD in crud.read('History'):
crud.delete('History', itemD)
for itemN in res:
crud.create('History', itemN)
print('Datos actualizados con exito')
return 'Datos Actualizados'
def create_quote():
if request.method == 'POST':
quote = request.get_json()
return crud.create(quote)
import crud
import random
import string
from person import Person
def create_name(length):
return ''.join(
random.choice(string.ascii_lowercase) for _ in range(length))
def create_person():
first_name = create_name(4)
last_name = create_name(8)
age = random.randint(1, 100)
sex = random.choice(['m', 'f'])
income = random.randint(0, 10000)
return Person(None, first_name, last_name, age, sex, income)
for i in range(10):
person = create_person()
try:
crud.create(person)
print(f'Person created: {person}')
except:
print('Error.')