HOME/Articles/

mysql example BarTiming (snippet)

Article Outline

Python mysql example 'BarTiming'

Functions in program:

  • def executeQuery(conn , index):

Modules used in program:

  • import mysql.connector

python BarTiming

Python mysql example: BarTiming

import mysql.connector

host = "INSERT_HOST"
username = "ROOT_USER"
database = "MY_DB"
password = "INSER_PASS"

paramArray = [
  # Sunday 12 - 15
  "2019-05-26 00:20:07",
  "2019-05-26 01:20:07",
  "2019-05-26 02:20:07",
  "2019-05-26 03:20:07",
  "2019-05-26 04:20:07",
  "2019-05-26 05:20:07",
  "2019-05-26 06:20:07",
  "2019-05-26 07:20:07",
  "2019-05-26 08:03:07",
  "2019-05-26 09:20:07",
  "2019-05-26 10:20:07",
  "2019-05-26 11:20:07",
  "2019-05-26 12:20:07",
  "2019-05-26 13:20:07",
  "2019-05-26 14:20:07",
  "2019-05-26 15:20:07",
  "2019-05-26 16:20:07",
  "2019-05-26 17:20:07",
  "2019-05-26 18:03:07",
  "2019-05-26 19:20:07",
  "2019-05-26 20:20:07",
  "2019-05-26 21:20:07",
  "2019-05-26 22:20:07",
  "2019-05-26 23:20:07",
  # Monday 06 - 05T
  "2019-05-27 00:20:07",
  "2019-05-27 01:20:07",
  "2019-05-27 02:20:07",
  "2019-05-27 03:20:07",
  "2019-05-27 04:20:07",
  "2019-05-27 05:20:07",
  "2019-05-27 06:20:07",
  "2019-05-27 07:20:07",
  "2019-05-27 08:03:07",
  "2019-05-27 09:20:07",
  "2019-05-27 10:20:07",
  "2019-05-27 11:20:07",
  "2019-05-27 12:20:07",
  "2019-05-27 13:20:07",
  "2019-05-27 14:20:07",
  "2019-05-27 15:20:07",
  "2019-05-27 16:20:07",
  "2019-05-27 17:20:07",
  "2019-05-27 18:03:07",
  "2019-05-27 19:20:07",
  "2019-05-27 20:20:07",
  "2019-05-27 21:20:07",
  "2019-05-27 22:20:07",
  "2019-05-27 23:20:07",
  # Tuesday 06 - 14
  "2019-05-28 00:20:07",
  "2019-05-28 01:20:07",
  "2019-05-28 02:20:07",
  "2019-05-28 03:20:07",
  "2019-05-28 04:20:07",
  "2019-05-28 05:20:07",
  "2019-05-28 06:20:07",
  "2019-05-28 07:20:07",
  "2019-05-28 08:03:07",
  "2019-05-28 09:20:07",
  "2019-05-28 10:20:07",
  "2019-05-28 11:20:07",
  "2019-05-28 12:20:07",
  "2019-05-28 13:20:07",
  "2019-05-28 14:20:07",
  "2019-05-28 15:20:07",
  "2019-05-28 16:20:07",
  "2019-05-28 17:20:07",
  "2019-05-28 18:03:07",
  "2019-05-28 19:20:07",
  "2019-05-28 20:20:07",
  "2019-05-28 21:20:07",
  "2019-05-28 22:20:07",
  "2019-05-28 23:20:07",
  # Wednesday 06 - 12
  "2019-05-29 00:20:07",
  "2019-05-29 01:20:07",
  "2019-05-29 02:20:07",
  "2019-05-29 03:20:07",
  "2019-05-29 04:20:07",
  "2019-05-29 05:20:07",
  "2019-05-29 06:20:07",
  "2019-05-29 07:20:07",
  "2019-05-29 08:03:07",
  "2019-05-29 09:20:07",
  "2019-05-29 10:20:07",
  "2019-05-29 11:20:07",
  "2019-05-29 12:20:07",
  "2019-05-29 13:20:07",
  "2019-05-29 14:20:07",
  "2019-05-29 15:20:07",
  "2019-05-29 16:20:07",
  "2019-05-29 17:20:07",
  "2019-05-29 18:03:07",
  "2019-05-29 19:20:07",
  "2019-05-29 20:20:07",
  "2019-05-29 21:20:07",
  "2019-05-29 22:20:07",
  "2019-05-29 23:20:07",
  # Thursday 06 - 13
  "2019-05-30 00:20:07",
  "2019-05-30 01:20:07",
  "2019-05-30 02:20:07",
  "2019-05-30 03:20:07",
  "2019-05-30 04:20:07",
  "2019-05-30 05:20:07",
  "2019-05-30 06:20:07",
  "2019-05-30 07:20:07",
  "2019-05-30 08:03:07",
  "2019-05-30 09:20:07",
  "2019-05-30 10:20:07",
  "2019-05-30 11:20:07",
  "2019-05-30 12:20:07",
  "2019-05-30 13:20:07",
  "2019-05-30 14:20:07",
  "2019-05-30 15:20:07",
  "2019-05-30 16:20:07",
  "2019-05-30 17:20:07",
  "2019-05-30 18:03:07",
  "2019-05-30 19:20:07",
  "2019-05-30 20:20:07",
  "2019-05-30 21:20:07",
  "2019-05-30 22:20:07",
  "2019-05-30 23:20:07",
  # Firday 06 - 11
  "2019-05-31 00:20:07",
  "2019-05-31 01:20:07",
  "2019-05-31 02:20:07",
  "2019-05-31 03:20:07",
  "2019-05-31 04:20:07",
  "2019-05-31 05:20:07",
  "2019-05-31 06:20:07",
  "2019-05-31 07:20:07",
  "2019-05-31 08:03:07",
  "2019-05-31 09:20:07",
  "2019-05-31 10:20:07",
  "2019-05-31 11:20:07",
  "2019-05-31 12:20:07",
  "2019-05-31 13:20:07",
  "2019-05-31 14:20:07",
  "2019-05-31 15:20:07",
  "2019-05-31 16:20:07",
  "2019-05-31 17:20:07",
  "2019-05-31 18:03:07",
  "2019-05-31 19:20:07",
  "2019-05-31 20:20:07",
  "2019-05-31 21:20:07",
  "2019-05-31 22:20:07",
  "2019-05-31 23:20:07",
  # Saturday 11 - 15
  "2019-06-01 00:20:07",
  "2019-06-01 01:20:07",
  "2019-06-01 02:20:07",
  "2019-06-01 03:20:07",
  "2019-06-01 04:20:07",
  "2019-06-01 05:20:07",
  "2019-06-01 06:20:07",
  "2019-06-01 07:20:07",
  "2019-06-01 08:03:07",
  "2019-06-01 09:20:07",
  "2019-06-01 10:20:07",
  "2019-06-01 11:20:07",
  "2019-06-01 12:20:07",
  "2019-06-01 13:20:07",
  "2019-06-01 14:20:07",
  "2019-06-01 15:20:07",
  "2019-06-01 16:20:07",
  "2019-06-01 17:20:07",
  "2019-06-01 18:03:07",
  "2019-06-01 19:20:07",
  "2019-06-01 20:20:07",
  "2019-06-01 21:20:07",
  "2019-06-01 22:20:07",
  "2019-06-01 23:20:07"
]

paramResultArray = [
  # Sunday 12 - 15
0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,
  # Monday 06 - 05T
0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
  # Tuesday 06 - 14
1,1,1,1,1,0,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,
  # Wednesday 06 - 12
0,0,0,0,0,0,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,
  # Thursday 06 - 13
0,0,0,0,0,0,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,
  # Firday 06 - 11
0,0,0,0,0,0,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0, 
 # Saturday 11 - 15
0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,0,0,0,0,0,0,0,0]

def executeQuery(conn , index):
  param = paramArray[index]
  sql_select_Query = """
select * FROM (select (%s BETWEEN t1.openingTimeDate AND t1.closingTimeDate) as isOpened , 
(CASE WHEN (%s BETWEEN t1.openingTimeDate AND t1.closingTimeDate) = 1 THEN TIMESTAMPDIFF(MINUTE,%s,t1.closingTimeDate) 
ELSE 0 END) as closingInMinutes from 
(select *, 
DAYNAME( DATE_ADD( concat( STR_TO_DATE( CONCAT('2019 2 ',day), '%X %V %W') ,' ', `opening_time`) ,
 INTERVAL `calculated_time` SECOND) ) as closingDay , (CASE WHEN DAYNAME(%s) = day THEN
   concat(Date(%s),' ', `opening_time`) ELSE concat( DATE_SUB( Date(%s) , INTERVAL 1 DAY ),' ', `opening_time`)  END )
     as openingTimeDate, DATE_ADD(CASE WHEN DAYNAME(%s) = day THEN  concat(Date(%s),' ', `opening_time`) 
     ELSE concat( DATE_SUB( Date(%s) , INTERVAL 1 DAY ),' ', `opening_time`) END, INTERVAL `calculated_time` SECOND ) 
     as closingTimeDate from `establishment_timings` where `establishment_id` = 159 ) as t1 where DAYNAME(%s) = t1.day 
     OR DAYNAME(%s) = t1.closingDay) as t2 where t2.isOpened = 1
   """
  # sql_select_Query = "SELECT * FROM `establishment_timings`"
  cursor = conn.cursor()
  cursor.execute(sql_select_Query , (param,param,param,param,param,param,param,param,param,param,param))
  records = cursor.fetchall()
  cursor.close()
  if len(records) == 0:
    print(str(index)+" -> "+param+" isOpened 0 -- expected "+str(paramResultArray[index]))
  for row in records:
    print(str(index)+" -> "+param+" isOpened "+str(row[0])+ " -- expected " +str(paramResultArray[index]))

  index = index+1
  if index == len(paramArray):
    print("break")
    return
  else:
    executeQuery(conn , index)



try:
  print("Start Connection")
  mySQLconnection = mysql.connector.connect(
  host= host,
  user= username,
  passwd= password,
  database = database
)
  print("Connecting Complete")
  executeQuery(mySQLconnection , 0)

except Exception as e:
  print(("Error MySQL", e))
finally:
  #closing database connection.
  if(mySQLconnection .is_connected()):
    mySQLconnection.close()
    print("MySQL connection is closed")


mockData = """
INSERT INTO `establishment_timings` (`id`, `establishment_id`, `day`, `opening_time`, `closed_time`, `calculated_time`, `status`, `created_at`, `updated_at`)
VALUES
    (916, 159, 'Monday', '06:00:00', '05:00:00', '82800', 'open', '2019-05-23 09:35:13', '2019-05-29 07:07:12'),
    (917, 159, 'Tuesday', '06:00:00', '14:00:00', '28800', 'closed', '2019-05-23 09:35:13', '2019-05-29 07:07:12'),
    (918, 159, 'Wednesday', '06:00:00', '12:00:00', '21600', 'open', '2019-05-23 09:35:13', '2019-05-29 07:07:12'),
    (919, 159, 'Thursday', '06:00:00', '13:00:00', '25200', 'open', '2019-05-23 09:35:13', '2019-05-29 07:07:12'),
    (920, 159, 'Friday', '06:00:00', '11:00:00', '18000', 'open', '2019-05-23 09:35:13', '2019-05-29 07:07:12'),
    (921, 159, 'Saturday', '11:00:00', '15:00:00', '14400', 'closed', '2019-05-23 09:35:13', '2019-05-29 07:07:12'),
    (922, 159, 'Sunday', '12:00:00', '15:00:00', '10800', 'open', '2019-05-23 09:35:13', '2019-05-29 07:07:12');
"""