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