def signup(username, email, password): conn.connect() cur = conn.cursor() sql_string = "INSERT INTO users (userName, email, password) VALUES(%s, %s, %s)" cur.execute(sql_string, (username, email, password)) conn.commit() cur.close() conn.close()
def get_by_username(username): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM users WHERE userName= %s" cur.execute(sql_string, (username, )) info = cur.fetchone() conn.close() return info
def get_user(user_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM users WHERE userID= %s" cur.execute(sql_string, (user_id, )) info = cur.fetchone() conn.close() return info[1]
def approve_place(place_id): conn.connect() cur = conn.cursor() sql_string = "UPDATE places SET approved = 1 WHERE placeID = %s " cur.execute(sql_string, (place_id, )) conn.commit() cur.close() conn.close()
def delete_review(place_id): conn.connect() cur = conn.cursor() sql_string = "DELETE FROM review WHERE placeID = %s" cur.execute(sql_string, (place_id, )) conn.commit() cur.close() conn.close()
def block_user(user_id): conn.connect() cur = conn.cursor() sql_string = "UPDATE users SET isBlock = 1 WHERE userID = %s " cur.execute(sql_string, (user_id, )) conn.commit() cur.close() conn.close()
def get_email(email): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM users WHERE email= %s" cur.execute(sql_string, (email, )) info = cur.fetchone() conn.close() return info
def post_review(place_id, user_id, review, rating): conn.connect() cur = conn.cursor() sql_string = "INSERT INTO review (placeID, userID, reviewDate, review,rating) VALUES(%s, %s,now(), %s, %s)" cur.execute(sql_string, (place_id, user_id, review, rating)) conn.commit() cur.close() conn.close()
def get_place_review(place_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM review WHERE placeID =%s ORDER BY reviewDate DESC" cur.execute(sql_string, (place_id, )) info = cur.fetchall() cur.close() conn.close() return info
def get_all_place_user(user_id): conn.connect() cur = conn.cursor() sql_string = "SELECT placeID,image_location FROM places WHERE userID =%s ORDER BY postedDate DESC" cur.execute(sql_string, (user_id, )) info = cur.fetchall() cur.close() conn.close() return info
def get_review_by_user(user_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM review WHERE userID =%s ORDER BY reviewDate DESC" cur.execute(sql_string, (user_id, )) info = cur.fetchall() cur.close() conn.close() return info
def get_to_approve(): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE approved = false ORDER BY postedDate DESC" cur.execute(sql_string, ) info = cur.fetchall() cur.close() conn.close() return info
def get_place_by_id(place_id): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE placeID = %s" cur.execute(sql_string, (place_id, )) info = cur.fetchone() cur.close() conn.close() return info
def get_place_by_name(name): conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE name = %s" cur.execute(sql_string, (name, )) info = cur.fetchone() cur.close() conn.close() return info
def get_place_by_name1(name): name = '%' + name + '%' conn.connect() cur = conn.cursor() sql_string = "SELECT * FROM places WHERE name LIKE %s" cur.execute(sql_string, (name, )) info = cur.fetchall() cur.close() conn.close() return info
def new_place(user_id, name, address, website, phone, image_loc, description): conn.connect() cur = conn.cursor() sql_string = "INSERT INTO places (userID, name, address, website, phone, image_location, description, postedDate) VALUES (%s, %s, %s, %s, %s, %s, %s, now())" cur.execute( sql_string, (user_id, name, address, website, phone, image_loc, description)) conn.commit() cur.close() conn.close()
from datetime import datetime from random import randrange from sqlalchemy import DECIMAL, Column, DateTime, Integer, MetaData, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, sessionmaker from connection import conn Base = declarative_base() meta = MetaData(conn).reflect() dwhConnection = conn.connect() SessionDwh = sessionmaker(bind=dwhConnection) sessionDwh = SessionDwh() class BaseTable(Base): __tablename__ = 'sales_order' entity_id = Column(Integer, autoincrement=True, primary_key=True) amount = Column(DECIMAL(14, 4), nullable=False) created_at = Column(DateTime(), nullable=False) def initSalesTable(): isRun = False if not conn.dialect.has_table(conn, 'sales_order'): Base.metadata.create_all(bind=conn) sessionDwh.commit() isRun = True