def insertRestaurantCuisines(restaurant_id, cuisines):
try:
db_config = read_db_config()
db_config['database'] = "warehouse"
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
for cuisine in cuisines:
cuisine = normalizeName(cuisine)
cuisine_id = getCuisineIdFromName(cuisine)
if not existRestaurantCuisine(restaurant_id, cuisine_id):
query = "INSERT INTO cuisine_restaurant (restaurant_id,cuisine_id) " \
"VALUES (%s,%s)"
args = [str(restaurant_id), str(cuisine_id)]
cursor.execute(query, args)
else:
print "Cuisine exists"
conn.commit()
except Error as error:
print "Error: insertRestaurantCuisines"
print error
finally:
cursor.close()
conn.close()
def create_database():
""" Connect to MySQL database """
global connection
global __unixSocket
db_config = read_db_config()
try:
connection = pymysql.connect(host=db_config["host"],
port=db_config["port"],
user=db_config["user"],
password=db_config["password"],
database="mysql",
unix_socket=__unixSocket)
if connection.open:
cursor = connection.cursor()
cursor.execute("DROP DATABASE IF EXISTS " + db_config["database"])
cursor.execute("CREATE DATABASE " + db_config["database"])
logging.info("Creating database " + db_config["database"] + "...")
__create_tables(db_config)
__create_procedures(db_config)
connection.close()
except pymysql.OperationalError as error:
logging.critical('Database creation failed: ' + str(error))
def connect(bDate, eDate):
db_config = read_db_config()
try:
print("Connecting to MySQL database..")
conn = MySQLConnection(**db_config)
if conn.is_connected():
print("Connection Established.")
else:
print("Connected Failed")
cursor - conn.cursor()
cursor.execute(
"SELECT * FROM products p, trans t, trans_line t1 WHERE p.prod_num = t1.prod_num AND t1.trans_id = t.trans_id AND t.trans_date BETWEEN %s AND %s ORDER BY t.trans_date",
(bDate, eDate))
contents = list(cursor.fetchall())
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print("Connection Close")
if contents is not None:
print("Here's the data")
return contents
def connect():
"""
Connect to SQL DB with connect file.
"""
db_config = read_db_config()
try:
print("Connecting to MySQL Database...")
conn = MySQLConnection(**db_config)
if conn.is_connected():
print("Connection Successful.")
else:
print("Connection Failed.")
cursor = conn.cursor()
cursor.execute("SELECT * FROM Trans")
rows = cursor.fetchall()
print("Total Row(s)")
for row in rows:
print(row)
#row = cursor.fetchone() #select first row
#while row is not None:
# print(row)
# row = cursor.fetchone() #select next row
except Error as error:
print(error)
finally:
conn.close()
print("Connection Closed.")
def connect(bDate, eDate):
"""
Connect to SQL Database with config file
"""
db_config = read_db_config()
try:
print("Connecting to MySQL database..")
conn = MySQLConnection(**db_config)
if conn.is_connected():
print("Connection Established!")
else:
print("Connection Failed")
# Select data using bDate and eDate as delimiters
cursor = conn.cursor()
cursor.execute("SELECT t.trans_id, DATE_FORMAT(t.trans_date, \"%Y%m%d%h%i\") as trans_date, SUBSTRING(t.card_num, 9,14) AS card_num, tl.qty, tl.amt, p.prod_desc, t.total FROM products p, trans t, trans_line tl WHERE p.prod_num = tl.prod_num AND tl.trans_id = t.trans_id AND t.trans_date BETWEEN %s AND %s ORDER BY t.trans_date", (bDate, eDate))
# Store it in a list
contents = list(cursor.fetchall())
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print("Connection Close")
if contents is not None:
return contents
def convDate(beg_date, end_date):
"""
Takes beginning date and end date as parameters to query the database.
Converts the inputs to proper format YYYY-MM-DD hh:mm
Args:
beg_date: date in YYYYMMDD format
end_date: date in YYYYMMDD format
Returns:
"""
db = read_db_config()
if(len(str(beg_date)) != 8 or len(str(end_date)) != 8):
print("Improper date format. Please use the format <YYYYMMDD>")
#Exit code 255 in Bash
exit(-1)
#Convert to list to add elements
convB_date = list(str(beg_date))
convE_date = list(str(end_date))
#Add dashes, whitespace, and time
#Year
convB_date.insert(4, "-")
convE_date.insert(4, "-")
#Month
convB_date.insert(7, "-")
convE_date.insert(7, "-")
#Time (whitespace)
convB_date.insert(11, " ")
convE_date.insert(11, " ")
#Time (value)
convB_date.insert(12, "00:00")
convE_date.insert(12, "23:59")
#Assign to new variables for database query
bDate = "".join(convB_date)
eDate = "".join(convE_date)
#Connect to database and retrieve contents for display
contents = connect(bDate, eDate)
if not contents:
print("No data for given date range")
#Exit code 254 in Bash
exit(-2)
#The fun part, making the fixed length record.
#Should always be 47 characters long
#Index, Name, Size, Type
#[0], Transaction ID, 5, int
#[1], Transaction date, 12, int
#[2], Card number, 6, int
#[3], Prod qty, 2, int
#[4], Prod amt, 6, int
#[5], Prod desc, 10, str
#[6], Prod total, 6, int
for entry in contents:
print('{0:05d}{1:012d}{2:06d}{3:02d}{4:06d}{5:10}{6:06d}'.format(int(entry[0]), int(entry[1]), int(entry[2]), int(entry[3]), int(entry[4]), entry[5], int(entry[6])))
def connect():
""" Connect to MySQL database """
global connection
global __unixSocket
db_config = read_db_config()
try:
logging.info('Connecting to MySQL database...')
connection = pymysql.connect(unix_socket=__unixSocket, **db_config)
except pymysql.Error as error:
logging.critical("Connection failed:" + str(error))
def connect():
""" Connect to Mysql databse """
db_config = read_db_config()
try:
conxn = MySQLConnection(**db_config)
if conxn.is_connected():
logger.info('Connected to database')
except Error as e:
logger.error(e)
# finally:
# conxn.close()
# logger.info('Connection Closed')
return conxn
def connect():
db_config = read_db_config()
try:
print('Connecting to MySQL database...')
conn = mysql.connector.connect(**db_config)
if conn.is_connected():
print('connection established.')
return conn
else:
print('connection failed.')
except Error as e:
print(e)
def all_comps():
"""
get all competitions and update their data
"""
db_dict = dbconfig.read_db_config()
db = MySQLdb.connect(host=db_dict['host'],
user=db_dict['user'],
passwd=db_dict['password'],
db=db_dict['database'])
cursor = db.cursor()
cursor.execute("select * from Competitions order by competition desc;")
competitions = cursor.fetchall()
competitions = [c[0] for c in competitions if 'FA' in c[0]]
for c in competitions:
update_matches(c, cursor, db)
def db_conn():
db_config = read_db_config()
conn = None
try:
conn = pyodbc.connect(**db_config)
cursor = conn.cursor()
try:
sql_command = """SELECT @@Version"""
cursor.execute(sql_command)
for row in cursor:
print(f'row={row}')
except Exception as e:
print("Table not available", e)
except Exception as e:
print("error", e)
return conn
def add_user(uid, username):
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = f"INSERT INTO {table_name} (uid, username) " \
f"VALUES ('{uid}', '{username}')"
try:
cursor.execute(query)
conn.commit()
print(f'[DataBase] --> (add_user)')
except Error as e:
print(f'[DataBase] ERR: {e} (add_user)')
pass
finally:
cursor.close()
conn.close()
def findRestaurantById(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM restaurants where restaurant_id = " + id)
row = cursor.fetchone()
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def ExecuteSP_Params(self,stored_proc,params):
try:
db_config = read_db_config()
cnx = pymysql.connect(host=db_config['host'], port=int(db_config['port']),
user=db_config['user'], passwd=db_config['password'], db=db_config['database'])
curr = cnx.cursor()
curr.callproc(stored_proc,params)
cnx.commit()
for i in range(0,len(curr._rows)):
print(curr._rows[i])
return curr._rows
except Exception as e:
print("Error at connection")
if(cnx == None):
return None
finally:
cnx.close()
def findCityById(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM city WHERE city_id = " + id)
row = cursor.fetchone()
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def updateJobStatus(uid, status):
query = "UPDATE message_status set processing_status=" + str(
status) + " where id=" + str(uid)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def insert_temperature(temperature):
query = "INSERT INTO temperature(id, field1, field2) " \
"VALUES(%s,%s,%s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, temperature)
conn.commit()
except Error as e:
print('Error: ', e)
finally:
cursor.close()
conn.close()
def getCityMappingByName(db_name,city):
try:
db_config = read_db_config()
db_config['database'] = db_name
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT word FROM city_mapping where mapping = '"+city+"'")
row = cursor.fetchall()
# print row
return row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def get_attractions(uid):
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = f'SELECT attractions ' \
f'FROM {table_name} ' \
f'WHERE uid = {uid} ' \
f'LIMIT 1'
try:
cursor.execute(query)
rows = cursor.fetchone()
print(f'[DataBase] --> {rows} (get_language)')
return rows
except Error as e:
print(f'[DataBase] ERR: {e} (get_language)')
finally:
cursor.close()
conn.close()
def fileError(uid, error, errorDesc):
query = "UPDATE message_status set error='1' and errorDesc=" + errorDesc + " where id=" + str(
uid)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def updateJobResults(uid, products):
query = "UPDATE message_status set response_obj = '" + products + "' where id=" + str(
uid)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def connect():
"""
Connect to SQL DB with connect file.
Args:
None
Returns:
SQL Connection
"""
db_config = read_db_config()
try:
print("Connecting to MySQL Database...")
conn = MySQLConnection(**db_config)
if conn.is_connected():
print("Connection Successful.")
else:
print("Connection Failed.")
except Error as error:
print(error)
return conn
def get_match_ids(competition, year):
db_dict = dbconfig.read_db_config()
db = MySQLdb.connect(host=db_dict['host'],
user=db_dict['user'],
passwd=db_dict['password'],
db=db_dict['database'])
cursor = db.cursor()
query = """
select distinct match_id
from match_dictionary
where league in %s
and season = %s
and match_id not in (select distinct id from match_info);
"""
cursor.execute(query % (competition, year))
match_ids = np.asarray(cursor.fetchall())
match_ids = np.reshape(match_ids, len(match_ids), 1)
return match_ids
def getAllCities(db_name):
try:
db_config = read_db_config()
db_config['database'] = db_name
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT city_id,name FROM city")
row = cursor.fetchall()
# print row
return row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def update_attractions(uid, attractions):
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = f'UPDATE {table_name} ' \
f'SET attractions = "{attractions}" ' \
f'WHERE uid = "{uid}" ' \
f'LIMIT 1'
try:
cursor.execute(query)
conn.commit()
print(f'[DataBase] --> (update_attractions)')
except Error as e:
print(f'[DataBase] ERR: {e} (update_attractions)')
pass
finally:
cursor.close()
conn.close()
def findAllCuisines():
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT cuisine_name FROM cuisine")
cuisines = []
rows = cursor.fetchall()
for row in rows:
cuisines.append(row[0])
return cuisines
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findRestaurantsByLocalityId(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM locality_restaurant where locality_id = " + id)
rows = cursor.fetchall()
for row in rows:
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findAllCuisines():
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if query:
query = query + "%"
cursor.execute(
"SELECT DISTINCT(cuisine_name) FROM cuisine_restaurant WHERE 1")
rows = cursor.fetchall()
except Error as error:
print error
finally:
cursor.close()
conn.close()
return rows
def connect():
db_config = read_db_config()
try:
print('Connectiong to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def findRestaurantByName(query):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if query:
query = "%" + query + "%"
cursor.execute("SELECT name FROM restaurants WHERE name LIKE '%s'" % query)
rows = cursor.fetchall()
if rows!=None:
return rows
return []
except Error as error:
print error
finally:
cursor.close()
conn.close()
def insertManyRestaurants(restaurants_info): query = "INSERT INTO restaurants(restaurant_id, name, address, rating_aggregate, delivery_fee, delivery_time, takeaway_time, minimum_order, payment_methods, voucher, data_resource) " \ "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.executemany(query, restaurants_info) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "RESTAURANT DATA INSERTED!!!"
def connect(): db_config = read_db_config() con = MySQLConnection(**db_config) cursor = con.cursor() return con, cursor
