def add_car(user_id: int, make: str, model: str, year: int, mileage: int,
weekly_mileage: int):
"""
Insert a car row into the cars table.
"""
message = lambda m, s=False: {'message': m, 'success': s}
query = """
INSERT INTO `cars` (
`user_id`, `make`, `model`, `year`, `mileage`, `weekly_mileage`
) VALUES (%s, %s, %s, %s, %s, %s);
"""
user = users.get_by_id(user_id)
if not user:
return message('USER_NOT_FOUND')
try:
db.execute(query,
(user_id, make, model, year, mileage, weekly_mileage))
conn.commit()
return message('Car added!', True)
except IntegrityError:
ez_log('DATA', 'DATA_WARNING',
'Car could not be added for user "%s".' % user_id)
return message('Something went wrong while adding your car.', False)
def is_token_taken(token: str):
"""
Test if token is already taken.
Returns true if token is taken.
"""
if not token:
return
query = "SELECT EXISTS(SELECT 1 FROM `users` WHERE token=%s) AS taken;"
db.execute(query, (token,))
return db.fetchone().get('taken') == 1
def get_by_id(user_id: int, fields='*'):
"""
Fetch user by its id.
Returns user dict or None.
"""
if not user_id:
return
query = "SELECT {} FROM `users` WHERE id=%s LIMIT 1".format(fields)
db.execute(query, (user_id,))
return db.fetchone()
def get_by_token(token: str, fields='*'):
"""
Fetch user by its token.
Returns user dict or None.
"""
if not token:
return
query = "SELECT {} FROM `users` WHERE token=%s LIMIT 1".format(fields)
db.execute(query, (token,))
return db.fetchone()
def update_car(car_id: int, mileage: int, weekly_mileage: int):
"""
Get a list of user's cars by the user's token.
"""
query = """
UPDATE `cars`
SET mileage=%s, weekly_mileage=%s
WHERE cars.id=%s
"""
db.execute(query, (mileage, weekly_mileage, car_id))
return db.fetchall()
def delete_car(user_id: int, car_id: int):
"""
Delete a car row from the cars table by its id field.
"""
query = """
DELETE cars.* FROM `cars`
INNER JOIN `users` ON users.id=cars.user_id
WHERE users.id=%s AND cars.id=%s
"""
print(user_id, car_id)
db.execute(query, (user_id, car_id))
def get_car(user_id: int, car_id: int):
"""
Get a car from the cars table by its id field.
"""
query = """
SELECT cars.* FROM `users`
INNER JOIN `cars` ON users.id=cars.user_id
WHERE users.id=%s AND cars.id=%s
"""
db.execute(query, (user_id, car_id))
return db.fetchone()
def get_cars(user_id: int):
"""
Get a list of user's cars by the user's token.
"""
query = """
SELECT cars.* FROM `users`
INNER JOIN `cars` ON users.id=cars.user_id
WHERE users.id=%s
"""
db.execute(query, (user_id, ))
return db.fetchall()
def get_by_username_or_email(username_or_email: str, fields='*'):
"""
Fetch user by its username or email address.
Returns user dict or None.
"""
if not username_or_email:
return
field = 'username'
if '@' in username_or_email:
field = 'email'
query = "SELECT {} FROM `users` WHERE {}=%s".format(fields, field)
db.execute(query, (username_or_email,))
return db.fetchone()